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.
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.
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.
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.
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.
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.
Step 6: Configure a folder group for your development servers. In this case we will add our SQL Express edition as a development server.
Step 7: Lets add our Express edition server to the development group. Right click on Development folder and select New Server Registration.
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)
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.