Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Change operating modes for SQL Server Database Mirroring


By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | More > Database Mirroring

Problem

SQL Server Database Mirroring offers two methods for exchanging data: synchronously and asynchronously. Sometimes there may be a need to switch between modes for an existing mirror and in this tip we will look at how this can be done.

Solution

The operating mode of a session is determined by the combination of its transaction safety setting and the state of the witness. Transaction safety is a mirroring-specific database property that determines whether a database mirroring session operates synchronously or asynchronously. There are two safety levels, either FULL or OFF.

If the transaction safety level is set to FULL, transaction safety causes the session to operate synchronously in high-safety mode and if a witness is present, a session supports automatic failover. If the transaction safety level is set to OFF, transaction safety causes the session to operate asynchronously, in high-performance mode. If the safety property is set to OFF, the WITNESS property should also be set to OFF (the default).

The transaction safety setting of the SQL Server database is recorded for each partner in the sys.database_mirroring catalog view in the mirroring_safety_level and mirroring_safety_level_desc columns. The database owner can change the transaction safety level at any time. By default, the level of transaction safety is set to FULL (synchronous operating mode). Here I am going to show you how to change the safety level or operating modes for SQL Server Database Mirroring.

Note: SQL Server Database Mirroring asynchronous mode is only supported in the Enterprise edition of SQL Server.

Change SQL Server Database Mirroring Operating Mode using T-SQL

Step 1: First let's check the Database Mirroring configuration details like the mirroring mode, status, etc. Run the below code to get the details. You can see in below screen shot that database "Manvendra" is configured for Database Mirroring and running with transaction safety level FULL (synchronous mode).

SELECT  databases.name AS DatabaseName,
       database_mirroring.mirroring_state,
       database_mirroring.mirroring_state_desc,
       database_mirroring.mirroring_role_desc,
       database_mirroring.mirroring_safety_level,
       database_mirroring.mirroring_safety_level_desc,
       database_mirroring.mirroring_safety_sequence FROM sys.database_mirroring    
INNER JOIN sys.databases
ON databases.database_id=database_mirroring.database_id

Check DB mirroring mode

Step 2: Now suppose we need to change the safety level of this Database Mirroring configuration to OFF (asynchronous mode), we can use the below ALTER statement to change the safety level.

ALTER DATABASE Manvendra SET PARTNER SAFETY OFF

SET SAFETY LEVEL to OFF

Step 3: We can verify this change by running the code we ran in step 1. We can see in the below screenshot that the operating mode for this database is now OFF (asynchronous mode). To make sure mirroring is still working, we can look at the mirroring_state_desc which shows "SYNCHRONIZED" which means all is perfectly well and you are done with your changes.

Check Safety level after change

Be aware that manual failover is not possible when the Database Mirroring configuration is in asynchronous mode. If you try to do a manual failover you will get a message like below. You can still do a force failover if you do need to failover.

Manual failover in asynchronous mode

Change SQL Server Database Mirroring Operating Mode using SSMS

Step 1: Database Mirroring operating modes can be changed using SQL Server Management Studio. Right click on the mirrored database, choose "Tasks" then click on "Mirror" as shown below.

DM property page

Step 2: Once you click on the Mirror, the database mirroring property window will appear. You can see the operating mode for database Manvendra in the below screenshot is currently asynchronous since we just changed it above.

DM property page window

Step 3: Now click on "High Safety without automatic failover (synchronous)" and click on the OK button to apply this change. Note: since there is not a witness in this mirroring configuration that is why the third option "High Safety with automatic failover (synchronous)" is disabled in this section.

Change SAFETY level TO FULL

Step 4: Now check the mirroring the configuration mode again by running the below code. We can see the operating mode is back FULL (synchronous mode). Another thing to take note of is the column "mirroring_safety_sequence". The value of this column will update for each change of the safety level. Initially it was 1 in the first screen shot, then we made two changes so the latest value is 3.

Check Mirroring mode after change

Next Steps


Last Update:






About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools