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

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

Rate This
  • Comments 12

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

After running the above code, you should have a database and an accompanying snapshot of that database. Within the database, you’ll have a table with at least 1 row in it and a view that simply returns the contents of that table.  This view is a fully qualified name of the original database, this creates 2 scenarios.  One is that it becomes possible to view the originating database from within the snapshot, and the second is the ability to run DML statements against the source database from within the database snapshot.

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?

Leave a Comment
  • Please add 8 and 4 and type the answer here:
  • Post
  • Well, what would it mean to fix this "bug"? You have three obvious options:

    * Snapshot all external data as well so everything is consistent. This is a non-starter. External data can come from remote sources which are not necessarily reachable right now, you need distributed transactions, you need to extend the engine to handle this data while still nominally presenting things as views.

    * Disallow any access to the view on the grounds that it refers to another database which is not a snapshot, and thus cannot offer data consistent with the snapshot. This requires that you implement and test new code specifically for accessing objects in databases that are snapshots.

    * Disallow any data modification statements that refer to objects in a snapshot database, regardless of where they point at or whether they would actually modify any data. Leave data retrieval alone (so no consistency guarantees). This is less work than the previous options but still requires special-case handling in the engine.

    Regardless of what you pick, changing this has a significant cost associated with it, and it makes the scenario mentioned in the article impossible. What are the benefits? Using snapshots becomes slightly easier to understand: all data is either read-only or it's inaccessible. People can use the snapshot without fear of inadvertent data modification. Is this guarantee worth it? I don't know, but my gut feeling would be "no". I'd file this under "you get away with this hack because the cure is not necessarily better than the disease".

  • The proposed solution is interesting. However, it's more like a wrapper to the original database.

    A much simpler, and developmentally efficient solution would be to use 2 connection strings:

    1. String #1 - for read-only operations, connecting to the snapshot

    2. String #2 - for insert/update/delete, connecting to the original database

  • Whilst the article title sounds interesting, it sounds like a design workaround and snapshot itself is not really updatable. Dont see this as an issue, more like a design thingy. Snapshot stays as it is, but the views itself points to the normal user database and hence no update\insert\delete error. Why not.

  • This was certainly a hot button for many folks. In some cases, users expected DB Snapshot to disallow any non-SELECT statements, especially given the Read-Only error message when running a DML against a base table (non-database qualified object).  Others see the novelty of the design but I haven't heard any production designs that use this, performance penalities or integrity challenges either.  Thank you for all the comments.

  • If the purpose of writing to a snapshot is obfuscation then it is better to use aliases, port changes and TDE. If the purpose is to add complexity to a simple process then I must ask, "Why?"

    The shortest distance between 2 points is a straight line. Follow it and you will arrive at the correct point. Deviate from it and you will end up at the wrong point. Simplicity is best.

  • How does this affect mirrored databases?

    Regards

    Perry

  • Definitely NOT a bug.  Things are working as intended, but you've pushed the technology beyond it's limits.  I'd call this a 'hack'.  Nice job thinking outside the box.

  • I think its a bug.

    Cause as a DBA if i allow a user to insert,update or delete to a snapshot i will definitely want to keep those operations isolated from original database .Which is not happening in this case and its too dangerous for the original DB for those users who might run into something like this unknowingly thinking that a such DML is local to the snapshot.  

  • Very clearly explained..... bravo!!!

  • One useful scenario comes to mind, not necessarily using snapshots but read-only databases. Essentially a snapshot is a read-only database made at a point in time. In this scenario, you could have a read-only database exposed to your applications, with views and stored procedures referencing your updateable database. When it is time for a design change, simply publish a new read-only database containing new version. You could even leave old and new versions of read-only database side by side to support multiple versions of your app simultaneously. This of course depends on the extent of changes being made to the writable database, but it is possible. In fact, this sounds similar to SQL Server master "resource database".

  • Very good. Tks for sharing

  • Very interesting. Let's test, tks.

Page 1 of 1 (12 items)