Before a couple of days we planned to manual failover of production servers and all live databases for that instance to mirror instance and did a failover too. It was a best experience for failover without fail anything like replication, scheduled jobs, linked servers, ssis packages, reports, windows tasks and whatever dependencies . Well perfect planning and team work were key for that succeed failover for us. This post is about to considering mirroring without witness server\automatic failover and transactional replication where production database act as a publisher and principal and the plan for same like following,
Planning
Mirror all production databases to mirror instance without witness server.
Created a dns alias for production sql server.
Used that alias as a data source in linked servers, reports, ssis packages in all servers which pointing production instance .
Created all scheduled jobs with disable status in mirror instance.
Created all linked servers of production instance in mirror instance.
Created all linked servers of production instance in mirror instance.
Created sql server agent operators of production instance in mirror instance.
Created windows scheduled tasks with disable status in mirror server.
All of above steps applied in advance with recent changes just before to start failover and need to change dns alias to mirror server, enable scheduled jobs and windows tasks during failover.
Problem
But had a little bit confusion for replication, How to manually failover of mirroring without affecting replication? That was an issue. I have applied solution and made it succeed. After manual failover, transactional replication started to raise an error and working stopped. because it was trying to connect publisher database but it became mirror after failover,
The process could not execute 'sp_replcmds' on '
'.
Workaround
There is one more step apart from above listed,
Add Failover Partner as a parameter (–PublisherFailoverPartner) in snapshot, log reader and queue reader agents.
How to add parameter?
I am sharing some screen shots which drive us for the explanation,
1. Go to Replication monitor and move to agent tab. Select agent from Agent types drop box, you will have list of agent, select it and click Agent Profiler from right click property.
2. Under Agent property, create a new user profile which will be created same as system profile, just need to add value
of –PublisherFailoverPartner parameter.
3. Add –PublisherFailoverPartner parameter value for all agents like snapshot, log reader, queue reader agent and merge agent we have merge replication configured.
Note : After creating a new user agent profile check the box “Use for this agent” . I created new agent profile because it is not allow to add –PublisherFailoverPartner parameter for system profile from user interface. But we can add it with system procedures with tsql script.
It is allow to add this parameter for system profiles but change the profile_id whatever system or user profile used for agent which you will get it from sp_help_agent_profile system procedure from distribution database.
How to confirm?
With following script we can confirm the parameter values for such agents. Run this script in msdb database from distributor server.
(Click on image to enlarge)
Hope you enjoyed this case and might help you a lot. Did you faced this issue or what is your solution? Something missing in failover plan? Please share your ideas and opinion about it. Your comments are most welcome!