function With($object, [ScriptBlock] $scriptBlock) {
try {
&$scriptBlock
} catch {
if (-not ($object -is [System.Data.SqlClient.SqlConnection])) { throw }
$exception = $_.Exception.GetBaseException()
if (-not ($exception -is [System.Data.SqlClient.SqlException])) { throw }
# Hide some errors
if ($object.Database -eq 'master') {
# 40613: 'Database is not currently available'
# For 'master' this error will be covered by Server State monitor.
# For user databases - seems like it's not always reflected in 'state' column of sys.databases, so error should be propagated.
if ($exception.Number -eq 40613) { return }
} else {
# 4060: 'Login failed' could mean unexisting database.
# If this is the case, then we shouldn't do anything, but wait for next discovery.
# But if database is here, then, most likely, wrong credentials provided and error should be propagated.
if ($exception.Number -eq 4060 -and -not (IsDatabaseExists $object.DataSource $object.Database $Username $Password)) { return }
}
throw
} finally {
foreach ($disposeCandidate in $object) {
if ($disposeCandidate -is [IDisposable]) { $disposeCandidate.Dispose() }
}
}
}
function IsDatabaseExists($serverName, $testDatabaseName, $username, $password) {
# Assuming that database should be there if anything goes wrong
if (-not ($serverName -and $testDatabaseName -and $username -and $password)) { return $true }
With ($testConnection = Create-SqlConnection $serverName 'master' $username $password) {
try {
With ($testCommand = $testConnection.CreateCommand()) {
$testCommand.CommandText = 'SELECT database_id FROM sys.databases WHERE name = @name'
$null = $testCommand.Parameters.Add('@name', $testDatabaseName)
$testConnection.Open()
function IsTransientError($exception) {
# Since this list may change in the feature - for now only consider 'Login failed' as non-transient.
# As soon as all SCOM agents will be guaranteed to run on .NET 4.0 - use Microsoft Azure CAT library with ReliableSqlConnection.
$exception -is [System.Data.SqlClient.SqlException] -and (4060, 18456) -notcontains $exception.Number
}
function WithRetry([ScriptBlock] $objectInitialization, [ScriptBlock] $_withRetryScriptBlock) {
# Seems like it's not possible to make it "safe" with closures,
# because then you will lose $objectInitialization execution results in parent scope
Retry 2 {
With (Invoke-Expression "($objectInitialization)") $_withRetryScriptBlock
}
}
Home
Show References: