1. Environment Configuration
Server 1:sqlmirror01
ip:192.168.10.176
Server 2:sqlmirror02
ip:192.168.10.177
Operating system: Windows R2
Install the. Net Framework 3.5/4.0
Turn off the server firewall or set up a whitelist
Database version: SQL Server 2014
2. Create a master key (not required)
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 mirrored endpoint
Sqlmirror01 execution
Use master
Go
Create Endpoint Endpoint_mirroring
state=started
As TCP (Listener_port = 5022,LISTENER_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,LISTENER_IP = All)
For database_mirroring (authentication = Certificate Sqlmirror02_cert, encryption = Required algorithm AES, role = ALL)
Go
5. Backing Up certificates
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 the certificate
Copy the Sqlmirror01_cert.cer to the Sqlmirror02 C:\Certificate_files\
Copy the Sqlmirror02_cert.cer to the Sqlmirror01 C:\Certificate_files\
7. Create a login name
Sqlmirror01 execution
Use master
Go
Create login Sqlmirror02_login with password= ' [email protected] '
Go
Sqlmirror02 execution
Use master
Go
Create login Sqlmirror01_login with password= ' [email protected] '
Go
8. Create a user that uses the 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 associated with 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 for the remote mirroring 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 the mirroring partner
Sqlmirror01 execution
Create a database
CREATE DATABASE [mirror_test_1]
On PRIMARY
(NAME = n ' mirror_test_1 ', FILENAME = N ' C:\dbdata\Mirror_test_1.mdf ', SIZE = 3072KB, MAXSIZE = UNLIMITED, filegrowth = 1 024KB)
LOG on
(NAME = n ' mirror_test_1_log ', FILENAME = N ' C:\dbdata\Mirror_test_1_log.ldf ', SIZE = 1024KB, MAXSIZE = 2048GB, Filegrow TH = 10%)
GO
Backing Up the database
Backup database mirror_test_1 to disk= ' C:\backup\Mirror_test_1.bak ' with compression,checksum
Copy the backup to Sqlmirror02
Sqlmirror02 execution
Restore database mirror_test_1 from disk= ' C:\backup\Mirror_test_1.bak ' with NORECOVERY
Connection Mirror
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 body Sqlmirror01 Execution
Use master
Go
ALTER DATABASE mirror_test_1 SET PARTNER SAFETY OFF
Go
SQL Server database mirroring build (no witness no domain control)
