Automate Your Central Management Server Registrations

  • S. Kusen

    SSChampion

    Points: 10849

    Comments posted to this topic are about the item Automate Your Central Management Server Registrations

  • liam.gavin

    Ten Centuries

    Points: 1297

    Good article, CMS is a very useful tool. Administration can be even simpler when you see that there are only actually two tables that define whole of CMS and you can insert\update\delete to these tables directly. You can also be a bit tricksy and put a value in group description that you can use as a LIKE with your standard server naming description to put similar servers into their own groups e.g. if all Europe production servers have PRODEU in the name, create a 'Europe - Prod' group with '%PRODEU%' in the group description column, running query below puts them into this group and you can run this at a later date to add any new ones that appear in your inventory system.

    insert into msdb.dbo.sysmanagement_shared_registered_servers_internal

    (server_group_id, name, server_name, description, server_type)

    select

    g.server_group_id

    , upper([ServerName] + case when [InstanceName] <> 'MSSQLSERVER' then '\' + [InstanceName] else '' end)

    , upper([ServerName] + case when [InstanceName] <> 'MSSQLSERVER' then '\' + [InstanceName] else '' end)

    , ''

    , 0

    from

    YOUR_INVENTORY_SYSTEM s

    join

    msdb.dbo.sysmanagement_shared_server_groups_internal g

    on

    upper([ServerName]) like g.description

    left join

    msdb.dbo.sysmanagement_shared_registered_servers_internal i

    on

    i.server_name = s.[ServerName] + case when s.[InstanceName] <> 'MSSQLSERVER' then '\' + s.[InstanceName] else '' end

    and g.server_group_id = i.server_group_id

    where

    i.server_name is null

    It is also worth noting that sysmanagement_shared_registered_servers_internal name column is purely descriptive so you might want to put a more user friendly name (or the DB name if servers only host one DB) so your merged results show this name.

  • CDA

    SSC Eights!

    Points: 848

    What are the current limitations / gotchas around CMS?

    Back in 2008, there was some guidance from Microsoft that CMS shouldn't group > 200 servers together. Does anyone know if this has increased in 2k12 or 2k14?

    Having said this, I'm suddenly unable to find any supporting documentation of this limitation - looking deeper.

  • S. Kusen

    SSChampion

    Points: 10849

    CDA (8/18/2014)


    What are the current limitations / gotchas around CMS?

    Back in 2008, there was some guidance from Microsoft that CMS shouldn't group > 200 servers together. Does anyone know if this has increased in 2k12 or 2k14?

    Having said this, I'm suddenly unable to find any supporting documentation of this limitation - looking deeper.

    I'm not personally aware of any limitations. We currently have 800+ instances across the various groups that I have defined. I regularly open up new queries against 350-400 at a time. Aside from a few that don't successfully connect, it has been pretty stable for me.

    I'm also using SQL 2012 Enterprise for my CMS server, if that matters.

  • S. Kusen

    SSChampion

    Points: 10849

    liam.gavin - good info to be aware of. I opted to use the stored procedures after tracing what the GUI does. In the situation I'm in, we have a separate inventory tool that we use to map systems to applications, but i agree this could be taken to another level by adding in the descriptions. Take care!

  • jeffrey yao

    SSCarpal Tunnel

    Points: 4244

    Nice to use stored procedure to add registered servers.

    But if you want to use PowerShell, you can check my article at: http://www.mssqltips.com/sqlservertip/3252/automate-registering-and-maintaining-servers-in-sql-server-management-studio-ssms/

    It can be a good complementary reading to the pure t-sql way described in this article.

    It will not only handle "Central Management Servers", it can also handle "Local Server Groups", however, I just use configure file for simplicity reason.

    Kind regards,

    Jeff_yao

  • i1888

    SSCommitted

    Points: 1806

    Hello, thanks very much for your article,

    I am wondering how do you get over crossing domain? as we have different domains and I would like to manage SQL Servers in one place.

  • S. Kusen

    SSChampion

    Points: 10849

    i1888 (6/9/2015)


    Hello, thanks very much for your article,

    I am wondering how do you get over crossing domain? as we have different domains and I would like to manage SQL Servers in one place.

    Without trusts set up between your windows account and the other domains, I don't know that it is possible since SQL authentication is not possible. See this link for reference.

    Item 3 on that page:

    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.

  • thaakurpratap28

    Grasshopper

    Points: 19

    Could you please share the SSIS package or used script  that connects to each instance in the inventory to populate the below tables data for all the servers.

    CREATE TABLE [dbo].[Servers](
        [computername] [varchar](255) NOT NULL,
        [IPAddresses] [varchar](500) NULL,
        [OSVersion] [varchar](1000) NULL,
        [OSPatchLevel] [varchar](10) NULL,
        [description] [varchar](max) NULL,
        [owner] [varchar](1000) NULL,
        [isActive] [int] NOT NULL,
        [isProd] [int] NOT NULL,
        [hasSAN] [int] NULL,
        [systemModel] [varchar](1000) NULL,
        [processorModel] [varchar](1000) NULL,
        [numofprocessors] [int] NULL,
        [RAM] [varchar](255) NULL,
        [functionalGroupName] [varchar](255) NULL,
        [insertDate] [datetime] NOT NULL,
        [OSEdition] [varchar](1000) NULL,
        [last_update] [datetime] NULL,
        [domain_name] [varchar](50) NULL,
        [last_reboot_datetime] [datetime] NULL,
        [fqdn] [varchar](255) NULL,
        [serialNumber] [varchar](255) NULL,
    CONSTRAINT [PK_ServerNames] PRIMARY KEY CLUSTERED
    (
        [computername] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

  • S. Kusen

    SSChampion

    Points: 10849

    thaakurpratap28 - Saturday, January 20, 2018 2:01 PM

    Could you please share the SSIS package or used script  that connects to each instance in the inventory to populate the below tables data for all the servers.

    CREATE TABLE [dbo].[Servers](
        [computername] [varchar](255) NOT NULL,
        [IPAddresses] [varchar](500) NULL,
        [OSVersion] [varchar](1000) NULL,
        [OSPatchLevel] [varchar](10) NULL,
        [description] [varchar](max) NULL,
        [owner] [varchar](1000) NULL,
        [isActive] [int] NOT NULL,
        [isProd] [int] NOT NULL,
        [hasSAN] [int] NULL,
        [systemModel] [varchar](1000) NULL,
        [processorModel] [varchar](1000) NULL,
        [numofprocessors] [int] NULL,
        [RAM] [varchar](255) NULL,
        [functionalGroupName] [varchar](255) NULL,
        [insertDate] [datetime] NOT NULL,
        [OSEdition] [varchar](1000) NULL,
        [last_update] [datetime] NULL,
        [domain_name] [varchar](50) NULL,
        [last_reboot_datetime] [datetime] NULL,
        [fqdn] [varchar](255) NULL,
        [serialNumber] [varchar](255) NULL,
    CONSTRAINT [PK_ServerNames] PRIMARY KEY CLUSTERED
    (
        [computername] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    In the attachment is the SSIS package that takes a servername parameter, and I am currently calling that in a loop in powershell (ps1 file included), and the stored procedure that is called by the ssis package to update the database table.

    Hope this helps!

  • Feeg

    SSCrazy

    Points: 2766

    Hi I came across this while searching on google, we are looking into automating an inventory of our ever changing environment.

    The SSIS package and Powershell script. Where are these attached. I would like to test this and amend for our environment

    MCITP: Database Administrator 2005
    MCTS SQL Server 2008
    MCP SQL 2012/2014
    MCSA SQL Server 2012/2014
    MCSE Data Management and Analytics

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply