Auditing SQL Server groups: Part 1 of ?

There are a few underutilized sides of SQL Server, especially when it comes to monitoring and performance tasks: PowerShell and Central Management Server (in SQL Server 2008 and up). Makes me think for a minute, though: why are they really under-appreciated? Maybe there is a reason? Well, PowerShell is the next great tool, but the learning curve of it… (oh my, oh, my, my 🙂 ). Trust me, though, by the time you are done reading this article, you will be slightly more convinced to invest precious time and efforts in learning PowerShell scripting. CMS (Central Management Server), on the other hand, is very simple to learn and use, however it requires a bit of investment: it runs on SQL 2008 and up; It is best if it does not run on a production server; it is potentially dangerous, since it gives opportunity to DBAs (and anyone with access to the CMS SQL Server!) to run bulk queries to multiple SQL Server instances. The good news is that even though CMS is a SQL 2008 feature, it can connect to any version of SQL Server. Now, let’s get to business. I will describe in this article how to utilize the power of CMS and the flexibility of PowerShell to monitor our (hundreds of) servers. Keep in mind that the following is more of a concept than a finished project, and at the end of the article I will summarize the general ideas and some possible further development paths. First, let’s setup our CMS. Take the following steps:

1. Connect to your instance of SQL Server 2008. My instance is called ALF, so make sure to replace the instance name to suit your needs in the scripts below.

2. Open the ‘Registered Servers’ tab from the View menu, or by pressing Ctrl+Alt+G

3. Right-click on the Central Management Server node and register your server. In simple words, your Central Management server is a central location which keeps a list of other servers, which can be grouped, monitored, queries can be executed per group or to a server at a time, Object Explorer can be launched by couple of clicks. Most importantly, any other DBA who connects to the CMS will see all servers added to it.

4. After registering the central server, add some groups; let’s say you need a group for Test servers and for Prod servers. In my case I have created only one group, called SQLTes1, so make sure to replace the group name according to your CMS setup.

5. Add some servers to your groups.

Before we continue further, here is a T-SQL script which will get the server names per group in our CMS:

SELECT distinct case convert(int,(charindex(”,s.server_name))) when 0 then RTRIM(LTRIM(s.server_name)) else isnull(substring(s.server_name,0,charindex(”,s.server_name)),”) end FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] s INNER JOIN msdb.dbo.sysmanagement_shared_server_groups ssg ON s.server_group_id = ssg.server_group_id — replace the name with your CMS group where ssg.name = ‘SQLTest1’

Now let’s get the PS script setup. What I would not like about the PS in this case is that it is saved on the filesystem as a text file, but still – if someone without permission gets to your PowerShell script on your filesystem, I am sure that you have much bigger problems than that… Let’s create a folder in our C: drive, called PowerShell. In this folder we will create a text file called getDiskSpaceCMS.txt. After we create it, we will open it, and will paste the following text in it:

# getDiskSpaceCMS.ps1 param ( #Get the parameters in #CentralManagementServer is the server you use to group your servers [string]$CentralManagementServer  = “local”, #ConfigurationGroup is the name of the group you would like to get information about [string]$ConfigurationGroup = “test” ) function main($serverName) { Get-MSSQL-DiskSpace $serverName } function Get-MSSQL-DiskSpace($serverName) { # unitOfMeasure could be KB, MB, GB, TB, PB; for bytes use empty string [string] $unitOfMeasure = ‘MB’ #get the volumes by querying the wmi $vols = Get-WmiObject -computername $serverName -query “select Name, DriveType, FileSystem, FreeSpace, Capacity, Label from Win32_Volume where DriveType = 2 or DriveType = 3” foreach($vol in $vols) { # Get the drive type [string] $driveType = switch ($vol.DriveType) { 0 {‘Unknown’} 1 {‘No Root Directory’} 2 {‘Removable Disk’} 3 {‘Local Disk’} 4 {‘Network Drive’} 5 {‘Compact Disk’} 6 {‘RAM Disk’} default {‘unknown’} } [string] $drive = “Drive: {0}, {1}, {2}, {3}” -f $vol.name, $driveType, $vol.FileSystem, $vol.Label [string] $capacity = “Capacity: {0}{1}” -f [System.Math]::Round(($vol.capacity / $(‘1’ + $unitOfMeasure)), 0), $unitOfMeasure [string] $freeSpace = “Free Space: {0}{1}” -f [System.Math]::Round(($vol.FreeSpace / $(‘1′ + $unitOfMeasure)), 0), $unitOfMeasure Write-Output $drive Write-Output $capacity Write-Output $freeSpace Write-Output “” } } function LoopThroughCMSGroups { # Create recordset of servers to evaluate $sconn = new-object System.Data.SqlClient.SqlConnection(“server=$CentralManagementServer;Trusted_Connection=true”); # Query the CMS to get server names in the group $q = “SELECT distinct case convert(int,(charindex(”,s.server_name))) when 0 then RTRIM(LTRIM(s.server_name)) else isnull(substring(s.server_name,0,charindex(”,s.server_name)),’a’) end FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] s INNER JOIN msdb.dbo.sysmanagement_shared_server_groups ssg ON s.server_group_id = ssg.server_group_id where ssg.name = ‘$ConfigurationGroup’ ” $sconn.Open() $cmd = new-object System.Data.SqlClient.SqlCommand ($q, $sconn); $cmd.CommandTimeout = 0; $dr = $cmd.ExecuteReader(); # Loop through the servers while ($dr.Read()) { $ServerName = $dr.GetValue(0); #foreach ($ServerName in $q) #   { write-output $serverName main($serverName) #   } } $dr.Close() $sconn.Close() } LoopThroughCMSGroups

After saving the text file, close it and change the extension from .txt to .ps1. Now let’s execute the script. This script will run on both PS1 and PS2. You can either run the SQL PowerShell or the PowerShell on your server. Run the following command:

SL “C:PowerShell”

This command will change the directory to the on where we saved our script. After that, run the following command:

.getDiskSpaceCMS.ps1 –CentralManagementServer “alf” -ConfigurationGroup “SQLTest1”

This command will start the PS script and will connect to the servers in the SQLTest1 CMS group and will use WMI to enumerate all drives (including mountpoints!!!!! ) and will print out the capacity and the space remaining on each drive. I have tried to write some comments in the PS script above, so I will not go in details here. I would recommend using the Quest Power GUI to edit your PS Scripts. Or Notepad++. In either case, I recommend reading more about PowerShell and CMS.

To summarize: you have seen how easy it is to group servers in the CMS and to query them by group. You have also seen how easy it is to write a PS script which uses the CMS groups to execute commands and queries. Of course, the above script has a lot of potential: here are a few directions to explore. How about having a similar PS script which runs as a scheduled job and imports the result in a database? How about a generalized PS script which pics up a set of WMI and SQL scripts and executes then on a schedule and imports the results in a database, which later on is used to send notifications to the IT people or to DBAs and warn them for potential problems. Furthermore, how about a PS script which takes as a parameter one or more CMS groups and runs the scripts to them.

/Feodor Georgiev