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

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.

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.

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.

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
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

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

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.