SQL Server high-availability mirroring

First, what is database mirroring

High-availability solutions for basic software

Fast Failover Recovery (3 second transfer), low hardware cost

Database-level-based implementations

Second, server roles in database mirroring

Principal server

Hosting the principal database

Accept user connections and transaction requests

mirror server

Hosting a mirrored database

Hot Backup as principal database (changes in principal database are uploaded to the mirror database in time)

Only accept user connections after failover, transaction requests.

Witness server (Monitoring)

Monitor server status and connectivity for automatic automatic failover

Third, database mirroring session

Session initialization

Mirror request transaction log records, synchronization with principal server implementation

Session process

Principal server transfers log records to the mirror server

Each role monitors session state with each other

Session End

Failover occurs

Administrator terminates database Mirroring

Four database mirroring three modes

Operating mode	Transaction security	Transmission mechanism	Whether arbitration is required	Witness server	Fail over type
Highly Available	Full	Synchronous	Y	Y	Automatic or Manual
High-level protection	Full	Synchronous	Y	N	Manual only
Performance	OFF	Asynchronous	N	N/A	Force only

High availability: Requires high service availability, requires automatic failover, and ensures complete data.

High-level protection mode: Data integrity requirements, not requiring automatic failover, and low availability of services.

High-performance Protection mode: The principal server and the mirror server are far away, the communication link has a significant delay, the performance of the matter is higher than the integrity of the data.

Five Configuration Demo

Environment: Database version SQL Server 2012 system version Windows servers R2 domain management

Mirroring test Scenario

Principal server--fetchingdata49\mssqlservertwo TCP 5022 port

mirror server--172.168.18.132\mssqlserver2012 TCP 5022 port

Witness Server--fetchingdata49

Mirroring operation mode: High availability. Transaction security: Full, transport mechanism: synchronous, whether Quorum: Y, witness server: Y, failover: Automatic.

--Step (1) "Principal server" is set to full recovery model, do a full backup and log backupALTER DATABASEMirroring_testSETRECOVERY Full Backup DatabaseMirroring_test to Disk='C:\data\Mirroring_Test.bak'  withInitBackup LogMirroring_test to Disk='C:\data\Mirroring_Test.bak'  

--Step (2) "mirror server" is restored to the mirror library (copy the backup file to the mirror server directory restore)--run the following statement to create a mirrored database that is in the restoring state and is overwritten.  Restore DatabaseMirroring_test from Disk='D:\data\Mirroring_Test.bak'   with file=1, move N'mirroring_test'  toN'D:\data\Mirroring_Test.mdf', move N'Mirroring_test_log'  toN'D:\data\Mirroring_Test_log.ldf', Norecovery,Replace Restore LogMirroring_test from Disk='D:\data\Mirroring_Test.bak'  with file=2, NORECOVERY

Create each port on each server

-- Step (3) Create endpoint  "principal server" create endpoint for partner communication, activate endpoint create  endpoint mirroring_test as TCP ( Listener_port=5022) for database_mirroring (role=Partner, Encryption=supported)

 --  "mirror server" create endpoints for Partner Communications, activating endpoints  create   endpoint mirroring_test  as  TCP (listener_port=  5022  )  for  database_ Mirroring (Role=  partner,encryption=  Supported) 

-- "Witness" creates endpoint for witness communication, activates endpoint Create Endpoint Mirroring_test  as TCP (Listener_port=5023) for database_mirroring (role=Witness, Encryption=supported)

Step 4 Create the Connect (connect) permission. Set the same account name and password for three DB instances

Step (5) The principal Server Configuration Mirroring Wizard

Test

--Test manual failover (performed on the principal server)

ALTER DATABASE mirroring_test SET PARTNER failover

--Test automatic failover (such as disconnecting the network cable from the principal database server, or stopping the instance)

--Test data synchronization changes data on the principal server, creating snapshot queries on the mirrored database

CREATE DATABASE Snap_mirroring_test

On (name=mirroring_test,filename= ' D:\Snap_Mirroring_Test.snap ')

As SNAPSHOT of Mirroring_test

--Delete Snapshot

DROP DATABASE Snap_mirroring_test

SQL Server high-availability mirroring