the correct way to pause transactional replication

[jpotucek]

We have Transactional replication running from ONE DB on our sql server 2000 box to a SQL Server off our network. While DR testing is going on, the Tunnel to the subscriber server will be down (FOR A COUPLE OF DAYS)

What is the best way to pause replication so I don't have a problem with logs filling up etc...

I stopped the log reader on the publisher... but I don't know if that was the right thing to do?????

 

[MissTipps]

Essentially you can't (and the logs will fill up). If you think about what that transactional replication is doing - you are saying "only commit this transaction when I can confirm it is written to both the publishing and subscribing databases". If you aren't writing to the subscriber, then the transaction must remain active and hence the log on the published database will grow.

MissTipps

CISSP, CEH, CEI, MCT, MCDBA, MCSE 2K3, CTT+, ECSA, Security+

 

[jpotucek]

understood. so what would you go in the case where the tunnel to the subscriber server is down for 3 days?

Someone told me to disable the distribution agents and the distribution cleanup job and let the distribution DB on the publisher queue everything until the tunnel is back up and the subscriber DB is available again.. Is that the wrong thing to do?

 

[MissTipps]

You can do that, but you would need to try and estimate how big the transaction log on your published database is going to grow in that time.

MissTipps

CISSP, CEH, CEI, MCT, MCDBA, MCSE 2K3, CTT+, ECSA, Security+

 

[jpotucek]

If our Publisher and Subscriber Databases are out of of sync, what is the proper procedure to follow to get them synced up again?

 

[mrdenny]

Fire up the agents and the data will begin flowing again.

Keep in mind that if the link is down for long enough SQL will expire the subscriber and you'll need to push the snapshot to the subscriber again.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[jpotucek]

how do I push a snapshot and is that something that NEEDS to be done off-hours?

 

[mrdenny]

It is recommended that the snapshot be pushed after hours if people are currently using the subscriber as pushing the snapshot drops the tables and recreates then then loads in the data (by default).

To re-push a snapshot, edit the publication and mark the subscriber as reinitialized. This can also be done in the replication monitor. After that start the snapshot wizard and when it's done the distribution agent will send the snapshot to the subscriber.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog