Getting a synchronized snapshot of several tables

Question

Post reply

Getting a synchronized snapshot of several tables

mark 4643

SSCommitted

Points: 1503
More actions
January 3, 2013 at 5:55 am

#268813

Hi Guys
I'm using SQL Server 2008 R2 (Standard Edition) and trying to get a nightly snapshot of several tables for auditing/integrity checking (from a 3rd party vendor's DB). As a simple example I have an items table which has qty on hand and unit value. From this I calculate my inventory value. I have a separate transaction table that records all issues, receipts, scrap, inventory adjustments. Unfortunately, the users make way too many adjustments and try and come up with workarounds for stuff and it makes month end reconciliation a nightmare. To try and find issues in a more timely manner, I figured I'd take a snapshot of the two tables (there are actually about 6) at midnight calculate the inventory value at end of Day 1 and again at end of Day 2 and make sure the sum of transaction matches the difference in inventory valuation. For example
Item 1, Date 1, Qty 10
Item 1, Date 2, Qty 5
Item 1 receipts between Date 1 and Date 2 = 10
Item 1 issues between Date 1 and Date 2 = -15
Item 1 Net changes = -5
Date 2 inventory - Date 1 Inventory = -5, so things are good for that period
Our warehouse works 24/y so the data is constantly changing and I'm having problems with sync, as the copy of the items table could start at midnight and take 1 min (for example) and then the transactions copy runs, so any transactions between 00:00 and 00:01 would not be reflected in the starting valuation. I could record the snapshot end time and then get transactions using that time instead of midnight.
I currently have 2 SP's to copy the tables as sequential steps in a single job, so this obviously and issue.
What's the best way to get an exact sync copy?
I could use 2 scheduled jobs and start both at midnight to improve things. however I'm not sure how SQL server works with copying large volumes of data and if something like snapshot isolation would work. For example, if I start to copy the transactions 00:00 and it takes 2 mins. If there's a transaction at 00:01, will it be in the copy that completes at 00:02.
Worst case, I could do a backup and restore, but same question applies: if I backup the DB while online at 00:00 and it takes 10 mins, am I getting the state at 00:00?
I'm sure these are common issues in OLTP environments, so was wondering if someone could pitch in with a solution
Thanks
Mark

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

Create a snapshot of the database in question at 00:00

CREATE DATABASE dbnamesnapshot ON (......) AS SNAPSHOT OF dbname

http://msdn.microsoft.com/en-us/library/ms175876.aspx

That way the data is static and you can then query the tables as no data changes can happen to a snapshot as it is a read only copy as of the moment the snapshot completes.

mark 4643 SSCommitted Points: 1503 More actions · Answer 2

Thanks for lightning fast Anthony

Looks like that will do the trick. Just need to add some stuff to manage the disk space

Seems I need to do the following

Create snapshot of orders as orders_temp

Copy the data I want

Drop orders_temp

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

A snapshot is a fairly low weight operation. It creates what is called a sparse file, which stores any original data which has been updated from the source DB.

These get created with an allocation size of the DB size at the point the snap was created, but actually consume 0 bytes until data starts changing.

So, you actually will only need a small amount of space for the snapshot to live in, if your exports are quick enough.

So you would do just as you say, snap, copy, drop.

Test it out and see how much space is actually consumed by the sparse file.

mark 4643 SSCommitted Points: 1503 More actions · Answer 4

Hmmm.. back to the drawing board:

Got this: "Database Snapshot is not supported on Standard Edition (64-bit)."

Not sure my boss is going to fork out the extra 25K for upgrade to next edition (would love it for data driven subscriptions in SSRs :-))

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

Darn it, I always get confused on that, as they give you backup compression in R2 Standard, but not Snapshots.

In that case I would look at setting the isolation level of the command to SNAPSHOT and read the tables from the same connection, which means setting the database option of ALLOW_SNAPSHOT_ISOLATION to on, but that will invoke the row version store, so you will have extra TempDB activity going on.