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

By:   |   Comments (1)   |   Related: > Database Mirroring


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.


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 AS DatabaseName,
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".

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, January 24, 2018 - 3:53:44 AM - Ibrar_Salam Back To Top (75029)

Dear Sir,

we have two sql servers in our network, the Main Server and Backup Server, so we configured the mirroring between these two servers, but two days ago the mirroring has stoped, so i removed the mirroring from Main Server, so the backup or Mirrored Server still thinks it is connected to Main server, the status of Backup (Mirrored) Server is Mirror\Disconnected\In Recovery.

so i need to remove this databas. but i am no able to do that

Help me if you can


get free sql tips
agree to terms