Two Useful Scripts for SQL Toolbelt Users
Redgate’s tool SQL Multi Script is super handy: you can execute a script (or scripts) against any number of SQL Servers in your estate. The catch is Multi Script needs to be aware of them. If you have a large estate this can get cumbersome fast as you have to individually add each server. There is hope though: If you have a colleague that has already done so they can export their list(s) and you can then import them. But since it has to be maintained by hand, instance by instance it’s unlikely they have a complete list either. We currently keep our SQL servers listed in a SQL database for tracking purposes and a while ago I hacked together a script that would generate import files based on a couple of different queries. Fast forward to the present and I found that I was not the only person that wanted a solution to this problem, except they had all their servers registered in SSMS or a Central Management Server. So I dug out my old script, dusted it off and came up with this script that leverages DBA Tools to pull lists of SQL Servers and break them out into lists by Source and Groups returned by Get-DbaRegServer that can be easily imported into Multi Script bypassing the onerous manual process. (And if you haven’t already added DBA tools to your PowerShell toolkit: why not?)
If that script is helpful there is also a second script for SQL Backup users that will enable compression on backups where native TDE is in use (otherwise the databases will be huge and unwieldy). Simply point this script at a server where you’ve installed SQL Backup and enabled TDE and the script will create the necessary registry setting and reboot the server (or not but the setting won’t take effect until the server has been restarted). Just like the previous script I do leverage DBA Tools because you really should have installed it a paragraph ago…
Of course it goes without saying don’t just run random scripts from somebody on the internet: read it, make sure you understand what’s doing and that you are comfortable with it and always run it in a non-critical environment first to be sure.