Database Mirroring of Fail over and synchronous & asynchronous concepts
Fail over:
Here you can see below two database Chinook db mirror test (mirror) and DB Mirror (Principal)
when right click database of mirroring and choose task and choose database mirroring monitor
you can find below screen shot
Here you can see complete info about principal and mirror and operating mode (bottom)
when you issue on mirror server
ALTER DATABASE [Chinook db mirror test] SET PARTNER FAIL_OVER;
you will get following error
M s g 1470, Level 16, State 1, Line 3
The alter database for this partner config values may only be initiated on the current principal server for database "Chinook db mirror test".
if Run above command on principal you will get executed successfully as see below
After refreshed the server
you will get Principal/Mirror details
To change synchronous to asynchronous:
Here our case DB mirror is Asynchronous mode as see below
Here also we need to we run command in principal
ALTER DATABASE [DB mirror]SET SAFETY FULL;
but if you run in Mirror you will get below error
M s g 1470, Level 16, State 3, Line 1
The alter database for this partner config values may only be initiated on the current principal server for database "DB mirror".
if we now run in Principal it executed successfully
if we right click to db choose properties on principal
you can see it changed to synchronous from asynchronous
or
we can also see from monitor as see below
also if you choose properties of mirror database it did not show any thing info about mirroring as see below
Pause the mirroring of database:
just choose pause on database mirroring as see below
now it asks to proceed
now it went to suspend mode
we can see status on both principal and mirror went to suspend mode as see below
resume mirroring
now we need to resume on principal server properties as see below
after we clicked refresh it went fine
after click submit both server show synchronized as see below
Database mirroring is primarily an availability feature, but it has been gaining popularity as a disaster-recovery option. Asynchronous mirroring, in particular, is being leveraged for disaster recovery in scenarios where log shipping might have been used in the past. Asynchronous mirroring offers several enhancements over log shipping, making it an attractive option.
Asynchronous Mirroring vs. Log Shipping
Asynchronous mirroring and log shipping share several common traits:
- Both can be used for increased availability and for disaster recovery.
- Both protect at the database level. Anything external to the database must be synchronized separately.
- Both provide limited point-in-time, read-only capabilities.
- Neither one supports the simple recovery model.
Although they’re similar, each technology has some unique advantages over the other. The most obvious advantage with log shipping is its ability to log ship to multiple secondary databases. In addition, log shipping supports several features that asynchronous mirroring doesn’t, such as the bulk-logged recovery model, delayed replaying of log records, and FILE STREAM support. The features supported by asynchronous mirroring but not log shipping include simple reversal of roles, database snapshots, near real-time transfer of transactions, automatic re synchronization of partners after a role change, support for fail over of a replication publication, and transparent client redirection.
One of the most compelling reasons to use asynchronous mirroring instead of log shipping is simplified management. This is particularly true for those times when you need to temporarily fail over the disaster-recovery server for maintenance or some other reason.
Understanding Asynchronous Mirroring
When I talk with administrators about implementing asynchronous mirroring as a disaster-recovery solution, one of their primary questions is about the potential for data loss. Not many people fully understand the nature of the data loss potential, and several misconceptions have arisen as a result. If you want to understand how data loss can occur, you first need to understand how the mirroring modes operate.
It’s easier to understand how the mirroring modes work if you think of mirroring as a system of queues. There’s a send queue on the principal server (aka principal) and a redo queue on the mirror server (aka mirror). The send queue’s job is to send log records to the redo queue as they are generated. The redo queue accepts the log records, hardens them to the mirror’s log, and sends back acknowledgements. The mirroring queues aren’t real service broker queues. The best way to describe them is as special views into the transaction log. The send queue is looking directly at the principal’s log file, and the redo queue is looking directly at the mirror’s log file.
With synchronous mirroring, when a transaction is committed, the log record is sent to the redo queue. The redo queue hardens it to the log and sends an acknowledgement back to the principal. When the principal receives the acknowledgement, it completes the commit operation on the principal. If the hardening fails on the mirror, the mirroring session will be suspended until the cause of the failure can be found and corrected. If the principal doesn’t receive confirmation from the mirror, the connection times out and the principal transitions to a disconnected state. When the principal is disconnected, transactions continue to be queued in the send queue and mirroring continues to operate in asynchronous mode until the session reconnects and the mirror catches up with the principal. Figure 1 illustrates how synchronous mirroring works.
When mirroring is operating asynchronously, the principal doesn’t wait for a transaction to be hardened on the mirror. As soon as it sends its log record, it completes the commit operation on the principal. If the record hardening fails on the mirror, the mirror disconnects from the mirroring session and the session is suspended. Activity on the principal continues as normal, and transactions continue to be queued in the send queue. When the condition that caused the failure is corrected, the mirroring session can be resumed. Figure 2 illustrates how asynchronous mirroring works.
Figure 2 also shows when data loss might occur. If the principal fails or crashes when running asynchronously, there’s a limited window for potential data loss. Data that has been committed on the principal but has not yet been hardened to the log on the mirror could be lost if you force service on the mirror. If you wait for the principal to return to service, committed transactions won’t be lost.
A big misconception that many people have about the potential data loss is exactly when that loss might occur. If the principal is down and you force service on the mirror, data loss has not yet occurred. If data is going to be lost, it will be lost when the principal comes back online, reconnects, and takes over the mirroring role. Resuming the mirroring session in this scenario is a manual process. Data will not be lost until you resume the session and the principal transitions to the mirror role. When the principal transitions to the mirror role, it performs a synchronization check and any extra transactions it has are rolled back.
Another misconception is that the only option you have for failing over to the mirror is to force service, allowing for data loss. Technically, this is true for asynchronous mirroring. However, if you perform a manual fail over, you can avoid this data loss.
Performing a Manual Fail over
The process for manually failing over an asynchronously mirrored database is straightforward: You switch to synchronous mode before failing over, perform a traditional fail over, and switch back to asynchronous mode after the fail over. Here are the steps:
- Change the mirroring mode (also known as the safety level) to synchronous using the command
ALTER DATABASE <DB Name> SET SAFETY FULL;
where <DB Name> is the name of the mirrored database.
- Wait for the databases to become synchronized.
- Perform the manual fail over with the command
ALTER DATABASE <DB Name> SET PARTNER FAIL OVER;
- After the fail over is complete, change the mirroring mode back to asynchronous using the command
ALTER DATABASE <DB Name> SET SAFETY OFF;
When you change the operating mode from asynchronous to synchronous, the mirroring session will perform a quick synchronization check. If you attempt to fail over before this check occurs, the attempt will fail and you’ll receive an error message stating that the database wasn’t ready. The check generally takes a fraction of a second, so this failure is difficult to catch unless you run the command to switch the operating mode and the fail over command in the same batch. If you encounter this error, simply reissue the fail over command. The command should successfully execute on the second attempt.
You can see the synchronization check occur by monitoring the Pages Sent/sec performance counter of the SQL Server : Database Mirroring object. In the s y s. d m_o s_performance_counters Dynamic Management View (D M V), this counter shows the total number of synchronization checks since the SQL Server service was restarted. If you view this counter in Performance Monitor, the counter shows the total number of synchronization checks per second rather than a cumulative count, so be sure to use the D M V.
The query in Listing 1(below) uses the s y s .d m_o s_performance_counters D M V to check the Pages Sent/sec counter. You can find a full explanation of Pages Sent/sec and the other database mirroring performance counters in my blog post “SQL U HA/DR Week – Database Mirroring Performance Counters”.
Automating Fail overs
Many environments aren’t simple single-database environments. They might contain several mirrored databases that use both synchronous and asynchronous mirroring. Mirroring fail overs should be as quick as possible, particularly in complex environments in which a single application might require the availability of multiple databases. This can be achieved through automation.
An automation script for failing over asynchronous mirrors needs to perform the following steps:
- Identify the databases to fail over.
- Set SAFETY to FULL for all asynchronous mirrors.
- Allow the mirrors that were running asynchronously to synchronize.
- Fail over all mirrored databases.
- Allow the fail overs to complete and the mirror to synchronize.
- Set SAFETY to OFF for all databases that were originally running asynchronously.
Above via the Download the Code link, you’ll find a script (Fail over. s q l) that you can use to fail over all mirrored databases on a server. This script requires that a linked server already exist on the current principal server. Fail over.s q l includes several time loops to allow certain processes time to complete. To ensure that you don’t wait too long for any one process to complete, you might need to reconfigure the maximum timeout for the time loops, as explained in the comments of the script.
In
Pro SQL Server 2008 Mirroring (A press, 2009), I provide a stored procedure that can be used for failing over all databases or a database by name. A similar stored procedure can also be found in my blog post “
Database Mirroring: Maximizing Availability Through Automation”.
Synchronizing External Objects
No matter what technology you use for disaster recovery, you need to synchronize objects external to the database before a fail over. Several types of objects might need to exist on the fail over server in order for applications to operate properly. They include:
- Logins
- Credentials
- Operators
- Alerts
- Linked servers
- Server triggers
- Server audits
- SQL Server jobs
- Database Mail profiles
- Server configuration settings (e.g., max text replication size if a replication publisher)
- File and backup paths
- Windows shares
- Custom drivers or add-o n s
- Windows services
One problem that seems to stymie a lot of people is that SQL Server authentication logins don’t work when they fail over to the mirror database. This problem is caused by mismatched S I D s. Windows logins get their S I D s from Active Directory (AD). SQL Server authenticated logins get their S I D s from the server on which they’re created. If you create identical SQL Server logins on two different SQL Server instances, you’ll get two different S I D s. Database users map to server logins by the SID, not the name.
A popular solution for this problem is to run a procedure to remap the database user to the login, thus updating the database user with a new SID. However, this is just a temporary solution. When you fail back to the original principal, you have to do it again. A better solution is to create the login on the mirror using the same SID as the login on the principal. To do so, you need to query the principal for the logins, their S I D s, and their password hashes. After you have this information, you can re-create the logins using the existing S I D s and passwords. The command to re-create the logins should follow the syntax:
CREATE LOGIN [S Q L U s e r]
WITH PASSWORD =
<Password Hash> HASHED,
SID = <SID String>;
I created a stored procedure named d b a_Copy Logins that handles Windows logins, SQL Server authenticated logins, server roles, and explicitly granted permissions. You can schedule this stored procedure to execute daily during the nightly maintenance window. The procedure relies on a linked server existing on the principal server. You simply pass in the name of the linked server to the principal as the @Partner Server parameter. The 139794.zip file includes d b a_Copy Logins.s q l. You can also download it from my blog post “
Transferring Logins to a Database Mirror”.
Asynchronous Mirroring as a Disaster-Recovery Solution
As technologies advance, the lines between availability and recovery are getting blurred. Log shipping and database mirroring are increasingly being used to fill both roles. Fail over clustering and synchronous mirroring are the mainstays of high availability, whereas log shipping and asynchronous mirroring have been relegated to secondary availability options or primary recover ability options. If you’re currently using log shipping for disaster recovery, I encourage you to consider using asynchronous mirroring instead. I think you will appreciate its enhanced manageability and features.
Listing 1: Query to Check the Pages Sent/sec Performance Counter
DECLARE @Obj Name s y s name;
-- Set to "M S S Q L$<Instance Name>" if a named instance.
SET @Obj Name = N'MS SQL$' + CAST(SERVER PROPERTY('Instance Name')
AS s y s name);
-- If not a named instance (NULL), set to "SQ L Server".
SET @Obj Name = I S NULL(@Obj Name, N'SQL Server');
-- Complete setting the object name of the counter.
SET @Obj Name = @Obj Name + N':Database Mirroring'
SELECT counter_name, instance_name, c n tr_value
FROM s y s.d m_o s_performance_counters
WHERE object_name = @Obj Name
AND counter_name = 'Pages Sent/sec'
