I am on a team supporting over 150 servers with varying numbers of instances and database ranging in size from several MBs up to a few in the TBs size now. We constantly add new projects and servers and managing all of them with be getting a little bit easier in the near future.
I am in the process of standing up a Central Management Server (CMS). How will this make my job easier? I am glad you asked. Using a CMS will allow me “centrally” manage all the servers in our environment. I can execute T-SQL code against all servers from one query window. Or I could use Policy Based Management (PBM) to evaluate all the servers to see if they conform to the policies we establish in our environment. I will be posting more about that in the future, but let me give you an example.
Let’s say you have a policy in your shop that all databases are backed up weekly. (I know but humor me for a minute). If you write one policy you can run it against all servers to see if they have been backed up in the last 7 days. If one or more violates the policy you will know away. Nice huh? Can you imagine having to manually check every server every week, let alone every day to see if all databases were backed up?
Now that we have an idea of a few things we can do with a CMS let’s get started with how we go about setting up and using one.
First open SQL Server Management Studio. You can do that by going to Start > Programs > SQL Server 2008 > Then looking for SQL Server Management Studio.
Optionally use the Windows key ( )and type “R” for run. Then type “ssms” and hit enter.
Next we need to find the Registered Servers tab. It may be located at the bottom on the Object Explored tab or you can find in by going to the “View” menu across the top of SSMS and clicking on Registered Servers link (Ctrl + Alt + G)
Now expand the Database Engine tree by clicking on the “+” (plus sign)
Next we want to right click on the Central Management Servers and choose Register Central Management server…
That will open window like this one.
Choose the servers from the drop down list. Since Windows Authentication is the only option you must have a domain controller to authenticate against. After choosing the server, click the button labeled “Test”. This will test the connection. After successfully connecting you will see this response.
Now that we have our CMS setup we need to add servers to it so we can manage them. You will do that by right clicking on the CMS you just created. Then choose “New Server Registration…”
Following the same process as before when adding the CMS pick the server from the drop down list and test the connection. After confirming and receiving positive confirmation save the server and you now have a server registered to your CMS.
Now that you have registered a server to use your new CMS to manage. You have several options. 1) You can run T-SQL code against all the registered servers. (more on that in a future post) 2) You can evaluate Policies (more on PBM in a future post). 3) You could import existing Policies. 4) Create a Server Group (I have my servers grouped by project). 5) Register additional servers.
Optionally, right clicking on the new registered server you are presented with the same options as above plus some additional ones.
Tasks: After creating groups if a server is move from one project to another you can move the register server simply by choosing the move option and selecting the new folder to more the server too. (Folder must already exist)
Service Control: Allows you to Start, Stop, Pause, Resume, and Restart the database service on the remote server.
As you can see, using a CMS to manage one or multiple servers can save you countless hours of work.
In the next post I will show you how to add Policy Based Management to the server and evaluating the new policies against your servers.