August 18, 2014 at 12:03 am
Comments posted to this topic are about the item Automate Your Central Management Server Registrations
August 18, 2014 at 1:49 am
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.
August 18, 2014 at 9:18 am
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.
August 18, 2014 at 9:53 am
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.
August 18, 2014 at 9:56 am
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!
August 18, 2014 at 12:29 pm
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
June 9, 2015 at 5:46 pm
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.
June 10, 2015 at 7:46 am
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.
January 20, 2018 at 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
January 26, 2018 at 2:54 pm
thaakurpratap28 - Saturday, January 20, 2018 2:01 PMCould 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!
October 4, 2019 at 9:26 am
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply