Intervallo di tempo ricorrente in secondi in cui eseguire il flusso di lavoro.
SyncTime
string
$Config/SyncTime$
Ora di sincronizzazione
Ora di sincronizzazione specificata usando il formato a 24 ore. Può essere omessa.
TimeoutSeconds
int
$Config/TimeoutSeconds$
Timeout (secondi)
Specifica il tempo di esecuzione consentito per il flusso di lavoro prima che venga chiuso e contrassegnato come non riuscito.
ScriptDelayMsec
int
$Config/ScriptDelayMsec$
Ritardo script (millisecondi)
Questo parametro imposta il ritardo tra le query T-SQL consecutive eseguite dal flusso di lavoro. Favorisce la riduzione del footprint generato dal flusso di lavoro in presenza di un numero elevato di oggetti di destinazione. Prima di cambiare questo parametro, consultare il Supporto tecnico Microsoft.
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
try
{
$SqlConnection.ConnectionString = $connectionString
$SqlConnection.Open()
return $SqlConnection
}
catch {
if ($SqlConnection -ne $null)
{
if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {
$SqlConnection.Close()
}
$SqlConnection.Dispose()
}
}
return $null
}
# 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 ConnectionString: $connectionString TcpPort: $tcpPort" + [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 $InstanceName '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');"
$queryMemory = "USE ""{0}"";
select
SUM((convert(float, memory_used_by_table_kb))/1024) as 'Tables_Used_Memory_MB',
SUM((convert(float, (memory_allocated_for_table_kb - memory_used_by_table_kb)))/1024) as 'Tables_Unused_Memory_MB',
SUM((convert(float, memory_used_by_indexes_kb))/1024) as 'Indexes_Used_Memory_MB',
SUM((convert(float, (memory_allocated_for_indexes_kb - memory_used_by_indexes_kb)))/1024) as 'Indexes_Unused_Memory_MB'
from sys.dm_db_xtp_table_memory_stats where object_id > 0;"
Home
ITA 
Show References: