Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting a synchronized snapshot of several tables Expand / Collapse
Author
Message
Posted Thursday, January 3, 2013 5:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 8:19 AM
Points: 33, Visits: 139
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
Posted Thursday, January 3, 2013 6:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 5,143, Visits: 4,942
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
Posted Thursday, January 3, 2013 6:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 8:19 AM
Points: 33, Visits: 139
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
Posted Thursday, January 3, 2013 6:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 5,143, Visits: 4,942
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.




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 #1402332
Posted Thursday, January 3, 2013 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 8:19 AM
Points: 33, Visits: 139
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 )
Post #1402344
Posted Thursday, January 3, 2013 7:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 5,143, Visits: 4,942
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.




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 #1402345
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse