Log ship strategy
paulh2
Posts: 2
We have a large database at Site A. We want to have a read only version on Site B. The comms between the two is relatively limited bandwidth.
If we log ship, the initialisation could take 4-8 hours due to connection. This itself is not a probem
However, if in future we have a db failure at site A, then (as I understand it) our only way to restore is either to take a backup of the mirror at B and apply subsequent logs. However the time to transfer the backup from site B would be too long from a business continuity perspective.
So... What we really want is the log ship, but with to hold a backup of the database at site A, on to which we can apply the subsequent logs i.e so we don't have to pull the bulk of the data back from site B.
Is there a way of doing this?
If we log ship, the initialisation could take 4-8 hours due to connection. This itself is not a probem
However, if in future we have a db failure at site A, then (as I understand it) our only way to restore is either to take a backup of the mirror at B and apply subsequent logs. However the time to transfer the backup from site B would be too long from a business continuity perspective.
So... What we really want is the log ship, but with to hold a backup of the database at site A, on to which we can apply the subsequent logs i.e so we don't have to pull the bulk of the data back from site B.
Is there a way of doing this?
Comments
It seems like Log Shipping is the solution you're looking for. After an initial backup and restore to the standby sql server, you need to backup the logs on the primary server, copy them offsite over the network, and restoring the logs to the standby. The bandwidth usage of transferring log backups is normally pretty low depending on the number of transactions going through the main database.