Database Mirroring
1. Datadase’s recovery model must be full
2. It provides a hot standby server and support rapid failover
3. DotNet Framework 3.5 Features is required
4. It uses TCP Endpoint to communicate between different partners

Operating Mode
1. Databse Mirroring has 2 mode:
a. High safely without automatic failover
b. High safely with automatic failover
c. High performation

High Safely without automatic failover
1. It is a default mode
2. Primary server will not commit a transaction, until primary server receive a acknowledge from mirror server
3. It has latency of the transaction since of the wait
4. The data is protected by synchronized between 2 places
5. Manula failover is allowed if all partners are connected and the database is synchronized.

High Safely without automatic failover
1. Primary server will not commit a transaction, until primary server receive a acknowledge from mirror server
2. It has latency of the transaction since of the wait
3. The data is protected by synchronized between 2 places
4. It need a witness server, it does not serve the database
5. It support automatic failover if the database is synchronized and the witness & mirror server remain connected
6. If automatic failover is not available, forcing service might be possible.
7. Forcing service will cause data loss

High performation
1. Only enterprise edition of SQL Server support this mode
2. Primary server commit transactions without waiting for acknowledge from the mirror server
3. This asynchronous operation can minimum transcation latency

Prepare of Database Mirroring
A. Auto-start “SQL Server Agent” at Production & Secondary Server
1. In SQL Server, “Start” > “Micrsoft SQL Sever 2016” > “SQL Server Configuration Manager”
2. Select “SQL Server Services” > Right-Click “SQL Server Agent” > “Properties”
3. Select “Service” tab > change “Start Mode” from “Manual” to “Automatic”

B. Install DotNet Framework 3.5 Features
1. Insert Windows Installation Disc
2. Using Dism.exe to install, in this example CD-ROM is D:
Dism.exe /online /enable-feature /featurename:NetFX3 /All /Source:D:\sources\sxs /LimitAccess

C. Setup Shared Folder
At Shared Folder at Production Server
1. “NT Service\SQLSERVERAGENT” & “NT Service\MSSQLSERVER” have full controll
2. Computer account of Secondary Server have Read ONLY

D. Create Database for lab

CREATE DATABASE DB_MIRROR;
GO
USE DB_MIRROR;
GO
-- Create Table_A
CREATE TABLE dbo.DB_MIRROR_Table_A
(
ID_A int not null IDENTITY(1,1) PRIMARY KEY,
Value_A nvarchar(80) NULL
)
-- Create Table_B
CREATE TABLE dbo.DB_MIRROR_Table_B
(
ID_B int not null IDENTITY(1,1) PRIMARY KEY,
Value_B nvarchar(80) NULL
)
ALTER DATABASE DB_MIRROR SET RECOVERY FULL WITH NO_WAIT;
-- Insert data to tables
INSERT INTO dbo.DB_MIRROR_Table_A(Value_A) VALUES ('ability'),('able'),('about'),('above'),('accept')
INSERT INTO dbo.DB_MIRROR_Table_B(Value_B) VALUES ('baby'),('back'),('bad'),('bag'),('ball')

Setup Database Mirroring
1. Make a Full and Log Backup on Primary Server

BACKUP DATABASE DB_MIRROR TO DISK='\\DB01\DB_MIRROS\Full_BK.BAK'
BACKUP LOG DB_MIRROR TO DISK='\\DB01\DB_MIRROS\LOF.BFK'

2. Restore Backup on Secondary Server

RESTORE DATABASE DB_MIRROR FROM DISK='\\DB01\DB_MIRROS\Full_BK.BAK' WITH NORECOVERY
RESTORE LOG DB_MIRROR FROM DISK='\\DB01\DB_MIRROS\LOF.BFK' WITH NORECOVERY

3. Create Endpoint on Primary Server

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

4. Create Endpoint on Secondary Server

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

5. Verify the Endpoint setting
a. Uses “netstat -a” 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”

7. Create inbound firewall for TCP Port 9011 at Primary Server

8. Create inbound firewall for TCP Port 9022 at Secondary Server

9. Enable mirroring session at Secondary Server

ALTER DATABASE DB_MIRROS SET PARTNER = 'TCP://db01.joeschoice.com:9011'

10. Enable mirroring session at Primary Server

ALTER DATABASE DB_MIRROS SET PARTNER = 'TCP://db02.joeschoice.com:9022'