How do you manage all your servers?
Chances are if you work with sql server you work on more than 1 instance. In my SQL Saturday sessions I always ask how many people use CMS, and to my surprise not many people raise their hand.
So how do you go about keeping track of all your servers?
Excel file on a network share? Access database with a winforms front end? Post-it notes next to the SA passwords under your keyboard?
All of these are options, just not very good ones.
now where did I put that instance?
Hey, I'm not here to tell you whether you should register them locally in an xml file with registered servers, or centrally in msdb on a central managed server
Each of these options has their pros and cons. If you're using anything less than 2008, then you can't use that instance as a CMS server. Also may god have mercy on your soul.
ok, I lied, I am here to tell you to use a CMS server. Engine specific only though.
CMS Black Magic
We use a CMS server for each domain and I can't imagine life without it. Kind of like when I discovered Amazon prime, or bought my first memory foam mattress.
The real magic of a CMS comes from being able to push jobs, or evaluate policies, on any server (targets) you want.
You can also execute T-SQL against all, or a subset of servers with either registered servers or CMS.
There are some caveats to look out for like collation differences and version specific DMVs when running queries across instances. Also security needs to be addressed. However, that is outside the scope of this post. You can find that information in the links in the first section.
hey CMS I can't imagine my life without you in it
I often use PoSh to do some heavy lifting in conjunction with folders in our CMS servers, like gathering historical data and inserting into a central repository. Or removing specific jobs from a subset of targets in a list or folder, and then adding them back later with a scheduled PoSh job.
However, sometimes I just want to know where an instance lives in our CMS server. Like when we get a list of servers that don't align in one CMS folder and need to [insert task here] on all the instances. I want to know what these instances are used for and I can get that from the folder structure and descriptions in CMS! YES!!!!
I could do this in PoSh, but that would be silly when I can do it set-based and more elegantly in a recursive CTE.
Marco!
We have a myriad of instances we support, all completely different from the next. Some are customer, some are vendor, some are internal, some are DEV/QA/TEST. Unfortunately there isn't much standardization across our domains. Some Instances are 2 folders deep, others a little more deepererer.
This actually brings me to the main point of this post. I have a simple CTE to create each groups folder structure, along with the description. I use this to join the server table when I need to know context of an alphanumeric server\instance.
I had seen a few posts online to get the immediate node folder. Derik Hammer (b|t) has a great post with case statements based on a standardized environment. Below is my go to query for for listing servers with N number of folders in a CMS.
;WITH cteCMSFolders (ServerGroupID,CMSName,CMSDescription,Parent,CMSPath) AS ( SELECT server_group_id, [Name], [description], parent_id , cast(([name]) AS NVARCHAR(2000)) as 'CMSPath' FROM msdb.dbo.sysmanagement_shared_server_groups_internal WHERE parent_id is null UNION ALL SELECT gps.server_group_id, gps.[name] , gps.[description], gps.parent_id, cast((CMS.CMSPath + '\' + gps.[name]) AS NVARCHAR(2000)) AS 'CMSPath' FROM msdb.dbo.sysmanagement_shared_server_groups_internal GPS INNER JOIN cteCMSFolders CMS ON gps.parent_id = CMS.ServerGroupId ) --join on on servers internal to join on groupid to get the full path select srv.server_name,srv.[name],srv.[description], cms.CMSPath from msdb.dbo.sysmanagement_shared_registered_servers_internal SRV inner join cteCMSFolders CMS on srv.server_group_id = CMS.ServerGroupID;