mssql2005 Database Mirroring Setup Tutorial _mssql2005

an overview

Database mirroring is a new technology used by SQL SERVER 2005 to improve database availability. Database mirroring transfers transaction log records directly from one server to another, and can quickly move to an alternate server in the case of a failure. You can write client programs to automatically redirect connection information so that once a failover occurs, you can automatically connect to the standby server and database.

Advantages: Database mirroring allows for fast failover without losing committed data, without specialized hardware , and is easy to configure and manage.

Two Environment Preparation

Operating system: Window 2003 Enterprise SP2 (at least two, if you want to enable automatic failover, must three)

SQL version: MSSQL SERVER SP3

To check the SQL Server version:

EXEC xp_msver

Select SERVERPROPERTY (' ProductLevel ')

Database preparation: Prepare a database: CCERP_JZT, backup This database restore to another machine, the other must be with no recovery

Here I assume the server a,b,c

A is the principal server, B is the mirror server, and C is the witness server

A Server

Use master

Go

Restore filelistonly from disk=n ' F:\databak\ccerp_jzt_backup_200911250100.bak '

Restore database Ccerp_jzt from disk=n ' F:\databak\ccerp_jzt_backup_200911250100.bak ' with Replace,recovery,

Move ' ccerp_ydswzip_data ' to ' d:\data\ccerp_jzt.mdf ',

Move ' ccerp_ydswzip_log ' to ' d:\data\ccerp_jzt_log.ldf '

exec sp_helpdb ' ccerp_jzt '

Backup database ccerp_jzt to disk =n ' F:\databak\sk.bak ' with init

--Changing the recovery model

ALTER DATABASE CCERP_JZT set recovery full

B Server:

CREATE DATABASE Ccerp_jzt

On

(NAME = Sales_dat,

FILENAME = ' D:\data\ccerp_jzt.mdf ',

SIZE = 10

)

LOG on

(NAME = ' Ccerp_jzt_log ',

FILENAME = ' D:\data\ccerp_jzt_log.ldf ',

SIZE = 5MB

)

Go

Restore filelistonly from disk=n ' F:\xxzx\data\sk.bak '

Use master

Go

Restore database Ccerp_jzt from disk=n ' F:\xxzx\data\sk.bak ' with Replace,norecovery,

exec sp_helpdb ' ccerp_jzt '

C server can only install SQL Server 2005, no more preparation

When you are ready to complete, the following illustration shows:

33 Types of models

To establish a trust relationship for database mirroring, there are three ways to establish a trust relationship in a win environment: domain accounts, certificate trusts, Windows anonymous login, and configuration instructions for the first two modes.

3.1 domain account mode:

3.1.1 Change the login mode of the MSSQLServer service for domain account login mode:

Enter the Windows Services management Console, change the service login account, and make the domain account have permission to change the status of the MSSQL Server service. Three machines have the same set.

Assigning a domain account to the sysadmin role

3.1.2 to establish an endpoint:

To create endpoints through a graphical interface:

Start SQLWB, follow the diagram to continue next

Log on with a domain account

If successful:

3.2 Certificate Mode

3.2.1 Establish certificates & Endpoints

Servers participating in a database mirroring session must trust each other. For local communications, such as communication within a domain, trust means that the SQL Server instance login account must have permission to connect to other mirror servers, including endpoints. First use the Create LOGIN command on each server, and then use the grant CONNECT on endpoint command. Communication between untrusted domains must use a certificate. If you create a self-signed certificate using the Creating certificate statement, the requirements for all data mirroring certificates are basically met. Confirm that the certificate is marked as active for Begin_dialog in the CREATE CERTIFICATE statement.

Establish a certificate:

The mirror server executes:

Use master;

CREATE MASTER KEY Encryption by PASSWORD = ' TEST ';

CREATE certificate Host_a_cert with subject= ' host_a certificate ', start_date= ' 2010-03-10 ';

Execution on the principal server:

Use master;

CREATE MASTER KEY Encryption by PASSWORD = ' TEST ';

CREATE certificate Host_b_cert with subject= ' Host_b certificate ', start_date= ' 2010-03-10 ';

Perform on the witness server:

Use master;

CREATE MASTER KEY Encryption by PASSWORD = ' TEST ';

CREATE certificate Host_c_cert with subject= ' Host_c certificate ', start_date= ' 2010-03-10 ';

The second establishes the endpoint:

The mirror server executes:

--create Mirror endpoint on primary A

CREATE ENDPOINT endpoint_mirroring

State = started as

TCP (listener_port=5022, listener_ip = All)

For database_mirroring

(Authentication = Certificate Host_a_cert, encryption = REQUIRED algorithm AES, role = ALL);

Execution on the principal server:

--create endpoint on mirror server B

CREATE ENDPOINT endpoint_mirroring

State = Started

As

TCP (listener_port=5022, listener_ip = All)

For

Database_mirroring

(Authentication = Certificate Host_b_cert, encryption = REQUIRED algorithm AES, role = ALL);

Perform on the witness server:

--create endpoint on witness server C

CREATE ENDPOINT endpoint_mirroring

State = Started

As

TCP (listener_port=5022, listener_ip = All)

For

Database_mirroring

(Authentication = Certificate Host_c_cert,

encryption = REQUIRED algorithm AES, role = witness);

SELECT * from sys.database_mirroring_endpoints;

Certificate Preparation:

The mirror server executes:

--backup Certificate

BACKUP certificate Host_a_cert to FILE = ' e:\HOST_A_cert.cer '

Execution on the principal server

--backup Certificate

BACKUP certificate Host_b_cert to FILE = ' e:\HOST_B_cert.cer '

Perform on the witness server:

BACKUP certificate Host_c_cert to FILE = ' e:\HOST_C_cert.cer '

Swap the certificates that are backed up, that is, the host_a_cert.cer copy to the B-machine e:\ Copy the Host_b_cert.cer to the e:\ of the A machine, which means that each server has three certificates

Third: The establishment of landing users:

The mirror server executes:

--create User

CREATE LOGIN host_b_login with PASSWORD = ' test ';

CREATE USER host_b_user for LOGIN Host_b_login;

CREATE Certificate Host_b_cert AUTHORIZATION host_b_user from FILE = ' e:\HOST_B_cert.cer ';

GRANT CONNECT on endpoint::endpoint_mirroring to [Host_b_login];

CREATE LOGIN host_c_login with PASSWORD = ' test ';

CREATE USER host_c_user for LOGIN Host_c_login;

CREATE Certificate Host_c_cert AUTHORIZATION host_c_user from FILE = ' e:\HOST_c_cert.cer ';

GRANT CONNECT on endpoint::endpoint_mirroring to [Host_c_login];

GRANT CONNECT on endpoint::endpoint_mirroring to [Host_a_login];

--query User SID

Select Loginname,name,sid from syslogins

execution on the principal server:

--create User

CREATE LOGIN host_a_login with PASSWORD = ' test ';

CREATE USER host_a_user for LOGIN Host_a_login;

CREATE Certificate Host_a_cert AUTHORIZATION host_a_user from FILE = ' e:\HOST_A_cert.cer ';

GRANT CONNECT on endpoint::endpoint_mirroring to [Host_a_login];

--Add Witness user

CREATE LOGIN host_c_login with PASSWORD = ' test ';

CREATE USER host_c_user for LOGIN Host_c_login;

CREATE Certificate Host_c_cert AUTHORIZATION host_c_user from FILE = ' e:\HOST_c_cert.cer ';

GRANT CONNECT on endpoint::endpoint_mirroring to [Host_c_login];

GRANT CONNECT on endpoint::endpoint_mirroring to [Host_b_login];

--query SID

Select Loginname,name,sid from syslogins

Perform on the witness server:

--create User

CREATE LOGIN host_a_login with PASSWORD = ' test ';

CREATE USER host_a_user for LOGIN Host_a_login;

CREATE Certificate Host_a_cert AUTHORIZATION host_a_user from FILE = ' e:\HOST_A_cert.cer ';

GRANT CONNECT on endpoint::endpoint_mirroring to [Host_a_login];

--add user Host_b_login to have pemission to access witness

CREATE LOGIN host_b_login with PASSWORD = ' test ';

CREATE USER host_b_user for LOGIN Host_b_login;

CREATE Certificate Host_b_cert AUTHORIZATION host_b_user from FILE = ' e:\HOST_B_cert.cer ';

GRANT CONNECT on endpoint::endpoint_mirroring to [Host_b_login];

Grant connect on endpoint::endpoint_mirroring to Host_c_login

Use master;

EXEC sp_addlogin

@loginame = ' Host_b_login ',

@passwd = ' Test ',

@sid = 0x1a914ca3d1d00c4793ebc96e4c4f4352;

ALTER DATABASE ccerp_jzt SET PARTNER = ' tcp://192.168.137.32:5022 ';

Four To establish a mirror:

Execute on the mirror server first:

ALTER DATABASE ccerp_jzt SET PARTNER = ' tcp://192.168.137.44:5022 ';

The principal server then executes:

ALTER DATABASE ccerp_jzt SET PARTNER = ' tcp://192.168.137.32:5022 ';

ALTER DATABASE ccerp_jzt SET witness = ' tcp://192.168.137.49:5022 ';

This citation certificate is established

Iv. test Operations

1. Main Standby Exchange

--Host execution:

1USE master;
2ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;

2, the main server down, standby emergency start-up and start service

--Standby Machine execution:

1USE master;
2ALTER DATABASE <DatabaseName> SET PARTNER force_service_allow_data_loss;

3, the original primary server recovery, you can continue to work, need to reset the mirror

1--the standby machine executes:
2USE master;
3ALTER DATABASE <DatabaseName> SET PARTNER RESUME; --Recovering mirrors
4ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER; --Switching the main standby

4, the original primary server recovery, you can continue to work

-By default, the transaction security level is set to full, that is, synchronous mode, and the SQL Server 2005 Standard Edition only supports synchronization mode.

--Turning off transaction security switches the session to asynchronous run mode, which gives you the best performance.

1USE master;
2ALTER DATABASE <DatabaseName> SET PARTNER SAFETY full; --transaction security, synchronization mode
3ALTER DATABASE <DatabaseName> SET PARTNER SAFETY off; -transaction unsafe, asynchronous mode

Error Description:
Message 1498, Level 16, State 3, line 1th

By default, database mirroring is disabled. The currently provided database mirroring is for evaluation purposes only and should not be used in a production environment. To enable database mirroring for evaluation purposes, use trace flag 1400 during the startup process. For more information about trace flags and startup options, see SQL Server Books Online.

Solution: Did not hit SP1 above the patch. It is highly recommended to play SP3

Message 1475, Level 16, State 2, line 1th

Database mirroring cannot be enabled because the "CCERP_JZT" database may have bulk-logged changes that have not been backed up. The last log backup of the principal database must be restored on the mirror.

Main: BACKUP LOG ccerp_jzt to disk = ' e:\log.trn ' with no_truncate

Mirroring: Restore log ccerp_jzt from disk= ' e:\log.trn ' with NORECOVERY