This post is going to walk you through the process of creating an SQL Server Central Management Server (CMS). Central Management Servers are designed in a way that allows them to store lists of instances of servers. The goal is to organize them in a more central location. In short, you can take action on the CMS and that action is then sent to all servers in the group. Some command actions include Transactions, Object Explorer, and Policy-Based Management.
Note: Keep in mind that it's only possible to designate SQL Servers as Central Management Servers if they are 2008 or later.
Permissions
There are only two roles in Microsoft Database that grant access to central management servers. Those roles are:
- Server Group Administrator Role: Manages the CMS.
- Server Group Reader Role: Allowed to connect to the CMS.
Since connections in the CMS are executed by users by using Windows Authentication, the permission of the registered users can vary from system to system. For example, you can assign a user to have full access to Server A, but limited access to Server B. This type of functionality really helps ensure that only people who need access to certain data have it.
Now that we've gone through the basics let's look at how to properly create a CMS.
How to Create a Central Management Server
1. Using SQL Server Management Studio, open up the View Menu. Then click on Registered Servers.
2. In the Registered Servers section, expand the Database Engine. Then perform a right-click on Central Management Servers. Finally, click on the Register Central Management Server button.
3. You will now find yourself on the New Server Registration screen. Select which SQL Server you want to make the Central Management Server. All servers should appear in the drop-down list. You'll have to use Windows Authentication for the CMS.
4. Enter a Server Name and Description. The description is optional.
5. Select the Connection Properties tab. Then click on Review or Modify the Network.
6. It's time to test the connection, so click on the Test button.
7. Once the connection is verified, click Save. The instance you selected in Step 3 will now be listed under the Central Management Servers folder.
8. Next, we want to create a new server group. Select New Server Group and then click Add Servers.
9. Create the new server group and then Add Servers to the group.
10. From the Registered Servers screen, expand Central Management Servers. Then perform a right-click on the instance server you selected in the previous step. Select New Server Group from the list.
11. Enter a name for the group and put in a description. The description is optional.
12. You should still be on the Registered Servers screen. From here, click on the New Server Registration button.
13. Select an instance of SQL Server and add them as part of the group. You can do this with as many servers as you want.
Note: Once you have created your Central Management Server and created server groups, you can execute query actions on the whole group of servers at once. This is useful if you need to update something on multiple servers.
Outsourcing the Creation of Central Management Servers
Again, this process requires quite a bit of technical expertise. Some of you might not have this unique skill set, so you'll need to outsource it. Fortunately, there are a lot of options here. Just remember to check the qualifications of a firm before allowing them access to your information. Furthermore, backup all information before they start, just to be safe.
