By David Postlethwaite
At my presentation on SQL Server Management Studio at SQL Saturday in Exeter I promised to write some articles on Gethyn’s blog about the bits I didn’t manage to cover.
One of the subjects I covered was Registered Servers and the Central Management Studio
These are two features in SSMS that allow you to register your SQL instances with a more meaningful name and then group them into folders such as project name or type.
This can make it much easier to find the instances used by a particular project especially for new starters who won’t know the names of all the instances and projects in your company.
The CMS can be shared by all of your DBAs or developers so there is one central list of instances and what they are used for.
One additional feature is the ability to run queries against multiple instances. You simply right click on a particular folder and select “New Query”. A new query window will open with a pink footer showing connected (x/x), where x is the number of instances in the folder.
Any query run in this window will execute on every instance in that particular folder.
If you select the CMS server itself you can run a query on every instance you have registered
A great feature you may think but what if you accidently ran this in a multi server query window?
exec sp_msforeachdb 'drop database [?];'
Since writing my presentation I have come across a DBA team that aren’t allowed to use the CMS because of the danger of accidently running a dangerous multi server query on every instance in the company.
I’ve hunted high and low but can’t find anyway of disabling the multi query option in SSMS so if you are not willing to risk it then the usefulness of the CMS will not be available to your team.
What do other think?
Does your company use the CMS?
Are the benefits of the CMS worth the risk of someone accidently running a dangerous multi server query?
Have you found a way of disabling the multi server query option?
Let me know?