Manage your environment with CMS

Question

Post reply

Manage your environment with CMS

Ryan Adams

SSC Enthusiast

Points: 173
More actions
April 29, 2012 at 2:21 am

#257132

Comments posted to this topic are about the item Manage your environment with CMS
Ryan J. Adams
Blog
Twitter

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

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

duquene.j SSC Veteran Points: 257 More actions · Answer 1

Hello,

I just want to share a work-around to allow to register the CMS as a server in the list.

The trick is to update the MSDB stored procedures dbo.sp_sysmanagement_add_shared_registered_server and dbo.sp_sysmanagement_update_shared_registered_server by commenting out the following piece of code:

--IF (UPPER(@@SERVERNAME collate SQL_Latin1_General_CP1_CS_AS) = UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS))
--BEGIN
-- RAISERROR (35012, -1, -1)
-- RETURN (1)
--END

I fully know it is not a good idea to update system stored procedures but in this case I don't see the point why this is prevented (specially as we can find tricks to work-around the restriction anyway...). So do this at your own risk.

You can see it on this posted on my blog (In French - sorry!).

Regarding the export/import, you can manage it through powershell as well.

You can find how to do it on another post of my blog (still in French but commands are easily understandable).

Cheers,

Jerome

sherifffruitfly Ten Centuries Points: 1198 More actions · Answer 2

I wasn't familiar with this before - thanks! In general, it sounds like a linked server server. 🙂

Questions:

1) Did you really say you cannot use sql service accounts with CMS?

2) Does it play nicely with other db's (oracle)?

thx,

sff

Ryan Adams SSC Enthusiast Points: 173 More actions · Answer 3

You are right about being able to alter that SP, but what happens when you install a CU or SP that changes it back? Although it can be done, it is not a good practice or reliable method. It would be better to use a workaround and use an alias when you create the connection so it looks the way you want it to.

Here is a link to a post on my blog where I go into this in more detail.

How to add your CMS server to a group[/url]

Ryan J. Adams
Blog
Twitter

Ryan Adams SSC Enthusiast Points: 173 More actions · Answer 4

1) Yes I did say that you cannot use SQL Authentication for registered servers in a CMS

2) To my knowledge it does not work with Oracle.

Ryan J. Adams
Blog
Twitter

Geoff A SSChampion Points: 11511 More actions · Answer 5

do you know what table in msdb holds the info on the designated CMS?

duquene.j SSC Veteran Points: 257 More actions · Answer 6

Ryan Adams (4/30/2012)
You are right about being able to alter that SP, but what happens when you install a CU or SP that changes it back? Although it can be done, it is not a good practice or reliable method. It would be better to use a workaround and use an alias when you create the connection so it looks the way you want it to.
Here is a link to a post on my blog where I go into this in more detail.
How to add your CMS server to a group[/url]

I fully agree with your point that's why I mentioned this is not a good practice and to do it at your own risk.

If a CU or a SP would change it back, no problem, the server would already be registered. It will only matter if you wanna update the concerned server (as the update stored procedure could have been roll backed as well).

Another point which might be blocking in case of a CU or SP would be if Microsoft would place a check constrain on the underlying table.

Do you think Microsoft plan to put time in that ?

When I looked up for the "update stored procedure solution", I was thinking about how to export/import a list of server from one box to another.

Using your workaround, you would need to replace all entries pointing to the receiving server in the exported file with the "tricked alias" and rename the entries of the source server having uncommon naming before importing it completely and cleanly.

Well both solutions, according to me, have pro and cons - it makes IT charm 😉

One question I still have in mind regarding this "restriction" is why has it been put in place and what is its added value ?

I would be glad to hear about potential side effect of working it around in anyway 🙂

Cheers,

Jerome

Ryan Adams SSC Enthusiast Points: 173 More actions · Answer 7

Jerome:

I've wondered the exact same thing. The only thing I can figure is that you have a connection to your CMS server already just by virtue of it being your CMS server. Of course that doesn't help if you need it to be part of a group somewhere else in your configuration.

Ryan J. Adams
Blog
Twitter

Ryan Adams SSC Enthusiast Points: 173 More actions · Answer 8

Geoff:

The group folders you create are stored here:

msdb.dbo.sysmanagement_shared_server_groups_internal

The registered server connections are stored here:

msdb.dbo.sysmanagement_shared_registered_servers_internal

Ryan J. Adams
Blog
Twitter

shaun.stuart SSCertifiable Points: 7033 More actions · Answer 9

Can someone explain the benefits of a CMS? From the article, it seems the main benefit is the ability to execute queries against multiple servers at once. But I can do that in SSMS - which allows both Windows and SQL logins for connections. I just need to create a group in my Registered Servers pane, right click it, and choose New Query to execute a query against all servers in that group.

Ryan Adams SSC Enthusiast Points: 173 More actions · Answer 10

You are exactly right about registered servers in SSMS. CMS is really the same thing, but instead of being local it is stored in a central location. For small shops, CMS probably does not hold much value. For large companies, however, it can be beneficial to store all your connections in the same place. This way you can be consistent and accurate. If a new server comes online, you add it to your CMS and everyone can see it instead of everyone having to manually add it to their SSMS.

The other advantages of having all of your connections in one central repository is using it with Policy Based Management. Click here to see my paper on that[/url]. You can even take it a step further by using CMS and PBM together with the Enterprise Policy Management Framework. EPMF is an open source project on codeplex that provides a data warehouse for reporting on PBM policy evalautions.

It can also come in handy with PowerShell. If you need to do something against a group of servers it's really easy to pull that group in from CMS into a PS array.

Ryan J. Adams
Blog
Twitter

shaun.stuart SSCertifiable Points: 7033 More actions · Answer 11

Thanks..Just wanted to make sure i wasn't missing something. Although, we're a fairly decent size shop, we also have untrusted domains and standalone servers, so it seems CMS wouldn't really be useful for us.

Thanks!

Ryan Adams SSC Enthusiast Points: 173 More actions · Answer 12

I work in a large shop and have the same problem with servers in a DMZ, test domains, and development domains. Those things are necessary but it requires me to triplicate everything. What I am working on now is moving the test and dev servers into the production domains. As long as the application can connect through whatever means you choose it sometimes does not matter what domain your SQL server sits in. Just because it holds development data does not necessarily mean it has to be in the development domain. I can't always move them, but I am starting to move every one of them that I can for ease of management.

Ryan J. Adams
Blog
Twitter

Jorge Serres Old Hand Points: 352 More actions · Answer 13

CMS is pretty slick but the setup is kinda' painful! it's almost what one has to do for traversing multiple servers using TSQL(sysservers) but one needs to have the linked servers created(equally painful), especially if one has to deal with over 500 instances.

The question that was brought up as an example was:"how long would it take you to find and evaluate the version of every SQL Server in your environment?"....This of course can be done without CMS as I just explained (through TSQL / Linkedservers)....

Now, Let's say that we're given the task to install 20 servers, we're required to check the Offset, Blocksize etc on all of the drives (at least 5 drives per server to hold Data/Log/TempDB/Backups etc)....So we do not have linked servers nor CMS since we have not even installed the SW(MS SQLServer) yet! So, what do we do?

The answer is POSH (Poweshell in combination with WMI). .I found that all of the CMS routines or TSQL routines traversing multiple servers, can get easily done via POSH and a lot faster, since there's no GUI that needs refresh. POSH/SQLPS is an outstanding tool that once you try it , you feel there's nothing like it.

Note: Depending on network bandwidth (10 MBPS), collecting version info for over 500 instances through POSH can take about 35 seconds. that's it!

anna.lunyova SSC Rookie Points: 35 More actions · Answer 14

Thanks Ryan, good article. I believe RedGate utilizes CMS in MultiScript tools to run a query against multiple SQL Servers. At the same time, I think there is one more piece of missing functionality in CMS (& MultiScript): you cannot schedule that script against multiple servers, can you? I cannot find how this can be done. For instance, if I need to run the same query against multiple 100+ instances at 4AM daily, while my environment is not busy. Please advise if there is a way to do so? Thanks