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 : A
fter 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!
