SQL Server2008 R2 Database Mirroring Implementation Manual (dual-machine) SQL SERVER2014 also works

This article mainly introduces the SQL Server2008 R2 Database Mirroring Implementation Manual (dual-machine) SQL Server2014 also applicable, the need for friends can refer to the following

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. Create certificate (master and standby can be executed in parallel)

?

1234567891011121314

--主机执行：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‘; --备机执行：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)

?

123456789101112131415161718192021222324252627282930313233

--主机执行： CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED ASTCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FORDATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );  --备机执行：CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FORDATABASE_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)

?

12345678

--主机执行：BACKUP CERTIFICATE HOST_A_cert TO FILE = ‘C:\SQLBackup\HOST_A_cert.cer‘; --备机执行： BACKUP CERTIFICATE HOST_B_cert TOFILE = ‘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)

?

1234567891011

--主机执行：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];  --备机执行：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

?

123456789

--executes the following statement in the standby machine:  alter database shishan set partner = ' tcp://192.168.1.155:5022 '    --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

?

123456789101112131415161718192021

--切换主备 use master;alter database testdb set partner failover; -- 备机强制切换use master;alter database testdb set partner force_service_allow_data_loss; --恢复镜像use master;alter database testdb set partner resume;  --取消见证服务器ALTER DATABASE testdb SET WITNESS OFF ; --取消镜像ALTER DATABASE testdb SET PARTNER OFF; --设置镜像数据库还原为正常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.

SQL Server2008 R2 Database Mirroring Implementation Manual (dual-machine) SQL SERVER2014 also works