Central Management Servers => Shared Connection Favorites

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…

You can read more about Central Management Servers in BOL. Kimberly Tripp also has a good blog post on CMS.

Comments (3)

  1. Adam Hobbs says:

    I must agree, I would be so happy if the CMS allowed me to specify whatever auth I wanted to save in each entry.

  2. Papy Normand says:

    At home, i have 2 computers

    – the old one with XP Pro SP3 and Dev 2008 ( W7 is arriving with a new compuer )

    – the "kids" computer with Windows 7 Home premium with Express 2008

    It is impossible to connect from the XP Pro to the Express 2008 with Windows authentification ( only with SQL Server authentification ).Bad luck for me, i can't use CMS to register servers as i have done when i had SQL Server 2005 on both computers

  3. Papy, You can still create server registrations using SQL Authentication in the "Local Server Groups" folder. This didn't change from SQL2K5 to SQL2K8. The limitation on Windows Authentication is only for servers registered with a CMS.