1

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.

  • Nice idea. I had considered something similar until i found Ola Hallengrens scripts, and that i can execute commands simultaneously using registered groups in ssms. Since then my need hasnt progressed. But i only have ~30 instances. The main thing i like about a centralised script is that you can do things sequentially so you can spread cpu usage & io around rather than thrash a host or swamp a san/nas with backups. – Sir Swears-a-lot Mar 18 '17 at 8:42
  • 2
    Have you looked at SQL Agent Multi Server Administration? You can use a single central instance to deploy identical jobs too every server. This would accomplish the same as your PowerShell script, but save you from reinventing the wheel. mssqltips.com/sqlservertip/2445/… – AMtwo Mar 18 '17 at 10:59
  • @AMtwo No, not yet considered that but it will certainly be part of my research now thanks. – Molenpad Mar 19 '17 at 9:53
3

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.

  • +1000 on the serial job execution – Erik Darling Mar 19 '17 at 0:31
  • I've accepted this answer because you've given me a lot to think about there. I'm embarrassed to say I'm fairly new to powershell so have you got any tips/pointers about parallel processing because I'm thinking there are a lot of small instances that could indeed run side by side easily, however our big environments would need separating I think. – Molenpad Mar 19 '17 at 9:58
  • 1
    @Molenpad Give this a read on runspaces – Shawn Melton Mar 19 '17 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.