Musings of a Data professional

Stuart Moore

Month: August 2016

So you want to present at a SQL Server Event

So you’re thinking about stepping up to speak at a SQL Server (or any other technical event), or are having your arm gently twisted by an organiser to do so. How bad is it going to be?

tl;dr version:
– Just do it, it’s easy, and it’s great!

Long Version:
Not very. Let’s break down the most comment arguments:

1) – I’m not using the later version
Doesn’t matter. Most people out there won’t be. As of writing this, there are very few people running SQL Server 2016, but there are a lot of people still on SQL Server 2012 (and older!). So don’t think you have to be talking about the latest greatest feature

2) – I’m not using the coolest technology
Yes, each SQL Server release has a must-use technology which people preach about. But that’s not always what people want to hear about. Replication is as old as dust, but it’s still something people want to learn about or know how to fix, a good replication talk aways gets listeners. I talk a lot about backups, and not the new features either, and those talks go down well. What about indexing and performance, well those are perennial favourites, and everyone does them differently so maybe you’ve got something to add there

3) – I’m not doing anything exciting
Neither are most people out there! The lie in the marketing papers is that everyone should be doing a billion transactions a second and have a multi terabyte Web Scale database!
Truth is, 90% of your audience aren’t doing that either. Most of us have the same issues, too many databases and not enough time to look after them all. Those are topics that will grab people

4) – I’m not going in depth enough.
I admit it, I love a good Bob Ward (w|t) or Bradley Balls (w|t) 500 level session on deep SQL Server internals, but then that’s me!
For most people a good level 200 session on a topic is a great introduction to that topic, pushing into 300 for someone who wants to move on to the next level. So don’t worry if you’re not breaking out the debugger or tracing into dll calls

4) – I’m not an MVP or other high end consultancy title
Neither are most of us doing the speaking. Don’t let that hold you back. You think they got those titles before they started speaking? It’s putting yourself out there that get’s you noticed.

5) – I don’t have enough content
You’ll be surprised how easy it is to fill up 50 minutes with content. And that’s without questions, once they come into the picture you’ll find yourself accelerating to get everything in. Demos always take longer than you plan as well, seriously, never underestimate how long demo can take in front of an audience!

6) – I’ve not done it before
We all start somewhere (Birmingham SQL User group for me many years ago), local user groups are good as you’ll have friendly faces around. If you want to dip a toe in the water then keep an eye out for events offering a shorter quicker intro, for example lightning talks of 10-12 minutes for you to have a go with, or there’s webinars, so you can present from the security of home.

7) – Don’t be afraid of questions, or you answers (An addition suggested by Rob Sewell (w|t))
Yes, people will ask questions. But don’t be scared of them. I’ve yet to see someone throw in a question explicitly to be nasty to a presenter. Most of the questions will be because someone’s not quite followed what you’re saying so repeat yourself and see how that goes. If you get a question you really can’t answer, you can’t answer in a reasonable amount of time, or is going to lose the rest of your audience you can always arrange to take it afterwards or give them your contact details and discuss it offline.

So there’s nothing insurmountable there. All group leaders and organisers want to see new speakers, so don’t be afraid to ask for help. We’ll happily let you know of any topic requests we’ve had from our members, or give you feedback on your topic. They’re also happy to go through your presentation with you before the big day to make sure it’s going.

Post up below if there’s anything else you’re worrying about. And if there isn’t, go and start writing that presentation

Using the SQLAutoRestores PowerShell Module

PowerShellThe SQLAutoRestores module as currently stands (04/08/2016  v is very much based on my personal usage patterns, but the plan is to make it as flexible as possible. So if you see something in this workflow that you’d like to change, or need something to cope with your SQL Sever backup procedures, then please leave a comment below, or drop in a feature request at github –

Current practice at work is that all SQL Server databases are backed up, either to a central share, or locally and then transferred to the central share. So we end up with a backup folder structure like:

 |    +-DB1
 |    +-DB2

I want to randomly pick a set of backup files to restore. So the first thing I want to do is to get all the ‘Bottom folders’ of this directory tree:

$folders = Get-BottomFolders \\backupserver\share

And if I’ve split the backups across multiple shares I can add more:

$folders += Get-BottomFolders \\server2\backups$\

So now I want to pick a folder at random from those. But, not everyone cleans up after themselves, so there could be folders that don’t contain anything we’re interested in. So there’s a Test-DBBackupsExist function that makes sure we have a file. So we loop until we get something useful:

$RestoreFolder = Get-RandomElement $folders
while (!(Test-DBBackupsExist $RestoreFolder)){
    $RestoreFolder = Get-RandomElement $folders

Great, we’ve got a folder with some SQL Server backups in it. Now we need to see what’s in the backup files. So we do a scan of the file headers, which needs a SQL Server, so we build a SQL Server connection as well:

$SQLconnection = New-SQLConnection 'server1\instance2'
$BackupObjects = Get-DBBackupObject -InputPath $RestoreFolder -ServerInstance $SQLconnection

This returns a simple PowerShell Object containing the header hightlights from each file in the folder.

Note; at this point we’ve not checked we’ve gotten a complete restorable set of files. For all we know, we got 30 Transaction log files and no Full Backup to start from!

I prefer to restore databases to random points in time rather than just the latest available. This gives a wider range of options to compare, and might just mean that you’ll discover than your SAN is corrupting the 22:15 t-log backup.

The next function checks we’ve got at least one ‘anchoring’ full backup, picks the earliest point in time that backup covers, and then gets the latest point in time covered by the backup files, and returns a random point between those 2 extremes. This will be our Recovery Point Objective

$TimeToRestore = Get-PointInTime -BackupsObject $BackupObjects

We then filter out backup files to just those needed to his this point in time:

$Objective = Get-RestoreSet -BackupsObject $BackupObjects -TargetTime $TimeToRestore

Or if you did just want the latest point then you can:

$Objective = Get-RestoreSet -BackupsObject $BackupObjects -Latest

Now we deal with moving the restored database files to a different location:

$Objective = Get-FileRestoreMove -BackupsObject $Objective -DestinationPath e:\some\path

And now we run some tests before the ‘expensive’ time taking restore itself. First off we’ll check we’re not about to clobber another database:

Test-DatabaseExists -RestoreSQLServer $SQLconnection -DatabaseName $Objective[0].DatabaseName

Then we check we have enough space to restore. This includes checking for any file growth during the restore (if your transaction log grows to a stupid size during the day, then it’ll be grown to that size during the restore and sized down later on, so you need to accomdate the largest amount of space your database occupies, not just it’s final size):

Test-RestoreSpace -BackupsObject $Objective -RestoreSQLServer $SQLconnection -RestorePath e:\some\Path

And then we test the difference beteen the SQL Server version of the instance that did the backup and the SQL Server instance we’re asking to perform the restore. Microsoft state that restoring more that 2 major versions isn’t allowed, so we fail it in this case (non SQL Server backups aren’t supported (yet!))

Test-DBRestoreVersion -BackupsOject $Objective -RestoreSQLServer $SQLconnection

And finally we restore the database:

Restore-Database -BackupsObject $Objective -RestoreSQLServer $SQLconnection -RestoreTime $TimeToRestore

Now, we want to check the restore is fine. It is possible to restore a corrupt database with no errors! (Demo and example here) :

Test-Database -DatabaseName $Objective.Databasename -RestoreSQLServer $SQLconnection

And then clean up after ourselves:

Remove-Database -DatabaseName $Objective.Databasename -RestoreSQLServer $SQLconnection

Rinse, repeat ad infinitum. I’ve this process running 24×7 on a dedicated restore instance. On average I restore 80 databases a day and cover every production database in a 2 week windows (it’s random so not guaranteed, but I have a priority list that skews it!)

Currently I collect my statistics with some simple Send-MailMessage usage, but I want something more robust in this module, so thats on the list of things to get fixed before we go to properly.

Hopefully that’s given some ideas on how to use the module. I’d love to hear any ideas on improvements or how you’d want to use it in your environment. Comment here, drop me an email, or ping me on twitter (accounts all linked top right).

PowerShell module to automate restoring SQL Server backups

PowerShellI’ve long been a proponent of automatically restoring as many of your SQL Server backups as you can. There are many benefits of doing this, amongst them:

  • Checking your backups are working, I’ve seen people with backups they can’t restore
  • Knowing how long it will take to recover, your boss isn’t going to be happy if they’re still waiting 3 hours after you said the business would be back up and running
  • Having logs of having restore them, Auditors love this sort of thing
  • Peace of mind knowing that your restores are going to work when the brown stuff hits the reciprocating draught machine.

I’ve been using a collection of scripts and functions based on my previous blogs and presentations on the subjects. But they’ve never really been in a state that I felt comfortable sharing with the world.

Until now:

Powered by WordPress & Theme by Anders Norén