Invoke-Sqlcmd

PowerShell Auto Add Database to SQL AllwaysOn Availability Group

This script adds all user databases which are not yet replicated to a specific SQL Server AlwaysOn Availability Group. After that a check procedure is started to check if replication state for all databases exists and are healthy. Alerts can be send to an e-mail address or to the Windows Eventlog (or both).

This script uses custum functions fnSendMailfnWriteEventLog to send alert messages and fnSQLCmd for handeling SQL statements.

# Import DBA modules (functions)...
Import-Module 'AdminFunctions.psm1'

# Global config settings...
[string]$cAOGroupName = "AO Group Name (LISTENER)"
[string]$cAOListenerName = "LISTENER\INSTANCE"
[string]$cDatabaseBackupLocation = "\\servershare$\_AlwaysOnTempDir"
[string]$sSMTPServer1 = "smtp.example.com"
[string]$sToAddress1 = "admin@example.com"

# Functions en Procedures...
function fnAddDatabase2AOGroup()
{
 param (
   [string]$sAOGroupName,
   [string]$sDatabaseName,
   [string]$sPrimaryServerName,
   [string]$sSecondaryServerName,
   [string]$sDatabaseBackupLocation
 )

 # Create a timestamp...
 [string]$sDate = Get-Date -UFormat %Y%m%d%H%M

 # Concat strings to create path...
 [string]$sDatabaseBackupFile = $sDatabaseBackupLocation + "\" + $sDatabaseName + "_" + $sDate + ".bak"
 [string]$sDatabaseLogFile = $sDatabaseBackupLocation + "\" + $sDatabaseName + "_" + $sDate + ".trn"
 [string]$sAOPrimaryPath = "SQLSERVER:\SQL\" + $sPrimaryServerName + "\AvailabilityGroups\" + $sAOGroupName
 [string]$sAOSecondaryPath = "SQLSERVER:\SQL\" + $sSecondaryServerName + "\AvailabilityGroups\" + $sAOGroupName

 # Backup Data + Log on primary...
 Backup-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseBackupFile -ServerInstance $($sPrimaryServerName)
 Backup-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseLogFile -ServerInstance $($sPrimaryServerName) -BackupAction 'Log'

 # Restore Data + Log on secondary...
 Restore-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseBackupFile -ServerInstance $($sSecondaryServerName) -NoRecovery
 Restore-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseLogFile -ServerInstance $($sSecondaryServerName) -RestoreAction 'Log' -NoRecovery

 # Add Database to Availability Group...
 Add-SqlAvailabilityDatabase -Path $sAOPrimaryPath -Database $($sDatabaseName)
 Add-SqlAvailabilityDatabase -Path $sAOSecondaryPath -Database $($sDatabaseName)
}

function fnSetAllDatabases2FullRecovery()
{
 param (
         [string]$sDBServerName
       )
  
  [string]$sQuery9 = "-- Declarations and temptables... `
                      DECLARE @iNumRecs int, @iRecNum int = 1, @vCommand nvarchar(max) `
                      CREATE TABLE #tCommands (ID int IDENTITY(1,1), Command nvarchar(max)) `
                      -- Insert Commands into temptable... `
                      INSERT INTO #tCommands (Command) `
                      SELECT N'ALTER DATABASE [' + name + '] SET RECOVERY FULL WITH NO_WAIT' `
                      FROM [master].[sys].[databases] `
                      WHERE recovery_model_desc = 'SIMPLE' AND name NOT in ('master', 'msdb', 'tempdb') `
                      -- Execute commands from temptable... `
                      SELECT @iNumRecs = COUNT(*) FROM #tCommands `
                      WHILE @iRecNum <= @iNumRecs `
                      BEGIN `
                        SELECT @vCommand = Command FROM #tCommands `
                        WHERE ID = @iRecNum `
                        -- Execute command and increase record number... `
                        -- EXECUTE(@vCommand) --Alternative, Not preferred... `
                        EXECUTE sys.sp_executesql @stmt = @vCommand `
                        SET @iRecNum += 1 `
                      END `
                      -- Cleanup... ` 
                      DROP TABLE #tCommands"
  fnSQLCmd -sInstance $($sDBServerName) -sQuery $sQuery9 -sDatabase "master" 
}


### Start main script... ###

# First we set all databases to full recovery...
fnSetAllDatabases2FullRecovery -sDBServerName $($cAOListenerName)

# Retrieve AlwaysOn information from listener...
[string]$sQuery0 = "-- Retrieve AllwaysOn information and status... `
                    SELECT nim.ag_name, arcs.replica_id, arcs.replica_server_name, arcs.join_state, `
                    arcs.join_state_desc, ars.is_local, ars.role, ars.role_desc, `
                    ars.operational_state, ars.operational_state_desc, ars.recovery_health, `
                    ars.recovery_health_desc, ars.synchronization_health, ars.synchronization_health_desc `
                    FROM [master].[sys].dm_hadr_availability_replica_states ars `
                    INNER JOIN [master].[sys].dm_hadr_availability_replica_cluster_states arcs ON ars.replica_id = arcs.replica_id `
                    INNER JOIN [master].[sys].dm_hadr_name_id_map nim ON arcs.group_id = nim.ag_id"
if ($oResult0) { Remove-Variable -name oResult0 }
# Force in object so we can always count...
[Object[]]($oResult0) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery0 -sDatabase "master"

# Retrieve primary and secondary nodes for group...
foreach($rows0 in $oResult0) {
  if (($rows0["ag_name"] -eq $($cAOGroupName)) -and ($rows0["role"] -eq "1")) { 
    [string]$cPrimaryServerName = $rows0["replica_server_name"] 
    [string]$cPrimaryReplicaID = $rows0["replica_id"]
  }
  if (($rows0["ag_name"] -eq $($cAOGroupName)) -and ($rows0["role"] -eq "2")) { [string]$cSecondaryServerName = $rows0["replica_server_name"] }
}

# Retrieve database names...
[string]$sQuery1 = "-- Return all unreplicated databases without `
                    SELECT name FROM [master].[sys].[databases] `
                    WHERE name NOT IN ('master','tempdb','model','msdb','SSISDB') `
                    AND replica_id IS NULL"
if ($oResult1) { Remove-Variable -name oResult1 }
[Object[]]($oResult1) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery1 -sDatabase "master" 

# Process the rows returned...
foreach($rows1 in $oResult1) {
  if (($cPrimaryServerName) -and ($cPrimaryServerName)) {
  fnAddDatabase2AOGroup -sAOGroupName $cAOGroupName -sDatabaseName ($rows1["name"]) -sPrimaryServerName $cPrimaryServerName -sSecondaryServerName $cSecondaryServerName -sDatabaseBackupLocation $cDatabaseBackupLocation
  } 
}

# Check database replication state...
Start-Sleep -m 10000
[string]$sQuery2 = "-- Check database replication state... `
                    SELECT db.name, db.replica_id, synchronization_state, synchronization_health_desc `
                    FROM [master].[sys].[databases] db `
                    LEFT OUTER JOIN [master].[sys].[dm_hadr_database_replica_states] dbrs ON db.database_id = dbrs.database_id `
                    WHERE name NOT IN ('master','tempdb','model','msdb', 'SSISDB') `
                    AND (db.replica_id IS NULL OR synchronization_state <> 2)"
# Force in object so we can always count...
if ($oResult2) { Remove-Variable -name oResult2 }
[Object[]]($oResult2) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery2 -sDatabase "master"

# Send report if sync state <> 2 or database not replicated... 
if ($oResult2.Count -gt 0) {
  # Build HTML body...
  [string]$sHTML_Body1 = ""
  [string]$sHTML_Body1 += $oResult2 | ConvertTo-Html name, replica_id, synchronization_health_desc -Fragment
  [string]$sHTML_Body1 += ""
 
  # Build TEXT body...
  [string]$sResult2 = $oResult2 | Out-String
  [string]$sTEXT_Body1 = $sResult2

  # Send messages...
  [string]$sSubject1 = ($cAOListenerName.Replace("\", "_")) + ":Database(s) not replicated..."
  fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sHTML_Body1 -swIsBodyHTML

  # Write to Eventlog...
  $sTEXT_Body1 = $sSubject1 + $sTEXT_Body1
  fnWriteEventLog -sNode ($cAOListenerName.Replace("\", "_")) -sMessage $sTEXT_Body1
}

PowerShell SQL Query Function

The function below can be used to a query to a database. The script returns any error as a result (2 lines).

function fnSQLCmd([string]$sInstance, [string]$sQuery, [string]$sDatabase) {
  # Register SQPPS Module...
  $oSQLPSModule = Get-Module | Where {$_.name -eq "SQLPS" }
  if ($oSQLPSModule.Count -eq 0) {
    Import-Module "SQLPS" -WarningAction SilentlyContinue
  }

  if ($sDatabase) {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -Database " + $sDatabase + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  } else {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  }
  return Invoke-Expression $sQuery
}

 

MSSQL Get Settings and Counters w. PowerShell

# Query Server Configuration...
Invoke-Sqlcmd -Query "SELECT * FROM sys.configurations WHERE description LIKE '%server memory%'"

# Server performance counter Page life expectancy (>(300s/4G))...
# Server performance counter Lazy writes/sec (take 2 samples with 1s between samples)...
Invoke-Sqlcmd -Query "SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE counter_name IN ('Total Server Memory (KB)', 'Page life expectancy', 'Lazy writes/sec')"

PowerShell T-SQL Scripting

Article written in the Dutch language…

Met onderstaande commando’s kun je d.m.v. T-SQL gegevens opvragen uit een MSSQL database m.b.v. SQL PowerShell Modules of SQLPS.

$sResult = Invoke-Sqlcmd -Query "SELECT @@SERVERNAME AS Servername;" `
                         -QueryTimeout 3 `
                         -ServerInstance [SERVERNAME][INSTANCE] `
                         -Database master

Vervolgens vraag je de inhoud van bovenstaande voorbeeld d.m.v. variable $sResult.Servername. Bij een een resultaat van meer dan een record worden de values opgeslagen in een array. In dat geval geeft de variabele $sResult.count het aantal geretourneerde regels terug (-gt 1). Je kunt dan bijvoorbeeld de eerste regel van het resultaat opvragen d.m.v. $sResult[0].columnname. Je kunt m.b.v. onderstaande statement afvangen of het resultaat uit meer dan twee regels bestaat.

if ($sResult.count -gt 1) {
  # TestVoorbeeld afhandelen Array...
  write-host "Er zitten " $sResult.count " values in de array..." 
  foreach ($oResultItem in $sResult) {
    $oResultItem.Servername
  }
} else {
  # Afhandelen string value...
  $sResult.Servername
}

Ook is het mogelijk vanuit een specifieke context gegevens op te vragen.

cd SQLSERVER:SQL{servername}{instancename}Databasesmsdb
Invoke-Sqlcmd -Query "SELECT * FROM sysjobs;" -QueryTimeout 3