Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.