Skip to content

remove-the-witness-from-a-database-mirroring-session-sql-server.md

Latest commit

 

History

History
97 lines (61 loc) · 4.93 KB

remove-the-witness-from-a-database-mirroring-session-sql-server.md

File metadata and controls

97 lines (61 loc) · 4.93 KB
title Remove database mirroring witness
description Describes how to remove a witness from a database mirroring session with SQL Server Management Studio (SSMS), or Transact-SQL (T-SQL).
author MikeRayMSFT
ms.author mikeray
ms.date 03/14/2017
ms.service sql
ms.subservice database-mirroring
ms.topic how-to
helpviewer_keywords
witness [SQL Server], turning off
witness [SQL Server], removing
database mirroring [SQL Server], witness

Remove the Witness from a Database Mirroring Session (SQL Server)

[!INCLUDE SQL Server] This topic describes how to remove a witness from a database mirroring session in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. 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

Security

Permissions

Requires ALTER permission on the database.

Using SQL Server Management Studio

To remove the witness

  1. Connect to the principal server instance and, in the Object Explorer pane, click the server name to expand the server tree.

  2. Expand Databases, and select the database whose witness you want to remove.

  3. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.

  4. 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.

Using Transact-SQL

To remove the witness

  1. Connect to the [!INCLUDEssDE] on either partner server instance.

  2. From the Standard bar, click New Query.

  3. 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 [!INCLUDEssSampleDBobject] database.

    ALTER DATABASE AdventureWorks2022 SET WITNESS OFF ;

Follow Up: After Removing the Witness

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.

Related Tasks

See Also

ALTER DATABASE Database Mirroring (Transact-SQL)
Change Transaction Safety in a Database Mirroring Session (Transact-SQL)
Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)
Database Mirroring Witness