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.
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
}