Local Server Groups

There is a cool feature in SQL Server Management Studio I’m not sure every DBA knows about. It’s the possibility to save groups of servers in SSMS and either just using this to open a connection to a single server faster or to run SQL code against the whole group, yes, awesome feature!

So open SSMS, and under View click on Registered Servers.

On Local Server Groups you can start by adding a group of SQL Servers, lets call them TEST, so create New Server Group called ‘TEST’

Now on TEST we right click and choose New Server Registration, add 2 or more test servers and make sure they are under TEST group. Ok? Cool!

To run SQL on them all you just right click on the TEST group and choose New Query. In the query window you will see that the bottom part is now a pink-ish colour, that means you are connected to a whole group of servers.

Try running some code, like

select name from sys.databases

The output will be a list of database names, (Duh!), but also a column showing which server these belong to, so SSMS took your code and ran it on each of the servers in the group, received the output from each and printed it on your Results pane.

I use this feature to easily run some sanity checks on all databases on all servers, like below script where I check if a database has auto_close or auto_shrink on, if page verify is NOT checksum or if it is lacking an owner, simple and basic checks.  We can expand this to check for missing backups or errors or whatever you check regularly.

SELECT s.name, s.is_auto_close_on, 
       s.is_auto_shrink_on, s.owner_sid as 'Owner', 
       s.page_verify_option_desc
FROM sys.databases AS s
WHERE s.is_auto_shrink_on = 1
or s.page_verify_option_desc <> 'CHECKSUM'
or s.is_auto_close_on = 1
or s.owner_sid  is null

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *