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