Art of the DBA Rotating Header Image

Validation and Inspiration

I’d like to take a brief break from the technical posts to talk a little bit about community. As I write this, I’m currently heading back to Denver from SQL Saturday Phoenix. As with other SQL Saturdays I have attended, this was a fun event with lots of great learning and camaraderie with my fellow SQL geeks. This is a bit of a love letter to those geeks, but I wanted share with you some of the impact this event had on me.

Validation

As with everything else I’ve done in 2015, my presentation at this event was Powershell related.  I gave a presentation on Powershell Tips and Tricks for SQL Server DBAs for the third time this year and was pretty pleased with my execution. What blew me away was the reception from the audience. I had a ton of positive feedback and comments and could tell the attendees really appreciated what I shared with them.

Why am I telling you this? Because I want to convey to you why you should present and the benefits of it. It’s more than just having your ego stroked or getting that pat on the back (though those don’t hurt). When you share your knowledge, you have an immediate and profound impact on other people’s careers. Each and every one of us has something that others can benefit from.  We need to share it. To know that I showed my audience a better way to do their jobs and help them step up to another level is extremely gratifying.

Much is made of technical presentations being used as a vehicle for advancing your career. They are also a vehicle to advance the careers of your peer group. The great thing is the more we help each other, the more we help ourselves and make our skills and abilities stronger. I could see this in the gratitude of my audience and the feedback they gave me from my session.

Inspiration

What I like most about technical conferences is not just the education and the sessions. These are valuable for both the presenters and attendees, but the true value is gained in the conversations that happen around the event. This is why it’s so important to make time to talk with the other people at these events, to chat with speakers, and to avail yourself of the social aspects. You’ll find inspiration for solving problems at work, developing new strategies for your current position, or defining the next moves in your career.

I had several such conversations while I was at the event. Coming away from this SQL Saturday, I was able to help some of my peers with strategies and ideas for their blogs, their presentations, and their jobs. Beyond that, though, I was inspired for other things I could do to both improve myself and my career.

One example was a conversation with Amy Herold(@texasamy), where we talked a lot about Powershell and automation. She gave me a few ideas that I could further develop and we talked about some projects we could collaborate on. I’ve got some exciting ideas that I hope to work with Amy on over the next few months that will help both of us grow.

It’s hard to have these kind of conversations during our day-to-day jobs. Since we’re usually only one of a couple people (or maybe the lone gunman) doing data work in our jobs, it’s difficult to bounce ideas off of others and get that inspiration. You’d be amazed at what kind of thoughts you will get when you have really smart people to talk with.

Satisfaction

The reason SQL Saturdays are such great events is it allows the greater SQL community to share, connect and learn with one another. If you haven’t been to a SQL Saturday, I strongly encourage you to go. I know it’s sometimes tough, being on a weekend when some of us would rather be getting along with our non-database lives.  I want you to think about what you could do with your career, though, if you gave up that one weekend. Where could you go if you could have that kind of free learning. Most of all, how much better will you be by plugging in to the SQL community and feeding off the energy and knowledge you can find at these events. Building your career is more than just learning, it’s collaborating and sharing. SQL Saturdays give you all of this in spades.

 

#Powershell and Automating SQL Server Builds-Part 3

Hopefully you’ve seen over the last two posts some basic techniques for automating and codifying your SQL Server builds.  There’s just two more items I want to cover here, questions you’re probably asking yourself already.

The build script

Let’s start with reviewing the whole build script to this point. While I wouldn’t recommend running this in production yourself, the finalized version looks something like this:

Run this from the install binary location
[Reflection.Assembly]::LoadWithPartialName(“System.Web”)
$SAPassword = [System.Web.Security.Membership]::GeneratePassword(16,4)
.\setup.exe /CONFIGURATIONFILE=<config file location> /SAPASSWORD=$SAPassword /IACCEPTSQLSERVERLICENSETERMS

#Configure the OS
New-Item -ItemType Directory G:\MSSQL\Data
New-Item -ItemType Directory H:\MSSQL\Logs
New-Item -ItemType Directory I:\MSSQL\TempDB

#Configure Instant File Initialization
$svcaccount = 'SDF\sqlsvc'
secedit /export /areas USER_RIGHTS /cfg C:\templocalsec.cfg
$privline = Get-Content C:\templocalsec.cfg | Select-String 'SeManageVolumePrivilege'
(Get-Content C:\templocalsec.cfg).Replace($privline,"$privline,$svcaccount") | Out-File C:\newlocalsec.cfg
secedit /configure /db secedit.sdb /cfg C:\newlocalsec.cfg

#Open the firewall for 1433
New-NetFirewallRule -DisplayName "Allow SQL Server" -Direction Inbound –LocalPort 1433 -Protocol TCP -Action Allow

#Set Server configurations
$smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') localhost
$smosrv.Configuration.MaxServerMemory.ConfigValue = 4000
$smosrv.Configuration.MinServerMemory.ConfigValue = 2000
$smosrv.Configuration.MaxDegreeOfParallelism.ConfigValue = 4
$smosrv.Configuration.OptimizeAdhocWorkloads.ConfigValue = 1
$smosrv.DefaultFile = 'G:\MSSQL\Data'
$smosrv.DefaultLog = 'H:\MSSQL\Logs'
$smosrv.Alter()

#disable sa
$smosrv.Logins['sa'].Disable()

Next Steps

After this, we should consider deploying maintenance jobs, restoring databases, or building out things like availability groups. While I don’t cover these here, keep in mind these tasks can be scripted out as well.  I encourage you to consider how you could do this.  For example, if you leverage either Ola Hallengren’s scripts or the new Minion Reindex from the Midnight DBAs(@MidnightDBA), you have another piece you can automate. The key is that you have a repeatable process.

At Xero we have all our maintenance jobs as part of our administrative database deployment. This is handled through SQL Server Data Tools and .dacpacs. We achieve consistency and deployment speed by managing that solution and deploying the administrative database project. By maintaining the database in source control we manage our tool set, keeping it standardized.  When we add or update it, we can apply the changes out to our environment. This also means when we build a new server, we have a standardized way to install our admin tools with a minimum of muss and fuss.

The Point

Automation is more about consistency than speed, but speed is a nice side benefit. By assembling these components into a single script, we have a repeatable build process for any SQL Server in our environment. Our instances will be built the same way, every time, so long as we aren’t changing our script. The bonus is, since it’s all scripted, there’s no fumbling with wizards, dialog boxes, and making sure we type in the right values, so it all just happens. And in a matter of minutes. At Xero, I can take a server from nothing to ready for databases in about 20 minutes using these techniques.

Now, we could go pretty crazy with how we build our automation script. In fact, there’s some pretty cool tools out there that will help you with this. I’ll let you do that on your own. Just make sure that, before you do, you know all the steps you have to build your SQL Server. It’s fine and dandy to be able to script out things, but without a plan or process to automate first, most of the scripts you could write won’t do you much good because you can’t use them again.

P.S. HUGE thanks out to Melody Zacharias(@SQLMelody) for helping me with this series of posts.

#Powershell and Automating SQL Server Builds-Part 2

When last we left our heroes, we were discussing how best to go about building SQL Servers quickly and consistently using Powershell.  The thing is, we hadn’t quite gotten to the Powershell part yet and only really covered the build process plus installing SQL Server.  Let’s change that and move on to the next steps.

Configure the OS

It’s time to flex Powershell. Because Powershell works directly with the OS, it makes it really easy to perform tasks that SQL Server isn’t so good at. Let’s start with creating three directories for our database files:

New-Item -ItemType Directory G:\MSSQL\Data
New-Item -ItemType Directory H:\MSSQL\Logs
New-Item -ItemType Directory I:\MSSQL\TempDB

This assumes that our sysadmins have built the box with those LUNs. There are, of course, ways to script that out as well, but we’ll skip over that for the sake of brevity.

Enabling Instant File Initialization is a little tricky, because editing the local security policy is not something Powershell can do directly. Insert a harumph here. Anyway, we can do some tricks using the secedit tool. This technique, borrowed from Kyle Neier(@Kyle_Neier), basically dumps out the local security policy to a text file, updates it, then re-imports it to the local security policy. Kyle provides  a full function to use, but you can boil it down to the following script:

$svcaccount = 'SDF\sqlsvc'
secedit /export /areas USER_RIGHTS /cfg C:\templocalsec.cfg
$privline = Get-Content C:\templocalsec.cfg | Select-String 'SeManageVolumePrivilege'
(Get-Content C:\templocalsec.cfg).Replace($privline,"$privline,$svcaccount") | Out-File C:\newlocalsec.cfg
secedit /configure /db secedit.sdb /cfg C:\newlocalsec.cfg

As a quick aside, you might want to consider working with your sysadmins to actually set this permission in your domain Group Policy. Means you’ll get it without having to configure it yourself.

Finally, we want to make sure we open the firewall for port 1433:

New-NetFirewallRule -DisplayName "Allow SQL Server" -Direction Inbound –LocalPort 1433 -Protocol TCP -Action Allow

Configuring SQL Server

Now with the OS configured, it’s time to configure our SQL Server. For this, we’re going to leverage our friend and pal, the .Net SMO. As with many things in the SMO, the beauty of it is that we get a consistent API to interface with our SQL Server. For our purposes, we want to look at the Configurations collection of the Microsoft.SqlServer.Management.Smo.Server object. In here we can find everything you’d find in the sys.configurations view through T-SQL. Now let’s configure our max and min memory, max degree of parallelsim, and optimize for ad hoc queries.

$smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') localhost
$smosrv.Configuration.MaxServerMemory.ConfigValue = 4000
$smosrv.Configuration.MinServerMemory.ConfigValue = 2000
$smosrv.Configuration.MaxDegreeOfParallelism.ConfigValue = 4
$smosrv.Configuration.OptimizeAdhocWorkloads.ConfigValue = 1
$smosrv.Alter()

Simple enough, right? You can get pretty fancy and add all sorts of logic to calculate these values, giving you a flexible setup script.

You could just as easily write these as a T-SQL script for a series of sp_configure executions to set your values. I’ve done it and know plenty of people who do. There are two reasons I’m moving away from this. The first is that some of my configurations, such the default database directories, need some specialized techniques (usually involving xp_regedit or something similar) to implement in T-SQL. I don’t like that approach because the extended stored procedures are usually unsupported and can have lots of issues. Powershell is much more graceful and (the second reason), using it across the board gives me a common interface for my configuration management.

In fact, let’s look at the default database directory settings. As stated, if you want to do this programatically in T-SQL, you are going to need to use xp_regedit to update the registry keys. Using the SMO is much, much easier:

$smosrv.DefaultFile = 'G:\MSSQL\Data'
$smosrv.DefaultLog = 'H:\MSSQL\Logs'
$smosrv.Alter()

Piece of cake, right? One last thing to do now, and that is disable the sa account:

$smosrv.Logins['sa'].Disable()

And we’re done!

To be concluded…

This builds the core of our server, but there’s a couple more odds and ends to discuss.  We’ll wrap up tomorrow with a few things, like how to handle maintenance jobs and other administrative tools.  We’ll also talk a little bit about the “why” of all of this.  Stay tuned for the thrilling conclusion!

(Well, I think it’s thrilling.)

#Powershell and Automating SQL Server Builds

Last week, my friend John Morehouse(@SQLrUs) and I had a bit of twitter banter about breaking and fixing things. Of course, my joke is usually “I have a script for that”, to which John replied:

Amusingly enough, I kind of do. It’s not quite the way you would think, though. Lately I’ve had a different attitude about “fixing” things in my environments, because sometimes it takes waaaaaaay more time to try and repair stuff than to just build it from scratch. Especially if you are scripting out your processes and automating your builds. This is what I would say to John;  if he walked in to my data center and abused my poor server with a wooden implement, I’d simply build a new one.

Before we get much further here, let’s talk about the fine print. Everything I’m going to talk about doesn’t get around backups, DBCC checks, or other processes we need to address as DBAs. We’re not talking about the data here, simply the server that hosts it all. It’s still vital that you do your due diligence to protect your data.

Building SQL Servers

So let’s talk about building our SQL Servers. Firstly, you should always have a defined build process for your environments even if you haven’t had a chance to automate or script it. Checklists and runbooks are the starting points for consistency in any IT shop and if you don’t have them, make them.

For our purposes, let’s assume the following build process. This is pretty basic and there’s lots of possible elements we could have, but I want to start simple. Here’s what we’re going to do:

  1. Install SQL Server
  2. Configure the OS
    1. Directory structures
    2. Instant File Initialization
    3. Open the firewall for SQL Server
  3. Configure SQL Server
    1. Configure max and min server memory, our maxdop setting, and optimize for adhoc queries
    2. Set default database directories
    3. Disable the sa account

I’m not going to give you a comprehensive script at this time, but instead show you how so you can use the same tricks in your environment. The reason for this is that everyone builds their servers a little differently, but if you define and script your process, you can  have your servers built and configured in 15-20 minutes.

Install SQL Server

This step is actually the easiest and has been around for a while, but I want to add a few twists to it. I always install SQL Server using a configuration.ini file. By using this approach, you can install your SQL Servers the same way, every time. You’ll find plenty of articles on the Googles on how to do this, so I won’t dive into it, but I want to address the matter of passwords.

The problem with using the .ini file is most folks (and this includes me) will put the passwords in plaintext in the file. This has numerous bad security implications. Depending on how accessible this file is, you could be giving away your service account and sa passwords and not even know it.

One step I take to manage this is I no longer include my sa password in the file, but instead randomly generate it when I install SQL Server. I went down this path because my friend Argenis Fernandez(@DBArgenis) got me thinking about how the sa account really gets used. The fact of the matter is that I rarely, if ever, log in to the server as sa. In my opinion, no one should even be using that account. This is why I will disable the account on my servers. I’m going to forgo the debate about whether this is a good idea or not (plenty of opinions on either side), but instead just work on the assumption that I need to provide some password to the account for the install. I still want a strong password, though, so I use the following code in Powershell to create my password and pass it as a command line switch. Feel free to read more up on the System.Web.Security.Membership GeneratePassword method for the details on how this works:

[Reflection.Assembly]::LoadWithPartialName(“System.Web”)
$SAPassword = [System.Web.Security.Membership]::GeneratePassword(16,4)
.\setup.exe /CONFIGURATIONFILE=<config file location> /SAPASSWORD=$SAPassword /IACCEPTSQLSERVERLICENSETERMS

This gets my install complete and, honestly, is the longest part of the process. There are ways to cheat around that, but I’ll save that for another post.

To Be Continued…

When I first started writing this, I figured it would be a quick post. Suddenly I found I was at 5 pages and going. So we’re going to break it up to make it a little more manageable.  Tune in tomorrow when we cover configuring the OS and SQL Server itself.

Copying SQL Logins with #Powershell

Managing SQL server logins, especially in the days of availability groups, can be quite the pain in the butt. Usually we’ll make use of scripts like sp_help_revlogin to manage this, copying logins en masse from one instance to another.  This works, but it’s pretty brute force and there might be logins you may not want to copy across.

So let’s use Powershell to be a little more…elegant. There’s a couple things we can use to get a nice login copying function that is flexible and robust.  The first is the .Script() method in the SMO’s Login object. This is actually a piece of functionality many of us use daily, whenever we script out objects in SQL Server Management Studio. The trick is to access this programmatically, which is a snap in Powershell.  The basic syntax is something like this:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$smoserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server localhost
$smoserver.Logins[‘SHION\mike’].Script()

What’s awesome about this method is the SMO will also script out all of the additional server level options, like default database and server roles.  Very handy for maintaining the consistency of the logins from one instance to another.

There are a couple of gotchas. First , if we want to include the SID in our script, we have to create a  ScriptingOptions object and specify that the LoginSid should be included. Second, if the login is a SQL Server login, the .Script() method will add in a line to disable the login and give it a completely random password. This is done for security reasons, so that any login scripted out can’t create a possible security hole. As an FYI, you’ll get the same if you script out the login in SSMS.

So what do we do? Managing passwords across instances is tricky, but must be done, otherwise things will start to break. With Windows based logins it’s simple, since that’s all managed in the Active Directory. SQL logins require more care. To handle this in T-SQL, you’ll want to create the login with the SQL Server password hash. Unfortunately, the SMO doesn’t provide an easy way to do this, so we have to get clever. To be clever, we have to:

  1. Retrieve the binary hash value from the source SQL instance.
  2. Convert the binary hash to a string for use in a CREATE LOGIN statement.
  3. Insert the password hash string into our CREATE LOGIN statement in the correct place.

Easy enough, right?  Honestly, all it takes is some string manipulation, which Powershell is pretty darn good at. Certainly better than SQL Server itself.  Turns out this is the biggest hurdle to the process and, once solved, the rest of the script comes together quite nicely.

The following function brings all these concepts together. It’s a little more complex than other scripts I’ve posted, but here’s the highlights:

  • You must declare a source instance. This is the instance from which all logins will be scripted from.
  • You can specify a string array/collection of logins. The function will only script out the logins you list. If you don’t list any (argument is null), the function will script out all non-system logins (everything but sa, pretty much).
  • Because I like the pattern of scripts to build scripts, the function will create a .sql script of all your create login statements. By default this will go to your My Documents folder, but you can specify another directory if you want.
  • If you provide an instance to the ApplyTo parameter, the function will attempt to create the logins on that instance if they don’t exist. Pass the -Verbose switch if you want to see which ones it creates and which ones it skips.
function Copy-SQLLogins{
    [cmdletbinding()]
    param([parameter(Mandatory=$true)][string] $source
            ,[string] $ApplyTo
            ,[string[]] $logins
            ,[string] $outputpath=([Environment]::GetFolderPath("MyDocuments")))
#Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#create initial SMO object
$smosource = new-object ('Microsoft.SqlServer.Management.Smo.Server') $source

#Make sure we script out the SID
$so = new-object microsoft.sqlserver.management.smo.scriptingoptions
$so.LoginSid = $true

#set output filename
$filename = $source.Replace('/','_') + '_' + (Get-Date -Format 'yyyyMMddHHmm') + '_logins.sql'
$outfile = Join-Path -Path $outputpath -ChildPath $filename

#If no logins explicitly declared, assume all non-system logins
if(!($logins)){
    $logins = ($smosource.Logins | Where-Object {$_.IsSystemObject -eq $false}).Name.Trim()
}

foreach($loginname in $logins){
    #get login object
    $login = $smosource.Logins[$loginname]

    #Script out the login, remove the "DISABLE" statement included by the .Script() method
    $lscript = $login.Script($so) | Where {$_ -notlike 'ALTER LOGIN*DISABLE'}
    $lscript = $lscript -join ' '

    #If SQL Login, sort password, insert into script
    if($login.LoginType -eq 'SqlLogin'){

      $sql = "SELECT convert(varbinary(256),password_hash) as hashedpass FROM sys.sql_logins where name='"+$loginname+"'"
      $hashedpass = ($smosource.databases['tempdb'].ExecuteWithResults($sql)).Tables.hashedpass
      $passtring = ConvertTo-SQLHashString $hashedpass
      $rndpw = $lscript.Substring($lscript.IndexOf('PASSWORD'),$lscript.IndexOf(', SID')-$lscript.IndexOf('PASSWORD'))

      $comment = $lscript.Substring($lscript.IndexOf('/*'),$lscript.IndexOf('*/')-$lscript.IndexOf('/*')+2)
      $lscript = $lscript.Replace($comment,'')
      $lscript = $lscript.Replace($rndpw,"PASSWORD = $passtring HASHED")
    }

    #script login to out file
    $lscript | Out-File -Append -FilePath $outfile

    #if ApplyTo is specified, execute the login creation on the ApplyTo instance
    If($ApplyTo){
        $smotarget = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ApplyTo

        if(!($smotarget.logins.name -contains $loginname)){
            $smotarget.Databases['tempdb'].ExecuteNonQuery($lscript)
            $outmsg='Login ' + $login.name + ' created.'
            }
        else{
            $outmsg='Login ' + $login.name + ' skipped, already exists on target.'
            }
        Write-Verbose $outmsg
        }
    }
}

There is one additional piece to this, and that’s a helper function for converting the binary hash value to a string. It’s pretty simple, taking the value passed to it and doing some formatting magic to get a value for the SQL statement.

function ConvertTo-SQLHashString{
  param([parameter(Mandatory=$true)] $binhash)

  $outstring = '0x'
  $binhash | ForEach-Object {$outstring += ('{0:X}' -f $_).PadLeft(2, '0')}

  return $outstring
}

Bring this all together and now we have a straightforward function we can call to help us manage our logins. Some examples:

#Create a SQL Script in My Documents for all logins
Copy-SQLLogins -source 'localhost'

#Create a SQL Script in My documents for the passed logins
Copy-SQLLogins -source 'localhost' -logins @('SHION\mike','test','pcuser')

#Create a SQL Script in My documents for the passed logins and try and create these logins on the target SHION\ALBEDO
Copy-SQLLogins -source 'localhost' -logins @('SHION\mike','test','pcuser') -ApplyTo 'SHION\ALBEDO' -Verbose

Hopefully this helps you in your day to management of your instances. I’ve used this script for migrations and managing my Availability Group nodes. In my opinion, it’s a heck of a lot cleaner than sp_help_revlogin (a venerable tool in its own right, to be sure). As always, this along with my other scripts, can be found up on my GitHub repository

#tsql2sday 63 – Security, Default Databases, and #Powershell

This month’s T-SQL Tuesday is about how we manage database security, hosted by the ever effervescent Kenneth Fischer(@sqlstudent144). While I didn’t have a lot of time to work on this post, I wanted to share with you all a quick little nugget I used for one of my most recent presentations.

So, default databases. This is a security bit that usually gets skipped over, unfortunately, but I think it’s a pretty important practice that is easy to implement and maintain. Let’s be honest, most of us leave the default database as ‘master’ when we create them. This then will usually lead us to accidentally doing work in the master, which potentially could be disastrous.

2042603602_fc289cf395_zSecurity is more than just locking people out or letting people in. While most of the situations with master can be be limited by reduced permissions, it doesn’t do anything to help or manage accounts that need to be able to work in that database.  Security is not just about active policies, but also passive rules to reduce and prevent mistakes. A lot of damage can be prevented by directing people out of dangerous places by default.

Fixing this is easy:

ALTER LOGIN [foo\bar] WITH DEFAULT_DATABASE = [tempdb];

Also, we’d ideally create the logins with that default database set to begin with:

CREATE LOGIN [foo\bar] FROM WINDOWS WITH DEFAULT_DATABASE=[tempdb];

However, this can be difficult to manage and keep up to date, especially since SQL Server doesn’t help by always defaulting to master. One thing that can be done is to have a way to set all your logins’ default databases to an appropriate database (I really like tempdb for this). So how about some Powershell?

Using the SMO, we can use the DefaultDatabase property of the Login class for a quick and easy way to alter our default database:

$login.DefaultDatabase = ‘tempdb’

Then, using the Logins collection in our Server object, we have an easy loop to go through an alter these default databases. Wrap that up in a fucntion and Bob’s your uncle. Well, he’s my uncle, not yours. Anyway, we’ll get a little something like this:

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

function Set-SQLDefaultDatabases{
    param([string[]]$Instances = 'localhost'
         ,[string]$defaultdb = 'tempdb')

foreach($InstanceName in $Instances){
    $smosrv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server $InstanceName
    if($smosrv.Databases.name -contains $defaultdb){
        foreach($login in $smosrv.Logins){
            Write-Verbose "Altering $login on $InstanceName to $defaultdb"
            $login.DefaultDatabase = $defaultdb
            }
        }
    else{
        Write-Warning "Database $defaultdb is not valid on $InstanceName."
        }
    }
}

Set-SQLDefaultDatabases

So this simple function will set all the logins (SQL, Windows, and AD Groups) to the default database of choice. It’s fairly brute force, but gives you a good starting point. At this point, you should be able to see the value of writing it in this function format and use techniques I’ve blogged about earlier to set this enterprise wide.

Thanks out to Ken for hosting this month’s T-SQL Tuesday! Looking forward to reading the other blogs!

Dynamically Extracting .dacpacs With #Powershell

As DBAs, backing up our databases isn’t quite enough.  We also want to back up our schemas for additional protection.  And while we could go through some complex scripting to actual write out all those schema objects, Microsoft has given us a handy tool with SQL Server Data Tools and Visual Studio to capture these schemas.

I’m talking about sqlpackage.  I hadn’t used this before I got to my current job, but it really is a snap.  Using sqlpackage (in conjunction with managing your database through a Visual Studio project), you can easily deploy or script out database code changes.  You can also extract your schema with a few simple commands, like so:

sqlpackage.exe /action:extract /targetservername:localhost /targetdatabasename:dummy /targetfile:someawesome.dacpac

So this is great and all, but when you’ve got dozens or even hundreds of databases, you need a quick and efficient way to run this command.  Since this is a command line operation, Powershell becomes the natural way to do this.  Here’s a simple script that will allow you to extract all the user database schemas from a server:

#extract all your database schemas as dacpacs
$server = 'PICARD'

$dbs = Invoke-Sqlcmd -ServerInstance $server -Database tempdb -Query 'SELECT name FROM sys.databases WHERE database_id >4'

foreach($db in $dbs.name){
$cmd = "& 'C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe' /action:Extract /targetfile:'C:\dacpacs\$db.dacpac' /SourceServerName:$server /SourceDatabaseName:$db"

Invoke-Expression $cmd
}

Let’s look at two things in play here.  The first is the use of the ‘&’, which is the call operator.  This takes a string expression you create and then executes it.  In this case, we build out the expression with that operator.  Then we use item number two, Invoke-Expression, to run the expression we built.  The rest is pretty simple.

This is a quick and dirty version of it, but we could get a lot fancier.  In fact, Gianluca Sartori(@spaghettidba) has a very nice blog post where he provides a function to do this.  My own function (which looks pretty similar) goes a little something like this:

function Export-SQLDacPacs{
    param([string[]] $Instances = 'localhost',
          [string] $outputdirectory=([Environment]::GetFolderPath("MyDocuments"))
)

#get the sqlpackage executable
$sqlpackage = (get-childitem C:\ -Recurse | Where-Object {$_.name -eq 'sqlpackage.exe'} |Sort-Object LastWriteTime | Select-Object -First 1).FullName

#declare a select query for databases
$dbsql = @"
SELECT name FROM sys.databases
where database_id >4 and state_desc = 'ONLINE'
"@

#loop through each instance
foreach($instance in $Instances){
    #set processing variables
    $dbs = Invoke-Sqlcmd -ServerInstance $instance -Database tempdb -Query $dbsql
    $datestring =  (Get-Date -Format 'yyyyMMddHHmm')
    $iname = $instance.Replace('\','_')

    #extract each db
    foreach($db in $dbs.name){
        $outfile = Join-Path $outputdirectory -ChildPath "$iname-$db-$datestring.dacpac"
        $cmd = "& '$sqlpackage' /action:Extract /targetfile:'$outfile' /SourceServerName:$instance /SourceDatabaseName:$db"
        Invoke-Expression $cmd
        }
    }
}

Export-SQLDacPacs -instances 'PICARD' 

What’s cool is with this is a function, we can now extend it and do some really cool things.  Remember that CMS lookup we used last week?  Let’s use it here:

$CMS=’PICARD’
$servers=@((dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$CMS").Name)

$servers+=$cms
Export-SQLDacPAcs -Instances $servers

And now I have a script to extract all my user database schemas from all the instances in my enterprise.  How cool is that?  Now I can put this into an agent job or a Windows scheduled task and have a regular schema extract, giving me an extra layer of protection for my systems.

Am I Alive? SQL Connectivity Checks with #Powershell

Inevitably, there will be the call from a developer or admin saying “I can’t connect to the MORDOR SQL Server!  Everything is terrible!  All is darkness and shadows!  HELP!”.  After explaining that one does not simply connect to MORDOR, I fire up SQL Server Management Studio and attempt to log in.  It’s not particularly onerous, but it takes time and can’t be particularly automated for more proactive connectivity checks.

The connectivity check, at the heart of it, isn’t really that complex.  Usually it’s just a case of connecting and running a simple select statement.  I’ve done this proactively in the past using a central management server and linked servers for all the instance in my environment.  While this works, it’s difficult to manage, automate, and isn’t very mobile.  Me being me, I turn to Powershell for a better way.

Test-SQLConnection

The function here is pretty simple: Loop through a collection of instance names and try to query the server name and TempDB creation time.  If I get a result set, then my connection test is successful.  If it errors out, then no connection.  Essentially, the test is whether or not my return set has a StartupTime (TempDB creation date) or not.  So now I can pass single instance or collection and test them all with the one function.

function Test-SQLConnection{
    param([parameter(mandatory=$true)][string[]] $Instances)

    $return = @()
    foreach($InstanceName in $Instances){
        $row = New-Object –TypeName PSObject –Prop @{'InstanceName'=$InstanceName;'StartupTime'=$null}
        try{
            $check=Invoke-Sqlcmd -ServerInstance $InstanceName -Database TempDB -Query "SELECT @@SERVERNAME as Name,Create_Date FROM sys.databases WHERE name = 'TempDB'" -ErrorAction Stop -ConnectionTimeout 3
            $row.InstanceName = $check.Name
            $row.StartupTime = $check.Create_Date
        }
        catch{
            #do nothing on the catch
        }
        finally{
            $return += $row
        }
    }
    return $return
}

Test-SQLConnection -Instances 'localhost'

Why do I return the TempDB creation date?  Well, a boolean value is easy enough to return, but I figure I could use more info to work with.  After all, if data comes back, then I know the connectivity is working.  So let’s do a little more.  By returning the TempDB creation date, I can find out if my instance restarted recently (or if it’s been up for a really long time).  This gives me a little more information to work with.

Extending the functionality

While calling this function adhoc is easy enough, the reason to put it in to Powershell is not so we can just have another way to test our connections.  Automation is the key here.  So what if we took this and combined it with another technique, getting a server collection from CMS?

$CMS='SHION'
$servers=@((dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$CMS").Name)

$servers+=$cms
Test-SQLConnection -Instances $servers

Using this, we can quickly test the connection of all the servers in our CMS.  We can also check to see if any have started up recently, perhaps warning us of a restart of our SQL Services.  From here, it’s not a tremendous leap to load it into a database table for regular health checks.

Another tool for the toolbox

What’s great about these functions is that once I write it, I have a new tool I can use.  Initially I wrote this function so I can verify which servers in my active directory are actually SQL Servers (assuming the default port, of course).  As we can see here, though, the function can be used for a variety of purposes.

 

Making SQL Backups better with #Powershell

SQL Server backups are a key part of any database administrator’s job and one of the first items that will get automated in an environment. In my career, I have seen a number of different approaches to this task, usually a combination of T-SQL and SQL Agent Jobs. When using these, you usually have two different approaches. The first is to create a single script to dynamically identify your databases and back them up serially. The second is to create multiple agent jobs, one for each database, that can execute in parallel, but must be created as static jobs.

Either approach presents us with some hurdles. The first enables us to dynamically handle adding databases to the server, but the backups must be executed serially. Serial backups can extend maintenance windows by not making efficient use of our resources. The second gives us the ability to run our backups in parallel, but leaves us with multiple jobs that are static and must be managed as new databases are added. Neither of these options are really ideal.

What if there was a third way?

Enter Powershell Jobs. While I usually don’t advocate database backups using Powershell (because they can be handled quite well with native T-SQL), I have come across a handy technique using Powershell Jobs and T-SQL to allow me to dynamically create backup statements and execute them in parallel. This allows me to find that intersection between minimal management and increased efficiency for my database backup management.

So what are Powershell Jobs?  They are a mechanism within Powershell for creating background jobs that can execute independent of the session that initiates them. Using this functionality, we can start multiple executions of any Powershell command in parallel, allowing them to run while other tasks execute. To find out more, you can read the documentation by running Get-Help about_Jobs or look them up on MSDN.

Start-Job is the key to this technique. To implement, I first use T-SQL code to get my databases for backup, much like I would when using pure T-SQL. Using this T-SQL, I will create an array of databases to be backed up and use that array to build a script block. This script block will contain a call to Invoke-SqlCmd for each backup statement, which can then be used with Start-Job to execute each backup job.

$dbs = Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query "SELECT name FROM sys.databases WHERE database_id > 4"
$datestring =  (Get-Date -Format 'yyyyMMddHHmm')

foreach($db in $dbs.name){
    $dir = "C:\Backups\$db"
    if(!(Test-Path $dir)){New-Item -ItemType Directory -path $dir}
    
    $filename = "$db-$datestring.bak"
    $backup=Join-Path -Path $dir -ChildPath $filename
    $sql = "BACKUP DATABASE $db TO DISK = N'$backup'"
    $cmd = "Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query `"$sql`" -QueryTimeout 6000;"
    $cmd += "Get-ChildItem $dir\*.bak| Where {`$_.LastWriteTime -lt (Get-Date).AddMinutes(-10)}|Remove-Item;"
    [scriptblock]$cmdblock = [ScriptBlock]::Create($cmd)
    Start-Job $cmdblock
}

In addition to using Powershell to create jobs, we also get some bonus features. For example, I can use Test-Path to validate my backup directories exist (and create them if they don’t).

(!(Test-Path $dir)){New-Item -ItemType Directory -path $dir}

Backup paths are big deal for SQL Server, because if that path doesn’t exist, we get an ugly backup error.  When using a T-SQL script for backups, administrators still need some additional step to create the backup directories. This is a task that is much easier using Powershell.
We can also leverage Powershell for deleting old backups by adding the following line:

$cmd += "Get-ChildItem $dir\*.bak| Where {`$_.LastWriteTime -lt (Get-Date).AddMinutes(-10)}|Remove-Item;"

Deleting files is something SQL Server is also particularly bad at. I’ve had a lot of sleepless nights that were the result of old backup files not being cleaned up. By leveraging this one line in the script block, the job will delete any full backups older than 10 minutes. We could change this window or be more elegant as our needs require, but the end result is that we only keep the most recent backup file.

What’s nice is that the script can be easily extended for log backups without a lot of retrofitting. I make four changes for this. The first is to filter our database query to ignore any databases in SIMPLE mode, as they can’t have their log files backed up.

$dbs = Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query "SELECT name FROM sys.databases WHERE database_id &gt; 4 AND recovery_model_desc != 'SIMPLE'"

The second is change the backup command from BACKUP DATABASE to BACKUP LOG.

$sql = "BACKUP LOG $db TO DISK = N'$backup'"

Next, I change the backup file extension from .bak to .trn, because I prefer to distinguish my backup file types in this manner.

$filename = "$db-$datestring.trn"

Finally I change the retention period for the delete step to 2 days, so I can keep transaction logs on disk in the event I need them for recovery.

$cmd += "Get-ChildItem $dir\*.trn| Where {`$_.LastWriteTime -lt (Get-Date).AddDays(-2)}|Remove-Item;"

The end result is this script, which works much like our full backup script:

The end result is this script, which works much like our full backup script:

$dbs = Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query "SELECT name FROM sys.databases WHERE database_id > 4 AND recovery_model_desc != 'SIMPLE'"
$datestring =  (Get-Date -Format 'yyyyMMddHHmm')

foreach($db in $dbs.name){
    $dir = "C:\Backups\$db"
    if(!(Test-Path $dir)){New-Item -ItemType Directory -path $dir}
    
    $filename = "$db-$datestring.trn"
    $backup=Join-Path -Path $dir -ChildPath $filename
    $sql = "BACKUP LOG $db TO DISK = N'$backup'"
    $cmd = "Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query `"$sql`" -QueryTimeout 6000;"
    $cmd += "Get-ChildItem $dir\*.trn| Where {`$_.LastWriteTime -lt (Get-Date).AddDays(-2)}|Remove-Item;"
    [scriptblock]$cmdblock = [ScriptBlock]::Create($cmd)
    Start-Job $cmdblock
}

So how do we use this in SQL Server? We can stick with the previous pattern of using SQL Server Agent Jobs. Since SQL Server 2008, Agent Jobs have supported a Powershell step. To implement that step, we would create our same backup job in the Agent as before, but now instead of creating a T-SQL step we can create a Powershell step and insert our script, like so:

MSF_AddJobStep_PoSHBackups

There is one additional piece of code we have to add to make it work, however. The way Agent Jobs operate is that once the script completes and our background jobs are initiated, the Agent Job step stops and closes our parent session. When this happens, the remote jobs we just kicked off get canceled and our backups don’t happen. We need to add a logic check to keep the Agent Job running until the background jobs are no longer running. This is simple and only requires the following code to accomplish our task:

$jobs = 1
while($jobs -gt 0){$jobs = (get-job | where {$_.State -eq 'Running'} | Measure-Object).Count}

With this in place, we can now schedule and run our backup task as we would have before, except now we have a more dynamic and more efficient Powershell script to do it with. We can also do the same with our log backup script as well.

Overall these are very basic scripts and could be enhanced, but the core functionality is what you should focus on. The emphasis is on making the best use of our tools and resources in the most efficient way possible. This approach is also nice for SQL Server administrators as the script is primarily a wrapper for T-SQL backup commands that we all know and love. For those SQL Server folks who are still getting their feet wet with Powershell, this is a very intuitive way to start using the syntax and get some quick bang-for-your-buck.

 

It’s business time!

Just a quick update on some on going items that will hopefully be of some interest to you.

SQL Saturday Albuquerque (358) and Phoenix (370)

For the third year in a row, I’ll be down at SQL Saturday Albuquerque.  I love this event and the people who run it, and going to Albuquerque is always a nice trip to the past for me (I used to live there many moons ago).  This year, I’m talking about Powershell.  A lot.  As in, I’ll be giving an Introduction to Powershell precon lot.  If you’re going to be in Albuquerque for this event and want (nay, need) to get started with Powershell, this is the perfect opportunity.  I’m starting from ground zero and building a foundation for learning and using Powershell in your environment.  I’d love to see you there.

Oh, and then I’ll be presenting a Powershell tips and tricks session at the event itself.

No precon at SQL Saturday Phoenix, but my Powershell tips and tricks presentation has been selected for that event as well.  I’m really excited about this session, because I can share some of the gotchas I’ve learned using Powershell and share it with folks who are just getting started.  The goal is to help people get going faster and better with Powershell.

A theme for 2015

As you can guess, there’s a bit of a theme here.  For me, 2015 will be the year of sharing Powershell with the community.  My community friends all know how much I love Powershell and the things I can do with it, but it’s still a technology that’s struggling to get traction in the SQL Server community.  There’s several reasons for that, but I think most of it is simply driven by a lack of familiarity.  No, I’m not going to tell you that Powershell should be used for everything.  It can do a lot and, especially in the day and age of the cloud and moving faster, this tool is becoming less optional.  I challenge you to get ahead of the curve and start learning.  Don’t worry, I’ll hold your hand.

The new gig

I’m not sure who knows, but I’ve been working a new job for about nine months now at a great company called Xero(@Xero).  It’s been a fun ride with some really smart people and I’m challenged every day.  Why do I mention this?  Because we’re hiring in our Denver office for a mid-level DBA.  If you want to work with me, get challenged every day, and work with the cutting edge of SQL Server (and other tech), fill out an app.

That’s all for now

Again, this was just a quick post for some business related items.  More of the usual content coming soon.