“The SQL Guy” Post #3: THE POWER OF SQL SERVER 2008 CENTRAL MANAGEMENT SERVER

Here is another great article from Saleem Hakani of the SQL Server product team at Microsoft.  Enjoy!

Imagine this: Your manager has asked you to find out the available disk space for all the drives from SQL Servers in the production environment and you have 6800 SQL Servers that are spread in North America, Europe, Asia and Australia. After an hour, your manager comes back and asks you to get a list of all the databases from the production environment that has Simple Recovery Model and Full Recovery Model option set. There are at-least 50 databases in each of the production servers. Again after an hour your manager asks you to find out the service pack level of all production SQL Servers. He wants all this information at the earliest. What will you do?

At the outset, these requirements seems to be lot of work as you will need to connect to 6800 servers and gather all the information your manager has requested. However, SQL Server has made our lives much easier with the introduction of a new technology called “SQL Server Central Management Server”

WHAT IS CENTRAL MANAGEMENT SERVER?

Microsoft introduced an innovative new feature in SQL Server 2008 called Central Management Server that allows you to administer and manage multiple servers from one central location. This is a very powerful feature that makes SQL Server 2008 very easy to manage thousands of servers with less efforts and overhead. Transact-SQL statements and Policy-Based Management policies can also be executed at the same time against servers registered in Central Management Servers. Central Management Server can only work with Windows Authentication. However; servers registered within Central Management Servers (servers that need to be managed) can be registered using SQL Authentication or Windows Authentication.

HOW TO USE CENTRAL MANAGEMENT SERVER?

From SQL Server Management Studio, On the View menu, click Registered Servers and expand Database Engine and then Right-Click Central Management Servers, and select New to register a server as Central Management Server. Once you have registered the Central Management Server, Right-Click on the Central Management Server and register all other SQL Servers that you would like to manage. If you have a list of servers in a file, you can also import the server list to register all the servers in central management server.

With the power of Central Management Server, you can now query multiple servers at the same time. The results returned by the query can be combined into a single results pane, or can be returned in separate results panes. The results set can include additional columns for the server name and the login name that is used by the query on each server.

THINGS TO KEEP IN MIND

§ You must be a member of SYSADMIN or SERVERGROUPADMINISTRATORROLE role in MSDB database to manage servers using Central Management Server.

§ You can load a list of servers from an xml or text file by importing all the servers in to Central Management Server

§ I recommend that you create separate Server Groups within Central Management Server (Ex: Production, Development, Test, etc.) to prevent queries from being run on servers that you don’t want to run on.

§ The connections maintained by a Central Management Server execute in the context of the Windows Authenticated user therefore, the effective permissions for the executing user on the registered servers might vary.

§ You can configure the results/output of a Central Management Server to Add a Login Name, Server Name, and/or Merge Results of all the servers in to result-set.

SOLUTION

Now you have learned about Central Management Server. How difficult would it be to gather the information your manager requested you in the above scenario: (It will now be just matter of minutes)

Once you register a Central Management Server and Register all the production servers within Central Management Server, Right-Click on the Central Management Server and Select New Query and execute the following commands:

Task 1:

This command will provide you with a list of Server Name, Drive and Available Disk Space from all production servers.

Task 2:

This command will provide you with a list of Server Name, Database Name and the Recovery Model of the database from all production servers.

Task 3:

or

This command will provide you with the Server Name and their version from all production servers.

With the introduction of Central Management Server in SQL Server 2008, you can now efficiently manage thousands of SQL Servers in the enterprise and increase employee productivity.