Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to setup a disaster recovery scenario and I'm stuck at this particular point. Here's what I've done so far:

A) Created principal database on Server A and and mirror on Server B

B) Setup the Distribution database on Server C and modified the agent profiles with –PublisherFailoverPartner to Server B.

C) Created two replication publishers on the principal.

D) Setup a subscriber on Server D to the two subscriptions.

E) Verified my subscriber was getting updates.

At this point, I shut down Server A hosting my principal database. I then ran ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS on Server B to get my mirror database online. With the old principal Server A still offline, I modified a record on a published table. The change never made it to Server D, my subscriber.

I checked replication monitor and it shows no errors. But on the Publisher to Distributor History it says 'Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.'

I understand that when I ran the FORCE_SERVICE_ALLOW_DATA_LOSS that my mirror became my principal and my old principal became the mirror (which will sync back to my new principal when brought online).

What I'd like to have happen is replication function like normal even while the Server A is down. Then when it comes back, shut down the Server B and re-run the FORCE_SERVICE_ALLOW_DATA_LOSS command on Server A and have my original principal server back without tons of work.

Is this possible? Does anyone have any advice outside of removing database mirroring after the initial failover (to get replication to work)? Any alternate solutions? What happens when Server A comes back online (regardless of whether mirroring was left in tact) with regard to the –PublisherFailoverPartner switch. Does replication automatically go back to Server A?

share|improve this question
    
Since you have three servers at your disposal, have you tried setting up mirroring in synchronous mode with a witness? If you do this combination with replication, your subscriptions will continue to work automatically after the publisher fails over. –  Steven Dec 12 '12 at 23:54

1 Answer 1

up vote 0 down vote accepted

Trace flag 1448 was the answer...

DBCC TRACEON (1448, -1) on Server B immediately sent the record to the subscriber. No restart necessary.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.