Das periodische Intervall in Sekunden, in dem der Workflow ausgeführt werden soll.
SyncTime
string
$Config/SyncTime$
Synchronisierungszeit
Die in einem 24-Stunden-Format angegebene Synchronisierungszeit. Kann ausgelassen werden.
TimeoutSeconds
int
$Config/TimeoutSeconds$
Timeout (Sekunden)
Gibt die Zeit an, die der Workflow ausgeführt werden darf, bevor er geschlossen und als fehlgeschlagen markiert wird.
ScriptDelayMsec
int
$Config/ScriptDelayMsec$
Skriptverzögerung (Millisekunden)
Über diesen Parameter wird die Verzögerung zwischen aufeinanderfolgenden T-SQL-Abfragen festgelegt, die durch den Workflow ausgeführt werden. Dadurch lässt sich die Ressourcenbeanspruchung durch diesen Workflow bei einer großen Anzahl an Zielobjekten reduzieren. Halten Sie Rücksprache mit dem Microsoft-Support, bevor Sie diesen Parameter ändern.
# Parameters should be provided after $SqlConnection and $query
# Parameters should be named in the query text as $p1...$pN
function SqlQueryTables($SqlConnection, $query) {
#if an error takes place during execution when the function throws exception
try {
$safeName = $dbName -replace """", """"""
$SqlCmd.CommandText = $query -f $safeName
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)|out-null
$res = $DataSet.Tables
$SqlAdapter.Dispose()
$SqlCmd.Dispose()
}
catch {
if( $SqlAdapter -ne $null)
{
$SqlAdapter.Dispose()
}
if( $SqlCmd -ne $null)
{
$SqlCmd.Dispose()
}
throw $_.Exception
}
return $res
}
#''' 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($SqlConnection, $DatabaseID)
{
$isAlwaysOn = -1
$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')"
$res = SqlConnQueryTables $SqlConnection $query
$res | foreach {
$hasAlwaysOn = $_.HasAlwaysOn
if ($hasAlwaysOn -eq 1) {
$query = " SELECT d.name, d.database_id,
CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica,
ar.secondary_role_allow_connections
FROM master.sys.databases d
JOIN master.sys.availability_replicas ar on d.replica_id = ar.replica_id
JOIN master.sys.servers s ON s.name = ar.replica_server_name AND s.server_id = 0 /*local server*/
WHERE d.database_id = @p1 "
$resAv = SqlQueryTables $SqlConnection $query $DatabaseID
$resAv | foreach {
$is_replica = $_.is_replica
if( $is_replica -eq 1) {
$isAlwaysOn = 1
$secondary_role_allow_connections = $_.secondary_role_allow_connections
if ($secondary_role_allow_connections -le 1) {
$isAlwaysOn = 0
}
return $isAlwaysOn
}
}
}
}
return $isAlwaysOn
}
#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 -Namespace $namespace -ComputerName $server -Class "win32_service" -ErrorAction SilentlyContinue | where {$_.name -like $service }
if ($obje -ne $null) {
return $obje.State
}
}
catch {
}
return "Unknown"
}
function Main
{
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 {
#check if connection string valid
$SqlConnection = GetValidatedConnection $computerName $sqlInstanceName 'master' $connectionString $tcpPort $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');"
$queryFiles = "USE ""{0}"";
select
Sum(CASE WHEN chf.state != 2 THEN 1 Else 0 End) NonActivePairs,
Sum(CASE WHEN chf.state = 2 THEN 1 Else 0 End) ActivePairs
from sys.dm_db_xtp_checkpoint_files as chf
where chf.file_type = 0;"