SQL Server Centralized Management–registering multiple instances at once

I have decided to dig into the field of centralized management of large amount of server so I am starting a series of posts in which I will just share my struggles in the area. I do not pretend neither to provide you the best solution, nor to be the most useful, bullet-proof, etc.

The idea came while I was supposed to investigate numerous servers for existing backups and as it seems there is not an easy way to do so without using a centralized management technique. A have heard of SCOM, SCCM, different third-party tools but still SQL Server has also their own suggestion – Centralized Management Server (and its local version of Locally registered servers). It is not discovering the electricity, just as of SQL Server 2008 it really gets useful. Let’s suppose you administer a hundred instances and you want to check last backup of all system and user databases or to check what are the server memory settings or whatever else you want. The easiest would be to just write a query, connect to each instance, execute it, copy the result and after 100 executions – summarize the result. Well…I am not lazy but not crazy too! In SSMS 2008/R2 you can register more than one instance and then run a multiserver query. This means that you execute a single query and as a result you have merged results from all servers. Well…life is easy (I guessed)…

A good reading for an introduction is the following article: http://sqlserverpedia.com/wiki/Central_Management_Server (please, watch and the video as Brent Ozar is quickly explaining what is that SQL CMS, how to use it and some other good points of the subject)

In this post I will dig deeper into registering a large amount of server (as doing it by hand is not very desirable (especially for 100+ instances). So – the trivial task of registering a single server is (I will show it in local context, the same goes and when you have a dedicated CMS):

1. Open SSMS (SQL Server Management Studio 2008 or 2008R2)

2. Open Registered Servers window (Ctrl+Shift+G)

3. Right click on Local Server Group and click on New Server Registration

image

4. Fill in required information

image

5. Click Save and you have your server registered.

So far, so good, but what happens if you have 100 instances to register?!? Well you can export and import server lists into CMS or local server group so I decided to simulate an export file and then import it. The exported file is an XML file with .regsrvr extension. I wrote a query that generates the XML file based on a table, which contains all instances to be registered. Find below the DDL for the table:

CREATE TABLE [dbo].[AllInstances](

    [RegName] [varchar](50) NOT NULL,

    [Address] [varchar](100) NOT NULL

) ON [PRIMARY]

- RegName is the name, that to be displayed in SSMS

- Address is the instance name which to be used for connection

After inserting all instances, run the following query and the result save to an regsrvr file.

--initial header

PRINT

'    <?xml version="1.0"?>

    <model xmlns="http://schemas.serviceml.org/smlif/2007/02">

      <identity>

        <name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>

        <baseURI>http://documentcollection/</baseURI>

      </identity>

      <definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns="http://schemas.serviceml.org/smlif/2007/02">

        <document>

          <docinfo>

            <aliases>

              <alias>/system/schema/RegisteredServers</alias>

            </aliases>

            <sfc:version DomainVersion="1" />

          </docinfo>

          <data>

            <xs:schema targetNamespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">

              <xs:element name="ServerGroup">

                <xs:complexType>

                  <xs:sequence>

                    <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />

                  </xs:sequence>

                </xs:complexType>

              </xs:element>

              <xs:element name="RegisteredServer">

                <xs:complexType>

                  <xs:sequence>

                    <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />

                  </xs:sequence>

                </xs:complexType>

              </xs:element>

            </xs:schema>

          </data>

        </document>

      </definitions>

  <instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns="http://schemas.serviceml.org/smlif/2007/02">'

 --group information for locally registered servers

 PRINT

'    <document>

      <docinfo>

        <aliases>

          <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</alias>

        </aliases>

        <sfc:version DomainVersion="1" />

      </docinfo>

      <data>

        <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">

          <RegisteredServers:RegisteredServers>

            <sfc:Collection>'

--list the info for the servers to be registered

DECLARE

    @RegName varchar(100),

    @Address varchar(100)


DECLARE List CURSOR

FOR

SELECT RegName, Address FROM AllInstances


OPEN List

FETCH NEXT FROM List INTO @RegName,@Address

WHILE @@FETCH_STATUS<>-1

BEGIN

    PRINT

'              <sfc:Reference sml:ref="true">

                <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/' + @RegName + '</sml:Uri>

              </sfc:Reference>'



FETCH NEXT FROM List INTO @RegName,@Address

END

CLOSE List

DEALLOCATE List


 PRINT

 '           </sfc:Collection>

           </RegisteredServers:RegisteredServers>

          <RegisteredServers:Parent>

            <sfc:Reference sml:ref="true">

              <sml:Uri>/RegisteredServersStore</sml:Uri>

            </sfc:Reference>

          </RegisteredServers:Parent>

          <RegisteredServers:Name type="string">DatabaseEngineServerGroup</RegisteredServers:Name>

          <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>

        </RegisteredServers:ServerGroup>

      </data>

    </document>'


--detail for all registered instances

DECLARE List CURSOR

FOR

SELECT RegName, Address FROM AllInstances


OPEN List

FETCH NEXT FROM List INTO @RegName,@Address

WHILE @@FETCH_STATUS<>-1

BEGIN

    PRINT

'    <document>

      <docinfo>

        <aliases>

          <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/' + @Address + '</alias>

        </aliases>

        <sfc:version DomainVersion="1" />

      </docinfo>

      <data>

        <RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">

          <RegisteredServers:Parent>

            <sfc:Reference sml:ref="true">

              <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>

            </sfc:Reference>

          </RegisteredServers:Parent>

          <RegisteredServers:Name type="string">' + @RegName + '</RegisteredServers:Name>

          <RegisteredServers:Description type="string" />

          <RegisteredServers:ServerName type="string">' + @Address + '</RegisteredServers:ServerName>

          <RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>

          <RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>

          <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>

          <RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @Address + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>

          <RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>

        </RegisteredServers:RegisteredServer>

      </data>

    </document>'



FETCH NEXT FROM List INTO @RegName,@Address

END

CLOSE List

DEALLOCATE List

PRINT

'  </instances>

</model>'

After doing this you will be able to import the configuration and have all the server registered.

Please note that there are no groups defined and the script can definitely be optimized…developers are welcome to play with it and provide even a better solution including group information.

In another post I will show you some multiserver query examples, SSMS options for multiserver queries and anything else that I have come across while working with centralized management.

About these ads

17 comments on “SQL Server Centralized Management–registering multiple instances at once

  1. Pingback: How to write multi-server queries in SQL Server Management Studio « Yet another SQL Server blog

  2. Thank you. The article is perfect except that the result failed to be imported because of the name ‘ManagedServer1′ in the script. As a quick remedy, we just change the line to

    /RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/’ + @Address + ‘

  3. A bit later, but another thank you :) I was really dreading doing the import of almost 300 instances one by one and this saved my bacon! The only issue I had was the first line printed:

    The white-space kept causing errors for me so I did away with it and everything worked beautifully.

  4. Such a great article, I get back to it almoust every time when I have to configure or use mssql cm server ;)

  5. You rock, this works just fine for me, after removing the white space making the mess
    this saved my day, i have around 350 SQL server instances and the export/import stuff is not a perfect tool but at least this trick is a great stuff
    Thanks so much

  6. Hello
    Thanks a lot for this post
    i did the exact same stuff months ago
    But faced the issue that finally did not make me happy with this tool
    1/ supports only windows auth
    2/ how can i create and submit jobs against all this registered servers

    I ll be so pleased to find a way to schedule jobs
    Any clue ?

      • Yes correct Windows authentication is by design which does not necessarily means
        this is a good design.
        Master Target servers is not the option i wanted to use because has nothing about Central Management server functionality , a master server for jobs can be defined even if the server is not the one used for Central Management server.

        Let’s say you have server A and you register it as a “Central manager server”
        then you inside this one you create a server group named : MONITOR
        you register server B,C,D,E in this group (in reality i have more than 300 SQL servers)
        Then you can right click this group MONITOR and connect to all the servers of this group in one shot and then execute queries against all the servers in this group.
        Why can’t we automate this process in a job that would be in the server A
        Is there a way to achieve this ??
        or the only way to run queries against a big number of SQL server is to create linked servers for each of them in server A and then create a job in server A to run OPENQUERIES for each linked servers ??

        i really appreciate all the comments because my intent is to learn.
        Thanks for all your help

      • I completely agree with you! Unfortunately it is not my decision whatsoever. I’ve been struggling with similar problem and what I evaluated as solutions are – using PowerShell which can easily execute a query agains a list of servers provided in a CSV file and the second one – to have a local admin database on each instance, which is populated by local SQL agent jobs and then I query the database(which is existing on all instances) via the CMS.

        Would any of those work for you?

      • Hi Ivan
        I guess PS this could work for me, however i’m not really experimented here
        Would you have examples that could help me ??
        Thanks a lot for all your help
        really appreciated

      • Ivan
        Thanks so much
        These articles are great i m gonna use the scripts samples and will adapt them for my needs
        Again thanks a lot
        Very kind of you

  7. Pingback: ElijahG | Create A SSMS Export File

  8. Pingback: SQL Server – How to Create Registered Servers to Easily Manage and Query Multiple Instances at a Time | SqlServerZest.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s