m82labs   about   posts   categories   feed

Automation Basics with Powershell and CMS

T-SQl Tuesday Logo
by Mark Wilkinson · September 12, 2017

As a DBA I use PowerShell every day. If it can be automated, I will automate it. In my environment I have 300+ instances to keep track of and maintain. When you manage that many instances, it’s important to have a “source of truth” for a full list of your instances. This is where Central Management Server (CMS) comes in. The combination of PowerShell and CMS is a powerful one. In this post we’ll explore a PowerShell function I wrote to retrieve servers from my CMS server.

I want to thank Rob Sewell (WWW/Twitter) for hosting this months T-SQL Tuesday! If you are interested in learning more about T-SQL Tuesday, take a look at Rob’s post, and check out the #tsql2sday hashtag on Twitter.

Central Management Server is an old feature, and easy to configure in Management Studio. CMS allows you to create a list of your instances on a central server. You can group your instances and, in Management Studio, run queries across multiple instances at a time. To learn more about CMS, head over to: Microsoft Docs.


Get-CmsHosts is a function I wrote as part of a custom PowerShell module we maintain internally at my employer. It is simple to use, but is the base of most automation projects I work on.

Simple Example

PS> Get-CmsHosts -SqlInstance 'srv-' -CmsInstance srv-mycms-01

This example will connect to srv-mycms-01 and return a distinct list of instance host names registered with that CMS server that start with the string srv-. This output can then be piped to other commands:

PS> Get-CmsHosts -SqlInstance 'srv-' -CmsInstance srv-mycms-01 | % {
    Invoke-SqlCmd -Query 'exec dbo.MyProc' -ServerInstance $_

In this example % is short-hand for ForEach. This will iterate through the list of instances and store the name of the current instance in a temporary variable $_. We then use that variable to run Invoke-SqlCmd. You can already see how useful this can be.

Specific Versions

It is common to use this function when we are remotely upgrading instances. In this specific use case it is very important that you only run the upgrade or patch on SQL Server running a specific version.

PS> Get-CmsHosts -Version '13.0.1605.1' -CmsInstance srv-mycms-01

This command will only return instances running version 13.0.1605.1 (2016 RTM) of SQL Server.

When using -Version, try to filter using the name as well. In order to get the version of the instance the function will connect to each one and run a query. If you have a lot of instances, this will take a lot of time.

List of Databases

Sometimes you might need to run a query of PowerShell cmdlet against all of the databases in on a given server, or group of servers. Using the -EnumDatabases you can do this:

PS> Get-CmsHosts -SqlInstance srv-server-01 -CmsInstance srv-mycms-01 -EnumDatabases | % {
    $CurrServer = $_
    $CurrDatabases = $_.Databases

    $CurrDatabases | % {
        Invoke-SqlCmd -Query 'exec dbo.MyProc' -Database $_ -ServerInstance $CurrServer

EnumDatabases will enumerate the databases on any instances that are returned. In the above example we are running dbo.MyProc against each instance, and each database on that instance, returned by the call to Cms-Hosts. When using EnumDatabases the list of databases on the instance is queried and returned with the instance. This means each instance returned by the function will have two members. One named Name that holds the name of the instance. And another named Databases that holds the list of databases. Besides upgrades, this can also be useful for tasks like setting database owners, or checking/applying a security configuration.

Configuration and Other Options

You can avoid having to pass in the CmsInstance parameter if you simply set an OS level environment variable named SQL_CMS that holds the name of your CMS instance. If you want to test this, the first example could be re-written to shows it’s use of the environment variable:

PS> $env:SQL_CMS = 'srv-mycms-01'
PS> Get-CmsHosts -SqlInstance 'srv-'

While CMS can be very useful, it isn’t always available, especially if you are testing your scripts on your local computer while disconnected from the network. For cases like this, and to keep your scripts consistent, you can pass a file path to the SqlInstance parameter. the file should contain a single instance name per line. If the function sees that a file was passed in, it will use the file contents instead of connecting to the CMS. All the other parameters still work as expected.

Get the Script!

PowerShell and CMS are a combination I use for almost all of my automation tasks. Grab a copy of the function and see what you can use it for. If you have any suggestions, or want to submit updates, please leave comments here or issue a pull request on GitHub.

You can get your copy of Get-CmsHosts at my Github repo: Post Scripts