Freigeben über

SQL Server 2008 Configuration Servers

  • Artikel

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

Comments

  • Anonymous
    May 14, 2008
    Can I add sql 2000 and 2005 machines to the group, also can I create a policy on the configuration server and have it apply that policy to the servers in the group or do I need to export that policy to the servers in the group first

  • Anonymous
    May 15, 2008
    Paul. You can add earlier machines and some policies will work with earlier editons of SQL Server.  You can test the policy against the servers in the configuration server group, but to deploy the policy to each server I would suggest a Powershell script (I am researchung this as I write this).  if you have more questions please e-mail me direct.

  • Anonymous
    May 15, 2008
    I exported the regsrv file from SSMS 2005 and tried to import on 2008. It fails. Is there a utility that will put the 2005 file in 2008 format?

  • Anonymous
    August 20, 2009
    I have a PC with 1GB of memory and 400GB HDD. I have installed windows XP in C:, windows server 2003 enterprise for active directory in D: and windows server 2003 enterprise for SQL Server in E:. But when I tried to install SQL server the system rejects the installation and says "this pc hasn't the capability to install SQL server" I don't know what is the problem? Any comments are welcomed.

  • Anonymous
    August 20, 2009
    Mirwais Difficult to say exactly what the problem is (which verison of SQL Server for example. The exact details should be in a log file (location depends on whihc version. If this is killing you you may want to raise a support call (you have 2 calls a year with your Technet subscription) Andrew