Share via

SQL Server Central Management Server

  • Article

Well I thought I would start with a subject I get to talk about almost every week.   It's the start of one of my favorite features of SQL 2008, and one that I feel is very powerful, but frequently overlooked.

Central Management Server

Why is this such a good thing?   Well it's easy, and it makes the DBA's life a bit easier.   Sounds like a Win-Win to me!   For some background, I see very large Enterprise customers most weeks.   These customers have DBA's and servers spread around the world.   What does this one little feature do?  Well it gives all DBA's a single list of ALL servers/instances.   Doesn't sound like a big deal?  Well if you have 300+ instances and 3000+ databases, it is.

This will let you build a structure around Dev, Test, Stage, QA, and Prod.  Separate those further if you like to App team, responsible DBA, or Location, etc...   Whatever works for your team.   

Now just a quick walk through how to do this.   First you need a SQL 2008 (2008 R2 Preferred) instance.   Here is the good part, it does not need any huge server behind it.   I do this on a Virtual Server most times.  Once the instance and client tools are installed,  we just need to open up Management Studio.

Once in MSSMS click on View -> Registered Servers.

 

 

After that is done you will see a new window show up there.  It will be named Registered Servers and have a few items in a list.  One of those items is a folder called Central Management Servers.  Right click on that and click Register Central Management Server...

 

 

That will open a new window to register a SQL instance.

 

 

In this window add the instance that you are connected to.   Yes, Register itself, you will see why later.

Once that is done, you should see a + sign next to Central Management Servers.  Click that and you will see your server name listed there.   Now we have a Central Management Server.  

Hold on we still have a few things to do.   I'm sorry to say that the Easy part is over.   Here comes the hard part....   You had your team have to come up with the structure of this folder layout.     I guess this means we have to have a meeting, Yes it usually does, but it can be brief.   I would recommend starting with something very simple.   Dev, Test, Stage/QA, and Prod.    Add a few servers to each one and then show what it can do.

First here is what it might look like.

 

 

 Now that we have done all of that work, what did we get out of it?   Well, go back to your desktop and open SQL 2008 R2 Management Studio.   Do the exact same steps as above.

  • View -> Registered Servers
  • Right click on Central Management Servers and select Register Server.
  • This time, you need to add the name of that instance that you just used above.
  • If you followed all of the above steps, you should be able to see all of the folders and instances that you registered previously.

 

This is the first cool thing here.   If all of the DBA's add this to their local install of Management Studio, then you ALL will have the exact same list of servers.   If one of you add a new one, then you all get to see that latest and greatest list.

 

There are a few things that you can do here, but this basically is a lead into Enterprise Management for SQL Server.   Best of all, this is included with SQL, so no 3rd party products to buy.

 

What else can I do?

  • If you right click on your Central Management Server, you will see that you can Open "New Query" and "Evaluate Policies"
  • "New Query" this will let you run a query on all of the instance below. The query must be valid on all instances (SQL 2000, 2005, 2008, 2008R2) Try something quick like "Select @@version"
  • "Evaluate Policies" this one I won't get into detail about as it will be my next Blog Post, but this is one way to Evaluate Policies across the entire environment. This is how Policy Management for SQL starts, but it gets more powerful

  

Just a few things to tie it all up.  

  • SQL 2008R2 is preferred for this Central Server. If your building it new, why install an older version?
  • SQL 2008R2 Client is preferred on all of the clients.
  • All servers registered must be NT Authenticated. You will notice there is not an option for SQL User.
  • If one of you change this list on your PC, it will change the list for EVERYONE!