Maybe more people would use the Central Management Server feature of Management Studio if it had a name that didn’t sound as utilitarian as a heat pump. Something like PowerServer. Or…Mega Instance Policy Ranger. Or…One Query To Rule Them All.

Because that’s what it really is.

Then again maybe many folks don’t use it because they only have 10 or less instances to manage. I imagine with so few instances they’re just using Registered Server names in SSMS. A couple of right-clicks and you’re good to go.

This is perfectly fine, unless like my team you have to herd around 300 SQL Server instances. That’s when CMS goes from being an interesting feature to a life-saving necessity. I really love building a nicely organized CMS, and not just because I always wanted to pretend I was an architect.

I mean, when we get a new DBA teammate who have Windows-level permission to all these servers I can say one of two things:

1. “Here’s a list of a few hundred servers you can register. Have fun entering the data.”
2. “Here’s the server where we have CMS. Connect to that and you can see all our instances.”

If I chose the former then I’d be a very bad man, Jerry.

We’re living in a society here!

If you have more than a handful of instances, you really owe it to yourself to take a few minutes and set up your own CMS. The hard part is getting a list of all the instances, but you need to do that anyways, right?

Once you have that list pick ONE SQL Server instance to create your CMS. You want this to be something with solid up-time that is accessible to other team members, but not an instance that’s already spending CPU cycles on something critical. This means:

• Not a heavily used Production instance
• Not a flaky, about to keel over pre-SQL Server 2008 instance
• Not that 2019 CTP installed on your laptop

So once you have that, go ahead and do what Microsoft says.

• In SQL Server Management Studio, on the View menu, click Registered Servers.
• In Registered Servers, expand Database Engine, right-click Central Management Servers, and then click Register Central Management Server.
• In the New Server Registration dialog box, select the instance of SQL Server that you want to become the central management server from the drop-down list of servers. You must use Windows Authentication for the central management server.
• In Registered Server, enter a server name and optional description.
• From the Connection Properties tab, review or modify the network and connection properties. For more information, see Connect to Server (Connection Properties Page) Database Engine
• Click Test, to test the connection.
• Click Save. The instance of SQL Server will appear under the Central Management Servers folder.

How you organize your instances is up to you. Some like to organize by geolocation or environment. Personally, I prefer to organize them by version so I can run quick checks to verify service pack level and configuration type of stuff, but that’s just me.

Yada yada yada

One final tip: you’ll notice you can’t register your CMS server name under itself. That’s a little silly since you can register any other instance more than once, but it’s not a showstopper. You can just set the Server Name name to either the fully qualified domain name, the IP address, or alias you may have registered in Active Directory, and set the “Registered Server Name” to the name you want to appear. Savvy.

I’m probably going to write some future posts about ways to leverage CMS to make you a more efficient DBA, but for now I think I’ve burned enough words. Unless you want to discuss One Hour Martinizing.