SQL Server 2008 Central Management Servers

  • Article

I have done a blogcast and a few posts around the configuration servers in the betas  of SQL Server 2008, however in SQL Server 2008 as released these are now called Central Management Servers.  For those of you who have held off form trying the latest version until it was released, this feature is  a sort of extension to registered servers that have always been in the SQL Server management tools.

What you do know is to register any instance of SQL Server as a Central Management Server in a given domain (the stuff here only works across serves in the same or trusted domains) in the registered servers tab in SQL Server Management Studio.

Underneath this you can then create a groups or register individual servers as we all used to do in SQL Server 2000.  When you right click on the Central Management Server or a group you can do a couple of useful things..

  • Create a new query.  Queries run in this window run against all of the servers below the Central Mangement  Server or the group (depending on where you right clicked).  So could run a select * from sys.database to list all of the databases you have on all of the servers.
  • Import and run policies over the servers in the same way.

You can register the same server multiple times to put it into different groups.  For example you could have a group for each version  of SQL Server you have (say 2000,2005, and 2008)and a set of groups for dev test and prod with each server being registered twice , once in group for the version it is running and once for the function (dev test or prod). 

With these two sets of groups you can now run sql or evaluate policies against all your dev servers or all of your 2005 servers. 

Two things to be aware of:

  • Case sensitivity, if one of your servers has this on then the sql may fail on that server where it wouldn’t on the others
  • Inserts will apply to each registered server so doing insert into mytable select * form sys.database won’t create one table  d in your Central Management Server, it will execute on each applicable registered server and create a table in each.

Thanks to Brent Ozar from Quest for all of this, the demo guru at last night Quest tools launch event.

If you want to know more I will be covering this at length at SQL Bits cubed and these TechNet events:

  • Manchester on the morning of 7th October (register here)
  • Exeter in the afternoon of 23rd October (register here).

Technorati Tags: Central Management Servers,Policy Based Management,SQL Server 2008