A word on Central Management Server in SQL Server 2008

Here comes a quick post about a new feature of SQL Server 2008, called Central Management Server (CMS). It is a great feature if you have to overview and manage a number of SQL Servers (anywhere between 2 and 1000000!) and if you have more than 2 DBAs. It is especially handy when the set of monitored / managed servers changes often.

 

It is as simple as this: a list of registered servers is stored on a Central Server. Every time a DBA connects to the Central Server, the list is available to them.

 

Benefits:

1. You can execute queries on multiple / sets of SQL Servers at once

2. You can open Object Explorer for multiple / sets of SQL Servers at once

3. You can use it for Policy-based management

4. It works with previous versions of SQL Server

 

An example:

You may want to create several different groups of registered servers, i.e. QA, Development, Production, etc. Each group of servers will be registered in its own folder within the CMS, and whenever you need to act on a certain group, you can simply right-click the folder, and select “New Query”, “Object explorer”, etc.

 

Attention:

1. Windows authentication only

2. The group-execute queries do not include the Central Management server itself. It might be a good idea to use a separate instance on a development server as a CMS.

3. Be very cautious about running group-execute queries, especially inserts, updates, creates, drops… Actually, be very careful with every group-execute, even with selects, from a performance point of view.

 

For further reading:

 – MSDN article

 – How to Configure and Use the Central Management Server Feature in SQL Server 2008