Shawn Melton

SQL Server and PowerShell

PS C:\Check-InstanceConfig (part 2)

Posted by Shawn Melton on 2013/12/09

In the previous post I discussed the data table that holds the default values for the configuration options in SQL Server 2000 up to SQL Server 2014.

In this post I will go over the function(s) that are included in the script. I will keep the same format as I did in the previous post:

  • How it was written
  • What it contains
  • A few examples

How it was written

If you have already downloaded the script you notice it had one function. I was attempting to make the output similar to what Mike did in his T-SQL script with the text output. After looking at it a bit more and thinking on best practices for PowerShell, I chose a different route. I always try to ensure whatever my functions output, you can do something with them down the pipeline. In order to keep that going I opted to break this up into separate functions. I also included a function that allows you to just pull the raw configuration options from the instance as you wish.

What it contains

There are 4 functions included in the current version of the script:

  • Pull-SqlConfig – Just pull the configuration options information on the instance
  • Check-SqlRunConfig – Compare the running value to the configured value
  • Check-SqlRunDefault – Compare the running value to the default value
  • Check-SqlBadDefaults – Compare the running value to the default value for specific options.

I decided to also include help text with each function so loading the script into your PowerShell session you can simply use the Get-Help cmdlet and it will show you the information on each function. Adding the “-full” switch to the command will output more detailed information as well.


A few examples

As mentioned above the function help information also includes a few examples of each function. So if you want to see them you can simply use the Get-Help function along with the “–Examples” switch.


Here is the full script. This link will always point to the current version of the script. My next blog post will explain the function in the script.

Posted in PowerShell, SQL Server | Tagged: , , | Leave a Comment »

PS C:\Check-InstanceConfig (part 1)

Posted by Shawn Melton on 2013/12/04

I was reading a blog post Mike Walsh published on Linchpin People last week: SQL Server Configuration Check Script.

In that script he is, as the title says, checking the configuration of a SQL Server instance for particular things like those options not set to default values and a few particular ones that still are set to default. I was intrigued by this and by the time I got to the end of the blog post was like “bet I can do that in PowerShell too”. I had a slow weekend with the holidays so thought I would sit down and see what I could come up with.

Well I came up with two specific things that I wanted to accomplish with this script:

  1. Provide a reference point to the configuration options for each version of SQL Server
  2. Provide a function that will take that reference point and validate it against any instance

If I tried to put all the information I want to help explain the script this blog post would be longer than you have the time to read through. I decided to just break it up into two parts based on the list above. So this post will just be on the reference point I wrote and hopefully by the end you will have learned the following things:

  • How it was written
  • What it contains
  • A few examples

A few caveats about the script: I did not do anything special in PowerShell that is specific to any particular version. This script should work on PowerShell 2.0 and higher. You will notice if you open this script up in the PowerShell ISE 3.0 or higher I did use regions, just a formatting feature in the ISE. It is treated as normal comment lines outside of the ISE.

One more thing: There is more than one way to do things in PowerShell, this is just the way I decided to go. As well I did not cut corners in this script. I wanted to make sure every command and variable was understood. I tried to not use any aliases with commands (e.g. “?” instead of where or where-object).

How it was written

The first section I put in the script was a comment block that contains the BOL links for the “Server Configuration Options (SQL Server)” page of each version:


I went back and forth a few times on how to actually build out a list of the configuration option names and default values based on the current versions of SQL Server that are available. I knew I needed the information in order to validate it against a SQL Server instance, no matter what version I might try. In Mike’s script he simply scripted out temporary tables with this information. So I kept to the same concept and decided to use a data table (System.Data.DataTable).

What it contains

I tried to make the columns self-explanatory so the column naming convention I chose:

  • ConfigOption – name of the configuration option in sp_configure or sys.configurations with new versions of SQL Server. The list contains those configuration options available from SQL Server 2014 to SQL Server 2000, as stated in the associated links in BOL for each.
  • Default_<4 digit year of SQL Version> – there is a column for each version of SQL Server the script will support (e.g. Default_2012). If a ConfigOption name is not supported or included in any particular version this value is set to –9 (negative nine).


An example of the –9 usage, “access check cache bucket count” is a configuration option only available in SQL Server 2008 and higher:

A few Examples

Ok, I am going to assume the reader is already familiar with how to run PowerShell scripts. I prefer the dot sourcing method in most cases. You can also simply copy and past this into your profile if you desire. Remember, Out-GridView is your friend.

  • To list out the complete data table execute this command:
    $sqlConfigDefaults | Out-GridView
  • I have variables already in the script to list out each version (reference lines 723-729 in the script). So if you wanted to list out the options for SQL Server 2014:
    $sql2014_Defaults | Out-GridView
    I could have filtered out the columns for the other versions, but I thought it to be a nice reference point to see those options that are still around from lesser versions. You can adjust this if you wish, but note that these variables are utilized in the function as well.

Here is the full script. This link will always point to the current version of the script. My next blog post will explain the function in the script.

Posted in PowerShell, SQL Server | Tagged: , , , | 2 Comments »

PS C:\> Calc-MaxSample

Posted by Shawn Melton on 2013/11/13

I am working on building a function that will allow me to gather performance counter data across multiple servers. I knew this had done by someone in the SQL Server family and went to search the inter-web. I came across Aaron Bertrand’s (blog | twitter) blog post here.

This gave me a starting point, however I also noticed that with the use of the basic Get-Counter cmdlet you provide the –MaxSamples value to tell PowerShell how long you want to collect counter information.

You would want to calculate this based on the –SampleInterval value. A few examples of this can be found in the comments on Aaron’s blog post. If you want to cover a 2 hour period and get a sample every 30 seconds, you would set MaxSamples to 240.

This is a mathematical equation that I did not want to have to keep doing every time. So I decided to build a small function that I added to my PowerShell ISE profile. I don’t use the the console all that much anymore with PowerShell 4.0. The below function includes a bit of help information as well. So, once you load this function into the console or your profile you can use Get-Help cmdlet if needed.

   Assist calculating the max sample for Get-Counter
   Calculates from the sampling value and the hours desired what the max sample value should be for the Get-Counter cmdlet
   Cal-MaxSample -secSample 15 -hours 3
   This example based on the SampleInterval and the number of hours desired output what the MaxSample value should be
.PARAMETER secSample
    The SampleInterval value to be used in Get-Counter command, Int32 value between 2 - 60. The default value for
    Get-Counter command is 1 (one)
    The number of hours desired for capturing performance counter data with Get-Counter command
function Calc-MaxSample
    [int]$calValue = (($hours * 60) * 60) / $secSample
    Write-Host "Based on following calculation: (($hours * 60) * 60) / $secSample)" -ForegroundColor Yellow -BackgroundColor Red
    Write-Host "MaxSample value = $calValue" -ForegroundColor Yellow -BackgroundColor DarkGray

Posted in PowerShell | Tagged: , | Leave a Comment »

PowerShell Tidbit #492

Posted by Shawn Melton on 2013/11/05

I do remote support work now as a DBA and have clients that provide my password for VPN login or Active Directory login. I usually access their network through a central remote server that I will use to manage their SQL Server environment. As some of you may know there is no way to change your password through remote desktop until it expires and prompts you to upon login. Depending on the version of Active Directory they are running you may get a little bubble down by the clock that you can use as well, but I tend to miss that most of the time.

So I wanted an alternative so I could change it when I needed to, as some clients do not have as strict a policy regarding password changes as I would like. I took the web and came across a nice post by the Scripting Guys here.

I was not really interested in using this as a function, just needed the commands to use. This article has the exact thing you need so go read it…

The one thing I needed to add was knowing the full AD path of my user account. You all know what yours is right?

Well you can use “gpresult /R” to get that information. All I did was put a bit of PowerShell flare into it. If you are not familiar with the results of this command go ahead and issue it on your computer, if you are part of a domain. It will give you the domain information about the computer and your current login. I like looking at this every so often to check the policies that are applied in the domain.

Anyway there are two AD paths that are returned with this command, I only want the second one. It always returns the computer account first and then your login. So this command provides me that and puts in the clipboard for me:

gpresult /R | Where {$_ -match "CN="} | select -Skip 1 | clip

Combine this with the commands from the article:

$acct = [adsi]"LDAP://<paste result here>"

You may normally see me write functions for this, but for this particular area I like to see it as it works, just to be safe. You may also see the “invoke” command take a while on some domains, just depends on the architecture I guess.

Once you hit enter on the CommitChanges command your password has been updated.

Posted in PowerShell | Tagged: , , | Leave a Comment »

PS C:\> Create-ServerDirectory

Posted by Shawn Melton on 2013/10/30

If you have to do it more than once, script it and blog about it…

function Create-ServerDirectory
        # server name or instance name of SQL Server to run assessment against
            HelpMessage='Provide a single or comma separated list of server names.' )]
    foreach ($s in $server) {
        $dirName = $s.Replace("\","_")
        $fullPath = "$location\$dirName"
        if(Test-Path $fullPath) {
            Write-Warning "The path $fullPath already exist"
        else {
            New-Item -Path $fullPath -ItemType Directory | Out-Null
            if(Test-Path $fullPath){
                Write-Host "$fullPath created successfully"
            else {
                Write-Warning "Some issue occurred creating the directory"

So a few examples:

Create-ServerDirectory –location c:\temp –server “Server1″,“Server2″,“Server1\Instance1″,“Server3″


$list = “Server1”,”Server2”,”Server1\Instance1”,”Server3”
Create-ServerDirectory –location c:\temp –server $list


<Some cmdlet that passes your server name down pipeline> | foreach {Create-ServerDirectory –location c:\temp –server $_}

Carry on…

Posted in PowerShell | Tagged: , , | Leave a Comment »

With PowerShell comes great things

Posted by Shawn Melton on 2013/10/23

I have been working with PowerShell since sometime around 2010 I think. I don’t recall the exact date because it started somewhat slowly. The previous employers I worked for were not environments I could really take advantage of it very well. I now work as a consultant so I have gotten much more involved in learning the advanced side of PowerShell.

I have developed and continue to evolve a PowerShell script that is utilized for making assessment reports on clients SQL Server environment. I have been looking to advance these scripts and have been searching out training to raise my PowerShell understanding and knowledge.

Oh boy, did I find a good one!!!

Microsoft created the Microsoft Virtual Academy back in 2011. They have gathered a decent amount of courses that you can work through, all for free!!! Now don’t go canceling your Pluralsight account or anything, they don’t have that many courses.

The jewel PowerShell course I found is right here: Advanced Tools & Scripting with PowerShell 3.0 Jump Start. This course is taught by Jason Helmick (Blog|Twitter) and Jeffrey Snover (Blog|Twitter). Now if you have been in PowerShell for a decent amount of time and researched the background you may recognize Jeffrey’s name.

Throughout the course Jeffrey’’ inserts little tidbits of information on the decision making made by the PowerShell team in designing PowerShell and version 3.0. That alone was the best part to me. However the second best part is the advanced level concepts that are taught.

If you want to step it up then take the time to go through the course.

Posted in Learning, PowerShell | Tagged: , , | Leave a Comment »

To the future…

Posted by Shawn Melton on 2013/10/17

I decided to jump into playing with Windows Azure today. I heard on Twitter yesterday that Azure had SQL Server 2014 CTP2 available. After waiting about 30 minutes I was able to login and see this:

Well the first thing I wanted to check out was the SQLPS module. Fired up PowerShell and fired off this command:

Import-Module SQLPS –Verbose

After which I ran the following command, to get a full list of “cmdlets” that are available:

Get-Command -Module SQLPS | Out-GridView

This will give you a nice list of commands to start getting familiar with:

CommandType Name ModuleName
Cmdlet Add-SqlAvailabilityDatabase SQLPS
Cmdlet Add-SqlAvailabilityGroupListenerStaticIp SQLPS
Cmdlet Add-SqlFirewallRule SQLPS
Cmdlet Backup-SqlDatabase SQLPS
Cmdlet Convert-UrnToPath SQLPS
Cmdlet Decode-SqlName SQLPS
Cmdlet Disable-SqlAlwaysOn SQLPS
Cmdlet Enable-SqlAlwaysOn SQLPS
Cmdlet Encode-SqlName SQLPS
Cmdlet Get-SqlCredential SQLPS
Cmdlet Get-SqlDatabase SQLPS
Cmdlet Get-SqlInstance SQLPS
Cmdlet Get-SqlSmartAdmin SQLPS
Cmdlet Invoke-PolicyEvaluation SQLPS
Cmdlet Invoke-Sqlcmd SQLPS
Cmdlet Join-SqlAvailabilityGroup SQLPS
Cmdlet New-SqlAvailabilityGroup SQLPS
Cmdlet New-SqlAvailabilityGroupListener SQLPS
Cmdlet New-SqlAvailabilityReplica SQLPS
Cmdlet New-SqlBackupEncryptionOption SQLPS
Cmdlet New-SqlCredential SQLPS
Cmdlet New-SqlHADREndpoint SQLPS
Cmdlet Remove-SqlAvailabilityDatabase SQLPS
Cmdlet Remove-SqlAvailabilityGroup SQLPS
Cmdlet Remove-SqlAvailabilityReplica SQLPS
Cmdlet Remove-SqlCredential SQLPS
Cmdlet Remove-SqlFirewallRule SQLPS
Cmdlet Restore-SqlDatabase SQLPS
Cmdlet Resume-SqlAvailabilityDatabase SQLPS
Cmdlet Set-SqlAuthenticationMode SQLPS
Cmdlet Set-SqlAvailabilityGroup SQLPS
Cmdlet Set-SqlAvailabilityGroupListener SQLPS
Cmdlet Set-SqlAvailabilityReplica SQLPS
Cmdlet Set-SqlCredential SQLPS
Cmdlet Set-SqlHADREndpoint SQLPS
Cmdlet Set-SqlNetworkConfiguration SQLPS
Cmdlet Set-SqlSmartAdmin SQLPS
Cmdlet Start-SqlInstance SQLPS
Cmdlet Stop-SqlInstance SQLPS
Cmdlet Suspend-SqlAvailabilityDatabase SQLPS
Cmdlet Switch-SqlAvailabilityGroup SQLPS
Cmdlet Test-SqlAvailabilityGroup SQLPS
Cmdlet Test-SqlAvailabilityReplica SQLPS
Cmdlet Test-SqlDatabaseReplicaState SQLPS
Cmdlet Test-SqlSmartAdmin SQLPS

Now go forth and conquer.

Posted in PowerShell, SQL Server | Tagged: , , | Leave a Comment »

XML, glad that is over

Posted by Shawn Melton on 2013/10/10

XML can be one of those things you come across and say “ah, there has got to be a better way”. It is something I have held off working with in SQL Server. However with things for SQL Server performance (e.g. query plans) and such it is hard to stay away from it, if you are a true DBA. My particular desire to understand it more came about after find the deadlock graphs accessible from the System Health session in SQL Server 2008 R2. [Side note about the link, Microsoft I found only wrote up an article on it for SQL Server 2012 but it has been there since SQL Server 2008.]

So how do you pull it out? I think ever one is lead to this article on SQLServerCentral, by Jonathan Kehayias (blog | twitter). So that is how I normally pull it out. imageHowever that just provides a list of rows with the full XML document in each. Which if you only get a few and you never get them again, you might be alright with this…not so much for me. I wanted more information and a pretty table that parsed it all out. This is where I needed to figure out the XML stuff. I looked around the Internet trying to see if folks had already done this type of script, no luck.

I am actually pretty shocked that I could not find a script already, maybe my Bing-fu or Google-fu is off. Most of my searches pointed me toward parsing the XML graphs from Profiler…not going there.

Well I let this subject die down for a while, put it on the back burner (much like my blogging schedule) until a few days ago. I was perusing through previous PASS Summit Sessions and came upon Kendal Van Dyke’s (blog | twitter) session on “Working with XML In SQL Server”, here. [You will need a login to access this presentation, just go ahead and sign up because it is worth it.]. After watching that a few times it provided a greater understanding of what you can do with XML documents.

That led me to try to find a script that parses through the Profiler deadlock graphs since I understood the XML querying stuff a bit more. I could get one that provided output similar to what I wanted and just whittle it down to what I need. I came across a script by Wayne Sheffield right here.

After a bit of work going through it I ended up with the output:


Now the script to get the output can be downloaded here: SystemHealth_Parsed_DeadlockInfo.sql.

Posted in documentation, Learning | Tagged: , , , | Leave a Comment »

SSDT–Importing Script Does Nothing

Posted by Shawn Melton on 2013/02/18

I have been working with SSDT the past few months at my day job, in order to get a database versioned. I am also trying to use it for a few things with a side job as well.

So, a simple task to start with is create a new SQL Database Project, and import a script (.sql) file to bring in all the database objects. You would think it was simple until you go through the process, get no errors and no objects in your project.



Mother Hubbard, what the heck is the matter? No errors, and the summary log does not give any help either. Well, I got curious so I opened up the script in Visual Studio and received this message:

Select “Yes”, and then save the file. Repeat the steps above and voila it works, at least for me. You will also notice it took a good bit more time to complete.


Posted in documentation, SQL Server | Tagged: , , , | Leave a Comment »

Windows 8 And Me

Posted by Shawn Melton on 2013/01/17

Finally purchasing a laptop, I ended up passing the deadline to get one loaded with Windows 7. So I have been working on Windows 8 now for about 3 or 4 months probably.

I have an HP Pavilion g6-2238dx, the prime reason I picked this particular model was price at Best Buy and it had a quad-core processor. It came with 4GB of RAM and have since moved this up to 8GB.

Overall Windows 8 is not that bad. It is a bit more drastic of a change than people may have experienced between Windows XP and Vista. With a laptop, at least with HP, there are gestures you can use on the mouse pad. Which at times this gets annoying depending on how you are used to use a mouse pad. It does take getting used to.

The biggest thing I hear about is that Microsoft took away the famed “start” button that showed up on the bottom left of the screen. Which it does not just show up on the task bar anymore but it did not go away. You just have to put your mouse at the bottom left corner to make it appear, then click.


The start menu itself though, is a bit annoying. As well are the “apps” that Microsoft put that initially tries to take over playing music, video, or viewing of pictures. On my laptop they just sit there and I end up changing the default program to open the particular file types. I wish they would work on that though, it could be improved with some minor changes I am sure.

Most applications I run have not had any problems running on Windows 8. I have a few that I use for clients (VPN software and such) that I chose to build a Windows 7 virtual machine (using VMPlayer), instead of cluttering up my laptop with it.

Summary, just give it time.


Ok, after giving it about 3 months…

Windows 8 has two “modes” of applications: Desktop and Tile (I think it is called Tile). The desktop mode applications you interact just like Windows 7. With the tile apps you are going through the new Start Menu. These application modes are take a bit to get used to. I chose to pretty much remove them. You will have some things like opening PDF files that the default program is set to the PDF Reader Tile app. With my laptop they are slow to load and you cannot interact with other applications very easily, at least I can’t figure out how to. One good example is the Kindle app for Windows 8. I have SQL Server books and tend to go between reading and then doing the example on my VM. With the Tile Application mode there is no way of easily doing this. SO I had to remove it and go find the Windows 7 desktop version of the Kindle Reader. You will find, at least so far I have, that most of the applications have a “desktop” version. There are a few that I had to dig around the Internet to find it.

Posted in General | Tagged: , , | Leave a Comment »


Get every new post delivered to your Inbox.