SQL Server 2008 Configuration Servers

Following on from my last post, when DBA’s have to manage multiple SQL Server 2000 instances you would normally see all of them registered on their local desktop in enterprise manager under a variety of groups such as dev, prod, test etc.

With the advent of SQL Server 2005 this practice seems to have died off, perhaps because no one knows that this option exists in Management Studio, or because there’s no advantage in doing so.  But that changes in SQL Server 2008, with the introduction of Configuration Servers.

So in this screen shot I have opened up the registered servers by selecting View –> Registered Servers (CTRL+ALT+G):

image 

As you can see there is a node for configuration servers and the first step is to add a server (in the same way as you register a new server). Then create a group underneath it and finally register the servers this configuration server will apply to ( again in the same as normal registered servers are added):

 

image 

Having done that, you can now apply policies to all of the servers in the group or a T-SQL command by running them from the configuration server.

So in this exercise if I run this

 USE master
GO
SELECT * FROM sysdatabases;
GO

on the configuration server (by creating a new query from the configuration server) I would get a message:

DBSERVER\INST03(DBSERVER\Administrator):

An error occurred while executing batch.

Error message is: The server is not connected.

DBSERVER(DBSERVER\Administrator): (11 row(s) affected)

DBSERVER\prod(DBSERVER\Administrator): (8 row(s) affected)

and all of the databases in each server will be in a grid in the results tab. Note the error above is because dbserver/inst03 is not running (as you can see from the management console screen shot above).

In the same way you can right click on the configuration server and run a policy over all of the servers in the group. 

So like lord of the rings you can have one server to control them all.

Technorati Tags: sql server 2008,policy Management,configuration servers