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
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
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.
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.
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.
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.
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.
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.
Next Steps
- Do not make this change unless there is a real requirement to do so.
- Do proper testing in a lower life cycle environment before replicating this change in a production environment.
- Explore more knowledge on SQL Server Database Administration Tips and SQL Server Database Mirroring Tips.
