Author: Stuart Moore Page 17 of 18

Nottingham based SQL Server DBA who dabbles with many other technologies. 15+ years of experience with databases and still keen to learn and explore.

Just a quick heads up for anyone in Nottingham or elsewhere in the Midlands wanting some high quality introductory SQL Server. The SQL Midlands SQL Server User group has 2 great speakers on the 22nd August. John Martin (twitter) will be presenting a Beginners Guide to SQL Server and Alex Whittles (blog|twitter) will be presenting a Beginners Guide to Business Intelligence.

The User Group is in Birmingham, but it’s a very simple trip down via train, with only a short walk from New Street to get to the venue. So well worth making the effort to get down for.

If you’re interested, register here to make sure there’s plenty of pizza.

Powershell to move Windows Roles and Features between servers

By Stuart Moore

On July 26, 2013

In powershell, Uncategorized

I’ve been working a lot with building some test and development environments for systems that didn’t have them. And as those ‘unimportant’ features have been missed out, it wasn’t too suprising to find that the Build documentation for the servers was less than stellar.

So, how could I make sure that the test and dev boxes had the same Windows Roles and Features without wasting too much time. Well, PowerShell is quickly becoming my default answer to anything like this. And it turns out there’s a nice 1 liner you can put together to do it as well (well, 1 liner if you’ve already got the module imported 🙂 )

Import-Module ServerManager
Get-WindowsFeature | Where-Object {$_.Installed -eq $True} | Install-WindowsFeature -computername devbox

First up we import the ServerManager module, it’s loaded by default if you’re running Windows Server 2012 but it’s always good practice to make sure you’ve got it.

Get-WindowsFeature returns a list of all the available Roles and Features on the server, we filter that with Where-Object so we only have those roles and features that are installed. Then we pass the whole lot to Install-WindowsFeature and tell it to install it onto the remote box.

And the nice feature of Install-WindowsFeature is that it will only install anything that’s missing, so this snippet can be used to check for missing sub features

Speaking at Cambridge SQL Saturday

By Stuart Moore

On July 20, 2013

In community, powershell, speaking, SQL Server Events

I was very happy on Friday morning to open my email and find an invitation from SQL Saturday Cambridge organiser Mark Broadbent (blog|twitter) offering me a speaker slot at the event. Accepting the offer didn’t really require much thinking about, not the sort of offer you turn down.

So I know where I’ll be on the 28th September 2013; Queens College, Cambridge. I’ll be talking about using PowerShell to Automate your SQL Server backups and Restores, offering you some ideas about to offload some of the more monotonous bits of a DBAs job and how to ensure you can sleep soundly knowing your backups are good, and you’ll be able to prove it.

Looking at the ‘leaked’ news on the Friday’s PreCons on the event’s Facebook page, I may well head down for one of those as well. Looking like another SQL Saturday event to me

SQL Southwest User Group presentation on Automating SQL Sever backups, restores and verifications with PowerShell

By Stuart Moore

On July 19, 2013

In community, powershell, speaking, sql server

SQL Backups, Restores and Verification with SQL Sever from Stuart Moore

Just a quick post to put up the slides from a presentation I did at SQL Southwest. Also here are the demo scripts to go along with the presentation – Demo scripts from powershell backup presentation July 2013

Hopefully everything’s self explanatory, but if you want to get in touch with any questions then please do,

T-SQL Tuesday #44 – The second chance

By Stuart Moore

On July 9, 2013

In community, personal development, sql server

It’s the 2nd Tuesday of the month, so it’s another T-SQL Tuesday. This months is being hosted by Bradly Ball (blog | twitter) who’s set us the assignment topic of “Second Chances”.

Now I’m sure most SQL Server DBAs (and all other techies for that matter) have lots of stories where something went horribly wrong ,and they got another chance to get it right, either with or without their employees best wishes. But those are mostly horror stories to scare people onto the right path!!

Instead I wanted to talk about about those ‘second chances’ you get, even when the first chance went well (And was probably ignored by the PTB becase of that, c’est la vie). They go well, and we feel happy as we leave the office. But there’s alway the niggling feeling that we could have done it better somehow. Maybe it could have been faster, or we could have taken the chance to implement more features, and that’s where our second chances come.

Even if I’ve done the same task before, I want another chance to try something different when I do it again. Perhaps I’ve read something online, and it looks good on my test rig, and now I want to try it on something large but not too important. Or I’ve been to some training (or conference) and picked up some useful information from a presenter. This is a perfect opportunity to try it out, but very carefully, unless you want to end up needing one of those less pleasant second chances.

And I take it as good practice to regularly review previously written scripts or setups. 3 months after you’ve written it’s amazing what can jump out at you as a less than brilliant solution, or a bit of fresh insight offers you a better way to set things up. Another good reason for keeping notes on how things went when you do a large piece of work. For example, I have a folder full of notes I’ve built up building SQL Server clusters over the years. Because I can refer back I can make sure that I don’t repeat mistakes (and there have been some), but can also look at what can be improved (even non technical aspects, my recording spreadsheet for clusters has become a thing of great use rather than an after thought).

As SQL Server professionals we should be trying to make sure we’re always learning and refining our skills, so we need to make sure we take all the opportunities we get to improve and learn. Even if it is just doing something we’ve done before again, or we think we can shortcut things by just reusing the same scripts we’ve used before.

So grab hold of second chances when they come along as they can be a good thing.

Add-WindowsFeature cmdlet fails with the error code 0x800f0922 adding IIS role/feature

By Stuart Moore

On July 7, 2013

In powershell, Uncategorized

I was working on trying install SSI (Server Side Includes) on an Windows 2012 IIS8 box, set up by someone else. Doing everything the usual way, I was using PowerShell (as I do for all server admining this days, just so much faster) like so:

import-module ServerManager
Add-WindowsFeature web-includes

Only to get the wonderful response shown above in the screenshot. Not very informative really. So I double checked the syntax, but that looked fine. Tried going through GUI to do it, same error just took longer to pop up. Spun up a Windows 2012 VM I had kicking around on my laptop and ran the same snippet, success. So what could it be. Googling showed some MS notes that didn’t offer much information that seemed relevant to this particular case, and in once case seemed to suggest a complete rebuild as the error was due to a corrupted system/iis config issue.

Not fancying a rebuild I thought I’d delve deeper into the IIS configuration to see if there was anything that could be manually fixed. Doing this I discovered that the box had once been part of a shared config setup across a number of boxes, and was now the only one left. BUT, it was still set up with a shared config folder. That was quickly removed and the config exported locally. Reran the Add-WindowsFeature, and this time everything added itself properly.

So it looks quite likely that the error is a catch all (for IIS at least) when the process can’t read or get a lock on the IIS config for one reason or another. So if it crops up checking through the config files for anything funny is probably a very good first step.

Getting Perfmon Data from SQL Server into R for analysis

By Stuart Moore

On July 1, 2013

In performance, R, sql server

In the last couple of posts we’ve looked at how to migrate perfmon data from CSV files into SQL Server (
Migrating perfmon CSV files into SQL Server for analysis) and how to use R to graph Perfmon data (
Simple plot of perfmon data in R).

So, the next obvious step is to combine the two, and use R to graph Perfmon data you’ve imported into SQL Server. It turns out that R makes this pretty simple via the RODBC Package.

Assuming you’ve created a SQL Server system DSN called perfmon_odbc, then the following code snippet would plot you a lovely graph of the counters:

% Processor Time
Avg. Disk Queue Length
Current Connections
Processor Queue Length

as recorded on Server A on the 19th June 2013:

install.packages(c("ggplot2","reshape2","RODBC"))
library("ggplot2")
library("reshape2")
library("RODBC")

perfconn<-odbcConnect("perfmon_odbc")
perf<-sqlQuery(perfconn,"select distinct a.CounterDateTime, b.CounterName, a.CounterValue from CounterData a inner join CounterDetails b on a.CounterID=b.CounterID where b.MachineName='\\\\Server A' and b.CounterName in('% Processor Time','Avg. Disk Queue Length','Current Connections','Processor Queue Length') and a.CounterDateTime>'2013-06-19' and a.CounterDateTime<'2013-06-20'")

perf$CounterDateTime <- as.POSIXct(perf$CounterDateTime)
ggplot(data=perf, aes(x=CounterDateTime,y=CounterValue, colour=CounterName)) +
geom_point(size=.5) +
stat_smooth() +
ggtitle("Server A OS Metrics - 11/06/2013")
odbcCloseAll()

Note that this time we didn’t have to melt our data like we did before, as the resultset from RODBC is already in the format we need. But also spot that we’ve had to add some extra \’s to the machine name. \ is an escape character in R, so we need the extra \ to escape the escape character.

Now you’ve all the flexibility of creating the dataset via T-SQL. This means you can start doing things like comparing 2 Servers across the same time period:

install.packages(c("ggplot2","reshape2","RODBC"))
library("ggplot2")
library("reshape2")
library("RODBC")

perfconn<-odbcConnect("perfmon_odbc")
perf<-sqlQuery(perfconn,"select distinct a.CounterDateTime, b.MachineName+' '+b.CounterName as 'CounterName', a.CounterValue from CounterData a inner join CounterDetails b on a.CounterID=b.CounterID where b.MachineName in('\\\\Server A', '\\\\Server B') and b.CounterName in('% Processor Time','Avg. Disk Queue Length','Current Connections','Processor Queue Length') and a.CounterDateTime>'2013-06-19' and a.CounterDateTime<'2013-06-20'")

perf$CounterDateTime <- as.POSIXct(perf$CounterDateTime)
ggplot(data=perf, aes(x=CounterDateTime,y=CounterValue, colour=CounterName)) +
stat_smooth() +
ggtitle("Server A &amp; B OS Metrics - 11/06/2013")
odbcCloseAll()

Note that in this SQL query we’ve specified 2 machine names, and then to make sure that R can distinguish between the counters we’ve appended the Machine Name to the Counter Name in the column list. I’ve also taken out the geom_point(size=.5) line, as with the number of counters now being plotted having the data points make the curves hard to see and compare.

You can extend this to pull the counters for a Server from 2 different days. This makes it easy to check if the spike is a normal daily occurence, or really is the source of your current issues:

install.packages(c("ggplot2","reshape2","RODBC"))
library("ggplot2")
library("reshape2")
library("RODBC")

perfconn<-odbcConnect("perfmon_odbc")
perf<-sqlQuery(perfconn,"select distinct a.CounterDateTime, 'Day 1 -'+b.CounterName as 'CounterName', a.CounterValue from CounterData a inner join CounterDetails b on a.CounterID=b.CounterID where b.MachineName='\\\\Server A' and b.CounterName in('% Processor Time','Avg. Disk Queue Length','Current Connections','Processor Queue Length') and a.CounterDateTime>'2013-06-19' and a.CounterDateTime<'2013-06-20' union select distinct a.CounterDateTime, 'Day 2 -'+b.CounterName, a.CounterValue from CounterData a inner join CounterDetails b on a.CounterID=b.CounterID where b.MachineName='\\\\Server A' and b.CounterName in('% Processor Time','Avg. Disk Queue Length','Current Connections','Processor Queue Length') and a.CounterDateTime>'2013-06-12' and a.CounterDateTime<'2013-06-13'")

perf$CounterDateTime <- as.POSIXct(paste("1900-01-01", substr(perf$CounterDateTime,12,28)))
ggplot(data=perf, aes(x=CounterDateTime,y=CounterValue, colour=CounterName)) +
stat_smooth() +
ggtitle("Server A Metrics - 13/06/2013 and 19/06/2013")
odbcCloseAll()

In this case we union the 2 results sets, and rename the counters to make sure R can identify which set are which date. We also set the date component on the time stamps to be the same day (I use 01/01/1900 as it’s nice and obvious when looking at a chart later to see that it’s been reset), this is to make sure R plots the time values against each other correctly.

Calling a PowerShell function in a Start-Job script block when it’s defined in the same script

By Stuart Moore

On June 30, 2013

In powershell

While writing some scripts some upcoming SQL Server User Group presentations I had to remind myself of how to do something in PowerShell, and thought I’d put a reminder up here for myself and for anyone else who needs it.

The problem was that I wanted to show firing off multiple SQL Server backup jobs asynchronously from within a PowerShell script. Sounds nice and simple, write some custom modules, and then fire them off with Start-Job as required. And while that’s the best way to do things (PowerShell rule1: Write functions, not scripts), in this case I wanted to keep everything in one script as it would make it easier when presenting

So the basics set up is:

function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}

while($i>$x){
    Start-Job -ScriptBlock <WhatDoIPutHere?>
}

Normally I’d just add the other script or module in there, but what about the function? Well, the trick is to pass the function in as a variable to InitializationScript, and then call it in ScriptBlock like so:

$func = {function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}
}

while($i>$x){
    Start-Job -ScriptBlock {backup_db_id} -InitializationScript $func
}

So that’s the first step. But I also want to pass through the 2 variables, so I’ll need to parametise the function pass. Which means stacking things like this:

$func = {function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}
}

while($i>$x){
    Start-Job -ScriptBlock {param($tm,$ti) backup_db_id $tm $ti } -InitializationScript $func -ArgumentList($x,$i)
    $i++
}

And now everything is passed through happily.

The other thing to note from the function definition here, is that I’ve had to include the Import-Module to load the SQLPS extensions as PowerShell will be starting up a new instance for every Start-Job and that instance won’t have the SQLPS extensions loaded up (unless you’ve set it as a system wide default, but I don’t always want to rely on that).

Upcoming SQL Server User Group sessions I’m speaking at

By Stuart Moore

On June 19, 2013

In community, speaking, sql server, SQL Server Events

Just a quick note about some upcoming sessions I’m presenting at SQL Server User groups over the next couple of months:

18th July – SQL SouthWest (Exeter) – Using PowerShell to Automate SQL Server Backups and Restores

7th August – SQL Southampton – Using PowerShell to Automate SQL Server Backups and Restores

17th October – Cardiff SQL User group – Using PowerShell to Automate SQL Server Backups and Restores

The session will be about how I’m using PowerShell to manage awkward backup scheduling (instances with hundreds of databases), performing automated restores to test those backups, and then to verify the restored database. With all of this being logged somewhere to keep the auditors happy.

Hopefully see some of you there.

What should a SQL Server DBA be doing in a disaster?

By Stuart Moore

On May 23, 2013

In disaster recovery, technical

You’ve had a disaster at work, the data centre is off line, management are panicing and money is being lost. What should you be doing?

Short answer :
nothing that isn’t in the Disaster Recovery plan (you do have one of those don’t you?)

Longer answer:
Some time ago I got into work at my usual hour to notice a distinct lack of server admins at their desks and other early starters unable to log in to Active Directory, neither of which filled me with confidence for a quiet day working on writing some training material.

A quick walk down to the data centre found some worried looking electricians, some annoyed looking server admins and a distinct lack of server noise. Obviously something major had failed and taken out everything, so time to dig out the disaster recovery plans..

In previous roles I’ve had responsibility for hardware and infrastructure so the urge to jump in and help out was strong. But I could see that the correct guys were on the case, and that with the influx of panicing management another body was only going to slow things down and get in the way.

So I went back to my desk, got out a copy of the SQL Server Disaster Recovery plan and started refreshing (you do have an offline copy you can read without power, network or server infrastructure?) . This was the best place for me to be, and the best use of my time while things were being fixed, I was ensuring that:

I could be found easily by anyone needing me
Would have information to hand as soon as needed. (which order to bring back the hardware back online, SAN dependencies, etc)
I could make a condensed checklist for what to do when things came back up based on time (ie; kill off an import that would fail due to a missing dependancy)
Keep out of the way of the people who were working hard to get their part of the disaster plan enacted. They really don’t need another person who doesn’t know what’s going on bugging them.
Work with management to work out the order for bringing back services
Make sure I had all the contact details I needed for the service owners who I’d have to liase with for final release (you just know that a disaster will happen while X is on leave, and you won’t have the number for Y who’s covering their role)

All of this meant that when the call came up that the physical layers were back up and running I quickly started bring up the SQL Server instances in an orderly fashion, and could quickly run through my checks before handing them over to the business users for clearance.

Disaster recovery plans take a long time to write and to get right, but if you’ve got it right then on the day it makes life go easier if you stick to it. When the business is suffering it’s not the right time to be improvising, or trying to work on something you’re not completely OK with, or the time to be trying out some new technique you’ve read on a random DBA’s blog.

Making sure you study it is even more important if it’s a disaster plan you didn’t write. If you ever get brought in to enact a disaster recovery plan you’ve never seen before and you don’t know the providence of, make sure you read it thoroughly before you start. You don’t want to get half way through and find out that you’re missing either a vital piece of infrastructure or software.

(Burnt server image courtesy of John/Roadhunter – http://www.flickr.com/photos/roadhunter/68017745/)

Musings of a Data professional

Author: Stuart Moore Page 17 of 18

Free Introductory training for SQL Server beginners

Powershell to move Windows Roles and Features between servers

Speaking at Cambridge SQL Saturday

SQL Southwest User Group presentation on Automating SQL Sever backups, restores and verifications with PowerShell

T-SQL Tuesday #44 – The second chance

Add-WindowsFeature cmdlet fails with the error code 0x800f0922 adding IIS role/feature

Getting Perfmon Data from SQL Server into R for analysis

Calling a PowerShell function in a Start-Job script block when it’s defined in the same script

Upcoming SQL Server User Group sessions I’m speaking at

What should a SQL Server DBA be doing in a disaster?