Art of the DBA Rotating Header Image

July, 2015:

Desired SQL Configuration with #Powershell – Part 2

Last week, as part of T-SQL Tuesday 68, I introduced you to two Powershell functions for validating and setting your SQL Server configurations. While useful, they depend on some sort of configuration source, which begs the question: How do we get this source? This post covers the third function that will help you create your configuration source, using an existing server’s values and allowing you to use them directly or modify them as you see fit.


The concept of Get-SQLConfiguration is simple: Get the sys.configuration values of a SQL Server instance and export them as a hash table that can be used by the other functions. The additional criterion to consider is that Test-SQLConfiguration and Set-SQLConfiguration both use the SMO properties to do this, so our configuration source must also use these names. The result is a function that uses the SMO to perform its export:

function Get-SQLConfiguration{
  ,[string[]] $Filter

$smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
$output = @()
  $configs = $smosrv.Configuration | Get-Member -MemberType Properties | Where-Object {$Filter.Contains($_.Name)}
  $configs = $smosrv.Configuration | Get-Member -MemberType Properties | Where-Object {$_.Name -ne 'Properties'}

foreach($config in $configs){
  $output += New-Object PSObject -Property ([Ordered]@{'Name'=$config.Name;

return $output


The function itself is not complex, but I did want to add one other feature to it. A simple export might be overwhelming, considering the number of configuration properties within SQL Server. Most of these will actually be left at their defaults. To aid us, I also added a Filter parameter that accepts an array of SMO configuration property names and will only export those properties.

There are a couple patterns to use this function. The simplest is to just run it for one server and use the output to check another:

$configurations=Get-SQLConfiguration -InstanceName PICARD
Test-SQLConfiguration -InstanceName RIKER -Configs $configurations

This call will essentially compare and find the differences between the two instances. If we wanted to get more specific, we could use our Filter parameter to only compare one or two configurations:

$configurations=Get-SQLConfiguration -InstanceName PICARD -Filter @('FillFactor')
Test-SQLConfiguration -InstanceName RIKER -Configs $configurations

It is also possible to store and manage these configurations by using the Get-SQLConfiguration function. Because we’re working with hash tables, we have a variety of storage options. The easiest to get started is to use the built in Export-CSV function and save our configurations as a delimited file (I like pipe, but commas will do):

Get-SQLConfiguration -InstanceName PICARD | Export-Csv -Path .\PicardConfig.cfg -NoTypeInformation

With the text file that is generated, we can go in and edit our configs, removing what we want and editing values we need to update. The result can then be stored in source control for tracking and audit purposes. We could even go as far as to load the configurations into a database table and call them from a central administrative server. There are a lot of possibilities.

The Code

While I’ve published the functions in these two blog posts, there’s an easier way to get ahold of them. These are part of my SQLConfiguration module published on GitHub, which you can download and use (along with my other SQL configuration functions). Please keep in mind this module and the code contained within should still be considered a work in progress and the functions are provided as is. The usual disclaimers apply, so be careful using it and test it thoroughly before making regular use of it. Of course, if you do find any issues, I would love to know about them so I can review and update my code.

There are many additional items that can be added to this. The largest that comes to mind is dynamic configuration values, as these functions consider your configs to be static. Items like minimum memory, maximum memory, and maximum degree of parallelism depend on the hardware configuration and there are various ways to calculate these values. There are also configurations not stored in this part of SQL Server, such as default database directories and SQL Server agent settings. These functions have plenty of room for enhancement. They’re a good start, however, to giving you additional tools to manage your environment, helping to make your SQL Server deployments more consistent and reliable.

#TSQL2SDAY 68: Desired SQL Server Configuration with #Powershell

Welcome to another month of T-SQL Tuesday, started by Adam Machanic(@adammachanic) and hosted this month by Andy Yun(@SqlBek). The topic for this month’s blog party is “Just say ‘NO’ to defaults!”, a call on what we have learned and how we manage SQL Server defaults in our environments. While you will probably find lots of posts out there on what you should or should not set your SQL Server configurations to, I wanted to share with you a post on some tools that can help you manage these configurations.

Management Overhead

Many database professionals, SQL Server or otherwise, learn very quickly that you don’t want to stick with your default settings. The database vendors usually try and set some general values that can apply to most situations, but these typically don’t last long in any enterprise. You probably have a set of configurations you change from the defaults whenever you install a new SQL Server instance.

The struggle, whether you have 5 SQL Servers or 500, is keeping these settings consistent. Sure, you can script out your changes (and should), but how do you manage the changes over time? What if someone changes a setting, how do you enforce your configurations? Or maybe someone else sets up an instance and doesn’t apply your scripts? It becomes an ugly problem to manage.

Since I’m a Powershell fan, I’m also a fan of Desired State Configuration. While the technology is still new on the scene, it’s quickly turning into an effective way to manage your server builds. When thinking about Andy’s topic and the problem of managing changes to your instance defaults, my ‘eureka’ moment was that the DSC model could easily be applied to managing these configurations. The result was three Powershell functions that provide tools you can use to control the settings in your environment.


The first function I wrote was to evaluate the configurations on an instance. To do this, I started with the assumption that I’d have a hash table of Config(name) and DesiredValue(value) pairs. Each pair would be the SMO Configuration Class property and the desired value I wanted to check. Then I would simply loop through each one and, if it did not match, I would add that to an output array. The function would then return a collection of configurations that did not match my desired state.

function Test-SQLConfiguration{
    ,[Parameter(Mandatory=$true)][PSObject] $Configs
  $smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
  $output = @()

  foreach($config in $configs){
    if($config.DesiredValue -ne $smosrv.Configuration.$($config.Name).RunValue){
      $output += New-Object PSObject -Property (@{'Configuration'=$config.Name;
  return $output

To test this, I put together a very simple pipe-delimited file of configurations I wanted to check. These configuration names had to match the SMO property names (which aren’t difficult to acquire) and the resulting file and output looks like this:



This function provides a quick view of which SQL Server configurations don’t match my desired values. What’s cool is now I can then take this function and easily run it across my entire enterprise.

With this function, I’ve removed the burden of validating my SQL Server instance configurations. Since the output is an object, there’s many flexible options for reporting and collecting the information. Writing this to a text file is a snap or uploading it to a database table for ongoing auditing.


The next step, after we’ve checked configurations, is to correct the violations. This next function works much like Test-SQLConfiguration and takes the same two parameters as Test-SQLConfiguration. The difference is that the function will now alter the value and then reconfigure the instance to apply the change.

function Set-SQLConfiguration{
    ,[Parameter(Mandatory=$true)][PSObject] $Configs
  $smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
  $output = @()

  foreach($config in $configs){
    if($config.DesiredValue -ne $smosrv.Configuration.$($config.Name).RunValue){
        $row = New-Object PSObject -Property (@{'Configuration'=$config.Name;
        $smosrv.Configuration.$($config.Name).ConfigValue = $Config.DesiredValue
        $row | Add-Member -MemberType NoteProperty -Name 'ConfiguredValue' -Value $smosrv.Configuration.$($config.Name).RunValue
        $output += $row
        if($smosrv.Configuration.$($config.Name).IsDynamic -eq $false){$reboot=$true}

  if($reboot){Write-Warning 'Altered configurations contain some that are not dynamic. Instance restart is required to apply.'}

  return $output


Because the function is built to accept a hash table of configurations, we can use the same pipe-delimited file (or any delimited file) to update the instance. Note the warning with the output. Because not all configurations are dynamic, the function will alert you if a non-dynamic configuration was changed. If you change a non-dynamic configuration, you will need to restart the SQL Service to complete the change.

Wrap Up

The last challenge is building out the configurations to check against. The third function I wrote will handle that, but I felt like covering that functionality would make this blog post to long. Next week I will cover the Get-SQLConfiguration function, additional techniques for extending these functions, and then tell you where you can get the code. Please note that this code is in a work-in-progress state, so use with caution. However, this also means that if you have any suggestions, I’d love to hear them so I can turn this into a functional tool that the community can use.

Thanks again to Andy for a great T-SQL Tuesday topic. Keep your eye on his invite blog post and the #TSQL2SDAY hashtag on Twitter for other great contributions.

Speaking Updates – July 2015

This is a short break from my usual blog posts to share some information on upcoming presentations I’m giving that might interest you.

Performance Palooza – Virtual Performance Chapter

Coming up in two weeks (July 23), I’ll be part of the annual Performance Palooza that is run by the Performance PASS Virtual Chapter. I’m pretty excited, as this will be a new session I’ve built from some work I’ve been doing on benchmarking SQL Server.


How fast do you think you were going? The only way to be sure is to have a reliable way to measure your performance. The challenge with SQL Server is that there are many aspects of your stack that you need to measure and understand. Powershell, with its ability to access and report on all these different levels, can give you a robust tool to benchmark your SQL Server. This session will cover benchmarking methodologies, the tools Powershell offers for capturing performance information, and will demonstrate how you can use them to measure performance statistics. After attending this session, you will understand how to effectively use Powershell to measure your database speed.

RSVP here if you’re interested:

Upcoming Precons

I’ve had the opportunity to present my Introduction to Powershell for SQL Server DBAs now at both SQL Saturday Albuquerque and SQL Saturday Atlanta. This has been a great time for me and I’ve already had comments from attendees of how they’ve used Powershell in their current jobs. If you plan on attending either SQL Saturday Omaha or SQL Saturday Denver, you have an opportunity to learn some Powershell and add this robust tool to your skill set.

Introduction to Powershell for SQL Server DBAs

This full day session will help build your foundation for learning and using Powershell.  While we will be focusing on using Powershell as a SQL Server Database Administrator (or Developer), much of the material will also review general use for system administrators.  By attending this training, you will gain an understanding of what Powershell is, how you can use it in your day to day management of your environments, and what specific things can be done using Powershell in a SQL Server environment.

RSVP to either of these links if you are interested:

IT/DEV Connections – Las Vegas, NV #ITDevCon

ITnDevConnections_logo_TylerOptimized_236x59I’m super excited to be speaking at this year’s IT/DEV Connections Conference in Las Vegas. This is a huge opportunity for me to share some of the useful Powershell techniques that I’ve built out in my work environments. I will be presenting two sessions:

There are TONS of great speakers at this event, so it’s a huge learning opportunity for attendees and will be a great time.

PASS Summit 2015 – Seattle, WA #PASSSummit2015

I'm Speaking Graphic_LargeThe grandaddy of ’em all. I’m honored to be part of this year’s Summit speaker line up. This is the premiere SQL Server conference and will be my fifth year in attendance. I’ll be speaking on a Powershell topic that will not only help out a lot of DBAs with their day-to-day work, but will also give you some insights in to how I think the world of database infrastructure is changing with the two magic buzzwords of “DevOps” and “the Cloud”. If you’re the kind of DBA that has to manage the struggles of deploying and maintaining SQL Servers in a highly-available environment or a large enterprise, this session can make your life a whole lot easier.

PowerShell and the Art of SQL Server Deployment

In today’s tech world, IT professionals are driven to perform tasks faster and more consistently. Automation is the watchword for our success, whether we are deploying SQL Server to private virtual clouds or public platforms such as Azure IaaS. This session focuses on rapidly creating and configuring SQL Servers, using PowerShell and desired state configuration. You will see practical examples of how to create repeatable builds of SQL Server that can be deployed in a matter of minutes. We will also review how these techniques can be applied to both public and private cloud platforms, helping to ensure success in any situation.

Even if you don’t want to attend my session, you should seriously consider registering for the Summit. It’s the largest pure-SQL Server conference in the world and has been a tremendous boost for my career over these past five years. And now I get to speak there! (How cool is that)

Checking Last Policy Based Management Execution

Contrary to popular belief, it’s not all about Powershell for me. Yeah, I do a lot with it and want to share that knowledge, but I’m still a SQL Server DBA and there are many challenges I encounter day to day where I use my other skills. Policy Based Management(PBM) is one of those areas where I need to apply this knowledge and solve problems.

A Dirty Window

My current shop makes heavy use of PBM for alerting and monitoring our environment. For more on using PBM, check out stuff by John Sterrett(@johnsterrett) or this book. It’s a very useful tool, but takes some getting used to. We have policies in place to alert on data file free space, last backups, database owners, and a host of other checks for the health and configuration of our environment. So many, in fact, that identifying what has failed can be a chore when things go wrong.

With PBM, it is very easy to know when a policy fails, but a lot more difficult to see exactly what has failed and why. Usually, the easiest way to see a failure is in SQL Server Management Studio (SSMS):


As you can see, this doesn’t tell us much. The next stop is the PBM history, which you can access by right clicking on the Management -> Policy Management node in SSMS and selecting ‘View History’:


The result opens up the SQL Server log viewer. As this is a simple example, it may not be clear to you, but the log viewer is a chore for history. If you have more than a few policies, you will have a lot of records to navigate and load, making troubleshooting extremely convoluted. When you’re a DBA looking to fix a problem, this is problematic. Fortunately, there is another way.

I Can See Clearly Now

Most of the information in SQL Server is stored in views. The SQL Server team has gone to great lengths with the Dynamic Management Objects (DMVs) to make sure most of the information displayed through the GUI is stored somewhere behind the scenes as a queryable object. With PBM, these system views are found in MSDB with the name dbo.syspolicy*. I dug in and found the following views that had the information I was looking for:

The query pattern from there is pretty typical: Show the most recent set of history records for a policy. The result is the following query:

;with last_pbm_execution as (
    ,max(history_id) as history_id
    end_date > GETDATE()-1
group by
    ,case h.result when 1 then 'Success' else 'Failure' end result
from msdb.dbo.syspolicy_policies p
    join msdb.dbo.syspolicy_policy_execution_history h on (p.policy_id = h.policy_id)
    join last_pbm_execution lpe on (h.policy_id = lpe.policy_id and h.history_id = lpe.history_id)  
    left join msdb.dbo.syspolicy_policy_execution_history_details d on (h.history_id = d.history_id)
order by

This query gives me a quick, easy to read report for the server of each policy and what the last result was. In the event of a failure, the target_query_expression, exception, exception_message, and result_detail columns give me the info about why the policy failed, helping me to fix it.

As DBAs we need quick answers and need to spend as little time as possible wading through logs of what went right just to get at what went wrong. This can be exacerbated when a tool doesn’t have good reporting right out of the box. Within SQL Server, we can overcome this by understanding the system views and the information stored there. That knowledge will help us get those quick answers, accelerating our troubleshooting and allowing us to make effective use of our tools.

#Powershell and SQL Server: SQLPS Challenges and Hurdles

Over the last two posts, I’ve covered what the SQLPS provider is and how you can get started with it. Now it’s time for other side of the story. If you’ve started working with the provider or done some reading on the web, various hurdles have probably popped up. These hurdles can frustrate and discourage you, so let’s discuss them before that happens.

Slower than Molasses in January

The initial complaint you’ll usually see with the provider is that it’s slow. Mind numbingly slow. So slow that it breaks tab completion in the ISE. Unfortunately, due to the way the provider was implemented using the SMO, we don’t have a lot of options

What can we do? Unfortunately, there are no magic workarounds to implement. This limitation is why you will see a lot of Powershell/SQL development either use the SMO directly or implement .Net code that avoids this completely. The challenge here is that these approaches can be too advanced for administrators who are not used to code development. My recommendation is to be patient with it. The provider is your entry point and is your starting point. As you get more comfortable with Powershell, you will find SMO and other .Net methods will perform better for you. Begin with understanding the language.

What You Least Expect

Another challenge with the provider is that sometimes it will behave in a fashion that doesn’t quite make sense. As with the speed, we’re tied to how the SQL Server team at Microsoft implemented the provider. It is, after all, an API and this means we can only use the it as it was written. What I struggle with is that there there are elements of the provider that weren’t thought all the way through and occasionally they catch me by surprise.

One example of this is an interesting piece of behavior I discovered recently with a several online colleagues (Paul Timmerman, Chris Sommer, Derik Hammer, and Johan Bijnens). Basically, we found that there was no real way to refresh a Central Management Server listing within an existing Powershell session. Once a CMS registry was populated in your provider session, you were stuck with it. If you made changes to your CMS in SSMS, you wouldn’t see those changes propagated until you opened a new Powershell session.

While frustrating, it’s not a show stopper. The challenge, however, is that there are other little land mines like this in the provider and they pop up when you least expect it. This particular issue is not really a problem because you can always save a script and re-open your host. However, it should behave a little more consistently and at least give the user the opportunity to execute a manual refresh of the cached object. I’m hoping that the SQL Server team will be able to address this in future versions of SQL Server.

The Mini-Shell

SQLPS_warts_1Speaking of things that behave not as you would expect, I want to cover the notorious mini-shell. If you have ever right clicked in Management Studio Object Explorer, you’ve probably seen a Start Powershell option.

The intent of this option is to open up a command window with the SQL provider already loaded. This shell also duplicates what SQL Server does if you ever run a Powershell script within a SQL Server Agent job step. What catches people by surprise is how it behaves. In SQL Server 2012 or prior, the host machine will load using Powershell version 1.0 or 2.0, no matter what version you have installed on the host machine. This can be annoying because modules won’t be automatically loaded and some cmdlets aren’t available to you. This was fixed in SQL 2014, so whatever version is installed on the host machine will be used.

While this can present a hurdle for developing scripts to be used in the SQL Agent, it will not block you completely. You can always double check this by starting the Powershell task in Management Studio and interrogating the $PSVersionTable system variable. Beyond that, I haven’t had much reason to use the mini-shell for any Powershell tasks and prefer to work in the ISE or the standard Powershell command window host.

A Fixer-Upper

The SQLPS provider is klunky. It suffers from a little neglect by the SQL Server team and some slapdash coding to implement it. In light of some of the Powershell tools provided for Windows Server, Exchange, and Active Directory, it’s pretty frustrating to see the SQLPS provider languish the way it has. I have some theories around that and I hope that someday I can talk with the product team to see what changes can be made.

However, it should be noted that if you’re just getting started with Powershell, the SQLPS provider is still the best place to start. If you’ve read my other posts, you know by now there has been an effort to make the provider an intuitive path for building automation around SQL Server. Don’t let the hurdles stop you,. The provider will let you get far enough on its own and help you develop patterns that you can later improve and enhance.

Please feel free to leave any comments below on your experience with the provider and other questions you may have regarding it.