Configuring SQL Server 2008 Mirroring

Configuration SQL Server Mirror

The first step is to check the underlying environment

because in a non-domain environment, so the need to do a lot more checks, following the demo environment, test the following conditions:

Windows account.

whether the network can be connected, and the port is available.

the disk configuration of the principal server and the mirror server is correct.

Whether the SQL Server version or patch meets the mirroring requirements.

recovery model, compatibility level for SQL Server databases.

whether there are regular backup jobs on SQL Server, especially log backups.

the principal server and the mirror server Whether SQL Server can interoperate.

whether there are shared folders on the principal server and the mirror server.

Check that the disk configuration is correct, the two database storage paths are best consistent

SQL database path Check :

Use master

Go

SELECT Physical_name

From Sys.master_files

WHERE database_id = db_id (' rjbdb ')

Check SQL Database recovery model, compatibility level: Recovery mode recommended full, same compatibility level

Use master

Go

SELECT name [ database name ],

RECOVERY_MODEL_DESC [ Recovery mode ],

case [Compatibility_level] = All Then ' 2005 '

When [compatibility_level] = "2008"

When [Compatibility_level] > ' 2008+ '

ELSE ' or lower version '

END [ compatibility level ]

From sys.databases

WHERE name = ' Rjbdb '

Modify The recovery model for SQL database is full

Use [master]

GO

ALTER DATABASE [adventureworks2008r2] setrecovery full with no_wait

GO

Formal implementation

To create a certificate:

to use a certificate to build the image, follow these steps:

Create the Database master key (if the master key does not exist).

in the The master database creates the certificate and encrypts it with the master key.

Create an endpoint (endpoint) with certificate authorization.

The backup certificate becomes a certificate file.

Create a login account on the server to provide additional instance access.

Create a user in the Master Library and map to the login account in the previous step.

Grant the certificate to these users.

authorized on the endpoint.

sets the mirror partner for the principal server.

sets the principal partner of the mirror server.

Configure the witness server.

host A Generate certificate Action

1 , create a local database master key

Use master

GO

CREATE MASTER KEY Encryption by PASSWORD = ' pa$ $w 0rd ';

2 , create a certificate, and encrypt it with the master key

Use master

GO

CREATE CERTIFICATE Host_a_cert

with Subject = ' host_a Certificate ',

expiry_date = ' 2026-10-1 ';

3 , create a certificate, and encrypt it with the master key

IF not EXISTS (SELECT 1

From sys.database_mirroring_endpoints)

BEGIN

CREATE ENDPOINT [databasemirroring] state = STARTED as TCP (Listener_port = 5022,

LISTENER_IP = All) for database_mirroring (authentication =

CERTIFICATE Host_a_cert, encryption = REQUIRED algorithm AES, role=

All);

END

4 , backup certificates

BACKUP CERTIFICATE Host_a_cert

to FILE = ' D:\2222\Host_A_Cert.cer ';

5 , create login account

CREATE LOGIN host_b_login with PASSWORD = ' pa$ $w 0rd ';

6 , create users, and map to Step 5 In the login account created in

CREATE USER host_b_user for Loginhost_b_login;

on the host B Perform the same certificate action: Set the certificate name to Host_b_certand the login account name to Host_a_login

1 , create a local database master key

Use master

GO

CREATE MASTER KEY Encryption by PASSWORD = ' pa$ $w 0rd ';

2 , create a certificate, and encrypt it with the master key

Use master

GO

CREATE CERTIFICATE Host_b_cert

with Subject = ' Host_b Certificate ',

expiry_date = ' 2026-10-1 ';

3 , create a certificate, and encrypt it with the master key

IF not EXISTS (SELECT 1

From sys.database_mirroring_endpoints)

BEGIN

CREATE ENDPOINT [databasemirroring] state = STARTED as TCP (Listener_port = 5022,

LISTENER_IP = All) for database_mirroring (authentication =

CERTIFICATE Host_b_cert, encryption = REQUIRED algorithm AES, role=

All);

END

4 , backup certificates

BACKUP CERTIFICATE Host_b_cert

to FILE = ' D:\2222\Host_B_Cert.cer ';

5 , create login account

CREATE LOGIN host_a_login with PASSWORD = ' pa$ $w 0rd ';

6 , create users, and map to Step 5 In the login account created in

CREATE USER host_a_user for Loginhost_a_login;

Certificate Import

Generate a certificate for the host to be copied into the other host.

Host A Import Certificate: use A certificate to authorize users

CREATE CERTIFICATE Host_b_cert

AUTHORIZATION Host_b_user

From FILE = ' D:\2222\Host_B_Cert.cer ';

Authorize the access port for the login account in step fifth

GRANT CONNECT onendpoint::[databasemirroring] to [host_b_login];

Host B Import Certificate: Use certificate to authorize users

CREATE CERTIFICATE Host_a_cert

AUTHORIZATION Host_a_user

From FILE = ' D:\2222\Host_A_Cert.cer ';

Authorize the access port for the login account in step fifth

GRANT CONNECT onendpoint::[databasemirroring] to [host_a_login];

on the host Restore Database on B : Restore the backup file of the host, select NoRecovery

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/87/DA/wKioL1fjf9OS420qAAFWmakBw_I709.jpg-wh_500x0-wm_3 -wmp_4-s_2505227310.jpg "title=" 1.jpg "alt=" Wkiol1fjf9os420qaafwmakbw_i709.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/87/DE/wKiom1fjf9_y7Xg7AAGymy4M4bg719.jpg-wh_500x0-wm_3 -wmp_4-s_854725895.jpg "title=" 2.jpg "alt=" Wkiom1fjf9_y7xg7aagymy4m4bg719.jpg-wh_50 "/>

Perform the following mirroring operation after recovery is complete

in B performing mirroring operations on the server

Win-th5pj0qfi67 is the hostname, can be changed to IP according to their own needs

ALTER DATABASE RJBDB

SET PARTNER = ' tcp://win-th5pj0qfi67:5022 ';

GO

in a performing mirroring operations on the server

ALTER DATABASE RJBDB

SET PARTNER = ' tcp://win-iteoq3dj4ri:5022 ';

GO

Mirroring switching operations

Under normal circumstances, manually switch the mirroring state from the primary mirror machine

ALTER DATABASE rjbdb SET PARTNER FAILOVER

Force mirroring to switch

Host Service stopped Mirror ( standby Machine ) on the execution make a forced switch

ALTER DATABASE rjbdb SET PARTNER Force_service_allow_data_loss

After the original host is started, the mirroring status will be displayed as pending, and the pending status can be adjusted to a normal state by executing the following command on the original host machine

Alter Database rjbdb Set Partner Resume

Reference

http://blog.csdn.net/dba_huangzj/article/details/27652857

This article is from the "Computer Network Technology" blog, please be sure to keep this source http://370220760.blog.51cto.com/6469179/1855430

Configuring SQL Server 2008 Mirroring