Now that I’m in a position where I am monitoring a large number of SQL Servers, I’m finding a new appreciation for SSMS. I’ve already written about the error-highlighting trick I recently discovered. Today I’d like to talk a bit about the ability of SSMS to issue commands against multiple SQL Servers at once. I first heard about this tip from Kimberly Tripp, although I can’t remember if I read it on her blog or saw it in one of her Technet videos.
I find writing maintenance scripts is an iterative process. I write something that does what I need and put it in a scheduled job on all my servers. Then some configuration or state comes around that I didn’t anticipate and the script breaks. I’ll modify the script to accommodate the situation and re-deploy the script. Over time, you get some robust scripts, but the re-deployment process can get a bit tedious when you have more than a few servers. If you had a way to deploy the script on multiple servers at once, you could greatly ease your pain. Luckily, SSMS can do that!
In SSMS, you can set up folders containing connection information for registered SQL Servers. Below is an image of how I have mine set up. You’ll notice I’ve grouped the servers by version. This makes things easy when using commands supported in a newer version of SQL and not an older one (such as Try…Catch blocks).
As long as you have valid connection information for the servers in each group, you can instruct SSMS to open a connection to all servers in that folder. Simply right-click the folder and choose New Query… A new query window will open. Look at the status bar along the bottom of the new windows and you’ll notice a couple of things:
On the far left it says “Connected. (8/8)” This let you know that SSMS has connected to all 8 servers in that group and any SQL statements executed in this window will be run against all 8 servers. (If any connections failed for some reason, you will see “Connected. x/8” where x is the number of successful connections. You’ll also see error messages indicating which servers were not able to be connected. You can still issue commands against multiple servers, but the non-connected ones will, obviously, not get the commands.) Next on the command line is “SQL 2005 Servers.” This is the group name I connected to. Lastly, you see “<various logins>.” My group contains SQL Servers I connect to using a mix of Windows Authentication and SQL Server accounts, so you don’t see a single account name here.
Most importantly, notice the status bar color. I have changed this from the default status bar color to serve as a visual reminder that commands run in this window will affect multiple servers. You can change the color by going to Tools –> Options and expanding the Text Editors group, then selecting Editor Tab And Status Bar, as shown below.
Another simplification step I have done is to create a DBA utility database on each of my servers. This is a database where I can store all my custom procedures and logging tables. (Some might prefer to use master or msdb, but I like to keep those free of user added stuff.) Since this database is on each server, when I need to update a maintenance script, I can just issue a USE <database> command and run my commands in the group server window. Simple!
2 thoughts on “Teaching An Old Dog New Tricks”
–> came here from SQL Server Central
Nice trick. I Like the DBA admin database idea too, I’ve done this on systems where I am not permitted to alter the application’s DB Schema in any way (because app updates overwrite things like all the stored procs).
I use both the SSMS Tools and the SQL Complete addons in SSMS. SSMS Tools has Window Connect coloring customisable by individual DB, so for example I have PROD DB’s show in Red to warn me that it’s dangerous territory and green for Dev. This color bar is not the same as the one in the SSMS settings though, it sits adjacent to it.