Sql-server – Maintenance Plan Using Powershell

maintenancepowershellsql serversql-server-2016

At the moment I have around 125 production instances, each with a script-based maintenance plan running as an agent job.

The tasks run are Index Reorg/Rebuild, Stats updates and Checkdb. Backups are looked after by Netbackup so they dont form part of the plans but for a couple of exceptions.

I moved all the instances last year to script-based maintenance plans from plans created with the SSMS wizard (hate those) and they're efficient and effective so overall I'm pleased.

I'm wondering whether it's feasible to take things a little further. I've recently been working on a powershell script that, when pointed at an instance, iterates through the databases on that instance and performs those three tasks on demand.

My question is whether anyone can see any downside by doing this for all instances, I.e. Having a single powershell script on our DBA server that iterates through a list of instances on a windows schedule and executes the maintenance tasks. Any errors would be handled / written out to logs etc.

The main benefit of this in my eyes that we won't be deploying mp jobs to new instances and configuring schedules. We will just be adding the name of any new instance to the instances the script must iterate through.

I'd welcome your thoughts.

Best Answer

I've recently been working on a powershell script that, when pointed at an instance, iterates through the databases on that instance and performs those three tasks on demand.

My question is whether anyone can see any downside by doing this for all instances, I.e. Having a single powershell script on our DBA server that iterates through a list of instances on a windows schedule and executes the maintenance tasks. Any errors would be handled / written out to logs etc.

Your problems will be based on how you wrote your process. So just a few things I can think of right off to consider. Although any answer is going to an opinion of the writer.

  • Does it do parallel processing or sequential?

If you are using sequential processing you will never know when one particular server is going to have a load from the index maintenance or stats maintenance. As well, it could also vary each time it runs based on other processes running on a given instance. Something to consider is if your maintenance window is large enough to handle that fluctuation.

  • When you run it from one server are you using something like Invoke-Sqlcmd to run it or Invoke-Command (PowerShell remote).

This is considering when you run a command in PowerShell will you process any amount of data in PowerShell or do it all in SQL Server. If you do any kind of piping with the data consider how it will be processed. Certain things (piping to an executable) can require all the data to be cached in memory before it moves down the pipe to the next command.

Related Question