sqlserver2008-Mirror Database Implementation manual (dual machine) sql-server2014 also applicable

SQL server2008r2- Mirror Database Implementation manual (dual-machine) SQL Server2014 also works

First, configure the main standby machine

1 , server basic information

The host name is: Host_a,ip Address: 192.168.1.155

The name of the standby machine is: Host_b,ip address: 192.168.1.156

second, the primary and standby instances of interoperability

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 5 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 , creating certificates (Master and standby can be executed in parallel)

-- Host Execution:

Use master;

CREATE MASTER KEY Encryption by PASSWORD = ' 123456 ';

CREATE CERTIFICATE host_a_cert with SUBJECT = ' host_a CERTIFICATE ',

start_date = ' 2012-08-02 ',

expiry_date = ' 2099-08-02 ';

-- Standby Machine Execution:

Use master;

CREATE MASTER KEY Encryption by PASSWORD = ' 123456 ';

CREATE CERTIFICATE host_b_cert with SUBJECT = ' Host_b CERTIFICATE ',

start_date = ' 2012-08-02 ',

expiry_date = ' 2099-08-02 ';

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_a_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_b_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_a_cert to FILE = ' C:\SQLBackup\HOST_A_cert.cer ';

-- Standby Machine Execution:

BACKUP CERTIFICATE host_b_cert to FILE = ' C:\SQLBackup\HOST_B_cert.cer ';

4 , Interchange Certificate

The certificates that are backed up to C:\SQLBackup\ are interchanged, that is, the host_a_cert.cer is copied to the C:\SQLBackup\ of the standby machine. Host_b_cert.cer copied to the host's C:\SQLBackup\.

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

The following operations can only be run from the command line and cannot be completed through the graphical interface. (Patch number as of SQL Server2005 is SP2)

-- Host Execution:

CREATE LOGIN host_b_login with PASSWORD = ' 123456 ';

CREATE USER host_b_user for LOGIN Host_b_login;

CREATE CERTIFICATE host_b_cert AUTHORIZATION host_b_user from FILE = ' C:\SQLBackup\HOST_B_cert.cer ';

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

-- Standby Machine Execution:

CREATE LOGIN host_a_login with PASSWORD = ' 123456 ';

CREATE USER host_a_user for LOGIN Host_a_login;

CREATE CERTIFICATE host_a_cert AUTHORIZATION host_a_user from FILE = ' C:\SQLBackup\HOST_A_cert.cer ';

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

third, establish the mirror relationship

The following steps are for each database, for example: There are 5 databases in an existing host the following procedures are performed 5 times.

1 , manually synchronizing logins and Passwords

One of the drawbacks of database mirroring mentioned above is the inability to maintain logins, so we need to maintain logins manually.

In general, the database will have a number of users to access the database, and the database will have a corresponding login name, but in the standby is missing the corresponding login name, such as a business system using ' myuser ' as the login to access the database, but there is no ' myuser ' in the standby login name, Therefore, once the primary and standby switch, the business system will not be able to log into the database, this situation is called "Orphaned users." Create the same user name and password on the host and standby database .

2 , prepare the standby database (host backup and mirror restore)

Back up the database on the host, make a full backup, and then make a log transaction backup.

1, master data must be set up in full mode for backup, such as:

Select "Recovery Mode" as "full mode".

2, backup database, such as:

Select "Backup Type" as "full" when backing up.

3, back up the transaction log, such as:

Select the backup type as the transaction log and the backup directory is consistent with the directory of the backup database.

Restore the backup files using the host's full files on the standby and use the "with non recover" option when restoring the data. ：

If the execution succeeds the database will look like this:

3 , create a mirror

-- execute the following statement in the standby machine:

ALTER DATABASE shishan SET PARTNER = ' tcp://192.168.1.155:5022 ';

Description: Shishan is the database name and needs to be modified according to the actual. 192.168.1.155 is the host IP address that needs to be modified according to the actual.

-- Host Execution:

ALTER DATABASE shishan SET PARTNER = ' tcp://192.168.1.156:5022 ';

Description: Shishan is the database name and needs to be modified according to the actual. 192.168.1.156 is the IP address of the standby machine , it needs to be modified according to the actual.

After successful execution:

To this, the SQL mirroring hot standby configuration is complete.

Iv. Common Commands

-- Switch Master and standby

Use master;

ALTER DATABASE TESTDB set partner failover;

-- Standby Force Switch

Use master;

ALTER DATABASE TestDB set partner Force_service_allow_data_loss;

-- Restore Image

Use master;

ALTER DATABASE TestDB SET partner resume;

-- canceling the witness server

ALTER DATABASE testdb SET WITNESS OFF;

-- Cancel Mirroring

ALTER DATABASE testdb SET PARTNER OFF;

-- set the mirrored database to normal

RESTORE DATABASE TestDB with RECOVERY;

Backup of the primary database appears: Backup a database on a hdds with a different sector size, you can perform the following statement backups:

Backup DATABASE MyDB to DISK = N ' D:\MyDB.bak ' with INIT, nounload, NAME = n ' MyDB backup ', STATS = ten, FORMAT

Summarize

To make the above image settings for SQL Server, be sure to use SQL Server Configuration Manager to turn on the TCP/IP protocol, as

If the TCP/IP protocol is not enabled, only the machine in the same network segment can be configured with the image, and the IP address used in the preceding configuration step is replaced with the corresponding instance name. The same network segment configuration and use of the mirror's time-solid, transfer rate is higher, suitable for large data volume synchronization, cross-network segment or cross-public SQL Server image is generally suitable for small data volume, when the real requirements of the data synchronization is not high, and the database in the public network synchronization is not secure.

sqlserver2008-Mirror Database Implementation manual (dual machine) sql-server2014 also applicable