Eliminating Successful Backup messages with Powershell and SMO

As a DBA, I enjoy knowing that the better job I do, the less likely it is that any of my customers know my name. In the same vein, I like my SQL Instances to behave in a similar fashion. I want the instance to remain silent unless I ask it a question or unless it has an issue – then I want it to scream loudly, but only so loud that the DBAs – not the customer – can hear it.

One setting of SQL Server I’ve grown to love is trace flag 3226. This trace flag has worked since SQL 2000, but was fully documented with SQL 2008R2 (http://msdn.microsoft.com/en-us/library/ms188396.aspx). With this flag enabled, gone are the countless messages that get in the way of seeing the real errors. While I understand logs can be filtered and software exists to parse logs, most of the time, these are unavailable. In environments with frequent log backups and numerous databases, this information can become overwhelming.

After enabling this flag globally, no longer is every successful backup recorded in the SQL Server error log nor is it placed in the Windows Application Event Log. That is all fine and dandy, however, how should I add this trace flag to 50+ instances? After weighing the options (clicking party, registry updates, WMI, SMO), I decided that SMO would be the most flexible given the situation.

Below is a Powershell script that will loop over all of the servers in a CMS group and add the 3226 trace flag to the StartupParameters property if it does not already exist.

$q = "`""

#timeout in seconds
#If query does not return in this interval
#timeout will fire and move to next object
#prevents locks and blocking over this interval
#set to zero to wait indefinitely
$QueryTimeout = 120

$CMSInstance = "CMSINSTANCE"
   
#Load SMO assemplies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.RegisteredServers') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Common') | out-null


$connectionString = "Data Source=$CMSINstance;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CMSStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)
$CMSDBStore = $CMSStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups["2005 + Instances"]


foreach($RegisteredServer in $CMSDBStore.RegisteredServers)
{
    $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $RegisteredServer.ServerName;
    Write-Host "Connected to $($srv.name)"  -ForegroundColor Blue
    
    try
    {
        
        #Get the managed computer object
        $SQLServerWMI = New-Object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $srv.ComputerNamePhysicalNetBIOS
        
        #$srv.ServiceName is not correct - derive service name here
        Write-Host $srv.ServiceName
        if($srv.InstanceName -eq "")
        {
            $ServiceName = "MSSQLSERVER"
        }
        else
        {
            $ServiceName = "MSSQL`$$($srv.InstanceName)"
        }
        #Get the info for this particular SQL Server Service
        $SQLServerServiceWMI = $SQLServerWMI.Services[$ServiceName]
        $StartupParameters = $SQLServerServiceWMI.StartupParameters
        #Get the startupparameters into an array
        $StartParamsArray = $StartupParameters.split(';')
        if($StartParamsArray -notcontains "-T3226")
        {
            #Add T3226 to the StartupParameters collection
            $SQLServerServiceWMI.StartupParameters += ';-T3226'
            $SQLServerServiceWMI.Alter()
            Write-Host "`tSuccessfully Added T3226 to $($srv.name)." -ForegroundColor Blue
        }
        else
        {
            Write-Host "`tT3226 already exists on $($srv.name)." -ForegroundColor Blue
        }
    
    }
    catch
    {
        Write-Host "Error Adding trace T3226 to $($srv.Name) on $($srv.ComputerNamePhysicalNetBios), "`
        "service name $($ServiceName)." -BackgroundColor Red -ForegroundColor Black
    }
    
}

Safely Restart SQL Agent with Powershell

I recently had to enable tokens in SQL Agent across about 50 instances. Unfortunately, enabling tokens requires a SQL Agent restart.

After weighing the options – look at Job Activity monitor for each instance, verify no running jobs, then restart the SQL Agent service or write a cool new Powershell script – I decided to write the Powershell. Growing up, it was explained to me that work isn’t supposed to be fun. However, scripts like this continue to prove that one can indeed get paid to have fun.

Using the power of SMO and Powershell, I was able to loop over all the instances in a particular CMS group and determine if any jobs were currently running. If no jobs were running, tokens were enabled and the SQL Agent cluster resource reset. In this case, all the instances were on Server 2008R2 clusters, but this could be modified to use the WMI Win32_Service class to restart the remote service on a standalone instance or cluster.exe for a cluster on an older OS.

$q = "`""

#timeout in seconds
#If query does not return in this interval
#timeout will fire and move to next object
#prevents locks and blocking over this interval
#set to zero to wait indefinitely
$QueryTimeout = 120

#The instance name of the CMS
$CMSInstance = "CMSInstanceName"
   
#Load SMO assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.RegisteredServers') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Common') | out-null


#Connect to the CMS instance
$connectionString = "Data Source=$CMSINstance;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
#The Registered Server store on the CMS
$CMSStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

#Define the group and sub group that we want to loop over
$CMSDBStore = $CMSStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups["2005 + Instances"]

#Get the cmdlets to manage 2008R2 clusters
Import-Module FailoverClusters

#Loop over each registered server and use SMO to interrogate the Agent
foreach($RegisteredServer in $CMSDBStore.RegisteredServers)
{
      $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $RegisteredServer.ServerName;

      #the SMO JobServer class represents the SQL Server Agent associated with the instance
      $SQLAgent = $srv.JobServer

      #If tokens are not enabled, try to enable them
      if($SQLAgent.ReplaceAlertTokensEnabled -eq $false)
      {
            $JobsExecuting = 0
            foreach($Job in $SQLAgent.Jobs)
            {
                  if($Job.CurrentRunStatus.ToString() -ne "Idle")
                  {
                        #Set the variable to 1 to respresent at least one job is running
                        #Break out of the loop
                        $JobsExecuting = 1
                        break
                  }
            }
            if($JobsExecuting -eq 0)
            {
                  #Only if no jobs are running
                  write-host "No Jobs Running on $($srv.Name)"
                  #Enable tokens and save the change
                  $SQLAgent.ReplaceAlertTokensEnabled = $true
                  $SQLAgent.Alter()
                  Write-Host "Alerts Token Enabled on $($srv.Name)"
                  #Use the cluster cmdlets to stop and start the resource
                  get-cluster $srv.ComputerNamePhysicalNetBIOS | get-clusterresource | 
                  ?{$_.ResourceType.Name -eq "SQL Server Agent" -and $_.Name -eq "SQL Server Agent ($($srv.InstanceName))"} | 
                  stop-clusterresource | start-clusterresource -ErrorAction Stop
            }
            else
            {
                  #If there are jobs running, alert the client with what instance they were running - try again later
                  Write-Host "$($srv.Name) has running jobs, try again later" -ForegroundColor Red
            }

      }
}