January 14, 2008 at 2:41 am
Hi all
Our production system consists of two SQL 2005 databases A and B on different servers. Server A is running SQL Backup 4.
Server B is running SQL Backup 5.
Server A has a set of Tables which are replicated to B with Transactional Replication.
Server B has a set of Tables, Server A Has Views of the same name which look at these tables. All other tables on B are subscriptions to A's Publication.
What I want to ensure is that the Full backup of these two databases is taken at precisely the same point in time. This will make restoring the db to another environment easier than doing the restores and then doing a WITH STOPAT Log Restore and trying to match them up based on DateTime.
Anyone know a good solution to this ?
(Would prefer not to put the dbs in SINGLE USER MODE)
Many thanks
Rich
January 14, 2008 at 7:24 pm
Theoretically, the two backups can start at precisely same time. But your two servers may not be precisely synchronized. You may have to use the STOPAT clause in your restore.
January 15, 2008 at 3:30 am
So, to clarify, say Ive time synchronized the servers - >
Database A is 3GB and takes 25 minutes to backup.
Database B is 150MB and takes 5 minutes to backup.
Database A's backup is started at 21:00:00.000 GMT exactly and completes at 21:25:00.000 GMT
Database B's backup is started at 21:00:00.000 GMT exactly and completes at 21:05:00.000 GMT
Is the snapshot Ive got in both database backups a point in time database at precisely 21:00:00.000 GMT, or would it be from the point that the backup completed?
Cheers
Rich
January 15, 2008 at 8:52 pm
It would be from the point that the backups completed. We did this kind of experiment. In a backup process, data files are backed up first, then transactions are applied.
You raised a really good question.
January 16, 2008 at 2:42 am
You simply cannot synchronise backups in this way. All it needs is a few milliseconds difference in the backup completion time and you have committed data on machine A that is not on machine B. It is totally impossible to predict on any given day the exact time you will need to start both backups so they complete at the same time.
When you recover from the backups, you will encounter what is called 'the jagged edge of recovery'.
The only way to eliminate a jagged edge is to:
a) Ensure the machine clocks are all synchronised with the domain clock
b) Use STOP AT on your recovery.
I suggest you plan to take a transaction log backup on all machines shortly after all the full backups complete. This way you know you have a backed-up log that will contain the required STOP AT time. Then document and test to ensure all required staff are familiar with the recovery process.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 16, 2008 at 2:53 am
Cheers guys -
EdV - this was also RedGate's suggested solution.
Looks like Im going to need to work on some backup scripts to streamline this operation.
Cheers
January 16, 2008 at 12:29 pm
No matter what you do the time factor is too large.
Sorry, but I don't know of any job scheduler that is capable of starting at the nano second level.
Given the numbers as high as 660.85 tpsE according to http://www.tpc.org for SQL Server I don't see that being possible (or at least reliable).
January 17, 2008 at 3:50 am
The critical time for synchronising backups is not the start time, not the end time, but the time in the middle when the actual full backup ends and the built-in transaction log backup starts.
When you do a full backup, what happens behind the scenes is:
a) You actuall do a full backup.
b) You also back up a portion of the transaction log. This covers from the LRSN of the oldest outstanding transaction when the full bacukp process started, to the LRSN of when the full backup ends.
The time when a) ends and b) starts is the synchronisation point you are aiming for. As a) will take a different amount of time depending on how much data is being backed up that day, the server load during the backup, etc, you have a target that is impossible to hit except by pure luck. This applies if you are trying to synchronise backups across different servers as well as multiple databases on the same server.
You have to go back to the underlying requirement, which gives you the answer - when the various databases are restored, they must all be restored to the same point in time. i.e. you must use a STOP AT in your recovery.
NOTE! The transaction log 'backup' above is only used within the full backup process and does not count in any way as a normal transaction log backup.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 17, 2008 at 4:16 am
Thanks Ed.
I will time synchronise, restore both full backups and both subsequent transaction logs up to the STOPAT point.
Cheers
Rich
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply