12 Days of dbatools – Second Day2 min read

powershell logo3


On the first day of Christmas dbatools gave to me: Find-DbaCommand

On the second day of Christmas dbatools gave to me: Get-DbaCmsRegServer


Command Information:

On the second day of Christmas we are going to build upon what we learned and how to scale the use of the commands in dbatools.  I am sure like me you do not just manage one SQL server.  If you do, dbatools is still an awesome tool for you.  However, in my opinion where it shines is that it takes the one command you are interested in and scales it to small, medium and enterprise size environments.  Lets say you wanted to see if the SQL Server Agent was running on all your servers.  Are you going to RDP in to each one and open Configuration Manager to see it running, no.  Are you going to open MMC on your local computer and remote connect to the services one-by-one, no.  You are going to use the Get-DbaCmsRegServer command to populate a server variable that can be passed to another dbatools command Get-DbaSqlService and see the current status of every SQL Server Agent in your environment.

 Command Description:

Gets list of SQL Server objects stored in SQL Server Central Management Server (CMS).

Command Example:

This will return all servers that have been registered in my CMS on my local SQL 2017 instance.

 Advanced Command Usage:

Now that is all good and fancy you can see what is stored in your CMS.  Now let me show you how to combine this output with a command that will be helpful.

 What we see is that we were able to get our list of servers from CMS then using the Get-DbaBuildReference command see what version of SQL Server is running on each machine.  Now we can see that we have some SQL patches that need to be applied in order to get our environment to the latest and greatest SQL goodness.

Leave a Reply

Your email address will not be published. Required fields are marked *