Using PowerShell to Execute SQL Maintenance

It’s an odd truth that laziness leads to better systems administration. That is, so long as it spurs you to automate and thoroughly document a repetitive or tedious task. For instance, I was recently tasked with reducing the excessive size of some system tables in our Microsoft Dynamics CRM environment. To start with, I accomplished this the way you would any one-off task. I RDP’d to each of the app nodes, disabled the service that interacts with the tables we’re performing maintenance on, RDP’d to my utility box, used SSMS to disable each of the SQL Agent jobs that might interfere (on two different nodes), opened the script (provided by Microsoft), tweaked it to my liking, and executed it. The next morning I did all of this in reverse, starting with cancelling the script. For one evening this isn’t really a big deal. However, we soon realized that in order to get the record count down to where we wanted it that several iterations of this maintenance would have to occur over the course of multiple weekends. Reviewing all the steps I’d just performed, my thought was “ain’t nobody got time for that”.

Confronted with performing multiple GUI-based steps during each of these maintenance windows I did what any good/lazy Sys Admin does, I scripted it. Below you’ll find an example of what I used. I run it from PowerShell ISE, executing whichever block is applicable to what I want to do at the moment. This allowed me to go from starting up the maintenance in fifteen minutes to under one minute. (I know, 14 minutes isn’t a big deal. But when you’re tired and it’s late every minute counts.) As I mentioned before, my particular case is CRM maintenance. So basically I disable services on the app nodes, disable SQL Agent Jobs that might interfere (my database is in an Availability Group, so I disable them on both nodes), start the SQL Agent Job containing the Microsoft script referenced above, and then do it all in reverse the next morning at the end of the maintenance window. I included service status checks at the bottom because I’m paranoid and want to confirm the services are actually stopped before starting the SQL script. Also, I did not script the stopping of the job. I always hope (in vain) that the job will have finished, signaling the end of this particular maintenance need. Since both SSMS and the script run from my utilities box I check it in SSMS every morning and simply alt-tab over to ISE after stopping the job to start everything back up.

It’s unlikely that you’ll have the exact situation as me, but hopefully this can give you some ideas for how to incorporate these methods into your own work. In any case I hope this helps, and feel free to contact me with any questions.

##################################################################
#Import SQL module.
import-module sqlps

##################################################################
#Stop and disable CRM services on the app nodes.

invoke-command -computername CRMSERVER1 -scriptblock {Set-Service MSCRMAsyncService -startupType Disabled}
invoke-command -computername CRMSERVER2 -scriptblock {Set-Service MSCRMAsyncService -startupType Disabled}

invoke-command -computername CRMSERVER1 -scriptblock {Stop-Service MSCRMAsyncService}
invoke-command -computername CRMSERVER2 -scriptblock {Stop-Service MSCRMAsyncService}


##################################################################
#Disable SQL Agent Jobs on both members of the Availability Group.

Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'CheckDB', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'POA_Maintenance', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Weekly Full with Archive', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Monthly Full with Archive', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'CheckDB', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'POA_Maintenance', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Weekly Full with Archive', @enabled = 0;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Monthly Full with Archive', @enabled = 0;"

##################################################################
#Kick off SQL Script against the Availability Group name.

Invoke-Sqlcmd -ServerInstance "SQLAGCRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_start_job N'CRM_SysTableCleanup';"

##################################################################
#Enable SQL Agent Jobs on both members of the Availability Group.

Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'CheckDB', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'POA_Maintenance', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Weekly Full with Archive', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER1\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Monthly Full with Archive', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'CheckDB', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'POA_Maintenance', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Weekly Full with Archive', @enabled = 1;"
Invoke-Sqlcmd -ServerInstance "SQLSERVER2\CRMINSTANCE" -Database MSDB -query "EXEC dbo.sp_update_job @job_name = N'Backups - Monthly Full with Archive', @enabled = 1;"

##################################################################
#Start and enable CRM services  on the app nodes.

invoke-command -computername CRMSERVER1 -scriptblock {Set-Service MSCRMAsyncService -startupType Automatic}
invoke-command -computername CRMSERVER2 -scriptblock {Set-Service MSCRMAsyncService -startupType Automatic}

invoke-command -computername CRMSERVER1 -scriptblock {Start-Service MSCRMAsyncService}
invoke-command -computername CRMSERVER2 -scriptblock {Start-Service MSCRMAsyncService}

##################################################################
#Check CRM Services

invoke-command -computername CRMSERVER1 -scriptblock {Get-Service MSCRMAsyncService}
invoke-command -computername CRMSERVER2 -scriptblock {Get-Service MSCRMAsyncService}

 

Scripting Failovers for SQL Server Maintenance

As much as we’d all like to set up our SQL instances and never again let anyone touch them, that’s just not possible in real life. We live in a world where entities, for good or for evil, are constantly exposing vulnerabilities both in the SQL software and Windows itself. For that reason it is the responsibility of good DBA’s to keep their systems updated, and that means failovers.

It is typical in many environments to have a separation of duties, often keeping DBA’s from having control over the Windows Failover Cluster itself. In my particular case I actually have access to the cluster. However, our OCC (Operations Command Center) is tasked with carrying out Windows Updates on a regular basis. Letting them fail instances over means the servers get updated regularly (and also that I have to get up in the middle of the night for one less thing). I needed a way to allow their team members, regardless of experience level, to carry out Windows Updates on our cluster. Enter PowerShell scripting.

The code below is a modified version of what our OCC uses to carry out Windows Updates on our Failover Cluster. (There are options for Availability Groups too, but that’s a post for another day.) In the simplified example below there are six instances (SQL Instance 1-6) balanced across six nodes (sqlnode1-6). To keep things simple, a few assumptions are made in the script:

1. All instances are named with a particular convention. In this case that is “SQL Instance #”.
2. The instance numbers have their permanent homes on nodes of the same number (ie SQL Instance 1 belongs on sqlnode1).
3. The script is run on one of the nodes of the cluster.
4. The script is run in PowerShell ISE section by section, not all at once.

With that in mind I present the code to you below. It is written in a manner such that a Systems Administrator would be reading it as an instruction sheet and carrying out the tasks on your behalf. I am sure there are more elegant and efficient ways to accomplish this task, but this has been successful for us and I hope it can help you in some way. If you have any questions please feel free to comment or contact me on Twitter. Enjoy!

#### 1. Import Cluster Module ####
Import-Module FailoverClusters

#### 2. Fail all instances over to nodes 04-06. ####
Move-ClusterGroup "SQL Instance 1" -Node sqlnode4
Move-ClusterGroup "SQL Instance 2" -Node sqlnode5
Move-ClusterGroup "SQL Instance 3" -Node sqlnode6
Move-ClusterGroup "SQL Instance 4" -Node sqlnode4
Move-ClusterGroup "SQL Instance 5" -Node sqlnode5
Move-ClusterGroup "SQL Instance 6" -Node sqlnode6


#### 3. Remove nodes 1-3 as possible owners and add 4-6.. ####
$ClusterGroups = Get-ClusterGroup

foreach ($Service in $ClusterGroups) 
    {  
      
      If ($Service -like "SQL Instance*")
      {
        Set-ClusterOwnerNode -Group  $Service.Name -Owners sqlnode4,sqlnode5,sqlnode6
        Get-ClusterOwnerNode -Group $Service.Name
      }  
      
    }

#### 4. Install updates on nodes 1-3. ####

#### 5. Remove nodes 4-6 as possible owners and add 1-3. ####
$ClusterGroups = Get-ClusterGroup

foreach ($Service in $ClusterGroups) 
    {  
      
      If ($Service -like "SQL Instance*")
      {
        Set-ClusterOwnerNode -Group  $Service.Name -Owners sqlnode1,sqlnode2,sqlnode3
        Get-ClusterOwnerNode -Group $Service.Name
      }  
      
    }

#### 6. Fail all instances over to nodes 1-3. ####
Move-ClusterGroup "SQL Instance 1" -Node sqlnode1
Move-ClusterGroup "SQL Instance 2" -Node sqlnode2
Move-ClusterGroup "SQL Instance 3" -Node sqlnode3
Move-ClusterGroup "SQL Instance 4" -Node sqlnode1
Move-ClusterGroup "SQL Instance 5" -Node sqlnode2
Move-ClusterGroup "SQL Instance 6" -Node sqlnode3


#### 7. Install updates on 04-06. ####

#### 8. Add all nodes as possible owners. ####
#### Note: This list is mostly random. It only has two goals.
#### Goal 1 is to set the permanent home at the first of the list. ####
#### Goal 2 is to make sure each of the cluster nodes is listed and that they are somewhat randomized. ####
Set-ClusterOwnerNode -Group "SQL Instance 1"  -Owners sqlnode1,sqlnode5,sqlnode2,sqlnode4,sqlnode3,sqlnode6
Set-ClusterOwnerNode -Group "SQL Instance 2"  -Owners sqlnode2,sqlnode4,sqlnode5,sqlnode3,sqlnode1,sqlnode6
Set-ClusterOwnerNode -Group "SQL Instance 3"  -Owners sqlnode3,sqlnode6,sqlnode1,sqlnode2,sqlnode5,sqlnode4
Set-ClusterOwnerNode -Group "SQL Instance 4"  -Owners sqlnode4,sqlnode3,sqlnode6,sqlnode5,sqlnode1,sqlnode2
Set-ClusterOwnerNode -Group "SQL Instance 5"  -Owners sqlnode5,sqlnode2,sqlnode4,sqlnode1,sqlnode3,sqlnode6
Set-ClusterOwnerNode -Group "SQL Instance 6"  -Owners sqlnode6,sqlnode1,sqlnode3,sqlnode2,sqlnode4,sqlnode5

#### 9. Fail displaced instances back over to their permanent nodes. ####


Move-ClusterGroup "SQL Instance 4" -Node sqlnode4
Move-ClusterGroup "SQL Instance 5" -Node sqlnode5
Move-ClusterGroup "SQL Instance 6" -Node sqlnode6