November 29, 2012 at 9:05 am
We have a replicated database (transactional replication) that is primarily used for reporting, although this also could be a secondary or tertiary (3rd option) disaster recovery solution if the other DR options fail. I inherited this situation, and it is not in ideal shape. A fair amount of the metadata has not been replicated from the production database, and it has proven to be more of a task than I thought to try to get this corrected. So, it would seem a good idea to just restore the replicated database from backup and then re-initiate replication. However, I am unsure of some things which will play into how I need to implement this.
We are using SQL Server 2005. The size of the database is over 8GB.
The full backup of the production database occurs shortly after midnight. The replicated database is at another site. It takes a couple of hours to get the backup compressed and moved across the wire to the server where the target instance resides. So, I'm unsure of how to deal with what happens during time it takes to get the backup file to the replicated server. We do take transaction log backups, so I assume I'll also need to get these transaction logs across the wire and restored as well while replication is down. I guess my question is at some point, a "last" transaction log needs to be taken, moved across the wire, restored and replication needs to be re-initiated before another database transaction occurs after this "last" transaction log was taken. I assume I'll need to take the production database offline briefly or perform the snapshot immediately upon completion of the transaction log in question.
From all I've read, I know this is not an uncommon scenario, so is there an article or something I have missed that deals with the particular issues that I have concerns about? Perhaps this is not as complicated as I'm thinking it is. It really should be no different than initially setting up replication (there will always be at least a little time lag, right?), actually, but I just don't recall any articles addressing dealing with the time lag in setting up the replicated database (subscriber).
Give me your thoughts and maybe you can fill in the gaps I have with my understanding.
December 5, 2012 at 11:24 pm
It is not uncommon at all. Basically what you want is to initialize the subscriber using a backup from the publisher. When you setup the publication you'll let SQL Server know this is what you want to do so it can begin sending transactions committed to the publisher to the distributor. Then when you initialize the secondary from the backup all the transactions that happened from the time the publication was setup will be applied to the subsscriber, i.e. no need to manually deal with tran log backups or worrying about timing, the distributor will manage that using the LSN.
Initializing a Transactional Subscription Without a Snapshot (SQL Server 2005)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 6, 2012 at 5:05 pm
To minimize the backlog, I take a full bak and start shipping it. When I have it restored, plus tran logs, then I add subscription. Take final tran log bak, ship & restore. Total latency maybe 15-20 minutes, not 24 hours+
hth,
chris
mssqlconsulting.com
Chris Becker bcsdata.net
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply