Remove the Witness from a Database Mirroring Session (SQL Server)
This topic describes how to remove a witness from a database mirroring session in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. At any time during a database mirroring session, the database owner can turn off the witness for a database mirroring session.
In This Topic
-
Before you begin:
-
To Replace remove the witness, using:
-
Follow Up: After Removing the Witness
To remove the witness
-
Connect to the principal server instance and, in the Object Explorer pane, click the server name to expand the server tree.
-
Expand Databases, and select the database whose witness you want to remove.
-
Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
-
To remove the witness, delete its server network address from the Witness field.
Note
If you switch from high-safety mode with automatic failover to high-performance mode, the Witness field is automatically cleared.
To remove the witness
-
Connect to the Database Engine on either partner server instance.
-
From the Standard bar, click New Query.
-
Issue the following statement:
ALTER DATABASE database_name SET WITNESS OFF
where database_name is the name of the mirrored database.
The following example removes the witness from the AdventureWorks2012 database.
ALTER DATABASE AdventureWorks2012 SET WITNESS OFF ;
Turning off the witness changes the operating mode in accordance with the transaction-safety setting:
-
If transaction safety is set to FULL (the default), the session uses high-safety, synchronous mode without automatic failover.
-
If transaction safety is set to OFF, the session operates asynchronously (in high-performance mode) without requiring quorum. Whenever transaction safety is turned off, we strongly recommend also turning the witness off.
|
Tip
|
|---|
|
The transaction safety setting of the database is recorded on each partner in the sys.database_mirroring catalog view in the mirroring_safety_level and mirroring_safety_level_desc columns. |
