Database Snapshots – Part 2 – Offload reporting

So now that we know a little bit about database snapshots in SQL Server, what else are they good for?  One thing you can do is use snapshots in conjunction with database mirroring to offload reporting onto another server.

Suppose you’re using mirroring as part of a high-availability solution.  Normally that mirrored database/server would sit idle, waiting until the day something happens to the primary server and it gets to take over.  Kinda seems like a waste of perfectly good cpu cycles, doesn’t it?  While you can’t query that mirror directly, you can create a snapshot of it and query that.  Then maybe you can offload your reporting to the mirror server, while keeping your primary server freed up for OLTP processing.  Let’s take a look at how to make that happen.

I’m going to assume you already have mirroring set up and not go into that here.  Right now, we have a primary Bookstore database on MyServer1 being mirrored to MyServer2.  Let’s assume that twice a day we want to refresh the data in the snapshot for reporting.  The first thing we do is create a snapshot on the mirror, and test it.

USE master;
GO
CREATE DATABASE Bookstore_Snap_AM
   ON
       ( NAME = 'Bookstore_data',
         FILENAME = 'D:Program FilesMicrosoft SQL ServerMSSQL10.MyServer2MSSQLDATABookstore_Snap_AM.ss')
AS SNAPSHOT OF Bookstore;
GO
USE Bookstore_Snap_AM;
GO
SELECT * FROM MyBooks;
GO

So far so good.  We could just have our reports run against this snapshot.  But when we create the new snapshot for the second half of the day, we’ll have to change the connections in the reports to read from that new snapshot.  That’s a little kludgy.  So instead what we’ll do is create another database called Bookstore_reporting.  And all that database will contain is synonyms pointing to the snapshot.  This way reports can be run against one database, regardless of what the current snapshot is called.

/*    Create separate database with synonyms    */
USE [master]
GO
CREATE DATABASE [Bookstore_reporting] ON  PRIMARY
( NAME = N'Bookstore_data', FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL10.MyServer2MSSQLDATABookstore_data.mdf' )
LOG ON
( NAME = N'Bookstore_log', FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL10.MyServer2MSSQLDATABookstore_log.ldf' )
GO
USE Bookstore_reporting;
GO
CREATE SYNONYM [dbo].[MyBooks] FOR [MyServer2].[Bookstore_Snap_AM].[dbo].[MyBooks]
GO
/*    Test the synonym    */
USE Bookstore_reporting;
GO
SELECT * FROM Mybooks;
GO

So now all of our reports are directed against Bookstore_reporting, which is really reading from our AM snapshot.  During the course of the morning, we have users making transactions in the primary Bookstore database, and in the afternoon we generate a new snapshot.

/*    update a record in the principal */
USE Bookstore; --this is the original database on the Primary server, MyServer1
GO
UPDATE Mybooks SET Published = '1936' WHERE Title = 'Gone With The Wind'
GO
/*    generate a new snapshot on the mirror server    */
USE master;
GO
CREATE DATABASE Bookstore_Snap_PM
   ON
       ( NAME = 'Bookstore_data',
         FILENAME = 'D:Program FilesMicrosoft SQL ServerMSSQL10.MyServer2MSSQLDATABookstore_Snap_PM.ss')
AS SNAPSHOT OF Bookstore;
GO
/*    query the old and new snaps and the synonym        */
SELECT * FROM Bookstore_Snap_AM.dbo.MyBooks;
SELECT * FROM Bookstore_Snap_PM.dbo.MyBooks;
SELECT * FROM Bookstore_reporting.dbo.MyBooks;
GO

Now we see that the AM snapshot has the original data, the PM snapshot contains the updated record, and Bookstore_reporting is still pointing to the AM snapshot.  So let’s redirect Bookstore_reporting to the PM snapshot.

/*    drop and recreate the synonym to point to the new snap and drop the old snap    */
USE Bookstore_reporting;
GO
DROP SYNONYM MyBooks;
GO
USE Bookstore_reporting;
GO
CREATE SYNONYM MyBooks FOR MyServer2.Bookstore_Snap_PM.dbo.MyBooks;
GO
USE master;
GO
DROP DATABASE Bookstore_Snap_AM;
GO
/*    Query the Synonym again    */
USE Bookstore_reporting;
GO
SELECT * FROM MyBooks;
GO

And even though our report connection hasn’t changed, we’re getting the updated data.  You can schedule the new snapshot creation and synonym switch into a job to automate everything, and it’s relatively seamless.  I would recommend creating a procedure to change all the synonyms in a single transaction.  You can find code examples of how to do this online.

Bear in mind that you may experience some blocking if you try to drop/recreate the synonyms while a report is running, and this may cause your synonym update or the report to timeout.  Don’t be tempted to use READ UNCOMMITTED in your queries to get around this, you could end up querying the old snapshot for one table and the new snapshot for another in the same query.  The dropping/recreating of a synonym is a fast process, so timeouts should be limited.

Coming up in Part 3 we’ll look at using database snapshots to recover data.

Also recommended: