Getting a synchronized snapshot of several tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Getting a synchronized snapshot of several...

Getting a synchronized snapshot of several tables

Rate Topic

Display Mode

Topic Options

Author

Message

mark 4643

Posted Thursday, January 03, 2013 5:55 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:37 PM
Points: 24, Visits: 77

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

Post #1402316

anthony.green

Posted Thursday, January 03, 2013 6:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1402321

mark 4643

Posted Thursday, January 03, 2013 6:11 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:37 PM
Points: 24, Visits: 77

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

Post #1402324

anthony.green

Posted Thursday, January 03, 2013 6:20 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1402332

mark 4643

Posted Thursday, January 03, 2013 6:57 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:37 PM
Points: 24, Visits: 77

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 Smile

)

Post #1402344

anthony.green

Posted Thursday, January 03, 2013 7:00 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1402345

« Prev Topic | Next Topic »

Permissions