Art of the DBA Rotating Header Image

The #SQLNewBlogger Challenge: Git ‘er done!

This week my friend Ed Leighton-Dick(@elieghtondick) announced his New SQL Blogger challenge.  It’s an effort focused on getting new bloggers to write regularly and build a habit of blogging. We’ve heard a lot about how blogging can build your personal brand, so I’m a big fan of this challenge and will participate, even though I’ve been blogging off and on for the past few years.  So far, some big names have come out in support of this challenge. Awesome to see. Not to try and ride their coat tails, but I want to add my own thoughts because I think it’s incredibly important to participate.

Most community members will be intimidated by this challenge. I say this because I’ve heard (and said) all the excuses that are probably going through your head when you think about blogging.  I want to show you how you can overcome that intimidation and participate successfully, jump starting your blogging career.

I don’t have anything to blog about

I hear this all the time. Really what people are saying is “I don’t have anything valuable to blog about” and I completely call shenanigans on this attitude, for much the same reason as why I tell folks they should start presenting. Everyone has something to share. Even if you think it’s simple or a no-brainer, I guarantee you someone will benefit from your knowledge.

Let’s consider why new bloggers get so intimidated. The perception is that current bloggers, especially the BIG names, always seem to have some clever script or gotcha to contribute. Something no one else has ever thought of. It’s a tough act to follow, especially if you are just getting started.

However, to butcher a song lyric, “Any blog is a good blog”.

I always recommend that new bloggers approach their blogging as self documentation. Write for yourself, don’t expect anyone else to read it (and if they do, BONUS!). There have been a number of times where I go back to my blog for a technique or script I wrote in the past. It’s a great entry point to get you to started writing and reduces the “freak out” about other people reading what you wrote.

They’re All Going to Laugh at Me

This could also be the “what if I’m wrong” clause. For new presenters and bloggers, there seems to be a permeating fear about getting called out for something wrong or bad that they publish. I’m sorry, have you met the #SQLFamily? What I love about the SQL Server community is that most folks out there are extremely supportive and helpful. If something is wrong, the community will help you fix it and learn from it.

The bonus of doing this in the internet is making corrections and updates is easy. If someone corrects you or shows you a better way, you can blog about it! If there’s an error, you can fix it! Consider your blog a living diary that can be adjusted as necessary. The only caveat here: Be public about your changes. Either write a new post or make an addendum calling out your edit. Stealth edits look fishy, be public.

Who Has Time To Blog?

Blogging is like any other part of your life where you need to grow: you need to make time for it.  It doesn’t have to be much, an hour or two. The trick is to schedule it like any other commitment and stick to that schedule.

My routine is to write every Saturday morning. I found a nice little tea shop near my house and include that in my morning routine:

  1. I’ll walk to the tea shop around 9 AM. It’s a 30 minute walk and gives me time to think about what I will write about. Plus, the physical activity energizes me.
  2. Once I get to the shop, I order my tea and breakfast (oatmeal, ‘cause I’m trying to lose ‘dat weight). Then I find a space, get plugged in, and start writing.
  3. The writing process is very stream of consciousness. I use Google docs and basically just spitball out what’s in my brain. I don’t worry so much about grammar or sentence structure, the idea is to get my thoughts on paper. This also might include hacking out scripts or testing the stuff I’m blogging about if it’s technical.
  4. Once the writing is complete, I’ll take a Twitter/Internet break (note, I shut Twitter down during the writing, reduce those distractions). Not long, maybe 15 minutes.
  5. After the break, I’ll do one major pass to clean up sentence structure and grammar. Then I shut it down and go home.

 

It should be noted that at this point the blog isn’t quite complete, but the bulk of the work is done.  Next steps for me are to get to get the post into WordPress and schedule it. I always schedule my blog posts for Tuesdays at 8 AM MDT, giving myself a deadline.

You need to commit to this to make it work. The best way I’ve found to hold myself to commitments is to set deadlines. Need to build a presentation? Commit to giving it on a specific date. Need to get a blog post up? There’s my weekly publish deadline. Will you hit those deadlines every the time? Probably not, but as long as it’s not a habit and you don’t let yourself get away with missing a deadline, you’ll be fine.

How Can I Help?

While I think this is a great challenge, I think it’s fairly obvious I’m not a new blogger. How I’m participating is lending my less-than-expansive blogging experience to get others started. This post is the first portion of me owning the challenge as I hope to show you the path to getting started. There’s yet more that I can contribute. So here’s the next steps:

  • Need someone to review your blog before you post it? Hit me up.
  • Want to bounce blog post ideas off of someone? I can do this.
  • Lacking inspiration for what to blog about? Let’s talk.

Let me help you make the most of this challenge. I’m not a expert, I’m not a big name, I’m just a dude doing his SQL thing. But I think I can share some of that with you to make the road a little easier.  Email me via mike at this blog.

Own that $#!+

Blogging, like presenting, is a huge part of building your career and personal brand. It will make you more visible to your peers, help you retain knowledge, and improve your writing skills through practice. By blogging you strengthen the larger SQL community by adding to the pool of tribal knowledge as well as making yourself a stronger member of that group. Remember, you have something to contribute, a unique piece of knowledge that you can share with your comrades in the community. I encourage you to step up and answer the challenge.

Auditing SQL Execution Times With #Powershell

Last week, I was talking with another DBA on my team about our release process.  He was running a series of SQL scripts as part of a code release.  There were about 4 different scripts and, while he was running each one, he would log the start and stop time for each script execution in a text file. We both recognized this as a perfect opportunity for some Powershell automation and I figured I’d put something together.

The pattern for logging this kind of work isn’t terribly complex. We have about 3 steps:

  1. Record the start time
  2. Execute the SQL command or script
  3. Record the end time

The simplest way to express this in Powershell-ese would be:

$StartTime = Get-Date
Invoke-SqlCmd -ServerInstance localhost -Database YourDB -InputFile ‘.\YourSQLFile.sql’
$EndTime = Get-Date
$Duration = New-TimeSpan -Start $StartTime -End $EndTime

Simple, effective, and can then be recorded to any desired output, such as a text file or a database table. Why stop here? Whenever we build automation, we should focus on flexibility and doing the work up front. So let’s enhance this.

If you’ve been reading my blog, you’ve probably noticed that I like to make use of custom Powershell objects for reporting. Custom objects give you a flexible and effective way to generate output, as it allows you to present your results in a variety of ways. With this in mind, I put together the following wrapper function to take either a SQL command or script, execute that command or script, and capture the relevant data about the execution.

function Measure-SqlCmd{
   param($instancename
       ,$databasename = 'tempdb'
       ,[Parameter(ParameterSetName = 'SQLCmd',Mandatory=$true)]$Query
       ,[Parameter(ParameterSetName = 'SQLScript',Mandatory=$true)]$InputFile)

   $output = New-Object System.Object
   $errval = $null

   $output | Add-Member -Type NoteProperty -Name InstanceName -Value $instancename
   $output | Add-Member -Type NoteProperty -Name DatabaseName -Value $databasename
   $output | Add-Member -Type NoteProperty -Name StartTime -Value (Get-Date)

   if($sqlscript){
       $output | Add-Member -Type NoteProperty -Name SQL -Value $sqlscript
       $sqlout = Invoke-Sqlcmd -ServerInstance $instancename -Database $databasename -InputFile $InputFile -ErrorVariable errval
   }
   else{
       $output | Add-Member -Type NoteProperty -Name SQL -Value $sqlcmd
       $sqlout = Invoke-Sqlcmd -ServerInstance $instancename -Database $databasename -Query $Query -ErrorVariable errval
   }

   $output | Add-Member -Type NoteProperty -Name EndTime -Value (Get-Date)
   $output | Add-Member -Type NoteProperty -Name RunDuration -Value (New-TimeSpan -Start $output.StartTime -End $output.EndTime)
   $output | Add-Member -Type NoteProperty -Name Results -Value $sqlout
   $output | Add-Member -Type NoteProperty -Name Error -Value $errval

   return $output

}

At the core, we’re using the same pattern as declared above, but we’re just capturing other pieces of data and putting them into the properties of our custom object.  This allows us to do all sorts of stuff.  Let’s start with simply running our function:

Measure-SqlCmd -instancename 'localhost' -databasename 'demoPartition' -Query 'exec usp_loadpartitiondata;'

The results are a nice little object list, giving us the information about our SQL command:

Measure-SqlCmd

Handy, no? Well, now the fun begins. Let’s say we have a couple commands to execute and we want to output this to a comma separated values(.csv) file for logging. Because we did all the work upfront of making a function and the custom object, the rest becomes a snap:

$total = @()
$total += Measure-SqlCmd -instancename 'localhost' -databasename 'demoPartition' -Query 'exec usp_loadpartitiondata;'
$total += Measure-SqlCmd -instancename 'localhost' -databasename 'demoPartition' -Query 'exec usp_fragmentpartition;'
$total | Select-Object InstanceName,DatabaseName,StartTime,EndTime,SQL,RunDuration | Export-Csv -Path 'C:\Temp\ExecutionLog.csv' -NoTypeInformation

Measure-SqlCmd_results

The bonus of using this pattern is I can use the object collection for a variety of reporting methods.  Using Export-Csv, I can easily create my .csv log file. Another option is to use Chad Miller’s(@cmille19) Out-DataTable and Write-DataTable to record this info to a database table. We now have a flexible logging process to work with.

We also go back to the principle of tool building. Here’s a tool for SQL execution that I can use to give me a repeatable logging process. As I continue to say, automation is about consistency. We can use this logging method to automate SQL script and command executions across multiple instances, track them all in the same way, and then store or handle this appropriate to our process. The end result is tracking our work the same way, every time.

Using #Powershell to Measure SQL Transactions

4333013417_ca6f8904d3_zA common question that comes up for SQL Server administrators is “how do I measure performance”? This is a tough question and there are a variety of ways to do this.  Just off the top of my head, you could measure wait statistics, logical reads and writes for a query, or query duration. When we talk about system performance, we need more general metrics and the one that business folks tend to look at is transactions per second.

For SQL Server, this metric is captured within the SQL Server performance counters.  There are actually two counters you could use: Batch Requests/sec and Transactions/sec. There’s some significant differences between these two counters and this SQL Magazine article covers them both in great detail.  For my work, I like to go with Batch Requests/sec because it captures more transactions going on within the instance, making it a more informative value for measuring system performance.

What’s the best way to capture this? Setting up a perfmon data collection is pretty easy, but then we have to manage creating the collection, then manage the output, and making sense of it. This can be a bear to handle and take a fair amount of manual work to get in place, especially if we just need a quick check on what’s going on in the system.

Powershell provides the Get-Counter cmdlet to query Windows perfmon counters, allowing us rapid access to the information without a lot of hassle. With this cmdlet we can easily measure system performance:

Get-Counter -Counter '\SQLServer:SQL Statistics\Batch Requests/sec'

The output is a PerformanceCounterSampleSet object that is a collection of samples for the call. Using the above command only gives us a single sample, so that makes it simple. We’ll talk about multiple samples in a bit.

 

The key properties of a sample to focus on are:

  • Timestamp – When the sample was collected
  • RawValue – Running cumulative counter for the object
  • CookedValue – The “actual” value, based on the calculating the previous RawValue and the current RawValue.

For reference, if you’ve ever looked at these values in sys.dm_os_performance_counters you have seen the RawValue. This means you are also probably used to doing your own math to measuring these values. Powershell and the perfmon object model will do this math for us, making it easier for us to use.

How do we leverage this for quickly measuring our instance’s transaction performance? I’ve put together the following function to do this for me.  The approach is simple: I give it an instance name and a duration in seconds, and my code will build a Get-Counter call to calculate the Batch Requests/sec information on my instance.

function Get-SQLTxnCount{

param([string]$InstanceName='localhost'
,[int]$DurationSec)

$smo = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
$ComputerName = $smo.ComputerNamePhysicalNetBIOS

$Samples = [Math]::Ceiling($DurationSec/5)
$output = New-Object System.Object
if($smo.InstanceName -gt 0){
$Counters = @('\MSSQL`$'+$smo.InstanceName+':SQL Statistics\Batch Requests/sec')
}
else{
$Counters = @('\SQLServer:SQL Statistics\Batch Requests/sec')
}

$Txns = Get-Counter -ComputerName $ComputerName -Counter $Counters -SampleInterval 5 -MaxSamples $samples
$Summary=$Txns.countersamples | Measure-Object -Property CookedValue -Minimum -Maximum -Average

$output | Add-Member -type NoteProperty -name InstanceName -Value $smo.DomainInstanceName
$output | Add-Member -type NoteProperty -name AvgTxnPerSecond -Value $Summary.Average
$output | Add-Member -type NoteProperty -name MinTxnPerSecond -Value $Summary.Minimum
$output | Add-Member -type NoteProperty -name MaxTxnPersecond -Value $Summary.Maximum
return $Output
}

Get-SQLTxnCount -InstanceName 'PICARD' -DurationSec 30

This function expands on the simple Get-Counter call in a couple ways.  First, I make sure I’ve narrowed the counters to only the specific named instance I want to measure. The second change is to the duration of the test and calculation of  how many 5 second samples will be collected over that duration. Finally, I create a custom output with the Measure-Object cmdlet to show the minimum, maximum, and average Batch Requests/sec for my samples.  This all comes together as a simple transactional measurement for my instance.

This isn’t a replacement for detailed benchmarks and analysis. For this, I recommend some of what Laerte Junior(@LaerteSQLDBA) has written. He has a much more detailed overview of Get-Counter and using it with SQL Server that I would highly recommend (P.S. His article gave me my start on this function).  Benchmarks are extremely important and necessary to make the information coming out of my function useful.

Just like a carpenter has his square or tape measure, this function lets me measure my SQL Server with just a few keystrokes. I still need the full context of what’s happening on my instance, but at least I’ll know the dimensions without a lot effort or additional work. I challenge you to start thinking about your own Powershell in the same way. Don’t think of simply creating scripts, consider how you can take your work and build out your own squares, calipers, or other tools to improve your skills as a craftsman.

Building Availability Groups with #Powershell

A couple weeks ago, I went over automating your SQL Server builds. That’s the kind of process that will benefit many SQL Server administrators. But why stop there?  I say that the more we can automate, the better.  So now let’s talk about automating another aspect of building SQL Servers: availability groups.

Availability groups were the killer feature in SQL 2012 and saw more enhancements with SQL 2014. In my opinion, this is a key feature that allows SQL Server to step into the horizontally scalable world. The problem is that it is not an easy feature to set up or configure. Availability groups have a lot of pitfalls and gotchas to navigate. This is where automation comes in as our ally to build a consistent, repeatable process to deal with these hurdles. Remember, automation is primarily about consistency and we can achieve that by scripting out as much of our build process as possible.

As before, let’s talk about the process we’re going to automate:

  1. Build a Windows failover cluster consisting of two nodes.
    1. The two nodes will already have SQL Server installed.
    2. We will add a file share witness into the cluster (because an even number of quorum votes is bad).
    3. The cluster will be simple, so we will skip things like heartbeat networks and other more advanced configurations.
  2. Build an availability group across both nodes of the cluster.
    1. Availability group will use standard endpoints
    2. Both nodes will be configured for synchronous commit and automatic failover
    3. Create a listener with a static IP address.

Building the Cluster

To ease the scripting process, Microsoft provides several cmdlets in the Failover Cluster module.  Using these, we can create our cluster.

Import-Module FailoverClusters

New-Cluster -Name 'NC1701' -StaticAddress '192.168.10.100' -NoStorage -Node @('PICARD','RIKER')| Set-ClusterQuorum -FileShareWitness '\\hikarudc\qwitness'

As you can see, the cluster creation is pretty easy. Keep in mind this is an extremely simple cluster with none of the usual items we include in production. While you can use this process for your production environment, make sure you work with a clustering expert to define all the additional configurations you will need to build in to make your cluster stable. I also want to call out that I created the fileshare for the witness outside of this script as well.

Before we proceed, I want to validate the cluster.  As with everything else we will use Powershell:

Test-Cluster -Name ‘NC1701’

Firewall Ports

An easy thing to overlook is the firewall ports for availability group communication. I know, I forget it all the time! We need to open up 1433 for SQL Server and listener communication and port 5022 for the availability group endpoints.  Some quick Powershell resolves this for us:

Invoke-Command -ComputerName RIKER -ScriptBlock {New-NetFirewallRule -DisplayName 'SQL Server' -LocalPort 1433 -Direction Inbound -Protocol TCP}
Invoke-Command -ComputerName PICARD -ScriptBlock {New-NetFirewallRule -DisplayName 'SQL Server' -LocalPort 1433 -Direction Inbound -Protocol TCP}

Invoke-Command -ComputerName RIKER -ScriptBlock {New-NetFirewallRule -DisplayName 'AG Endpoint' -LocalPort 5022 -Direction Inbound -Protocol TCP}
Invoke-Command -ComputerName PICARD -ScriptBlock {New-NetFirewallRule -DisplayName 'AG Endpoint' -LocalPort 5022 -Direction Inbound -Protocol TCP}

Building the Availability Group

Microsoft provides some cmdlets for building availability groups in the SQLPS module. The process is fairly straightforward, since we’re only declaring two synchronous nodes with automatic failover. If we were to have additional nodes, we would need to put in additional logic for determining which nodes would perform which roles.

Import-Module SQLPS -DisableNameCheckin
$AGName = 'ENTERPRISE'
$PrimaryNode = 'PICARD'
$IP = '192.168.10.101/255.255.255.0'
$replicas = @()

$cname = (Get-Cluster -name $PrimaryNode).name
$nodes = (get-clusternode -Cluster $cname).name

$sqlperms = @"
use [master];
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM];
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM];
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM];
"@

foreach($node in $nodes){
    Enable-SqlAlwaysOn -Path "SQLSERVER:\SQL\$node\DEFAULT" -Force
    Invoke-Sqlcmd -ServerInstance $node -Database master -Query $sqlperms
    $replicas += New-SqlAvailabilityReplica -Name $node -EndpointUrl "TCP://$($node):5022" -AvailabilityMode 'SynchronousCommit' -FailoverMode 'Automatic' -AsTemplate -Version 12
}

New-SqlAvailabilityGroup -Name $AGName -Path "SQLSERVER:\SQL\$PrimaryNode\DEFAULT" -AvailabilityReplica $replicas

$nodes | Where-Object {$_ -ne $PrimaryNode} | ForEach-Object {Join-SqlAvailabilityGroup -path "SQLSERVER:\SQL\$_\DEFAULT" -Name $AGName}

New-SqlAvailabilityGroupListener -Name $AGName -staticIP $IP -Port 1433 -Path "SQLSERVER:\Sql\$PrimaryNode\DEFAULT\AvailabilityGroups\$AGName"

There are a couple cmdlets we’re using to make this all work:

  • Enable-SQLAlwaysOn enables Always On with the SQL Server service. This must be done before we can create our AGs.
  • New-SqlAvailabilityReplica creates a SMO object that represents our node for the availability group. We signify that we are creating SMO objects by using the -AsTemplate switch. Without that switch, the cmdlet would try creating the actual replica, which we can’t do since the availability group doesn’t actually exist at this point.
  • New-SqlAvailabilityGroup requires two items: the primary node the availability group will be created on (declared as a SQL provider path) and the collection of replica nodes.  Running the cmdlet will create the availability group and join the replicas to it.
  • Join-SqlAvailabilityGroup is used to join the replicas to the availability group. Weirdly enough, when you create the availability group, it won’t join the other replicas, so we have to take an additional step to join the non-primary nodes.

You’ll note we wrap the replica process in a foreach loop built on the Get-ClusterNode output. I use this approach so that, no matter how many nodes my cluster contains, I can add them all without explicit calls to the node names. The loop will add the SMO objects to a collection that will eventually represent all the nodes for the availability group (in this case, only two).

A gotcha to call out is the SQL Statement I execute on each node. When working on this, I kept encountering error 41131. It surprises me, because I believe these permissions should be granted when you enable Always On for the service. However, most of the time this does not happen, so I’ve included the SQL Statement to guarantee that my permissions are correct for creating the availability group

Once we’ve created the availability group, we need to create the listener. To do this, we need the listener name, the port we will use, the static IP that will be assigned to the listener name, and the availability group (declared as the provider path). To ease the implementation, I use the availability group name as the listener name and port 1433. This actually takes the longest of the steps, because the cmdlet has to validate the IP isn’t already in use, but as long as the address is available your listener will be created and your availability group built.

 At this point, you should have a functional availability group and can add databases to it.

Another approach

The interesting thing about these cmdlets is they all work using the SMO and related objects. That means that if you want a more controlled approach where you get down and dirty with the code, you can go that route. Thomas Stringer(@sqllife) has a great post on this method and I actually was going down that route originally.

The question I had to ask myself was if it was worth re-inventing the wheel for my availability group builds. I’m not a big fan of how the SQLPS cmdlets rely on the SQL Server provider. Additionally, if you build your script using Thomas’ method, your script will work anywhere you have .Net and doesn’t rely on you installing the SQL Server client tools to get SQLPS. However, using the SMO/.Net method means I have to do a lot more work to get my script running, where the SQLPS cmdlets have taken care of most of that work for me.

Which approach should you use? That’s for you to decide, but I encourage you to look at both methods and evaluate which approach works better for you. When all was said and done, I went with the SQLPS cmdlets because it meant I didn’t have to spend a lot of time writing additional code.

Just as with our SQL Server build process, we’re building consistency and repeatability into our environment. The added benefit is, using this script, I can create my availability group in about a minute. While I may not be creating availability groups often, my script ensures that when I do create them I can maintain my build standards and keep my systems aligned.

 

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!