How to add the dedicated Configuration Server in the Centralized Management Solution

I’ve been working lately on so many different presentations and issues, that I rarely have time to take a note to what I’ve wanted to write you about. Anyway, I managed to find some free time and a very interesting case to share Smile – how to add an instance, that is dedicated as Centralized Management Instance (Configuration Server) to the registered instances so this instance can actually be part of the entire CMS environment. The default state of SQL Server does not let you register an instance in the CMS if this very instance is the same as the Configuration one i.e. if I have an instance SQLINSTANCE3 on my server, that is dedicated as CMS – I cannot register it in the CMS itself:

image

The error itslef is: “You cannot add a shared registered server with the same name as the Configuration Server. (.Net SqlClient Data Provider)”

As I really do not have the opportunity to use another instance that is just dedicated for Configuration Server and the solution I am implementing is working only with CMS Groups for evaluating policies, I needed some option that I can include the CMS Instance in the CMS groups.

Well this turned out to be very easy Smile – just create an Alias in the SQL Server Configuration Manager that is pointing to the dedicated CMS instance and then register the Alias in the CMS. Yep…that easy!

Now I am able to evaluate policies on all of my instances without loosing the CMS Configuration Server, which also should be evaluated as it is a full-blown instance after all!

Advertisements
By Ivan Donev Posted in DBA Tagged

SQL Server CMS – complete solution

While I was preparing a small presentation on SQL Server centralized management I came across a very elaborate solution on the subject, posted in Codeplex. The solution incorporates three different techniques for gathering, storing and presenting information for a complete environment. The solution is based on Policy Based Management, Management DataWarehouse (Data collection), MAPS tool (Microsoft Assessment and Planning Solution Accelerator) and Report builder for reporting purposes.

The solution provided in Codeplex can be easily modified, expanded and/or rebuild according to the environment specifics but in general it provides you a quick way of implementing a Centralized Management Solution without having to invest in third party tools but use only already installed SQL Servers.

Here is the link to the solution. There is also a very good presentation provided with the .zip file, so enjoy.

How to write multi-server queries in SQL Server Management Studio

This post will show you in few quick steps how to write multi-server queries and what are the pitfalls where you should pay more attention.

Multiserver queries are something introduced in SSMS since SQL Server 2008 and can be very useful in many cases, especially in large environments with more that 50 instances for example.

Before you start using multiserver queries you should make sure you have SSMS 2008 or SSMS 2008 R2 installed. Then you should register all servers you wish to manage (find a related post here). If you have already done that, then go to Local Server Group (for sake of easiness of my demonstration I will not use a registered management instance and you should consider everything written here to be valid for both cases – with management server and with locally registered servers), right click on it and click on New Query button. Then a new query window is open which is the same as the one used to query a single instance but with one difference – before being able to execute a query, the management studio will try to connect to all registered instances so be careful and patient.

image

After writing a query and execute it what happens is that the query is executed on every instance and when the result is returned, the studio is merging it, adding one additional column to the returned dataset – the one with the server name as registered in the studio (not @@SERVERNAME but the name in the Registered Server window of the studio)

Common pitfalls and points to be noted:

1. Write queries valid for all engine editions of the group of server against you are running the multiserver query. If it is not compatible and error is raised and result form this server is not included (the query is returning results after all but not from all servers)

2. If you are creating objects in your query/script, be careful and perform a clean-up because this object is created in all servers!

3. If the first server from the list to be queried returns error, then the studio will not show any results (this is addition to point 1. above)

 

There are some properties that can be edited in the studio concerning returned results. Go to Options –> Query Results –> SQL Server –> Multiserver Results

image

There are three options which basically speak for themselves so no need of additionally describe them.

So, after writing so much theory, it is time to simply provide you a quick query, which can quickly provide you an overview of the environment you are managing.

 
   1: SELECT     

   2:        SERVERPROPERTY('ProductVersion') as [Product Version]   

   3:       ,SERVERPROPERTY ('ProductLevel') as [Product Level]   

   4:       ,SERVERPROPERTY ('Edition') as [Edition]   

   5:       ,SERVERPROPERTY('EngineEdition') as [Engine Edition]   

   6:       ,SERVERPROPERTY('IsClustered') as [IsClustered]

Enjoy and stay tuned for my next posts where I will summarize my research on extracting information about database backups, integrity checks, maintenance plans and SQL Server configuration.

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.