How to manual failover mirroring without affecting replication - SQL Server by serverku

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 good 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 was key for that succeed failover for us. This post is about to considering mirroring without witness server\automatic failover and transactional replication where the production database to act as a publisher and principal and the plan for same like following.

Planning

• Configured mirror all production databases to mirror instance without witness server.
• Created a dns alias for production server.
• Used that alias as a data source to connect production sql server instance in linked servers, reports, ssis packages in all servers which pointing production instance and in application too .
• Created all scheduled jobs with disable status in mirror instance.
• Created all linked servers of production instance in mirror instance.
• Created all logins of production instance in mirror instance.
• Created all database mail profiles 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 confusing for replication, How to manually failover of mirroring without affecting replication? That was an issue. I have applied the solution and made it succeed. After manual failover, transactional replication started to raise an error and stopped working. Because it was trying to connect publisher database but it became a mirror after failover,

The process could not execute 'sp_replcmds' on '<original Publisher Server>'.

Workaround
There is one more step apart from listed above.

• 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 agents, 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 <failover partner> 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 a new agent profile because it does not allow to add –PublisherFailoverPartner parameter for system profile from the user interface. But we can add it with system procedures with tsql script.

USE distribution 
GO 

-- For Snapshot Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 1, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- For Log Reader Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 2, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- For Distribution Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 3, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- For Merge Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 4, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- Queue Reader Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 9, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

It has allowed to add this parameter for system profiles, but change the profile_id whatever system or user profile used for an agent which you will get it from sp_help_agent_profile system procedure from distribution database.

How to confirm?
By following script we can confirm the parameter values for such agents. Run this script in msdb database from distributor server.

USE msdb 
GO

SELECT a.profile_id, 
       a.profile_name, 
       a.description, 
       a.def_profile, 
       b.parameter_name, 
       b.value 
FROM   msagent_profiles a 
       INNER JOIN msagent_parameters b 
               ON ( a.profile_id = b.profile_id ) 
WHERE  b.parameter_name = '-PublisherFailoverPartner' 

(Click on image to enlarge)
Hope you enjoyed this case and might help you a lot. Did you face 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!