Ever had to document 20+ SQL instances by yourself? If so, you will recognize the agony of having to run the same script over and over again just to get the same info from all your instances. I have worked at clients with 200+ instances and little or no documentation. It can be a true headache.
Fortunately the fine folks of the Microsoft have had the same problem (I used to work for Microsoft and believe me their SQL Server landscape is HUGE!!) and someone (Buck Woody, I think) created and implemented a nice solution to cope with the ever increasing numbers of SQL instances. This too little known feature is called the SQL Central Management Server or SQL-CMS for short.
What is it…. Well is it a way to centrally administer the connection strings to instances in your landscape. These connections are (securely) accessible by other DBA’s or developers and will help you get and keep control over your landscape. Is that it… Nope, it is much more…. Once you have set it up and registered and grouped your SQL instances you can actually connect to 1, a group or all SQL instances in your CMS with just 1 click. It will open a new query window in the SSMS but this window is connected to all the servers in the CMS group you connected to. As you might imagine, this will come in very handy when collecting information about your instances, servers, databases, etc…. Oowww is that all. Well no it’s not… you can also do some really nice other stuff. For one you can also use the power of policy based management and evaluate your entire landscape on the correct use of e.g. the auto-close setting for databases. Or you can run all the Best-Practices policies (they come with the Dev and Enterprise editions) against your landscape… Hhhmmmm now things start getting interesting, right….
A few things to think about…. Does it do everything you might want? Nope! The security model to protecting the registered server in the CMS is limited. You either can see them all, or you can’t see any. There is no option of limiting access to a certain group of server in the CMS. Is that unfortunate yes, but it is no problem. Just make sure you have the proper security set up on your instances and you should be fine. Another thing to be mindful of. It only works with Windows Integrated Security. Again not really a big problem in most cases, but you will always run into that one exceptional client where they do not use an AD. Oohhh well, nothing is perfect right? 🙂 Still it has proven it’s usefulness to me on more then one occasion and the best part is… it is free!!! It comes with all versions of SQL, even the Express version. I know, I know, there are other products that do pretty much the same thing but most of those cost money and don’t integrate into the Management Studio.
In my opinion this is one of the most underrated features of the SQL Server product. It is free, useful, easy to setup and flexible enough to tune if you would want to. So why not look at it and play around with it. I promise you it will be worth it!