Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)
Applies To: SQL Server 2016
To set up a witness for a database, the database owner assigns a Database Engine instance to the role of witness server. The witness server instance can run on the same computer as the principal or mirror server instance, but this substantially reduces the robustness of automatic failover.
We strongly recommend that the witness reside on a separate computer. A given server can participate in multiple concurrent database mirroring sessions with the same or different partners. A given server can be a partner in some sessions and a witness in other sessions.
The witness is intended exclusively for high-safety mode with automatic failover. Before you set a witness, we strongly recommend that you ensure that the SAFETY property is currently set to FULL.
On the witness server instance, ensure that an endpoint exists for database mirroring. Regardless of the number of mirroring session to be supported, the server instance must have only one database mirroring endpoint. If you intend to use this server instance exclusively as a witness in database mirroring sessions, assign the role of witness to the endpoint (ROLE=WITNESS). If you intend to use this server instance as a partner in one or more other database mirroring sessions, assign the role of the endpoint as ALL.
To execute a SET WITNESS statement, the database mirroring session must already be started (between the partners), and the STATE of the endpoint of the witness must be set to STARTED.
To learn whether the witness server instance has its database mirroring endpoint and to learn its role and state, on that instance, use the following Transact-SQL statement:
SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
If a database mirroring endpoint exists and is already in use, we recommend that you use that endpoint for every session on the server instance. Dropping an in-use endpoint disrupts the connections of the existing sessions. If a witness has been set for a session, dropping the database mirroring endpoint can cause the principal server of that session to lose quorum; if that occurs, the database is taken offline and its users are disconnected. For more information, see Quorum: How a Witness Affects Database Availability (Database Mirroring).
If the witness lacks an endpoint, see Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL).
If the partner instances are running under different domain user accounts, create a login for the different accounts in the master database of each instance. For more information, see Allow Network Access to a Database Mirroring Endpoint Using Windows Authentication (SQL Server).
Connect to the principal server and issue the following statement:
ALTER DATABASE <database_name> SET WITNESS =<server_network_address>
where <database_name> is the name of the database to be mirrored (this name is the same on both partners), and <server_network_address> is the server network address of the witness server instance.
The syntax for a server network address is as follows:
where <system-address> is a string that unambiguously identifies the destination computer system, and <port> is the port number used by the mirroring endpoint of the partner server instance. For more information, see Specify a Server Network Address (Database Mirroring).
For example, on the principal server instance, the following ALTER DATABASE statement sets the witness. The database name is AdventureWorks, the system address is DBSERVER3—the name of the witness system, and the port used by the database mirroring endpoint of the witness is
ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://DBSERVER3:7022'
The following example establishes a data mirroring witness. On the witness server instance (default instance on
Create an endpoint for this server instance for the WITNESS role only using port
CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP (LISTENER_PORT=7022) FOR DATABASE_MIRRORING (ROLE=WITNESS) GO
Create a login for domain user account of partner instances, if different; for example, assume that the witness is running as
SOMEDOMAIN\witnessuser, but the partners are running as
MYDOMAIN\dbousername. Create a login for the partners, as follows:
--Create a login for the partner server instances, --which are both running as MYDOMAIN\dbousername: USE master ; GO CREATE LOGIN [MYDOMAIN\dbousername] FROM WINDOWS ; GO --Grant connect permissions on endpoint to login account --of partners GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MYDOMAIN\dbousername]; GO
On each of the partner server instances, create a login for the witness server instance:
--Create a login for the witness server instance, --which is running as SOMEDOMAIN\witnessuser: USE master ; GO CREATE LOGIN [SOMEDOMAIN\witnessuser] FROM WINDOWS ; GO --Grant connect permissions on endpoint to login account --of partners GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [SOMEDOMAIN\witnessuser]; GO
On the principal server, set the witness (which is on
ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://WITNESSHOST4:7022' GO
For a complete example showing security setup, preparing the mirror database, setting up the partners, and adding a witness, see Setting Up Database Mirroring (SQL Server).
ALTER DATABASE (Transact-SQL)
Allow Network Access to a Database Mirroring Endpoint Using Windows Authentication (SQL Server)
Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL)
Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL)
Remove the Witness from a Database Mirroring Session (SQL Server)
Database Mirroring Witness