Sql-server – Resuming Replication w/ Database Mirroring when Principle is unavailable

mirroringreplicationsql-server-2008

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?

Best Answer

Trace flag 1448 was the answer...

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