When the going gets tough, the tough hide under the table

That's a quote from one of my favorite comedy shows, Blackadder

With Hurricane Sandy bearing down on the North East Coast of the US today, whether you're in its path or not, it's a good time to consider what your reaction will be when faced with a disaster that affects the data you're responsible for.

Will you be hiding under the table or calmly and confidently working through your disaster recovery plan to bring your company's data life-blood back online? Hopefully the latter.

For those who will be working through power-outages and data loss today, and as food for thought for those who won't, I've brain-dumped a quick list of some helpful questions, advice, and useful links around disaster recovery.

In no particular order:

  • Now is the time to move your on-site backups onto a different I/O subsystem from the databases. 
  • Make sure you start gathering off-site backups before the incoming disaster strikes and communications and travel become difficult. 
  • Make sure you have a disaster recovery plan (see this blog post), that's been tested (see this blog post), and that those on call have practiced. You shouldn't be learning the syntax for RESTORE during a real disaster.
  • Don't assume that everyone will be online and pulling for the company. People's lives and families will come first. See this blog post.
  • How will you all communicate if the land-lines and cell-phone towers are inoperable?
  • If you have to do a bare-metal install, where is the media? Do you have ISOs on a SAN somewhere? If not, download them now before communications drop out.
  • If your data center is damaged, what's the alternate location to spin up some servers? For a small business, someone's garage will do in a pinch.
  • Don't forget that if the SAN is down, you can get 1TB drives at electronics stores – and they're better than nothing if you just want to get your business online again, albeit slowly.
  • If your generators don't start, go to Home Depot and expense a few household generators to plug critical servers into. I've seen this done. Make sure you know someone with a big pickup truck.
  • Make sure to enable instant file initialization on new instances before restoring databases from backups to save time.
  • Make sure you know the order which databases have to be restored, based on importance to the business.
  • Do you have the contact info for the on-call personnel from the server, networking, SAN, customer service, and any other teams you need to liaise with?
  • Who is in control of the DR effort? Who decides whether to do one thing or another if there's disagreement?
  • Who decides when to escalate?
  • Who decides when to ask for third-party help?
  • Who needs to be kept informed of progress? Executives? Customers? Partners?
  • How to: rebuild system databases (2012, 2008R2, 2008)
  • How to: install 2005 (including system databases)
  • How to: restore master (2012 through 2005)

Basically the more prepared you can be, and the more eventualities you can think through, the more likely you'll be able to get back up and running within your downtime and data-loss SLAs.

Whatever happens over the next few days for those of you in Sandy's path, conduct a post-mortem to see what went right and wrong, and rework your HA/DR plan accordingly.

And don't forget that #sqlhelp on Twitter can be invaluable for advice.

Good luck out there!

Survey: do you have a baseline?

For the last few weeks I've been thinking about performance baselines, mostly because I've been producing Erin's new Pluralsight course on Benchmarking and Baselining. It got me thinking that it would be interesting to know how many of you are using baselines, and what for, and if not, why not.

So I present to you two surveys, which I'll report on in a few weeks.

Firstly, if you do not have any performance baseline of your SQL Server:


Secondly, if you do have one or more performance baselines of your SQL Server:


These will allow me to get counts and reasons.

Thanks!

Parent transaction ID in 2012 fn_dblog output

All kinds of little bits of information have been added to the output of various DMVs, functions, and commands in SQL Server 2012.

One which I only discovered recently, and about which I’m really excited (ok, I should probably get out more :-), is the inclusion of the parent transaction ID in the output of fn_dblog. This allows us to see which transaction is the parent of nested system transactions and other sub-transactions.

You might wonder why I care? It allows us to see the hierarchy and definitive order of transactions during operations we’re performing with SQL Server, to aid in working out how operations work under the covers, rather than having to guess and assume.

For instance, when inserting the first row into a new table, the allocation system has to allocate a page from a mixed extent. Using the new field, we can see the definitive order of operations:

CREATE TABLE t1 (c1 INT);
GO

CHECKPOINT;
GO

INSERT INTO t1 VALUES (1);
GO

SELECT
[Current LSN],
[Operation],
[Context],
[Transaction ID],
[AllocUnitName],
[Transaction Name],
[Parent Transaction ID]
FROM fn_dblog (NULL, NULL);
GO

Current LSN            Operation              Context       Transaction ID AllocUnitName           Transaction Name           Parent Transaction ID
———————- ———————- ————- ————– ———————– ————————– ———————
00000119:000001c2:0001  LOP_BEGIN_XACT        LCX_NULL      0000:0000a216  NULL                    INSERT                     NULL
00000119:000001c2:0002  LOP_BEGIN_XACT        LCX_NULL      0000:0000a217  NULL                    AllocHeapPageSimpleXactDML 0000:0000a216
00000119:000001c2:0003  LOP_LOCK_XACT         LCX_NULL      0000:0000a217  NULL                    NULL                       NULL
00000119:000001c2:0004  LOP_SET_BITS          LCX_SGAM      0000:00000000  Unknown Alloc Unit      NULL                       NULL
00000119:000001c2:0005  LOP_BEGIN_XACT        LCX_NULL      0000:0000a218  NULL                    AllocFirstPage             0000:0000a217
00000119:000001c2:0006  LOP_MODIFY_ROW        LCX_PFS       0000:0000a218  dbo.t1                  NULL                       NULL
00000119:000001c2:0007  LOP_BEGIN_XACT        LCX_NULL      0000:0000a219  NULL                    AllocMixedExtent           0000:0000a217
00000119:000001c2:0008  LOP_SET_BITS          LCX_GAM       0000:0000a219  Unknown Alloc Unit      NULL                       NULL
00000119:000001c2:0009  LOP_SET_BITS          LCX_SGAM      0000:0000a219  Unknown Alloc Unit      NULL                       NULL
00000119:000001c2:000a  LOP_COMMIT_XACT       LCX_NULL      0000:0000a219  NULL                    NULL                       NULL
00000119:000001c2:000b  LOP_MODIFY_ROW        LCX_PFS       0000:0000a217  Unknown Alloc Unit      NULL                       NULL
00000119:000001c2:000c  LOP_FORMAT_PAGE       LCX_IAM       0000:0000a217  dbo.t1                  NULL                       NULL
00000119:000001c2:000d  LOP_HOBT_DELTA        LCX_NULL      0000:0000a217  NULL                    NULL                       NULL
00000119:000001c2:000e  LOP_MODIFY_ROW        LCX_IAM       0000:0000a218  dbo.t1                  NULL                       NULL
00000119:000001c2:000f  LOP_CREATE_ALLOCCHAIN LCX_NULL      0000:0000a217  NULL                    NULL                       NULL
00000119:000001c2:0010  LOP_LOCK_XACT         LCX_NULL      0000:0000a217  NULL                    NULL                       NULL
00000119:000001c2:0011  LOP_COMMIT_XACT       LCX_NULL      0000:0000a218  NULL                    NULL                       NULL
00000119:000001c2:0012  LOP_HOBT_DELTA        LCX_NULL      0000:0000a217  NULL                    NULL                       NULL
00000119:000001c2:0013  LOP_LOCK_XACT         LCX_NULL      0000:0000a217  NULL                    NULL                       NULL
00000119:000001c2:0014  LOP_ROOT_CHANGE       LCX_CLUSTERED 0000:0000a217  sys.sysallocunits.clust NULL                       NULL
00000119:000001c2:0015  LOP_LOCK_XACT         LCX_NULL      0000:0000a217  NULL                    NULL                       NULL
00000119:000001c2:0016  LOP_ROOT_CHANGE       LCX_CLUSTERED 0000:0000a217  sys.sysallocunits.clust NULL                       NULL
00000119:000001c2:0017  LOP_FORMAT_PAGE       LCX_HEAP      0000:0000a217  dbo.t1                  NULL                       NULL
00000119:000001c2:0018  LOP_LOCK_XACT         LCX_NULL      0000:0000a217  NULL                    NULL                       NULL
00000119:000001c2:0019  LOP_ROOT_CHANGE       LCX_CLUSTERED 0000:0000a217  sys.sysallocunits.clust NULL                       NULL
00000119:000001c2:001a  LOP_COMMIT_XACT       LCX_NULL      0000:0000a217  NULL                    NULL                       NULL
00000119:000001c2:001b  LOP_INSERT_ROWS       LCX_HEAP      0000:0000a216  dbo.t1                  NULL                       NULL
00000119:000001c2:001c  LOP_SET_FREE_SPACE    LCX_PFS       0000:00000000  Unknown Alloc Unit      NULL                       NULL
00000119:000001c2:001d  LOP_COMMIT_XACT       LCX_NULL      0000:0000a216  NULL                    NULL                       NULL

Pretty darn cool, eh? We can see that the system started an implicit transaction for us, which it called INSERT. This then started a sub-transaction called AllocHeapPageSimpleXactDML to handle the internals of the allocating a page. This in turn started two further sub-sub-transactions to deal with the extent allocation and allocating the page.

>Note that the setting of the free space of the new page in the PFS is not done as part of a transaction (called being non-transactional), as it would be impossible to maintain transactional free space bits without causing major blocking in the system.

In future I’ll be making use of this in my spelunking exercises.

Enjoy!