March 18, 2008 at 6:13 pm
I have 2 servers (2005 with the good sp2).
They run a database with mirroring (high safety manual failover).
This database then has transactional replication set up with a remote distributor and 2 remote subscribers.
The replication works fine until I fail the mirror over.
The mirror was created and working fine.
I created the distributor first and added the -PublisherFailoverPartner MirrorServerName to the snapshot and log reader profiles. Then I created the publishers and subscribers.
I have since created a new agent profile and clicked the change existing agents and restarted the jobs.
That didn't work so I added -PublisherFailoverPartner [MirrorServername] to the agent step (second step) in the ServerName-Publication-Number job, stopped and started the job.
Still fails.
The replication monitor continues to show the agent attempting to run replcmds on the primary server and failing.
Any ideas?
March 20, 2008 at 4:21 am
It sounds like the Distributor is not recognising that a failover has occurred. Have you tried this set up with a Witness Server, and configured it to automatically failover? Bit of a wild guess, but it might help the Distributor get the message.
March 24, 2008 at 3:54 pm
I will give that a try.
I have run the log reader in command mode and when I down the primary it does only try the primary.
In the startup parameters it certainly notes the Publisher failover partner and the server name is correct.
Can the presence of a witness have any real affect though? The client (I believe) is completely unaware of the presence of the witness.
I was under the impression that the client attempts to connect to the primary and if it is unavailable and there is a secondary server specified it just tries that blindly.
March 27, 2008 at 2:40 am
Yep, that is my understanding too. Only the Principal and Mirror know about the Witness.
So, if your Distributor is only ever trying to contact the Principal, it sounds like your Distributor does not know about the failover. That probably means one of two things.
1) The Distributor did not detect the failover
2) The Distributor is not correctly configured to use a Mirror
To try and problem-solve (1), is why I suggested using a Witness Server; although the Distributor would not connect to the Witness Server itself, it might cause the failover to happen in a slightly different way (which the Distributor could detect).
As for (2), I've never run a Mirror Database with Replication, so I'm afraid I can't help you much. I did take a quick look at Chapter 14 of "Pro SQL Server 2005 High Availability", and it notes that only read-only subscribers and queued updating subscribers are supported. Also the Publisher should be configured before the Database Mirror.
Andy
March 30, 2008 at 3:55 pm
Well I have set up the mirror and tested that, still no joy.
I have followed all the MS doco on setting it up and verified the setup with PSS.
I had one of my developers write a quick mirror failover test app (basically it connect s to a db and does select @@servername every 3 seconds which it prints to screen with a datestamp). When I fail over the db manually the util fails once and then picks up the mirror and continues happily. The util uses the failover partner connection string parameter which I assume is the same as what the replication utilities are using. This I figure proves that mirroring is working correctly.
So we now have an outstanding call with PSS. Been a week so far with no response form them.
Unfortunately this was for a client who has happily said that if the design is implemented how MS say to do it they are happy that what I have done is correct and I can go off to my next client. So I may never know how this one turns out.
If I hear from them I will no doubt try and find this thread and post the response.
March 31, 2008 at 1:35 am
Would be nice to know the outcome. The setup you have configured should work; I don't think there is a known, critical bug in the way Replication works in conjunction with a Mirror. Oh well, in the, err, "safe hands" of PSS, we may never know...
Andy
May 4, 2008 at 8:48 pm
Well looks like it is resolved. From my client:
"It seems we found the problem for the replication with mirroring. If we
make sql port number same for primary server and mirror server , it works.
The replication ignore any settings for aliases or configuration for mirror server, it use the same port number for primary server."
Guess PSS can come through occasionally even if it may take a little while.
May 5, 2008 at 1:18 am
Interesting.
Just to confirm, when you say Port Number, do you mean for the SQL Database Server, or for the Mirroring EndPoints?
May 5, 2008 at 2:08 am
Not 100% (just copy and paste of mail from client) but assuming they mean the primary and mirror as the remote replication servers would not know anything about the primary and mirror as we are using push replication.
October 23, 2008 at 12:30 pm
Go to MSDN and search for the article Replication and Database Mirroring. (Books online Sept 2007).
http://msdn.microsoft.com/en-us/library/ms151799(SQL.00,printer).aspx
I belive that is the link to printer friendly version.
It addresses what you are describing here under the requirements and considerations section.
Also make sure to read the maintaining a mirrored publication database.
Summary: the publisher instance is stored in metadata at the mirror with the principal not the mirror. It has syntax to use to get replication working.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply