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:
Continue reading

SQL Relay Nottingham 2016

SQL Relay logp

After a great time last year, we’re bring SQL Relay back to Nottingham on October 6th 2016.

We will have 3 tracks covering SQL Server, BI, and Analytics. There’ll be 3 tracks of 1hr sessions, plus a workshop track with half day sessions.

We’ll be covering a wide range of topics such as SQL Server performance, management, and development, Azure/Cloud technology, R, big data, cubes, reporting and dashboarding. These topics will be covered at a variety of levels so there’s something to suit everyone whether an accidental DBA, a hotshot BI pro, or a jack of all trades.

All of this for the princely sum of 0p! We’ll even provide lunch. You won’t get a better SQL Server training off in Nottingham in 2016

Speaker submissions are open if you want to speak. Closing date is 19th August

The timetable for the day should be out shortly after that.

Register to attend or speak here – Nottingham SQLRelay Registration

Deleting Large amounts of data in SQL Server – Part 2

In the last part (Part 1) we looked at a reasonably generic script for deleting large amounts of data from SQL Server that helps when you issue a delete statement and SQL Server just sits there with your transaction logs filling up and refusing to clear out.

This time, we’re going to look at how to deal with that wonderful error message of:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__address__personi__239E4DCF". The conflict occurred in database "dbgrow", table "dbo.address", column 'personid'.
The statement has been terminated.

Which is SQL Server’s way of letting you know that the data you’re trying to delete is referenced as part of a Foreign Key relationship, so can’t be deleted unless you delete the record on the other end of the relationship.

So how do you work around this? Continue reading

SQL Server, getting the size of table partitions with T-SQL

Needed to quickly tell someone how large the partitions on a CRM 2011 auditbase table were (neither of us had access to the nice CRM GUI to answer it (ah they joys of not being trusted!)). So if anyone else needs some thing similar this is what I came up with:

sum(alloc.total_pages/128) AS TotalTableSizeInMB,
sum(alloc.used_pages/128) AS UsedSizeInMB,
sum(alloc.data_pages/128) AS DataSizeInMB
FROM sys.allocation_units AS alloc
INNER JOIN sys.partitions AS part ON AU.container_id = CASE WHEN alloc.type in(1,3) THEN part.hobt_id ELSE part.partition_id END
LEFT JOIN sys.indexes AS idx ON idx.object_id = part.object_id AND idx.index_id = part.index_id
where part.object_id = object_id('auditbase')
group by partition_number

If you compare the numbers with thos provided by CRM you’ll notice a differece:


And that’s just because Dynamics CRM doesn’t include the space the indexes take up.

New-AzureRmAutomationCredential returning “Input string was not in a correct format”

Having banged my head against this misleading error message, I thought I’d post this.

it’s nothing to do with any of the parameters you’re passing in, believe me I checked.

It’s actually complaining that the Credential already exists! I was doing a redeployment and the credential hadn’t been removed.

And in other useful annoyances, Find-AzureRmResource doesn’t work for Automation Credentials. So now my deployment scripts looks like this:

if ((Get-AzureRmAutomationCredential -ResourceGroupName $rgname  -name "meadmin" -AutomationAccountName $AutoName)){
    Remove-AzureRmAutomationCredential -ResourceGroupName $rgname  -name "meadmin" -AutomationAccountName $AutoName 

New-AzureRmAutomationCredential -AutomationAccountName $autoname -ResourceGroupName $rgname -name "meadmin" -Value $acred -Verbose

The term ‘Test-AzureResource’ is not recognized as the name of a cmdlet, function, script file, or operable program

Microsoft have followed through with the deprecated warning on Test-AzureResource and it’s finally been removed. So there’s now a lot of PowerShell scripts out there that no longer work.

A quick alternative that involves nothing more than a search and replace (at least in my scripts so far!) is Find-AzureRmResource, behaviour is the same returning $FALSE if the Azure resource doesn’t exist and $TRUE if it does. So this snippet:

    if (!(Test-AzureRMResource `
        -ResourceName $vmName `
        -ResourceType "Microsoft.Compute/virtualMachines" `
        -ResourceGroupName $rgName)) {

can simply be replaced with:

    if (!(find-AzureRMResource `
        -ResourceName $vmName `
        -ResourceType "Microsoft.Compute/virtualMachines" `
        -ResourceGroupName $rgName)) {

and everything’s good.

Using Powershell to push registry fixes around multiple machines

PowerShellNeeding to push this (Microsoft KB 911272 around a bunch of web servers in a hurry. Luckily, thanks to 2 good design decisions:

  • Enabling PS remoting
  • A sane machine naming convention of the form server-1, server-2

We could push the change around with a small bit of Powershell:

$ccred = Get-Credential

    New-ItemProperty "HKLM:\Software\Microsoft\ASP.NET" -Name "FCNMode" -Value 1 -PropertyType "DWord" 
    New-ItemProperty "HKLM:\SOFTWARE\Wow6432Node\Microsoft\ASP.NET" -Name "FCNMode" -Value 1 -PropertyType "DWord" 

while ($i-lt 9){
    $cname = "app-prod-web-$i"
    Invoke-Command -ComputerName $cname -ScriptBlock $ScriptBlock -Credential $ccred

We grab some credentials for the remote boxes (remember, seperate accounts for desktop and server admin duties is a GOOD idea).

As we’re passing through multiple commands/lines to Invoke-Command using a scriptblock is the easiest way, so create one of those. Quickly use New-ItemProperty to add the required DWORD values under the right keys, and then an iisreset to make sure they take affect.

A nice quick and dirty while loop allows us to push the scriptblock to the 8 servers in this set, and we’re done. A quick change of the $cname variable and I can push it to the next set as well. And another edit and it’s pushed into the staging environments as well.

Nottingham SQL Server User group begins

After many successful years running in Leicester we’re proud to announce that the PASS East Midlands chapter is moving to Nottingham for 2016.

Gavin had been running the group since it’s birth, but has now handed the reigns over to myself and Tobiasz. As we’re both based in Nottingham we have decided to move the SQL User group to Nottingham. With the response we had for Nottingham’s first SQL Relay event we wanted to build on that, and this also makes it more accessible with Nottingham’s connections to the rest of the East Midlands and Lincolnshire area.

We’re going be based at Capital One’s Trent House on Station Street, easy to get to from Derby, Leicester, Loughborough, Lincoln, Sheffield and all parts of Nottingham

We have 4 SQL Server User group dates lined up for next year:

As with all PASS SQL User groups there is no charge for attending, yep, that’s free training! We’ll even provide some food and drinks while you learn!

Myself and Tobiasz are speaking at the first event, and we’re currently lining up some great home and international speakers for the other sessions.

But we’d love to hear from you if there’s any particular topics you’d like covering, I’m happy to go out and find speakers covering areas of special interest to our members. Also let us know at what level you’d like sessions presented, if you want an introduction to a particular topic that’s great, as is wanting a level 500 brain warper from the likes of Bob Ward!

All details for future meetings will be posted via our Meetup page – PASS East Midlands User Group.

Please register with the meetup group to keep up to date. You’ll also need to register for the event to attend. This is because we need to provide Capital One with a list of names to be signed in at security, so if you haven’t registered then you won’t be allowed in I’m afraid.

Look forward to seeing some of you there. Please post any comments here or at PASS East Midlands User Group