Tuesday, October 18, 2011

Successfully execute an INSERT, UPDATE and DELETE against a Database Snapshot

by shauntj-us via Microsoft SQL Server Development Customer Advisory Team on 10/18/2011 2:06:00 AM

Author: Shaun Tinline-Jones

Reviewers: Mike Ruthruff, Sanjay Mishra, Alexei Khalyako

Not too long ago an ISV that developed solutions using SQL Server as the RDBMS, asked me how they could query a database as at a point in time. This was a relatively easy answer, thanks to the Database Snapshot feature. I was however surprised at the next question “Can we update the database snapshot?”

A reactive response is “No. You cannot update a Database Snapshot”

Msg 3906, Level 16, State 1, Line 1
 Failed to update database "Orig_Snapshot" because the database is read-only.

A creative answer is a tentative “….well maybe…depending on what the objective is?”

This blog demonstrates that it is possible to run an INSERT, UPDATE or DELETE against a Database Snapshot.  This will not update the snapshot, but rather the database that has a "Database Snapshot" associated to it.

Imagine a scenario where a reconciliation of data at a point in time must be carried out. Database Snapshot provides the ability to present the data as at a point in time, however the common understanding is that any compensating modifications requires a second connection or a USE statement. The USE statement is not permitted in a database module:

Msg 154, Level 15, State 1, Procedure testSP, Line 4
a USE database statement is not allowed in a procedure, function or trigger.

Listing 1 creates the objects needed to prove it is possible to successfully execute DML statements against a Database Snapshot.

    IF DB_ID('Orig') IS NOT NULL
        DROP DATABASE [Orig]
    GO
    
    CREATE DATABASE [Orig]
     ON PRIMARY ( NAME = N'Orig', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL02\MSSQL\DATA\Orig.mdf')
    GO
    USE [Orig]
    GO
  
    IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
        DROP TABLE dbo.TestTable
    GO
    
    CREATE TABLE dbo.TestTable (Col1 int)
    GO
    
    IF OBJECT_ID('dbo.UpdView', 'V') IS NOT NULL
        DROP VIEW dbo.UpdView
    GO
    
    CREATE VIEW dbo.UpdView
    AS
    SELECT Col1 FROM Orig.dbo.TestTable
    GO
    
    INSERT INTO dbo.TestTable (Col1) VALUES (1)
    GO
    
    IF DB_ID('Orig_Snapshot') IS NOT NULL
        DROP DATABASE [Orig_Snapshot]
    GO
    
    CREATE DATABASE [Orig_Snapshot]
     ON PRIMARY ( NAME = N'Orig', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL02\MSSQL\DATA\Orig_Snapshot.ss')
     AS SNAPSHOT OF [Orig]
    GO
    
    USE [Orig_Snapshot]
    GO
    
    SELECT * FROM dbo.UpdView
    GO

Listing 2

    INSERT INTO dbo.UpdView VALUES (2), (3);
    UPDATE dbo.UpdView SET Col1 = 99 WHERE Col1 = 3;
    DELETE FROM dbo.UpdView WHERE Col1 = 1;
    SELECT * FROM dbo.UpdView
    GO

Running the code in listing 2, you should have a result set as shown below:

In conclusion, the error message that informs us the database is read-only, while identical to the message returned when writing to a Read-Only database, is only partially accurate in the context of a Database Snapshot. Questions that come to mind are:

• Is it a bug?
• Would this be considered a good practice?
• What does the code management look like?
• What useful scenarios could leverage this insight?

Let me know if you think it’s a bug. From my perspective, this makes sense as the database snapshot is actually a read/write database, persisting older values as records change. Additionally, we are not actually updating the Database Snapshot, the changes are still made directly to the main database.

As far as been a good or recommended practice, my reservations about using this in a design are:

• Views must be created for each table that can be updated from the database snapshot
• Separate objects/statements must be created for DML operations that already exists for the main table
• It isn’t intuitive in terms of troubleshooting code. In fact, if you start down this road you may end up with one of those horrendous applications that have layers upon layers of views ultimately leading to poor performance.

It does however reduce the cost of creating and managing a new connection, especially if the DML statements are a result of a query that originated from the snapshot. Additionally, the logic could reside in the same SP as the query, allowing for conditional logic.

Can you think of other scenarios, pitfalls or benefits of updating the main database via a database snapshot?