page contents

About the Post

Author Information

How TO: Create SQL Central Management Server and Server Group

One of the cool things that was added a few years back was the Central Management Servers feature. It was introduced in the Standard and Enterprise editions of SQL Server 2008. This feature helps DBAs to maintain multiple SQL Server instances across an enterprise environment and provides two major benefits when multiple SQL Server instances need to be affected by a specific action:

  • An execution of a specific T-SQL query against SQL Server instances registered within Central Management Servers
  • An evaluation of a set of policies on SQL Server instances registered within Central Management Servers.

After you create a central management server, you can execute queries against a whole group (single or multiple server groups) at the same time and one (or more) registered servers. Using SQL Server Management Studio, this is how you can create a central management server. (I will also show you how to add one or more server groups to the central management server and add one or more registered servers to the server groups)

Create a central management server

  1. In SQL Server Management Studio, on the View menu, click Registered Servers.


  2. In Registered Servers, expand Database Engine, right-click Central Management Servers, and then click Register Central Management Server.


  3. In the New Server Registration dialog box, select the instance of SQL Server that you want to become the central management server from the drop-down list of servers. You must use Windows Authentication for the central management server.


  4. In Registered Server, enter a server name and optional description.


  5. From the Connection Properties tab, review or modify the network and connection properties. For more information, see Connect to Server (Connection Properties Page) Database Engine
  6. Click Test, to test the connection.


  7. Click Save. The instance of SQL Server will appear under the Central Management Servers folder.


Create a new server group and add servers to the group

  1. From Registered Servers, expand Central Management Servers. Right-click the instance of SQL Server added in the procedure above and select New Server Group.


  2. In New Server Group Properties, enter a group name and optional description.


  3. From Registered Servers, right-click the server group and click New Server Registration.


  4. From New Server Registration, select an instance of SQL Server. Click Test, to test the connection and then Click Save.


  5. The instance of SQL Server will appear under the Central Management Servers folder. Add more servers as appropriate.


  6. Now you can execute queries against the whole group


  7. I ran this query


— Change backup compression to be on

sp_configure
‘backup compression default’, 1 reconfigure

  1. Here are the results


For more information, please see the following: Create a Central Management Server and Server Group (SQL Server Management Studio)


Tags: , , , , ,

5 Responses to “How TO: Create SQL Central Management Server and Server Group”

  1. tlb #

    Scott…sorry to post this here but couldn’t find a comment area on the blog entry I think needs to be fixed (see: http://www.smattie.com/2012/04/02/having-an-issue-trying-to-copy-user-profiles-in-windows-7/).

    There is a problem with the “Windows Enabler” application links in the blog entry for copy-user-profiles-in-windows-7. The “bluemoonpcrepair” is now a a malware site. softpedia.com has a legitimate download option for “Windows Enabler” (I did a test download and scanned using MalwareBytes AntiMalware and my antivirus getting a clean scan on both).

    I hope you can change the “Windows Enabler” app links in your blog entry because I like to refer people to it as it’s very helpful…

    Thanks!

    February 12, 2016 at 9:03 PM
    • Scott Mattie #

      Thanks, it looks like the original author of this tool has not kept his website up so I had to find it elsewhere… here is the link: http://www.softpedia.com/get/Others/Miscellaneous/Windows-Enabler.shtml

      February 12, 2016 at 10:08 PM
      • tlb #

        Hey Scott…

        Yes…excactly–I about had a heart attack when I was checking the links in my own PDF. I’m also using the softpedia.com download link in my PDF on copying user profiles.

        I hope you will have time to update the “Windows Enabler” app links in your having-an-issue-trying-to-copy-user-profiles blog post. As I noted, I like giving that blog post link to people because I found it so helpful.

        By the way…I sent you a message via your “contact us” link and I wasn’t sure it you received it which is why I posted in the Comment section of this blog post.

        Please feel free to delete my comments as they are not exactly on topic…’-}}

        February 12, 2016 at 10:41 PM
      • tlb #

        Hey Scott…

        Just checked the copy-userid blog and see that you’ve made the changes to the Windows Enabler app–should have check before I sent my last reply…duh…’-}}

        Thanks so much for getting the updated link in so quickly. I will be adding the link to your blog post in m PDF…

        Again, please feel free to delete this comment as it is also not on topic…’-}}

        February 12, 2016 at 10:54 PM
      • tlb #

        Hey Scott…

        Just checked your copy-user-profile blog and see that you’ve made the changes to the Windows Enabler app links–should have check before I sent my last reply…duh…’-}}

        Thanks so much for getting the updated links in so quickly. I will be adding the link to your blog post in my PDF…

        Again, please feel free to delete this comment as it is also not on topic…’-}}

        February 12, 2016 at 10:56 PM
Copy Protected by Chetan's WP-Copyprotect.