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

4. Fill in required information

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.