May 28, 2010 at 5:20 pm
Here is one that has me baffled. Maybe some of you senior DBA’s might know what’s going on. So, here’s a good one to noodle on.
Overview
I have 2 fairly large and very busy DBs on a single physical SQL server:
•DB-A functions as a complex message queue.
•It is ~45GB and produce ~10GB of logs per day.
•Messages are written to the DB at a rate of ~30-40/sec.
•Several parallel processes then read these messages out of the D, translate them, and write them into DB-B.
•DB-B functions as an OLTP application DB.
•It is ~101GB and produces ~25GB of logs per day.
•A single message will update multiple tables in this DB.
Processing a single message looks like this:
1.Read the message from DB-A
2.BEGIN Transaction
3.Update DB-B (includes a timestamp of datetime)
4.Mark message as processed in DB-A (includes a timestamp of datetime)
5.COMMIT Transaction
I’ve run into a situation where I need to restore both DBs to exact same point in time, rolling back all uncommitted transitions. Here’s what I did.
1.Restore Full Backup of DB-A
2.Restore Log(s) Backups for DB-A and on the appropriate log use STOPAT = '2010-05-28 01:28:55.000'
3.Restore Full Backup of DB-B
4.Restore Log(s) Backups for DB-B and on the appropriate log use STOPAT = '2010-05-28 01:28:55.000'
Example RESTORE LOG command:
RESTORE LOG @DBName FROM DISK='R:\DBA_backup_201005280130.trn' WITH RECOVERY, STOPAT = '2010-05-28 01:28:55.000'
Once both DBs are restored it seems that I have messages that are marked processed in DB-A, and the changes that those message would have made are not reflected in DB-B. After doing this a few times using backups from different nights. It would seem that DB-B always seem 20-30 seconds “behind” DB-A.
What is the granularity of the timestamps that is kept with the logs? Is it down to the 0.003 that SQL supports? Is it to the minute? Is there are better way to restore two DBs to the exact point into time? I’ve tried to surmise a way to us the sequence numbers, but I haven’t been able to discover a method to map sequence number between DBs.
I’ve used STOPAT in the past to get out of many complicated jams. However, I’ve never had to get 2 DBs synced so precisely. I’m actually completely floored that these DBs would more than a few milesconds apart using a STOPAT restore scenario. I mean, that’s seems like it should be a fundamental piece of a transactional DB.
If anybody wants more info, I’m happy to provide.
I’m using this version of SQL:
Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02
(Build 3790: Service Pack 2)
May 28, 2010 at 5:39 pm
I am just guessing here, but it would seem to me that DB-B's transactions that were in process at the same time that you have specified would be rolled back. Could that account for the difference between the two databases?
Again, just guessing - but I would set the STOP AT parameter for DB-B 30 seconds to a minute later than DB-A and build code to remove invalid transactions from DB-B. It's probably the only way you are going to be exactly in synch between these two databases.
If this were a requirement that I had - I would consider a redesign that would bring the objects from DB-B into the same database as DB-A (possibly using a different schema). That way, I could assure transactions are synchronized between the two schemas.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2010 at 5:53 pm
Since there is a single transaction that spans the update on DB-A and DB-B, there shouldn't be any in process transactions in DB-A that wouldn't also be in process in DB-B (at least any I care about). Thus the in process transactions should be rolled back in both DB given I used the same STOPAT time, correct?
Putting the DBs together is certainly an option, although it would require a bit of system rework. Also I have a few of these systems running and the other DBs are much large 300GB and 90GB. I don't know about making those any large. 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply