November 15, 2016 at 5:44 pm
Hi All ,
I did fail over between Principal and Mirror server sql server 2008 R2 in production last week and now all replications are not working at all . It was working well before fail over
The error that I got when I right click on publication are as below :
-Subscription option tab : Only functions and some extended Stored procedure can be executed from within a function
-Publication Access List tab : Could not find the distributor or the distributor database for the local server . The distributor may not be installed , or the local server may not be configured as a publisher at the distributor
-Agent Security tab : The distributor has been installed correctly
When I right click on View Snapshot Agent Status :
“ Could not retrieve agent status ( could not find stored procedure “)
And when I click Start
The error is “ The agent could be started – Non- SQL publisher cannot be found . Execute sp_helpdistpublishers to view a list of available publishers . Change database context to “xxx”
What I did is :
-On Principal server , Right click on Distributor properties , I added mirror server there
-On Principal server , in Agent profile ( Both Snapshot and log Reader file) , on parameter PublisherFailOverPartner I put the mirror server as the value
But it is not working unfortunately
What should I do to overcome this issue ?
Any feedback are much appreciated
Cheers
November 17, 2016 at 4:09 pm
Hi All,
Anyone can give me the idea how to deal with this please?
Really appreciated
Cheers
November 20, 2016 at 6:51 pm
WhiteLotus
Let me understand your setup:
You have 3 servers (Server A, Server B, Server C) in which all db's are mirrored from Server A to Server B?
On Server A you had replication setup to Server C and after failing over to B replication doesn't work and you get errors in Server C?
On which server is your distributor currently?
In order for replication to failover distributor must be setup on a separate server like Server C.
Here is the link for setup
https://www.pythian.com/blog/how-to-configure-transactional-replication-mirroring-failover/
November 20, 2016 at 10:16 pm
Hi Alex
Many thanks for the valuable feedback , It believe it will be very useful
I will use witness server to act as DISTRIBUTOR then
Questions :
1.Should it install with the same SQL server edition like principal and mirror ? I assume we don’t need to ?
2.My principal has been set up to act as its own distributor , How do I change it so I can use remote distributor ?
Thank you ?
November 21, 2016 at 2:10 pm
WhiteLotus (11/20/2016)
Hi AlexMany thanks for the valuable feedback , It believe it will be very useful
I will use witness server to act as DISTRIBUTOR then
Questions :
1.Should it install with the same SQL server edition like principal and mirror ? I assume we don’t need to ?
2.My principal has been set up to act as its own distributor , How do I change it so I can use remote distributor ?
Thank you ?
Follow these steps:
https://www.pythian.com/blog/how-to-configure-transactional-replication-mirroring-failover/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply