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}