By: Manvendra Singh | Comments (1) | Related: > Database Mirroring
Problem
Suppose you have a requirement to change the failover mode from automatic to manual for a SQL Server database mirroring configuration. As we know, the witness server is mandatory for automatic failover so we need to remove the witness server in order to change the SQL Server database mirroring failover mode from automatic to manual. Follow the step by step process of this tip, if you need to remove the witness server from an existing database mirroring configuration.
Solution
Database mirroring is solution for increasing the availability of SQL Server databases. The operating modes of database mirroring are high-safety mode and high-performance mode. High-safety mode supports synchronous operation whereas high-performance mode runs asynchronously. Manual failover can be used in both operating modes, but if you need automatic failover then high-safety mode will require a third server instance known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning.
Removing Witness from an existing database mirroring configuration using SSMS
Step 1: First we should check our database mirroring status whether it's working fine and in sync with the primary database. We can also see whether the witness server is added to this mirroring configuration. Run the below command for the status of the witness server as well as the status of the database mirroring configuration.
SELECT databases.name AS DatabaseName, 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, database_mirroring.mirroring_witness_name, database_mirroring.mirroring_witness_state, database_mirroring.mirroring_witness_state_desc FROM sys.database_mirroring INNER JOIN sys.databases ON databases.database_id=database_mirroring.database_id WHERE databases.database_id > 4
You can see the mirroring state is synchronized and the witness server is configured. You can check the "mirroring_witness_name" for the witness server value and its state description shows "CONNECTED" in the below screenshot.
Step 2: Now we have to remove this witness server from this configuration so we can change it from automatic failover to manual failover. To do so, right click on the mirrored database and choose "Tasks" and click on the "Mirror..." option as shown in the below screenshot.
Step 3: This is the Database Properties window for your target database (Manvendra). Navigate to the "Mirroring" tab from the left pane of the window. You can see "High safety with automatic failover" is the operating mode selected in the below screenshot because the witness server is configured.
Step 4: You can see the witness server name is mentioned in the Witness field and "High safety with automatic failover" operating mode is selected. Now we need to remove the witness server instance from the database mirroring configuration, so clear the witness server instance name from the witness field. The moment you clear/remove the witness server name, the operating mode will be switched from "High safety with automatic failover" to "High safety without automatic failover" as shown in the below screenshot.
Step 5: Click on the "OK" button to apply the change for the database mirroring configuration. Once the change is applied successfully, the database mirroring property window will close.
Remove the Witness from an existing SQL Server Database Mirroring configuration using T-SQL
The witness server has already been removed from the database mirroring configuration using SQL Server Management Studio in above steps, but we can also remove it using T-SQL commands. The T-SQL method is very simple and you need to just run one ALTER statement. Run the command below on your primary SQL Server instance to remove the witness.
--Replace your database name with "Manvendra". ALTER DATABASE Manvendra SET WITNESS OFF;
You can see in the below screenshot where I have removed the witness server instance by running the above T-SQL ALTER statement.
Validate SQL Server Database Mirroring Witness Server Removal
We removed the witness server from an existing SQL Server database mirroring configuration, now we will check whether the witness is removed from the mirroring configuration. Run the same command we ran above to check the mirroring state in step 1.
You can compare this screenshot with the one from step 1. We can see there is no value in the "mirroring_witness_name" column and the "mirroring_witness_state" value has changed to 0 which means there is no witness configured. The mirroring_witness_state_desc value also shows UNKNOWN where it was CONNECTED in step 1.
Next Steps
- Do not make these changes on a repetitive basis, by adding and dropping and adding a witness server.
- These changes can be made if required and you should first test in a lower life cycle environment.
- Explore more knowledge on the following topics:
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
