Managing Server Config Data

You have the basic script-building skills, so let's build a script that retrieves and stores SQL Server configuration data.

• By Joshua Jones
• 10/19/2009

My last few columns have dealt with the basics of setting up PowerShell and creating a script. Now, this series will cover a few specific areas where PowerShell scripting can help a DBA/SQL Server administrator automate some common tasks in PowerShell.

Let's start by looking at how to retrieve and store SQL Server configuration information. This can be a quick and easy way to audit your servers, and will be flexible across different versions and editions of SQL Server. Here's the data we'll be gathering:

Server Name Windows Edition/Version SQL Edition/Version Server Settings Some key items from sp_configure (clr enabled, Database Mail XPs, xp_cmdshell)

This script will take the info, and shove it into a SQL Server table (in an Admin database). Here's the rather simple table that the script below works with:

use Admin go create table ServerInfo
(    ServerID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,    
     Name sysname NOT NULL UNIQUE NONCLUSTERED,    
     SQLVersion varchar(30),    
     SQLEdition varchar(30),    
     WindowsVersion varchar(50),    
     clrenabled bit,    
     DatabaseMailXPs bit,    
     xp_cmdshell bit
)

Notice that I am managing duplicates the easy (aka cheap) way; a UNIQUE constraint on name. In a larger environment, you'll likely need to think about situations where you have multiple instances, and handle the data fields appropriately.

First, let's build a script to retrieve data from a single server. Fig. 1 shows the version 1 script.

Figure 1. PowerShell script for retrieving data from a single server. (Click image to view larger version)

Now let's break it down into some components.

The first line is simply loading the correct namespace:

#make sure we are loaded correctly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null

This is actually only necessary if you are running the script from the vanilla installation of PowerShell. If you're running it from SQLPS.exe, this line can be left out. I have it here because I was using the PowerShell_ise.exe editing environment (which doesn't automatically use the SQLPS extensions).

Then we have the meat of our work. First, we need to declare some server level variables and point them to the correct namespace so that we can step through the properties as needed. We also need to define which server we are working with:

#Create and populate server variables
##Connect to the SMO and set a "server" objects.
$servername = 'JJONESLT7'

$serverinfo = new-object ('Microsoft.SqlServer.Management.Smo.Server') $servername
$wininfo = get-wmiobject Win32_OperatingSystem -computername $servername

Notice that we're setting the $serverinfo to the SQL Server SMO namespace, but for Windows info, we're querying WMI directly. This is a personal preference, because I don't like the Windows information available in the SQL SMO. You could easily use the property OSVersion and get the numeric version number. I prefer the plain English value.

Next, we set our values.

#grab values from the server SMO object
$winver = $wininfo.Caption
$sqled = $serverinfo.Edition
$sqlver = $serverinfo.Version
$clrenabled = $serverinfo.Configuration.IsSqlClrEnabled.RunValue
$dbmail = $serverinfo.Configuration.DatabaseMailEnabled.RunValue
$xpcmdshell = $serverinfo.Configuration.XpCmdShellEnabled.RunValue

While this is a small representation of the data you might actually want to collect, it gives you an idea of how to get at the values you are looking for. When looking for high level server information, we simply look at the value in the corresponding property, i.e. Edition or Version. However, for the server configuration information, we have to look a bit deeper because of how the hierarchy in the namespace works.

For the "sp_configure" type info, we have to look at the ".Configuration" space, and then reference a specific property (DatabaseMailXPs), and then pull the value out of the RunValue property. For each of the configuration properties, there are four potential values: maximum, minimum, current, and new (or running). For an in-depth exploration of the various configuration values, check out these MSDN articles:

• Configuration properties
• ConfigProperty members

Now, we can open a connection to the SQL Server of our choice, create a command and execute it to insert the data.

    #put the data we have into SQL Server    
    ## open database connection and insert our data    
    $connection = New-Object System.Data.SqlClient.Sqlconnection("Data Source=JJONESLT7; Initial Catalog=Admin; Integrated Security=SSPI")    
    $connection.Open()    
    $cmd = $connection.CreateCommand()    
    $cmd.CommandText ="INSERT ServerInfo VALUES ('$servername' , '$sqlver', '$sqled', '$winver', '$clrenabled', '$dbmail', '$xpcmdshell')"    
    ##uncomment the next line to echo the command being executed back to the console    
    #echo $cmd    
    $cmd.ExecuteNonQuery()    
    $connection.Close()

This is fairly simple for anyone who's done scripting (particularly for VB and VBScript folks), but if you're just now getting into a scripting language, here's the breakdown. First, define a connection for us to use. In this case, we're connecting to a machine named JJONESLT7, specifically the Admin database, using Windows Integrated security:

$connection = New-Object System.Data.SqlClient.Sqlconnection("Data Source=JJONESLT7; Initial Catalog=Admin; Integrated Security=SSPI")

Now, open the connection, making it available for use:

$connection.Open()

Next, we define a variable named $cmd, and tell it that we are creating an executable command:

$cmd = $connection.CreateCommand()

Let's build the command using the variables we already populated:

$cmd.CommandText ="INSERT ServerInfo VALUES ('$servername' , '$sqlver', '$sqled', '$winver', '$clrenabled', '$dbmail', '$xpcmdshell')

Note: Unlike T-SQL (and other languages), we don't need to wrap the string variables in quotes just because we are building a dynamic query. The interpreter knows how to substitute the values correctly. This is known as interpolation.

We're done, so let's clean up:

$cmd.ExecuteNonQuery() Execute the query! $connection.Close()

I left out a few lines there that are basically for troubleshooting:

    ##uncomment the next line to echo the command being executed back to the console    
    #echo $cmd

ECHO can be used to show the output of the variables you are populating in a script when you are developing it interactively. Obviously, you'd remove the hash (or pound sign) when saving this for everyday use.

Obviously, you can take this premise and expand it for your use. Next time, we'll create an iterative version of this script. Until then, have fun!