Setting up replication and dealing with a time lag

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SS2K5 Replication » Setting up replication and dealing with a...

Setting up replication and dealing with a time lag

Rate Topic

Display Mode

Topic Options

Author

Message

Del Lee

Posted Thursday, November 29, 2012 9:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:35 AM
Points: 235, Visits: 329

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

Post #1390641

opc.three

Posted Wednesday, December 05, 2012 11:24 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 6,695, Visits: 11,711

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1393330

Chris Becker

Posted Thursday, December 06, 2012 5:05 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 11:49 AM
Points: 220, Visits: 216

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

Post #1393796

« Prev Topic | Next Topic »

Permissions