The recurring interval of time in seconds in which to run the workflow.
SyncTime
string
$Config/SyncTime$
Synchronization Time
The synchronization time specified by using a 24-hour format. May be omitted.
TimeoutSeconds
int
$Config/TimeoutSeconds$
Timeout (seconds)
Specifies the time the workflow is allowed to run before being closed and marked as failed.
ScriptDelayMsec
int
$Config/ScriptDelayMsec$
Script Delay (milliseconds)
This parameter sets the delay between consecutive T-SQL queries executed by the workflow. This may help to reduce the footprint generated by the workflow in case of large number of target objects. Please advise with Microsoft Support before changing this parameter.
# 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;"
Home
ENU 
Show References: