Back in SQL Server 2008 my team implemented the Central Management Server (CMS) feature. If you work as part of a DBA team and you haven’t tried it out I encourage you to do so. It’s available on all editions, even Express. In short, the CMS allows you to share entries in registered servers with other people. This is really powerful if, as I mentioned, you work in a team environment or you use multiple machines.
I recently configured a set of VM environments, one for each major version of SQL Server going back to SQL Server 2000. I use a desktop machine and a laptop, in addition to the machine running all of the VMs. I started setting up Registered Servers, albeit local registrations with connection short cuts to each of the instances. Then it dawned on me:
“You dork what why are you entering this connection information multiple times? Why not designate one of the instances as a CMS and store them once?”
I felt like a complete idiot. Here my team implemented the feature and I completely forgot about it. So I stopped with the madness, picked an instance to use as my CMS, or as I like to call it my SSMS Shared Connection Favorites, and registered all of the instances there. Now in each SSMS I use I simply add the instance storing the Shared Favorites in Registered Servers and Voilà, register each instance once and use in multiple locations!
I did run in to a limitation of the CMS, which made me very unhappy but I have no one to blame but myself. I wanted to register my SQL Azure instance with the CMS. But SQL Azure only supports SQL Authentication and the CMS only supports Windows Authentication. Argh! There are a number of reasons why we imposed the CMS limitation, I won’t go into them here, but right now I’m not happy about it. I’ll have to see what I can do about that…