Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Setting up replication and dealing with a time lag


Setting up replication and dealing with a time lag

Author
Message
Del Lee
Del Lee
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 1193
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.



Del Lee
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8259 Visits: 14368
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
Chris Becker
Chris Becker
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 268
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search