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

 

How to Remove SQL Server witness server from an existing Database Mirroring Configuration


By:   |   Read Comments   |   Related Tips: More > Database Mirroring


Latest on-demand video "Optimize SQL Server Performance" (watch now for free)


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.

Check the SQL Server Database Mirroring Configuration

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.

Launch the SQL Server Database Mirroring configuration Page

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.

SQL Server Database Mirroring configuration Page

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.

Remove witness server in SQL Server Database Mirroring

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.

Remove witness using t-sql in SQL Server Database Mirroring

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.

Validate witness removal in SQL Server Database Mirroring

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


Last Update:


signup button

next tip button



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
Related Resources





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