DaveWentzel.com            All Things Data

December 2011

Unit testing with TSQLT

I mentioned here and here how much I really love tsqlt for unit testing.  I noticed today that redgate has a product out to assist with tsqlt and unit tests via some GUI enhancements for Management Studio.

SQL 2012 Readable Secondary

I wrote a long time ago about a solution I started to devise to run read-only workloads against a database snapshot that was constantly being refreshed (ie, tran logs were being restored to it every x mins).  I didn't get to finish the project but other people on the team did see it through to fruition.  It utilized log shipping from the OLTP server to the reporting server.  From there we created database snapshots and synonyms to handle the reporting workload.  It was very kludgey because transaction log restores cannot be performed on a database with a snapshot (or any read only activity) against it.  We spoke with MS at the time regarding adding some switches to make all of this less cumbersome and they listened, but my take-away was that they had no plans to look at this further.  That was about 4 years ago.  

With SQL Server 2012 they seem to have fixed a lot of the issues that made this overly cumbersome.  This is the backbone of the AlwaysOn Readable Secondary. This feature is specifically meant to allow your expensive investment in High Availability features to be leverage for read only workloads.  That's a great value proposition.  

The solution is very elegant.  What I tried to do with my kludgey solution was to allow read-only activity to co-exist, as best it could, with the recovery thread that was applying the transaction logs from the OLTP system.  MS has changed the architecture so that these activities effectively do not block each other.  This is accomplished by silently mapping Readable Secondary activity to Snapshot Isolation level.  And all locking hints you may have embedded in your queries are ignored.  

How to Setup a Readable Secondary


With my kludgey solution the readable snapshot was always behind about 5 minutes from the OLTP instance due to log shipping and the time it takes to rebuild the snapshot.  For our needs this amount of latency was no big deal.  For reports that absolutely required the latest transaction data we pointed those reports to the OLTP instance.  With SQL 2012 Readable Secondaries latency is reduced substantially, but the secondary may still be behind by just a few minutes, not unlike a transactional replication replica.  It is important to understand this.  Even on a "sync" replica there WILL be some amount of latency.  A "sync" replica is similar to a two-phase commit where the primary will not commit its changes until the seconddary sends an ACK that it received the changes.  The ACK is not the same as saying the transaction was actually *applied* to the secondary.  The "sync" replica is meant to guarantee there is no data loss, which is different than latency.  

Note that in the "sync" setup it is possible that your reporting workload on the Readable Secondary could cause the ACK to be delayed which could impact your OLTP throughput.  


SQL Server Page Level Restore

I saw a demo of this and took notes in case I ever need to do this in the future.  This is untested.  

  1. DBCC CHECKDB will list corrupted pages.  
  2. You can only use this method on user objects (table data, index data, blob data)
  3. Take a full backup or at least a "tail of the log" backup first.  
  4. Restore "into" the existing db using a full backup that does *not* have the corrupt page(s).  


PAGE = 'x:yyyyy, x:yyyyy, ...'




SQL Server 2012 - New Conversion Functions

There are 3 new functions in SQL Server 2012 which I find useful.  


Syntactically this is identical to CAST.  However, instead of returning an error if the value cannot be CAST'd, it returns a NULL.  A "culture" can be passed as well, which is handy when dealing with internationalized date issues.  You must have the CLR enabled.