Script to Database Mirroring in SQL Server by serverku

Apart from replication articles which I have written earlier, today i want to share for database mirroring configure with windows authentication without automatic failover. So let me share steps and required scripts for the same.

1. Make sure principal database has FULL recovery model.

2. Make sure Windows User on which mirror configured has enough access of both servers and databases.

3. Make sure TCPIP port open in firewall for both SQL Server instances on both server accordingly if firewall enabled.

4. Make sure database service running on windows user for which mirroring endpoint will be created.

5. Take a full backup of principal database and restore at partner server with norecovery.

6. Take a transaction backup of principal database and restore at partner server with norecovery and make sure all log backups restored with norecovery created after full backup of principal database.

7. Create a mirroring endpoint.

USE master
go

CREATE ENDPOINT [DatabaseMirroring]
AUTHORIZATION [<DomainName>\<UserName>]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = DISABLED)

8.  Get mirroring role and status details for confirmation.

USE master
go

SELECT 
 state_desc,
 type_desc 
FROM sys.database_mirroring_endpoints

9. Make sure 5022 port or whatever port used should be open in firewall if firewall enabled.

10. Run following command at partner server.

USE master
go

ALTER DATABASE <DatabaseName>
SET PARTNER ='TCP://<PrincipalHostName>.<DomainName>.local:5022'

11. Run following command at principal server.

USE master
go

ALTER DATABASE <DatabaseName>
SET PARTNER ='TCP://<PartnerHostName>.<DomainName>.local:5022'

12.Run following command in one of server if want to change high performance or high safety as per need and if it is supported.

USE master
go

-- OFF : High performance
-- FULL : High Safety
ALTER DATABASE <DatabaseName>
SET SAFETY OFF 

Hope you like this post and might be useful to you.

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!

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up - Issue in SQL Server Replication by serverku

Hope you read my earlier post of "Replication components are not installed on this server" issue, you may like it. One day suddenly replication went to high latency and I clicked it during monitoring. I opened the replication monitor, during analysis I found one message which was showing latency from publisher to distributor due to some issue. The message is as follows,

“Replicated transactions are waiting for next Log backup or for mirroring partner to catch up”.

As per message i checked the transaction log backups were happened or not, checked it and log backups were happening . Finally one option is pending to review and it is mirroring. I checked the status of mirroring  and see the  principal database went to synchronizing mode. May be mirroring went in synchronizing mode due to heavy or so many transactions in the route to apply at mirror database. So replication is waiting  to be synchronized status of principal database. I exactly do not know why replication went on waiting even transaction log backups were happening. I checked online solution and received some of the solutions from here and it suggests following,

1. EXEC sp_replicationdboption 'PublisherDB','sync with backup',false
This means that not need to backed up of all transactions before being delivered to the distribution database. Please visit this option here, which sets a replication database option for the specified database. This stored procedure is executed at the Publisher or Subscriber on any database.

2. Enable trace flag 1448
After this setting the Log Reader Agent can continue replicating changes regardless of the mirroring state. Please read more here.

I never applied these suggestions. Replication and mirroring both are using transaction logs and this could be the reason why replication was in high delay status while mirroring was stuck or in process to synchronize the mirror database. So I turned off mirroring and monitored replication status. Finally replication was succeeded to remove the delay and applied all pending commands. Then I configured to mirror again after doing with replication sync.

Conclusion : We have two options, either to wait for synchronized status of principal database or turned of mirroring, getting replication synced properly and reconfigure mirroring again depends on priority and importance. It will be better to go for correct solution to avoid such issue. But question is here, why replication went on waiting for a synchronized of mirroring even transactions log backups happened while? I would like you to share if received such issue and solution or any opinion which you applied to resolve it. This may help to me and all people facing the same issue.