SQL Server 2016 – How to Automate Creating Credentials Securely

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.

If you have Kerberos in the mix you may run into SPN issues. – Jeff A

Depending on your domain functional level (need to be 2012 I believe), you could use Group managed service accounts – Bob Klimes

Any manually created SPNs will need to be deleted and recreated. Any SPNs automatically created by the service on startup will be removed on shutdown, so that shouldn't be a concern. I'll second Bob Klimes' suggestion that you should go to Group Managed Service accounts, if possible. They're absolutely fantastic. Finally, you may need to configure service account permissions within each instance depending on your current configuration. Generally these accounts can be minimally provisioned, but it'll depend on your current config. – John Eisbrener

PowerShell can be used to automate – Bob Klimes

_{Community Wiki answer compiled from question comments. Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.}