O intervalo de tempo recorrente em segundos no qual executa-se o fluxo de trabalho.
SyncTime
string
$Config/SyncTime$
Hora da Sincronização
A hora da sincronização especificada usando um formato de 24 horas. Pode ser omitido.
TimeoutSeconds
int
$Config/TimeoutSeconds$
Tempo Limite (segundos)
Especifica o tempo que o fluxo de trabalho pode funcionar antes de ser fechado e marcado como com falha.
ScriptDelayMsec
int
$Config/ScriptDelayMsec$
Atraso do Script (milissegundos)
Este parâmetro define o atraso entre as consultas T-SQL consecutivas executadas pelo fluxo de trabalho. Isso pode ajudar a reduzir a pegada gerada pelo fluxo de trabalho em caso de um grande número de objetos de destino. Consulte o Suporte da Microsoft antes de alterar esse parâmetro.
function Main(){
ModuleAction $computerName $sqlInstanceName $connectionString $tcpPort $delay $serviceName
}
function ModuleAction
{
param(
$computerName,
$sqlInstanceName,
$connectionString,
$tcpPort,
$delay,
$serviceName
)
#error messege has to be logged duaring execution
$log_error_msg = $false
$error_msg = ""
if($DEBUG_MODE -eq 1)
{
$DEBUG_MSG = "ComputerName: $computerName InstanceName: $sqlInstanceName"
$DEBUG_MSG += " ConnectionString: $connectionString tcpPort: $tcpPort delay: $delay" + [Environment]::NewLine
}
# Prepare MOM API and property bag object
$api = New-Object -comObject "MOM.ScriptAPI"
$SqlConnection = $null
#if service is not in running state when exit without any error
$state = GetServiceState $computerName $serviceName $sqlInstanceName
if(($state -ne "Running") -and ($state -ne "Unknown"))
{
return
}
try
{
$SqlConnection = SmartConnect $connectionString "master" $computerName $sqlInstanceName $DEBUG_SA $DEBUG_PWD
#if connection is null when exit the script without error
if($SqlConnection -eq $null )
{
PrintDebug $api "The server was not found or was not accessible."
return
}
$queryDB = "USE ""{0}"";
SELECT name as dbName, database_id as dbID
FROM sys.databases
WHERE source_database_id IS NULL AND state = 0 AND is_read_only = 0 AND name not in ('master', 'model', 'msdb', 'tempdb');"
$queryFX = "USE ""{0}"";
SELECT type, filegroup_guid FROM sys.filegroups WHERE type = 'FX';"
-- get the total deleted row counts by looking at active delta files
select @deleted_row_count = SUM (deleted_row_count)
from sys.dm_db_xtp_checkpoint_files
where state = 2 and file_type = 1
-- get total inserted row count by looking at active data files
select @inserted_row_count = SUM (inserted_row_count)
from sys.dm_db_xtp_checkpoint_files
where state = 2 and file_type = 0
-- get the effective % of active rows after accounting for the deleted rows
select @effective_row_percentage = CASE WHEN @inserted_row_count > 0 THEN (1 - convert (float, @deleted_row_count)/@inserted_row_count) Else 0 End
-- Compute the effective usage fill factor for the storage.
-- This should be >= 50% otherwise it is an indication that auto-merge is not
-- keeping up
select
@storage_usage_fill_factor = str (convert (varchar(100), ((SUM (file_size_used_in_bytes)*@effective_row_percentage)/SUM (file_size_in_bytes)) *100 ),5, 2)
from sys.dm_db_xtp_checkpoint_files
where state = 2 and file_type = 0
select
@storage_usage_fill_factor as 'storage_usage_fill_factor',
@effective_row_percentage as 'effective_row_percentage',
@inserted_row_count as 'inserted_row_count',
@deleted_row_count as 'deleted_row_count'"
function SqlQueryScalar($SqlConnection, $query) {
$sqlAdapter = New-Object 'System.Data.SqlClient.SqlDataAdapter' ($query, $SqlConnection)
$dataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($dataSet) | Out-Null
if(($dataSet.Tables.Count -eq 0) -or ($DataSet.Tables[0] -eq $null))
{
throw 'Can not query data from {0} database. Please check read permissions for this db.' -f $SqlConnection.Database
}
return ($dataSet.Tables[0]|Select -first 1)[0]
}
#''' Returns -1: If DB is not in AlwaysOn
#''' Returns 0: If DB is in AlwaysOn and replica allow connections is NO
#''' Returns 1: If DB is in AlwaysOn and replica allow connections is YES
function AlwaysOnReplicaAllowConnections([System.Data.SqlClient.SqlConnection]$SqlConnection, $DatabaseID)
{
$query = " SELECT columns.id, CASE WHEN OBJECT_ID('sys.availability_replicas') IS NOT NULL THEN 1 ELSE 0 END AS HasAlwaysOn " +
" FROM master.sys.syscolumns columns where name = 'replica_id' and id = OBJECT_ID('sys.databases')"
$dr = SqlConnQueryTables $SqlConnection $query | Select -First 1
$query = "SELECT d.name, d.database_id, drs.is_primary_replica AS db_is_primary_replica
, CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica
, CASE WHEN drs.is_primary_replica = 1 THEN ar.primary_role_allow_connections ELSE ar.secondary_role_allow_connections END AS role_allow_connections
, CASE WHEN drs.is_suspended = 0 THEN -1 ELSE suspend_reason END AS db_suspended_state
FROM sys.databases as d
JOIN sys.dm_hadr_database_replica_states drs ON drs.database_id = d.database_id
JOIN sys.availability_replicas ar on d.replica_id = ar.replica_id
WHERE drs.is_local = 1 AND d.database_id = @p1"
$rdr = SqlQueryTables $SqlConnection $query $DatabaseID | Select -First 1
#-------------------------------------------------------------------------------
#The function returns service or "Unknown" state
#Input:
# server - compute name
# service - system service name
# InstanceName - sql server instance name
#Output:
# service state or "Unknown" state
function GetServiceState($server, $service, $InstanceName)
{
try {
if ($service -eq "MSSQL") {
$service = "MSSQL`${0}" -f $InstanceName
}
$namespace = "root/cimv2"
$obje = Get-WmiObject -Property 'Name,State' -Namespace $namespace -ComputerName $server -Class "win32_service" -Filter "Name = '$service'" -ErrorAction SilentlyContinue
if ($obje -ne $null) {
return $obje.State
}
}
catch {
}
return "Unknown"
}
function GetSQLServiceName($InstanceName)
{
if ($InstanceName -eq "MSSQLSERVER")
{
return "MSSQLSERVER"
}
return 'MSSQL$' + $InstanceName
}