SQL Server database mirroring construction (no witness, no domain control)
1. Environment configuration
Server 1:sqlmirror01
IP:192.168.10.176
Server 2:sqlmirror02
IP:192.168.10.177
Operating system: Windows 2012 R2
Install .net Framework 3.5 Universe 4.0
Turn off the server firewall or set the whitelist
Database version: SQL Server 2014
two。 Create a master key (optional)
Use master
Go
Create master key encryption by password='zaq12WSX'
Go
3. Create a certificate
Sqlmirror01 execution
Use master
Go
Create certificate sqlmirror01_cert with subject='sqlmirror01 certificate',expiry_date='2099-1-1'
Go
Sqlmirror02 execution
Use master
Go
Create certificate sqlmirror02_cert with subject='sqlmirror02 certificate',expiry_date='2099-1-1'
Go
4. Create a mirror endpoint
Sqlmirror01 execution
Use master
Go
Create endpoint Endpoint_Mirroring
State=started
As tcp (listener_port = 5022 listeners IP = all)
For database_mirroring (authentication = certificate sqlmirror01_cert, encryption = required algorithm aes, role = all)
Go
Sqlmirror02 execution
Use master
Go
Create endpoint Endpoint_Mirroring
State=started
As tcp (listener_port = 5022 listeners IP = all)
For database_mirroring (authentication = certificate sqlmirror02_cert, encryption = required algorithm aes, role = all)
Go
5. Backup certificate
Sqlmirror01 execution
Use master
Go
Backup certificate sqlmirror01_cert to file ='C:\ Certificate_files\ sqlmirror01_cert.cer'
Go
Sqlmirror02 execution
Use master
Go
Backup certificate sqlmirror02_cert to file ='C:\ Certificate_files\ sqlmirror02_cert.cer'
Go
6. Copy a certificate
Copy sqlmirror01_cert.cer to C:\ Certificate_files\ of sqlmirror02
Copy sqlmirror02_cert.cer to C:\ Certificate_files\ of sqlmirror01
7. Create a login name
Sqlmirror01 execution
Use master
Go
Create login sqlmirror02_login with password='abc@123456'
Go
Sqlmirror02 execution
Use master
Go
Create login sqlmirror01_login with password='abc@123456'
Go
8. Create a user with this login name
Sqlmirror01 execution
Use master
Go
Create user sqlmirror02_user for login sqlmirror02_login
Go
Sqlmirror02 execution
Use master
Go
Create user sqlmirror01_user for login sqlmirror01_login
Go
9. Certificate is associated with the user
Sqlmirror01 execution
Use master
Go
Create certificate sqlmirror02_cert
Authorization sqlmirror02_user
From file='c:\ Certificate_files\ sqlmirror02_cert.cer'
Go
Sqlmirror02 execution
Use master
Go
Create certificate sqlmirror01_cert
Authorization sqlmirror01_user
From file='c:\ Certificate_files\ sqlmirror01_cert.cer'
Go
10. Grant CONNECT permission to the login of the remote mirror endpoint
Sqlmirror01 execution
Use master
Go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlmirror02_login]
Go
Sqlmirror02 execution
Use master
Go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlmirror01_login]
Go
11. Configure Mirror partner
Sqlmirror01 execution
Create a database
CREATE DATABASE [Mirror_test_1]
ON PRIMARY
(NAME = UNLIMITED, FILENAME = NissC:\ dbdata\ Mirror_test_1.mdf', SIZE = 3072KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON
(NAME = 2048GB, FILENAME = Numeric C:\ dbdata\ Mirror_test_1_log.ldf', SIZE = 1024KB, MAXSIZE = 2048GB, FILEGROWTH = 10%)
GO
Backup database
Backup database Mirror_test_1 to disk='C:\ backup\ Mirror_test_1.bak' with compression,checksum
Copy backup to sqlmirror02
Sqlmirror02 execution
Restore database Mirror_test_1 from disk='C:\ backup\ Mirror_test_1.bak' with norecovery
Connect the mirror image
Sqlmirror02 execution
Use master
Go
ALTER DATABASE Mirror_test_1 SET PARTNER = 'TCP://sqlmirror01:5022'
Go
Sqlmirror01 execution
Use master
Go
ALTER DATABASE Mirror_test_1 SET PARTNER = 'TCP://sqlmirror02:5022'
Go
Change to high performance mode
Mirror principal sqlmirror01 execution
Use master
Go
ALTER DATABASE Mirror_test_1 SET PARTNER SAFETY OFF
Go
