Witness Server
1. It is an optional function in a high-safety mode
2. It does not serve the database
3. It can be a SQL Server Workgroup and SQL Server Express

Automatic Failover only occur
1. Synchronized Secondary Server losses connection to Primary Server
2. Synchronized Secondary Server has connection to the Witness Server
3. Witness Server losses connection to Primary Server

In this a install SQL Server Express at “AD01.joeschoice.com”.

Add Witness Server into existed Database Mirror
1. Change Service Account of “SQL Server (MSSQLSERVER)” from “NT Service\MSSQLSERVER” to “joeschoice\mssql-admin”
a. In SQL Server, “Start” > “Micrsoft SQL Sever 2016” > “SQL Server Configuration Manager”
b. Select “SQL Server Services” > Right-Click “SQL Server (MSSQLSERVER)” > “Properties”
c. Enter “joeschoice\opr-MSSQL” into “Account Name:” and enter password into “Password:” and “Confirm password:”
d. Click “OK”

2. Create Endpoint on Witness Server

-- We create endpoint named DB_MIRRORING
-- It uses tcp port 9011
-- It communicate with JOESCHOICE.COM\DB01$ & JOESCHOICE.COM\DB02$
-- Enables the endpoint to perform in the role of a partner
CREATE ENDPOINT DB_MIRRORING
STATE=STARTED
AS TCP (LISTENER_PORT=9011)
FOR DATABASE_MIRRORING (ROLE=ALL)
CREATE LOGIN [JOESCHOICE\DB02$] FROM WINDOWS WITH DEFAULT_DATABASE=master
CREATE LOGIN [JOESCHOICE\DB01$] FROM WINDOWS WITH DEFAULT_DATABASE=master
GRANT CONNECT ON ENDPOINT::DB_MIRRORING TO [JOESCHOICE\DB02$]
GRANT CONNECT ON ENDPOINT::DB_MIRRORING TO [JOESCHOICE\DB01$]

3. Verify the Endpoint setting
a. Uses “netstat -a” or “telent” to verify the endpoint’s tcp port whether in listening status
b. Vertify Communication sevrer login account by expend “Security” > “Logins”
c. Vertify endpoint object by expend “Server Objects” > “Endpoints” > “database Mirroring”

4. Create inbound firewall for TCP Port 9033 at Witness Server

5. Add Witness Server setting at Primary Server

ALTER DATABASE DB_MIRROR SET WITNESS = 'TCP://ad01.joeschoice.com:9033'

Manual Failover by GUI
1. Login to Principal Server
2. “Start” > “Microsoft SQL Sever Tools 17” > “Microsoft SQL Server Management Studio 17”
3. Connect to Production Server > Expand “Databases”
4. Right-Click “DB_MIRROR” DB > “Properties”
5. Select “Mirroring” > Click “failover”
6. Click “OK” twice

Manual Failover by TSQL

-- It need run on Principal Server
USE master;
ALTER DATABASE DB_MIRROR SET PARTNER FAILOVER

Remove Witness Server by TSQL

ALTER DATABASE DB_MIRROR SET WITNESS OFF

Force Service
1. If Principal server and Witness server is down, we can force mirror server to become principal server.
2. However, it will casue data loss
3. It only can run on mirror server

ALTER DATABASE DB_MIRROR SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

4. When Principal server online again, the Database Mirroring will be suspended. We need to start database mirroring again.

ALTER DATABASE DB_MIRROR SET PARTNER RESUME

Remove Database Mirroring
This TSQL only delete database mirroring setting. The endpoint and login account need to delete manually.

ALTER DATABASE DB_MIRROR SET PARTNER OFF