SQL Server dual-machine hot standby documentation (no domain)

1. Preparation environment

os:win7 Db:sql Server R2

2. Basic formulation

1. Turn on SQL Server services such as-1

Figure-1

2. Open the TCP/IP protocol for SQL Server, Configmanagement, Configtool, sqlServerR2, Start-to-program, such as-2

Figure-2

Note: After you turn on this service, you will need to restart the SQL Server service to take effect.

3. Establish the same login username password on the machine (master, slave, and optional witness) that needs to be backed up.

4. Open the 5022 port used for the image. In the Win window, enter Control Panel \ System and Security \ Windows Firewall, click [Advanced Settings] in the pop-up window and add Port 5022 in inbound and outbound rules.

5. Turn on the Database Remotedacenabled property. Right click on [DB instance]-> select aspect, in the popup window select [Peripheral application] Set the Remotedacenabled property to True. -3

Figure-3

6. Create the same login name and password on three machines

Click the security below the DB instance, right click on new login, build the same login and password on three machines, mainly for the application to use in the connection data.

3. Backup RESTORE Database

Copy the full database backup from the primary server to the same directory in the same file as the log. Right [database name], click [Tasks], select Backup, do a full backup first, then log backup. Restore the backup contents of the master library in the repository. Right [database name], click [Tasks], select Restore. -4

Figure-4

4. Start Hot standby

Interoperability can be achieved by using a domain or certificate, consider the implementation of the simple, the following method of selecting a certificate implementation. Note: Implementing the "Master and Standby DB instance interoperability" operation only needs to be done once, for example, in order to make the mirroring relationship for the 3 databases in two instances of SQL Server 2008, it is only necessary to do one of the following operations: each of the primary and standby instances (not the database) to do the interoperability.

1. Create certificate (master and standby can be executed in parallel)

--Host execution:

Use master; CREATE MASTER KEY Encryption by PASSWORD = ' MasterWord '; CREATE CERTIFICATE host_pri_cert with SUBJECT = ' host_primary CERTIFICATE ', start_date = ' 01/11/2011 ', expiry_date = ' 01/ 11/2013 ';

--Standby Machine execution:

Use master; CREATE MASTER KEY Encryption by PASSWORD = ' MasterWord '; CREATE CERTIFICATE host_min_cert with SUBJECT = ' Host_minor CERTIFICATE ', start_date = ' 01/11/2011 ', expiry_date = ' 01/11/ 2013 ';

--Witness Server execution

Use master; CREATE MASTER KEY Encryption by PASSWORD = ' MasterWord '; CREATE CERTIFICATE host_pro_cert with SUBJECT = ' host_witness CERTIFICATE ', start_date = ' 01/11/2011 ', expiry_date = ' 01/ 11/2013 ';

2. Create the endpoint of the connection (master and standby can be executed in parallel)

--Host execution:

CREATE ENDPOINT endpoint_mirroring
State = STARTED
As
TCP (listener_port=5022, listener_ip = All)
For
Database_mirroring
(authentication = CERTIFICATE Host_pri_cert, encryption = REQUIRED algorithm AES, ROLE = All);

--Standby Machine execution:

CREATE ENDPOINT endpoint_mirroring
State = STARTED
As
TCP (listener_port=5022, listener_ip = All)
For
Database_mirroring
(authentication = CERTIFICATE Host_min_cert, encryption = REQUIRED algorithm AES, ROLE = All)

--Execution on the witness server

CREATE ENDPOINT endpoint_mirroring
State = STARTED
As
TCP (listener_port=5022, listener_ip = All)
For
Database_mirroring
(authentication = CERTIFICATE Host_pro_cert, encryption = REQUIRED algorithm AES, ROLE = All)

3. Back up the certificate for interconnection (master and standby can be executed in parallel)

--Host execution:

BACKUP CERTIFICATE host_pri_cert to FILE = ' D:\HOST_pri_cert.cer ';

--Standby Machine execution:

BACKUP CERTIFICATE host_min_cert to FILE = ' D:\HOST_min_cert.cer ';

--Execution on the witness server

BACKUP CERTIFICATE host_pro_cert to FILE = ' D:\HOST_pro_cert.cer ';

4. Interchange Certificate

The Backup to D:\ Certificate interchange, that is, the Host_pri_cert.cer,host_pro_cer.cert copy to the d:\ of the standby machine, host_min_cert.cer, Host_pro_cer.cert Copy to the host D:\, Copy the Host_pri_cert.cer,host_min_cert.cer to the d:\ of the witness machine.

5, add login name, user (master and standby can be executed in parallel)

--Host execution:

CREATE LOGIN host_min_login with PASSWORD = ' MasterWord ';
CREATE USER host_min_user for LOGIN Host_min_login;
CREATE CERTIFICATE host_min_cert AUTHORIZATION host_min_user from FILE = ' D:\HOST_min_cert.cer ';
GRANT CONNECT on endpoint::endpoint_mirroring to [Host_min_login];

CREATE LOGIN host_pro_login with PASSWORD = ' MasterWord ';
CREATE USER host_pro_user for LOGIN Host_pro_login;
CREATE CERTIFICATE host_pro_cert AUTHORIZATION host_pro_user from FILE = ' D:\HOST_pro_cert.cer ';
GRANT CONNECT on endpoint::endpoint_mirroring to [Host_pro_login];

--Standby Machine execution:

CREATE LOGIN host_pri_login with PASSWORD = ' MasterWord ';
CREATE USER host_pri_user for LOGIN Host_pri_login;
CREATE CERTIFICATE host_pri_cert AUTHORIZATION host_pri_user from FILE = ' D:\HOST_pri_cert.cer ';
GRANT CONNECT on endpoint::endpoint_mirroring to [Host_pri_login];

CREATE LOGIN host_pro_login with PASSWORD = ' MasterWord ';
CREATE USER host_pro_user for LOGIN Host_pro_login;
CREATE CERTIFICATE host_pro_cert AUTHORIZATION host_pro_user from FILE = ' D:\HOST_pro_cert.cer ';
GRANT CONNECT on endpoint::endpoint_mirroring to [Host_pro_login];

--Execution on the witness server

CREATE LOGIN host_min_login with PASSWORD = ' MasterWord ';
CREATE USER host_min_user for LOGIN Host_min_login;
CREATE CERTIFICATE host_min_cert AUTHORIZATION host_min_user from FILE = ' D:\HOST_min_cert.cer ';
GRANT CONNECT on endpoint::endpoint_mirroring to [Host_min_login];

CREATE LOGIN host_pri_login with PASSWORD = ' MasterWord ';
CREATE USER host_pri_user for LOGIN Host_pri_login;
CREATE CERTIFICATE host_pri_cert AUTHORIZATION host_pri_user from FILE = ' D:\HOST_pri_cert.cer ';
GRANT CONNECT on endpoint::endpoint_mirroring to [Host_pri_login];

6. Configuration image

Right click on the database you want to mirror, click [Tasks], select [Mirror], click "Configure security", such as:

A wizard will appear that configures database security

Then click Next, because it is mirrored on three machines, so the mirror port is the default 5022, if you do it on a machine, you need to use a different port number

The service account name is empty due to the way the certificate was used, not the domain

Continue to the next

Click Finish, then click Start Image to complete the database mirroring settings

5. Testing

6. Reference

Http://www.cnblogs.com/killkill/archive/2008/05/23/1205792.html

Http://msdn.microsoft.com/zh-cn/library/ms187798.aspx

http://liulike.blog.51cto.com/1355103/339183/

SQL Server dual-machine hot standby documentation (no domain)