Art of the DBA Rotating Header Image


DSC – The Custom SQL Resource

Last post I gave the rundown of Desired State Configuration and how it worked, but I skipped over the “secret sauce” around SQL Server. Of course, that was the tease to get you to come back for this week’s post.  Let’s dig in to the process and see how we can use DSC to install SQL Server without ever having to log in to the box.

Installing the Resource

The first thing to understand is that your custom resource will be contained within a Powershell module.  This means it lives in your WindowsPowershell\Modules directory.  The structure of the resource is specific and requires a couple pieces:

  • <Folder – Your Resource Name>
    • Your Resource Name.psd1 (Powershell Data file describing the module)
    • Folder – DSCResources (Contains all resources in the module
      • Folder – Your Resource Name (folder containing your specific custom resource)
        • Your Resource Name schema file (descibes the resource)
        • Your Resource Name script

Now, if you’re using the Resource Designer toolkit, these will all get created for you.  I highly recommend doing that, because you miss one piece and you’ll be bashing your head against this for a week or so.  Ask me how I know.  :)

Another thing to setup is your execution policy.  As this is a custom script, the local server needs to it is trustworthy.  I set the execution policy to RemoteSigned for this (Set-ExecutionPolicy RemoteSigned).  If you don’t do this, you’ll get an invisible failure, where your configuration will fail but you will have no feedback on the reason.  Again, ask me how I know!

Custom Resource

When you actually create the resource script, you need three functions:

  • Get-TargetResource
  • Test-TargetResource
  • Set-TargetResource

Get-TargetResource is the function that will return the resource you’re checking for.  It returns a hash table to represent the key values of the resource.  Test-TargetResource is a boolean check, returning true if the resource exists, false if it does not.  Set-TargetResource does all the work, as it is the function that is called if the resource needs to be created.  You can have other internal functions if you want to further modularize your process, but these three must exist for DSC to work.  The internal operations must be written by you, but as long as the names and outputs are consistent you are set.

The other key piece is not in the resource itself, but up in the data file describing your module.  Within that file you need to have a GUID set for the module so that the DSC engine can reference it when it is installed on other machines.  I tripped up on this many times, so be careful.


VPn1q6NSo let’s talk about the resource I constructed.  As I said in my previous post, I was frustrated with the limitations of the Microsoft resource.  The biggest limitation was the inability to use a config file, which is pretty much how I do SQL installs now.  So that’s how I approached writing mine, leveraging the .ini file for most of the installation detail.

 The resource accepts the following parameters:

  • InstanceName – (required) The name of the instance you plan to install, MSSQLSERVER if you want the default.
  • InstallPath – (required) The UNC path for where the setup files are.
  • ConfigPath – (required) The UNC path for the config.ini file.
  • UpdateEnabled – Boolean, defaults to false.  If true, the setup will check for and attempt to install available SPs and CUs.
  • UpdatePath – If UpdateEnabled is true, this is the path where the update files reside.
  • MixedMode – Boolean, defaults to false.  If set to true, the install will set authentication to mixed mode and create ‘sa’ with a randomly generated password.

I’ve tried to keep the parameters to a minimum and allow for customization to happen within the the config file.  The biggest gap right now is that the service accounts and their passwords will be contained in plain text in that file.  Ideally, you’d use managed service accounts to get around this, but I still am working on setting those up.

We then look at the functions within the resource.  Get-TargetResource should return a hash table, so what will return from this resource is the InstanceName, InstallPath, and ConfigPath.  This is because these are the required parameters for the function, but really we only care about the InstanceName.   To get that, it’s a simple check of the services on the target machine to find a service with the desired InstanceName.  If we find it, it returns that name.  If we don’t, it returns NULL.

Test-TargetResource is a lot simpler.  Since we want a boolean, all we do is use Get-TargetResource to get the resource hash table.  Then we check the hash table’s InstanceName with the desired InstanceName.  If they match, we return true and indicate the resource exists.  If they don’t match, the resource doesn’t exist and we return false.

Set-TargetResource is fairly straightforward.  Using these arguments along with some default ones, the resource will build out a call to setup.exe.  Once this string is built, the resource will invoke the setup.exe call just like any other unattended install.  After the setup run is complete, the script finds the most recent Summary.txt file and checks it to see if the installation was successful.  If the install was successful, it restarts the server and we’re done.  Otherwise, it will report an error.

Work To Be Done

There’s still some other gaps in the code that I will be working on.  First, it assumes the local machine account that the Local Configuration Manager runs under has permissions to the file shares for the SQL install.  If your environment is more locked down, this could be a problem.  There’s also the issue of logging that still needs to be addressed, because the current logging is not useful.  All of these items (and others that come up) will be addressed eventually.

I also plan to add other resources to this.  I’d like to get one to handle service pack and cumulative updates outside of the SQL install so that you can use it to keep everything to the correct version as well.  I’ve posted the code to my GitHub repository  As with the other scripts there, it is a work in progress and can use a fair amount of improvement.  If you have any suggestions or recommendations for this code, I’d love to hear of them.

Desired State Configuration

As a DBA, I’m always concerned with consistency in my environments.  From maintenance to code deploy, I want things to be done the same way every single time.  Usually this is a process question, making sure that you harness the power of check lists and repeatable steps.  You then make it better by wrapping your process in scripts and leveraging other tools.

When I go into a new shop, one of the first things I usually have to do is bolt down the server build process.  Most environments will manually build servers, where an engineer goes in and manually installs the appropriate packages and features.  Then a DBA will install SQL Server, adding maintenance jobs and deploying admin databases.  Even with building scripts and sketching out steps, there will be a lot of spots where configurations can be missed and the process can breakdown.  Fortunately, we now have a tool in Powershell that helps us solve these problems.

You might have heard of Chef or Puppet.  Well, now Powershell has its own answer for managing server configurations:  Deired State Configuration or DSC.  DSC is an engine included in Windows Management Framework 4 that allows folks like us to create declarative configurations for our servers which can then be acted on by our servers.  This brings the next level of automation, infrastructure as code, to our servers.

How does it work?

The process for DSC relies on two things:  Your configuration and your resources.  You create a configuration, which specifies the resources you want (or don’t want).  You then use DSC via Powershell to deploy the configuration to a target server, which then runs the configuration through a Local Configuration Manager (LCM) to check those resources.  If those resources are present, the LCM takes note and moves on.  If the resources is not present as declared in the configuration, the LCM will then attempt to install or configure the resource as specified in the configuration file.

Simple, eh?  It’s meant to be that way.  The idea is to have an intuitive way to deploy servers in a standardized method, whether it’s 1 or 100 (or 1000 for that matter).  It’s also meant to be flexible.  DSC ships with some basic resources (things like File, Windows Feature, and Registry Key), but because it’s built in Powershell you have the ability to create your own resources.  This means the sky’s the limit with what you can do.

What do I need?

Let’s dig a little more into the detail of how DSC works.  First off, if we want to use DSC, we need a couple things:

  • Windows Management Framework 4.0 on all machines involved
  • Permissions to run custom scripts on the targets (if we’re using custom resources, more on this later)
  • Custom resources need to be deployed to the target ahead of deploying the configuration

Note I call out some requirements around custom resources.  These are scripts you write yourself.  We’ll talk about those in a bit, just file these bits away for later reference.

Now, the configuration.  This is a configuration I created for deploying a base installation of SQL Server, so the resources are designed around that.  Here’s the script:

Configuration SQLServer{
   param([string[]] $ComputerName)

   Import-DscResource -Module cSQLResources

   Node $ComputerName {

       File DataDir{
           DestinationPath = 'C:\DBFiles\Data'
           Type = 'Directory'
           Ensure = 'Present'

       File LogDir{
           DestinationPath = 'C:\DBFiles\Log'
           Type = 'Directory'
           Ensure = 'Present'

       File TempDBDir{
           DestinationPath = 'C:\DBFiles\TempDB'
           Type = 'Directory'
           Ensure = 'Present'

       WindowsFeature NETCore{
           Name = 'NET-Framework-Core'
           Ensure = 'Present'
           IncludeAllSubFeature = $true
           Source = 'D:\sources\sxs'

       cSQLInstall SQLInstall{
           InstanceName = 'MSSQLSERVER'
           InstallPath = '\\HIKARU\InstallFiles\SQL2014'
           ConfigPath = '\\HIKARU\InstallFiles\SQL2014\SQL2014_Core_DSC.ini'
           UpdateEnabled = $true
           UpdatePath = '\\HIKARU\InstallFiles\SQL2014\Updates'
           DependsOn = @("[File]DataDir","[File]LogDir","[File]TempDBDir","[WindowsFeature]NETCore")

SQLServer -ComputerName MISA

 This looks like (and is) a Powershell function, just a special one using the ‘Configuration’ key word.  When you call the function, it will create a special file for the Node(s) declared within the configuration, a Managed Object Format (.mof) file.  This is a standardized file type that defines our configuration.  Note, this file is not Windows specific, as the design for DSC is to ultimately be used with non-Windows OS machines and hardware.  It’s the .mof that will be deployed to our target machines and acted upon by the LCM.

 Next up in the file, the Node.  This is the target for our configuration.  You’ll see that in the script, it is parameterized as an array, meaning we can run the script for as many different targets as we want, we just have to pass the array of machine names we want.  We could also have multiple nodes within the configuration script, if we wanted this configuration to have different types of targets.

 Within the Node section, we have our resource declarations.  This is the meat, the pieces of code that identify what needs to be configured.  The nice thing is reading them is fairly intuitive.  Let’s look first at the File resource.  These three declarations are for my default directories.  I have to declare specify that it is a directory (the Type), the path (the DestinationPath), and that is must exist (Ensure = Present).  As an aside, I could get a lot more involved with the File resource, copying things from a net share down to the target if I wanted, things like config files or web source directories.

 The Windows Feature resource is also easy to understand.  Using this, I can make sure certain Windows features exist on the target.  Since I’m using this particular configuration to install SQL Server, I’ve declared that I want the Net-Framework-Core feature installed.  This is a prerequisite for installing SQL Server.

 Finally, I have the cSQLInstall resource.  I’m going to save the explanation of this detailed custom resource for the next blog post, but I want to call attention to the DependsOn argument.  With this, we can declare prerequisites for our resources.  This give us some additional resilience for our install, allowing us to create some precedence within the configuration.

Where do I run it from?

Once you have the configuration written, you simply need to run it.  It will create a directory named after your configuration and contain a .mof file for each node within it.  The next step is to use the Start-DscConfiguration cmdlet and call the path where all our files live.  After that, a Powershell background job kicks off and DSC handles the rest.


Any not cool parts?

So not everything is unicorns and rainbows with DSC.  First off, it’s a stone cold female canine to debug, because it’s difficult to capture any logging around it.  Many times I would try and run something and it would fail before it even entered my script.  Most of this was around my custom stuff, but it was extremely frustrating. 

As for the custom scripts, DSC is still fairly immature.  Microsoft has some resource kits out there, but most of the resources in there are pretty weak.  The SQL resource provided has several gaps, such as being designed specifically for SQL 2012 and not being able to use .ini files.  So you’re probably going to write resources on your own, which has a LOT of gotchas.  You’re going to want to start with the Resource Designer Tool, which will solve most of the gotchas for you.

 Also, since it’s immature, there’s not a lot written about it.  Especially for SQL Server. and MSDN are going to be your best bets for help, but this is a brave new world.  Be prepared to walk a fair amount of the way on your own.  If you want to start, go with this book from Don Jones.

 Infrastructure as code is the future.  DSC gives us intuitive tools with a straightforward interface (once your resources are written, of course) to make our environments more consistent and easier to manage.  I’m super excited about this feature and will be blogging more about it in the weeks to come.  Definitely stay tuned for next week, where I’ll walk through my custom SQL Server resource (and tell you where you can get the code yourself).  Also, hit me up if you’ve got questions or any experiences of your own around DSC, I’d love to hear about them.

RestoreAutomation #Powershell Module

When you become involved with the SQL community, you see a lot of great tools contributed to make life easier for the rest of us.  The function of these tools range greatly, from helping to analyze your system state, collect data about your enterprise, or performing regular maintenance.  I’ve used many of these tools over the years and can’t imagine being a DBA without them.  Well, now it’s time to give back.

A little over two years ago, I posted a Powershell script for building database restores.  It has been very handy and I’ve used it a lot over the years.  However, it always felt like it could use some polish.  I began work a few months ago to add that polish and was able to debut this output at SQL Saturday Sacramento a few weeks ago.  Now I want to share that same output with you here on my blog.


I’ve compiled the previous Powershell script into a module, making it easily transportable and allowing DBAs to use it more effectively as a tool.  No messing with script files, all you need to do to use it is download the module file and then import it into your Powershell session.  The file is now available on my new GitHub repository and can be accessed via the following links: (raw version)

To install the module, follow these steps:

  1. Open one of your default Powershell module locations ($env:PSModulePath).  I recommend your user specific one.
  2. Create a folder named RestoreAutomation in that locatoin
  3. Place the RestoreAutomation.psm1 file in the new folder.

Full Microsoft install instructions can be found here.

To load the module once you’ve installed it, just open a Powershell session and enter Import-Module RestoreAutomation.  After that, you can see the available functions by using Get-Command -Module RestoreAutomation, which will provide the following list:

CommandType     Name                ModuleName
-----------     ----                ----------
Function        Get-DBCCCheckDB     RestoreAutomation
Function        Get-Header          RestoreAutomation
Function        Get-RestoreObject   RestoreAutomation
Function        New-Restore         RestoreAutomation
Function        Sync-DBUsers        RestoreAutomation

Using the module

While five functions are listed, two of them are internal and used by the core restore process to generate the necessary code.  I’ll cover the primary three functions here, but I have made full use of Powershell’s comment based help, so you can use Get-Help on these functions at any time to get information on how to use them.

This is the core functionality that was created from my initial script.  When you run this function, it will create a .sql script in your Documents folder to restore the associated files.  The function takes a directory path and then search that directory path for all backup files within it, ordering them appropriately (latest full, then latest differential, then the right sequence of transaction logs).

You’ll see that the function has a lot of parameters.  The only two required are:

  • dir – The directory where the necessary backup files live
  • server – A SQL Server instance where the script generation commands will be run (but not necessarily restored to)

I will not cover the optional parameters in detail here, but review Get-Help New-Restore -full for a full listing of the parameters and what they do.

The function makes the following assumptions:

  • All the backup files in the directory belong to one database only.
  • Backup file types are defined by extension (.bak for full, .dff for differential, and .trn for transaction log).
  • No striped backup files, meaning a database backup will be contained on only one file.
  • No backup sets, meaning a backup file only contains a single database backup.

To use the function, simply get a directory of backup files and run a command similar to:

New-Restore -dir “X:\backups\db” -server “localhost”

Then, look in your Documents folder for a restore_db.sql file.  This will be your database restore script.

If you want to test drive it yourself using a dummy database, I’ve provided a full demo script and files for download.

This function I added to help me with database migrations.  Often, I’ll migrate a database via backup and restore.  Afterwards I always need to clean up the orphan logins.  This function is a wrapper for SQL queries that I use to do this, providing me an easy to use interface for managing the process.

The function accepts only two parameters:

  • server – The SQL instance that the database resides on
  • database – Database that contains users you wish to correct

To run the function, just call your server and database name:

Sync-DBUsers -server “localhost” -database “restoredemo”

The function will then find all orphaned users and correct them.  Any orphans it can not find corresponding logins for, it will return as an output list for review and correction.

When building out my automated restore process, I always wanted to do a DBCC CHECKDB on the restored database to validate everything was ok.  So I wrote this function in order to give me a manageable interface for the process.  The function will then run a DBCC CHECKDB with PHYSICAL_ONLY on my target database and return the results.

The function has three parameters:

  • server – The SQL instance that the database resides on
  • database – Database that you want to check
  • Full – Switch parameter.  If used, the function will execute a full DBCC CHECKDB.  If omitted, it will only perform a check with PHYSICAL_ONLY

To use the function, call the server and database name:

Get-DBCCCheckDB -server “localhost” -database “restoredemo”

The output will be a data table of the check results.

Only the beginning

Hopefully this module can help you as much as it has helped me.  The point is to use this as a tool to facilitate other tasks.  Many of these tasks I will provide examples of over the next month or so, to demonstrate how you can leverage this in your own environment.

I already have a list of enhancements that I plan to add.  I expect that as folks out there review it and play with it, I’ll get more.  Please do not hesitate to post suggestions and comments to the GitHub repository.  The idea, as with many of these other tools, is to build a reliable offering that other DBAs can use in their day to day jobs to make things easier and allow them to work on more important tasks and projects.

TIL: Broken Log Chains

Something that always bites me in the butt from time to time is restoring a database in full recovery model and forgetting to do a full right away.  Then I find out shortly when my log backups start failing and spamming me with error messages.  I’m fairly certain this has happened to you, as well.  It’s an easy thing to forget, especially when you’re dealing with non-critical databases.

Humans are typically bad at remembering things.  That’s why we make task lists, have calendar reminders, document processes, and script for automation.  We either have to remind ourselves to do something or (my preference) build something that’s smart enough to correct our oversight.  So when I found a way to do this with broken log chains, I was pretty happy.

The key was a new dynamic management view (DMV) I discovered.  Most people who know me know I love the DMVs and all the information they can provide.  My new discovery is the sys.database_recovery_status view, which provides recovery info for all the databases.  The key field is the last_log_backup_lsn field, which will be NULL if the log chain has somehow been broken.

What’s cool about this is now we have a programmatic way we can use to validate if a full backup needs to be performed.  By adding a quick check to a log backup script, we can make sure we never have a broken log chain.  A quick and dirty way would be:

if (select last_log_backup_lsn from sys.database_recovery_status where database_id = db_id(‘foo’)) is NULL
    backup database [foo] to disk=<<path to full backup>>

backup log [foo] to disk=<<path to log backup>>

This is obviously the most simple approach you can use, but the pattern is the important piece.  By combining this with your standard backup scripts (please tell me you have standard backup scripts), you can always ensure that you never get an error because you forgot a full backup.

Because your script will remember for you.


A PoSH way to skin the cat

Today my friend John Morehouse(@SqlrUs) posted a handy T-SQL script to configure your SQL Server memory based on some rules provided by Jonathan Kehayias(@SQLPoolBoy).  This is a great script and helps automate a lot of the work a DBA needs to do when setting up a new instance. Hopefully, you know by now about my passion for automating and standardization.  However, when reading John’s post, I had the following internal conversation:

“Wow, self, John’s got a neat little script here.”

“Yeah, but T-SQL is so passé.  Everyone does it.  Plus, not sure how easily we could add that into a server deployment script if we wanted total hands-off-ness.”

“Self, I know what you mean.  Nothing wrong with it, it just doesn’t fit my own particular….oh…particular….”


“Yes!  Idiom!  Hrmmm…how about a Powershell script?”

“A Powershell script?  Brilliant!”

And so I sat down and cranked out the following script, which essentially does what John’s script does, just using Powershell.

 Configures a SQL Server instance per the Jonathan Kehayias' guidelines.
 This script will configure your SQL Server instance per the guidelines
 found in Jonathan Kehayias' blog post:
 The rules are:
 - 1GB for initial OS reserve
 - +1GB per 4GB server RAM up to 16GB
 - +1GB per 8GB server RAM above 16
 -instance SQL instance name, i.e. localhost\SQL2012, DBASERVER01
 -apply Switch parameter, call if you want to actually apply the changes. Otherwise, a report will be produced.
 Configure-SQLMemory -instance DBASERVER01 -apply

param([parameter(Mandatory=$true)][string] $instance
 , [Switch] $apply

#load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

 $sqlhost = $instance.Split("\") | Select -First 1
 $sqlhost = $instance

#set memory variables
$totalmem = (gwmi Win32_ComputerSystem -computername $sqlhost).TotalPhysicalMemory/1GB
$sqlmem = [math]::floor($totalmem)

#calculate memory
while($totalmem -gt 0){
 if($totalmem -gt 16){
 $sqlmem -= [math]::floor(($totalmem-16)/8)
 elseif($totalmem -gt 4){
 $sqlmem -= [math]::floor(($totalmem)/4)
 $totalmem = 4
 $sqlmem -= 1
 $totalmem = 0

#if not in debug mode, alter config. Otherwise report current and new values.
$srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server $instance
 "Instance:" + $instance
 "Max Memory:" + $srv.Configuration.MaxServerMemory.ConfigValue/1024 + " -> " + $sqlmem
 "Min Memory:" + $srv.Configuration.MinServerMemory.ConfigValue/1024 + " -> " + $sqlmem/2
 $srv.Configuration.MaxServerMemory.ConfigValue = $sqlmem * 1024
 $srv.Configuration.MinServerMemory.ConfigValue = $sqlmem/2 * 1024
 "Configuration Complete!"

Now it should be noted that I’m not going to tell you which of these scripts are “better”. Neither is, really, they’re just different ways of approaching the problem. And that’s the fun of it. When working in technology, cats can be skinned in multiple ways, we just have to use something. Whether it’s T-SQL, Powershell, C#, or some other thing, the point is that you want scripts to automate your work.

What was also cool about this was it gave me another exercise to practice Powershell.  After all, practice makes perfect and when learning something, any excuse to make me use it is good.  John and I actually had a conversation shortly after I wrote the script about these sort of opportunities, and we’ll continue to look for ways to challenge each other for learning.

Edit:  Well, that was embarrassing.  Amazing how sometimes you can get the math little screwy.  I’ve updated this script as of 9:44 PM MST to correct me having the math backwards.  If you grabbed/reviewed this script before hand, please make sure up grab the updated version.

The Moving Van

A problem DBAs commonly face is moving database files around, especially when we’re trying to manage space on a drive.  I know that I’ve had to do it and, when you have a database instance with hundreds of databases, you need an automated way to manage this.  Recently, someone on DBA Stackexchange had this specific problem and I was able to provide a Powershell script that I’ve used in the past to manage this problem.  And when I haven’t blogged for a while, a good way back into it is to share a Powershell script.  Mostly because it’s easy.  :)

First, let me talk about a pattern I commonly use.  A lot of times, I could write a single script to take an action, but instead I build a script to write a script.  The main reason I do this is that it’s very hard, especially with many of our administrative tasks, to write a one-size-fits-all process.  So instead, I have a tool that gets me 90% of the way which I can then tweak or alter that script to address that final 10% to fit that specific need.

That’s what this script is doing.  It doesn’t perform the actual file moves itself, but writes out a script that will execute those files moves.  It’s fairly generic, creating a script that moves all the database files from their current location (wherever that may be) to two common directories: data and log. It’s rare that you would move all your database files in this fashion, but you can edit the generated script to suit your specific situation.

This script makes use of the techniques detailed here by Ben Miller(@dbaduck) with one significant exception. I’ve found that the .Offline() method for databases is very unreliable because you’re basically executing your normal “ALTER DATABASE [foo] SET OFFLINE;”. The main problem with this command is it has to wait for all connections to close gracefully before it can proceed, something that doesn’t happen to frequently. I prefer to use “ALTER DATABASE [foo] SET OFFLINE WITH ROLLBACK IMMEDIATE;” because it forces all connections to close and open transactions to rollback, no waiting involved. Since the SMO methods don’t have an option for that,  I call the SQL using the Invoke-SQLCmd cmdlet. I find this is a much cleaner option for my script.

Specifically, the script accepts 4 parameters:

  • $newdata – New data file path

  • $newlog – New log file path

  • $instance – SQL instance you want to move files for (defaults to local)

  • $outputfile – Output file name (defaults to Documents, FileMover.ps1)

When you run it, it will spit out a FileMover.ps1 script to you Documents folder (though, using that last parameter, you can change the output location and name).  This FileMover.ps1 will be the script you can then edit to suit your specific needs.  The script is below, but you can also download it here.

   Builds a Powershell script for moving many database files from one
	 set of directories to another.
   Used to generate a Powershell script for moving many database files
	 from one location to another.  This will typically be used with a
	 large number of databases that need to be relocated.  Caveats for
	 this script include:
	 		-Destination for all files will be the same.
			-User that runs the script must have access to source and destination locations
			-This uses the 2008 R2 version of the SMO.

	The script generates a FileMover.ps1 script (by default in My
	Documents).  The reason for generating a separate script is so
	specific configurations can be edited/updated before actually
	execyting the move.

.PARAMETER <paramName>
   instance - Instance owning the databases to be moved
	 newdata - New data file location, no trailing '\'.  example: "C:\DBFiles\Data"
	 newlog - New log file location, no trailing '\'.  example: "C:\DBFiles\Log"
	 $outputfile - Full path and name of output file.  By default, FileMover.ps1 in My Documents.

   .\Build-FileMover.ps1 -newdata "C:\DBFiles\Data" -newlog "C:\DBFiles\Log"

param([parameter(Mandatory=$true)][string] $newdata,
			[parameter(Mandatory=$true)][string] $newlog,
			[string] $instance="localhost",
      [string] $outputfile=([Environment]::GetFolderPath("MyDocuments"))+"`\FileMover.ps1")

#load SMO
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#Create server object and output filename
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server $instance

#get your databases

#build initial script components
"Add-PSSnapin SqlServerCmdletSnapin100" > $outputfile
"Add-PSSnapin SqlServerProviderSnapin100" >> $outputfile
"[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') `"$instance`" | out-null" >> $outputfile
"`$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server " >> $outputfile

foreach($db_build in $db_list)
	#only process user databases
		#script out all the file moves
		"#----------------------------------------------------------------------" >> $outputfile
		"`$db=`$server.Databases[`""+$db_build.Name+"`"]" >> $outputfile

		$dbchange = @()
		$robocpy =@()
		foreach ($fg in $db_build.Filegroups)
			foreach($file in $fg.Files)
				$robocpy+="ROBOCOPY `"$oldloc`" `"$newdata`" $shortfile /copyall /mov"


		foreach($logfile in $db_build.LogFiles)
			$robocpy+="ROBOCOPY `"$oldloc`" `"$newlog`" $shortfile"

		$dbchange+="Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET OFFLINE WITH ROLLBACK IMMEDIATE;`" -ServerInstance `"$instance`" -Database `"master`""

		$dbchange >> $outputfile
		$robocpy >> $outputfile

		"Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET ONLINE;`" -ServerInstance `"$instance`" -Database `"master`""  >> $outputfile

Server Inventories

Ok, I’ve been promising this to people for a while, time to get at it.  Brace yourself for a long one.

 As enterprise DBA’s, we usually have many servers to manage.  Whether it’s around ten, a hundred, or a thousand (or more!), tracking this manually is a bear.  However, there are still many shops maintain convoluted spreadsheets and other manual documentation to track their inventory.  Manual sucks.  As John Sansom(@SQLBrit) says, the best DBAs automate everything, including their server inventories.

 There’s a lot of approaches and third party tools you can use for your own environment.  In my mind, I’m totally cool with spending the money on a third party tool because of the depth they can provide in my tracking and monitoring.  A lot of times, though, we might not have access to those tools or we may need more information than those tools provide.  It’s at this point we sit down at our keyboard, crack our fingers, and start banging out some code.  Of course, the code I started banging out was Powershell (shocking, I know!).

 I didn’t start from scratch, though.  When putting my own script together (which we’ll get to later), I found two great resources that got me about 80% of the way:

 Allen White(@SQLRunr) – Let PowerShell do an Inventory of your Servers
Colleen Morrow(@ClevelandDBA) – Building A SQL Server Inventory

These are both great resources that will get you started, but I needed a little more.  The biggest challenge I had was a need to poll cluster information and collect SQL instance information separate from my machine information.  I also wanted to do some consolidation of my data sets.  So while I borrowed heavily from both Allen and Colleen, but then molded it to my own purposes.


Before we dig into the code, I first want to touch on the collection process and how I handle it.  It’s not complex, but code doesn’t make sense until you understand the “why” of the different components.

What I’m collecting

  • SQL Instance information – Whether it’s an instance living on a cluster node, a physical stand alone, or a VM host, I want the SQL Instance without really caring about the machine it lives on.  At least not meshed in directly with the machine, though I DO collect the physical host name so I can identify which machine information I need.

  • Physical Machine information – Since I’m not tying the machine info directly with the SQL Instance collection, I want the physical information for the servers my instances live on.  This is where I get everything that isn’t tied directly to the SQL Instance.

How I’m collecting it

  • I’ve got 4 tables, 2 live tables and 2 stage tables, that I use for the collection.  Nothing out of the ordinary, but there’s an instance table with a stage table and a machine table with a stage table.

  • I use a Powershell script to perform the collection itself.  It uses instance table as its primary driver, where the idea is that I provide the initial population of instance names to track into the table and the inventory process will fully populate the rest of the information.

  • When the Powershell scripts complete, there is a stored procedure it executes to load data from the stage tables into the live tables.  The load is simple, where the instance table is updated with data from the stage and the machine information is deleted/replaced.

  • The Powershell script is run by a SQL Agent job running under a specific monitoring Active Directory account created as a credential in SQL Server.  The gotcha here is that the monitoring account needs domain access to the clusters and machines it’s going to be querying.

The Code

I won’t list the full scripts here, but you can download and review them here:

SQL Objects
Powershell script

 Let’s first talk about the dependencies:

  • The SMO, so you’re going to need to have either your script or your profile load the 2008 snap-ins or import the 2012 sqlps module.

  • Chad Miller’s Out-DataTable and Write-DataTable (I combined them into one file, DataTables.ps1).

  • The FailOverClusters module, part of the Remote Admin pack if you’re not running the code on a machine with the Windows Server OS, part of the Fail Over Cluster components if you are.

There are two key functions I use, Get-Instance to gather the SQL instance information and Get-Machine to gather the machine information:

#Collects and returns SQL Instance information
function Get-Instance([string]$instcoll,[int]$id,[string]$name)
	$smo = new-object ('Microsoft.SqlServer.Management.Smo.Server') $name
	$sname = $smo.NetName
	$iname = $smo.InstanceName
	if($iname.Length -eq 0 -or $iname -eq $null) { $iname = "MSSQLSERVER" }

	$managedcomp = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $sname
	$output = New-Object System.Object

	$port = $managedcomp.ServerInstances[$iname].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value
	$ip = (Test-Connection $sname -count 1).IPV4Address.ToString()

	$output | Add-Member -type NoteProperty -name InstanceId -value $id
	$output | Add-Member -type NoteProperty -name SQLVersion -value $smo.VersionString
	$output | Add-Member -type NoteProperty -name SQLVersionDesc -value $smo.ProductLevel
	$output | Add-Member -type NoteProperty -name SQLEdition -value $smo.Edition
	$output | Add-Member -type NoteProperty -name MemoryMinMB -value $smo.Configuration.MinServerMemory.RunValue
	$output | Add-Member -type NoteProperty -name MemoryMaxMB -value $smo.Configuration.MaxServerMemory.RunValue
	$output | Add-Member -type NoteProperty -name MAXDOPVal -value $smo.Configuration.MaxDegreeOfParallelism.RunValue
	$output | Add-Member -type NoteProperty -name IP -value $ip
	$output | Add-Member -type NoteProperty -name Port -value $port
	$output | Add-Member -type NoteProperty -name PhysicalHost -value $smo.ComputerNamePhysicalNetBIOS
	return $output
	write-host "Error collecting $name"
	return $null

#Get host machine information via WMI
function Get-Machine([string]$name,[string]$clst)
	$comp = gwmi Win32_ComputerSystem -Computer $name | select Model,Manufacturer,TotalPhysicalMemory
	$proc = gwmi Win32_Processor -Computer $name | select NumberOfLogicalProcessors,MaxClockSpeed
	$os = gwmi Win32_OperatingSystem -Computer $name | select OSArchitecture,Name,Version,ServicePackMajorVersion,ServicePackMinorVersion

	$output = New-Object System.Object

	$output | Add-Member -type NoteProperty -name MachineName -value $name
	$output | Add-Member -type NoteProperty -name Model -value $comp.Model
	$output | Add-Member -type NoteProperty -name Manufacturer -value $comp.Manufacturer
	$output | Add-Member -type NoteProperty -name Architechture -value $os.OSArchitecture
	$output | Add-Member -type NoteProperty -name PhysicalCPUs -value $(if(!$proc.Length){"1"}else{$proc.Length})
	$output | Add-Member -type NoteProperty -name LogicalCPUs -value ($proc | Measure-Object NumberOfLogicalProcessors -sum).Sum
	$output | Add-Member -type NoteProperty -name CPUSpeed -value ($proc | Measure-Object MaxClockSpeed -max).Maximum
	$output | Add-Member -type NoteProperty -name MaxMemory -value ($comp.TotalPhysicalMemory/1MB)
	$output | Add-Member -type NoteProperty -name OSName -value $"|")[0]
	$output | Add-Member -type NoteProperty -name OsVersion -value $os.Version
	$SPMaj = $os.ServicePackMajorVersion
	$SPMin = $os.ServicePackMinorVersion
	$output | Add-Member -type NoteProperty -name SPVersion -value "$SPMaj.$SPMin"
	$output | Add-Member -type NoteProperty -name Cluster -value $clst

	return $output
	write-host "Error collecting $name"
	return $null

Both leverage the SMO and the WMI to get relevant info.  I’ll let you sort through the individual elements I’m pulling, it’s fairly standard config info.  Data gathering, because I think in terms of tables, is a collection of objects that are essentially rows.  I append each new object with all the appropriate properties to a larger collection, then use Write-Datatable to push that data directly into the related stage table.

Look at the entire script for my full process.  I’ve been pretty happy with it and it’s been chugging away in my environments now for about 8-9 months without a whole lot of interaction from me.  If we stand up a new server, then I just pop that name in the instance table, run the job, and everything gets updated.  What made me most proud was that we had a recent meeting with a new VP and pulled up the SSRS report built off of this information to give him an idea of what our environments.  His comment was “This is the most documentation I’ve seen since I’ve got here.”  The beauty is that it was provided without a whole lot of manual maintenance on my part.

This is very much an evolving work on my part, but hopefully gives you some insight into how I manage my environments.  I’ve still got lots of ideas on improvements, including automatic population (or other use) of Central Management Server.  If you have any ideas on how this can be improved, I’d love to hear them.

Slipstreamin’ Away

It’s nothing new: I love anything that makes less work for me.  One thing that has been a great contributor to this has been slipstream installs.  While that’s a bit of a fancy name, basically it’s an install of SQL Server where the service packs and cumulative updates are rolled up into one install package.  This saves the tedious chore of constant install after install to get a new SQL Server installation up to a patch level consistent with the rest of your enterprise.

Now, the problem with slipstream installs is that they’re a pain in the butt to create, at least previous to SQL 2012.  Not difficult, but tedious.  You have to copy some files, merge some directories, and edit some config files.  It’s nothing that can’t be done, but very repetitive.  To boot, if something isn’t copied or configured right, the whole package won’t work and you won’t know until you attempt the install (and it fails).

Enter Powershell.

Building a slipstream is no different than any other task that we should automate.  After all, we’re doing the same actions every time, the only thing that might change are the files we use to build our slipstream.  What I’ve done is put together a script that does all this for me.  As with any script, there are a few rules to how you use it:

  • The script was designed for building SQL 2008 and 2008 R2 slipstreams.  I tried it with 2005, but that process is different enough that this script won’t work.
  • RTMSource and output are the two mandatory parameters.  The SPFiles and CUFiles parameters will be skipped if left null.  Basically, if you are making just a SQL 2008 R2 SP2 slipstream, you would only need to supply the service pack files.
  • The script expects the base install (the RTM media) to be un-extracted and the service packs or cumulative updates in their .exe form.  The script will extract the additional files from the .exe into the slipstream package.
  • I built the script to work along my organizational thought process, which means I put each set of files in their own subdirectory.  This shouldn’t be a big deal, but be aware that if you point the script at a location, it won’t just pull out the files it needs, it will try and use every .exe file in the subdirectory.

Other than that, here’s the script:

   Builds a SQL Server slipstream install.
   This script will take the various components of a SQL Server isntall (RTM binaries, SPs, CUs) and assemble
	 them to create a slipstream install.  To use this script, you will need the unextracted RTM binaries (.iso
	 or DVD media) and the executables for the SP and CUs you want to use.
	 Mike Fal (htp:// 2-28-2013
.PARAMETER <paramName>
   RTMSource - File path for the RTM source files
	 SPSource - File path for the Service Pack executables (if no value is passed, no SP will be added)
	 CUSource - File path for the Cumulative update executables (if no value is passed, no CU will be added)
	 output - filepath where slipstream will be written, directory will be created if it doesn't exist.
   .\Build-Slipstream.ps1 -RTMSource "C:\Users\mfal\Downloads\en_sql_server_2008_r2_developer_x86_x64_ia64_dvd_522665"
	 												-SPSource "C:\Users\mfal\Downloads\2008R2SP1"
													-CUSource "C:\Users\mfal\Downloads\2008R2SP1CU2"
													-output "C:\SQL2008SP1CU2_Slipstream"

param([parameter(Mandatory=$true)][string] $RTMSource,
			[parameter(Mandatory=$false)][string] $SPSource,
      [parameter(Mandatory=$false)][string] $CUSource,
      [parameter(Mandatory=$true)][string] $output)

#function to extract and copy SPs and CUs
function Modify-Source{
	$PATCHFILES=get-childitem "$PATCHDIR\*" -include *.exe
	if (!(test-path("$WORKDIR\$TYPE"))) {mkdir $WORKDIR\$TYPE }
	if (!(test-path("$WORKDIR\$TYPE"))) {Throw "Error: Patch file destination not available"}

	set-location $PATCHDIR

	foreach($FILE in $PATCHFILES)

			iex "./$($FILE.basename)$($FILE.extension) /x:""$WORKDIR\$TYPE"" /q"
			"Waiting for $FILE to extract..."
			while (@(Get-Process $FILE.Name.Replace(".exe","") -ErrorAction SilentlyContinue).Count -ne 0)
			Start-Sleep 1
  "Copying $TYPE files..."
	robocopy "$WORKDIR\$TYPE" "$WORKDIR" setup.exe /ndl /nfl /njh /njs
	robocopy "$WORKDIR\$TYPE" "$WORKDIR" setup.rll /ndl /nfl /njh /njs

if (test-path("$WORKDIR\$TYPE\x86")) {robocopy "$WORKDIR\$TYPE\x86" "$WORKDIR\x86" /XF Microsoft.SQL.Chainer.PackageData.dll /ndl /nfl}
if (test-path("$WORKDIR\$TYPE\x64")) {robocopy "$WORKDIR\$TYPE\x64" "$WORKDIR\x64" /XF Microsoft.SQL.Chainer.PackageData.dll /ndl /nfl}
if (test-path("$WORKDIR\$TYPE\ia64")) {robocopy "$WORKDIR\$TYPE\ia64" "$WORKDIR\ia64" /XF Microsoft.SQL.Chainer.PackageData.dll /ndl /nfl}

set-location $WORKDIR

#Test path locations for validity

if (!(test-path($RTMSource + "\setup.exe"))) {Throw "SQL RTM Source does not exist!"}
if (!(test-path($SPSource)) -and !($SPSource -eq $null)) {Throw "Invalid Service Pack file location!"}
if (!(test-path($CUSource)) -and !($CUSource -eq $null)) {Throw "Invalid Cumulative Update file location!"}
if (!(test-path($output))) {mkdir $output}
if (!(test-path($output))) {Throw "Unable to create build directory!"}

#begin merge process, RTM
"Copying RTM..."
robocopy "$RTMSource" "$output" /s /ndl /nfl
set-location $output

Copy-Item ".\x86\DefaultSetup.ini" ".\"

#merge Service Pack
if(!($SPSource -eq $null))
	Modify-Source "PCU" $output $SPSource
	"`n`nPCUSOURCE=`".\PCU`" `n" |Out-File "DefaultSetup.ini" -Append

#merge Cumulative Update
if(!($CUSource -eq $null))
	Modify-Source "CU" $output $CUSource
	"`n`nCUSOURCE=`".\CU`" `n" |Out-File "DefaultSetup.ini" -Append

copy-item "defaultsetup.ini" .\ia64 -force
copy-item "defaultsetup.ini" .\x64 -force
copy-item "defaultsetup.ini" .\x86 -force

Set-Location $startloc

Powershell Shredding

I’ve been playing around a bit recently with Powershell and XML. It’s one of those expirements where I’m not sure what the immediate benefit is, but it certainly is interesting seeing what kind of functionality we have available to us as data folks. I’m going to see what more I can coax out of it, but I wanted to share with you what I’ve learned so far.

First off, understand that I’m not that strong when it comes to XML. I get what it is, I understand the basic structure, but wrangling it isn’t something I’ve had to do a lot of. As a result, I’m still very much a newbie with XPath and XQuery. I understand nodes and properties, but then it starts to get muddy. Just a disclaimer before we get to far into this.

.NET, and by extension Powershell, has an XML data type.  This is useful because query plans are XML documents, whether we save them off or stored in the plan cache. So it’s a fairly simply matter to suck a query plan into an XML variable:

1$plan=(gc SomeSQLQuery.sqlplan)

From here, we can start browsing through our plan using the dot notation to parse the plan. The query plan itself is going to be found under the ShowPlanXML node. Under that, there’s a fairly complex layout that you can really dig into by looking at the full schema documentation. Suffice to say, if we want to see the SQL text from the query, we’d need to look at:


That’s a lot of drilldown! What’s worse is, if we wanted to started finding specific operators, we would quickly get lost in a recursive arrangement of RelOp nodes and actual operators. So if we want to extract something useful out of the XML, we need to leverage XPath using the .SelectNodes() method of the XML data. The only problem here is that, for some reason, the namespace that is contained within the plan confuses any calls using .SelectNodes(). To get around this, I basically ripped out the namespace so that the defaults can be used:

1$plan=(gc SomeSQLQuery.sqlplan) -replace 'xmlns=""'

At this point, I can now start using XPath to analyze my query plan. So if I wanted to pull up all my table scans:

$plan.SelectNodes('//TableScan/Object') | ft

Or, if I wanted to get all my table or index scans:

$plan.SelectNodes('//*[contains(name(),"Scan")]/Object') | ft

And so and so forth.

Now, what does this get me? At this point, I’m not sure. I started down this road after seeing Jason Strate’s(@StrateSQL) presentation on shredding the plan cache with T-SQL. My thought process was that this might be an easier way to dissect the plan cache for useful information. In a way, I was right, because it was a little easier to grasp, but it also seems like it’s the long way around the horn to get at that information. I’ll continue to poke at it and see what I can coax out of it.

Finding Orphan Logins

Cleaning up old security info is always something that’s hard to stay on top of.  Employees who leave or change responsibilities can make it difficult to track which logins need to remain in our instances.  The best way to manage this is by using Active Directory groups and managing access from the group level instead of by explicit login.  However, because of the communication and organization that’s required to set up these groups in the first place, it’s not uncommon to find ourselves managing explicit logins in our severs.

The unfortunate side effect is this typically results in orphaned logins within our SQL Servers.  A question on how to manage this came up recently on and, while I couldn’t find a way to answer the specific question (because it was focused around finding orphaned logins in SQL 2000), I was able to come up with a handy utility script for 2005+ that makes use of xp_logininfo.  It’s not a complex script and provides a list of the actual drop login statements you can execute as part of your cleanup.

declare @user sysname
declare @domain varchar(100)

--store total output
declare @return table
(login_name sysname
,sql_statement varchar(max)
,error_msg varchar(100))

--Store output of xp_logininfo
declare @junk table
(a_name sysname NULL
,a_type char(8) NULL
,priv char(9) NULL
,m_login sysname NULL
,p_path sysname NULL)

--Declare domain you are checking
set @domain = 'FOO'

declare recscan cursor for
select name from sys.server_principals
where type = 'U' and name like @domain+'%'

open recscan
fetch next from recscan into @user

while @@fetch_status = 0
	begin try
		insert into @junk
		exec xp_logininfo @user
	end try
	begin catch
		--Error on sproc because login does not exist
		insert into @return
		values(@user,'drop login '+convert(varchar,@user),ERROR_MESSAGE())
	end catch

	fetch next from recscan into @user

close recscan
deallocate recscan

select * from @return

A couple notes on this script:

  • It currently doesn’t handle removing the logins from individual databases.  I have another script I use to do that and will be working on combining the two.
  • I don’t automatically execute the drop login because I’m paranoid.  :)  By providing a list, I can review the logins, along with the error message output, and verify that I want to remove them

Hopefully this little utility script can help you with login management in your environments.