Intro to Central Management Server

Do you have a DBA team that consists of more than one dba? Do you have a windows authenticated account that has access to all the SQL Server instances in your farm? If so, you might really want to consider leveraging the Central Management Server (CMS) to do your heavy lifting.

Today, we will cover creating your first Central Management Server. Come back later in the week to see a great example to automate the process of verifying full daily backups.

image

 Step 1: You might not be able to registered serves or see Central Management Servers in SQL Server Management Studio. To view them click on view in the menu bar and click on Registered Servers.

image

 Step 2: Now that you can see Central Management Server folder right click on it and click on Register Central Management Server. This will allow you to designate a server as your Central Management Server.

image

 Step 3: Register your new CMS. In this example I am going to use PBMDEMO\SQL2008 as my Central Management Studio. Once you enter the name test the connection and then save your settings.

image

Step 4: Now that the PBMDEMO\SQL2008 Central Management Server is configured lets add a group to hold development and production instances. This is done by right clicking on the instance (PBMDEMO\SQL2008) and clicking on New Server Group.

image

Step 5: Configure a folder for your production servers. We will use Production as the group name. This will be shown as a folder in the screen shot below.

image

Step 6: Configure a folder group for your development servers. In this case we will add our SQL Express edition as a development server.

image

Step 7: Lets add our Express edition server to the development group. Right click on Development folder and select New Server Registration.

image

Step 8: Add PBMDEMO\SQLEXPRESS as the server name and test the connection. Once its successful save the connection. Lets now repeat steps 7 and 8 to add four instances of SQL Server to the production group (PBMDEMO, PBMDEMO,2008 and PBMDEMO\2005, PBMDEMO\2000)

image 

The screen shot above should be the final result for adding five production instances and one development instance. Come back later in the week as we show you how to leverage the CMS server to run and automate the execution of multi-server queries.