SQL Server database Mirroring + replication Scenario

Goal:

The host makes Mirror and replication, and when the host problem occurs, replication and Mirror implement automatic failover (Mirror and replication are switched to the standby, and when the host

After restarting, the role of the standby machine automatically).

Environment:

Five virtual machines, configured for Windows2008 Enterprise + SQLSERVER2008R2 Enterprise

08r201:mirror Witness Machine (WITNESS) ip:192.168.56.101

08r202: Host (Rep+mirror) ip:192.168.56.102

08r203:rep Distribution Machine ip:192.168.56.103

08r204:rep Subscriber ip:192.168.56.104

08r205: Mirror Machine (Mirror) ip:192.168.56.105

Steps:

Mirroring with the witness server configured

1. Creating certificates and Endpoint

Run the following script on 08r202 (Master):

--Host execution: use master; CREATE MASTER KEY Encryption by PASSWORD = ' 123456abc ';  CREATE CERTIFICATE host_a_cert with SUBJECT = ' host_a CERTIFICATE ', start_date = ' 11/08/2010 ', expiry_date = ' 10/31/2099 '; --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); --Host execution: BACKUP CERTIFICATE host_a_certto FILE = ' d:host_a_cert.cer ';

Run the following script on 08r205 (Mirror):

--Standby Machine execution: use master; CREATE MASTER KEY Encryption by PASSWORD = ' 123456abc '; CREATE CERTIFICATE host_b_cert with SUBJECT = ' Host_b CERTIFICATE ', start_date = ' 11/08/2010 ', expiry_date = ' 10/31/2099 '; --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); --Standby Machine execution: BACKUP CERTIFICATE host_b_certto FILE = ' d:host_b_cert.cer ';

Run the following script on 08r201 (WITNESS):

--The witness machine executes the use master; CREATE MASTER KEY Encryption by PASSWORD = ' 123456abc '; CREATE CERTIFICATE host_w_cert with SUBJECT = ' Host_w CERTIFICATE ', start_date = ' 11/08/2010 ', expiry_date = ' 10/31/2099 '; --Witness machine execution CREATE ENDPOINT endpoint_mirroring state = STARTED as TCP (listener_port=5022, listener_ip = All) for Database_ Mirroring (authentication = CERTIFICATE Host_w_cert, encryption = REQUIRED algorithm AES, ROLE = All); --Witness Machine execute BACKUP CERTIFICATE host_w_certto FILE = ' d:host_w_cert.cer ';

1. Exchange Certificate

Copy the Host_b_cert.cer and Host_w_cert.cer to the "D:cert" directory of the 08r202 machine;

Copy the Host_a_cert.cer and Host_w_cert.cer to the "D:cert" directory of the 08r205 machine;

Copy the Host_a_cert.cer and Host_b_cert.cer to the "D:cert" directory of the 08r201 machine;

Run the following script on 08r202 (Master):

--master CREATE LOGIN host_b_login with PASSWORD = ' [email protected]# '; CREATEUSER host_b_user for LOGIN Host_b_login; CREATE CERTIFICATE host_b_certauthorization host_b_user from FILE = ' d:certhost_b_cert.cer '; Grantconnect on endpoint::endpoint_mirroring to [Host_b_login]; CREATE LOGIN host_w_login with PASSWORD = ' [email protected]# '; CREATE USER host_w_user Forlogin host_w_login; CREATE CERTIFICATE Host_w_cert authorizationhost_w_user from FILE = ' d:certhost_w_cert.cer '; GRANT CONNECT onendpoint::endpoint_mirroring to [Host_w_login];

Run the following script on 08r205 (Mirror):

--mirror CREATE LOGIN host_a_login with PASSWORD = ' [email protected]# '; CREATEUSER host_a_user for LOGIN Host_a_login; CREATE CERTIFICATE host_a_certauthorization host_a_user from FILE = ' d:certhost_a_cert.cer '; Grantconnect on endpoint::endpoint_mirroring to [Host_a_login]; CREATE LOGIN host_w_login with PASSWORD = ' [email protected]# '; CREATE USER host_w_user Forlogin host_w_login; CREATE CERTIFICATE Host_w_cert authorizationhost_w_user from FILE = ' d:certhost_w_cert.cer '; GRANT CONNECT onendpoint::endpoint_mirroring to [Host_w_login];

Run the following script on 08r201 (WITNESS):

--witness CREATE LOGIN host_a_login with PASSWORD = ' [email protected]# '; CREATEUSER host_a_user for LOGIN Host_a_login; CREATE CERTIFICATE host_a_certauthorization host_a_user from FILE = ' d:certhost_a_cert.cer '; Grantconnect on endpoint::endpoint_mirroring to [Host_a_login]; CREATE LOGIN host_b_login with PASSWORD = ' [email protected]# '; CREATE USER host_b_user Forlogin host_b_login; CREATE CERTIFICATE Host_b_cert authorizationhost_b_user from FILE = ' d:certhost_b_cert.cer '; GRANT CONNECT onendpoint::endpoint_mirroring to [Host_b_login];

1. Backup RESTORE Database

Back up the database on 08r202 (Master):

Backup DATABASE reptest to disk= ' D:tempreptest.bak ' backup LOG reptestto disk= ' d:tempreptest.trn '

Copy the backup file to 08r205 (Mirror) for restore (preferably logged in as sa account, so the database owner is "sa"):

--restore RESTORE DATABASE reptest from DISK = N ' d:reptest.bak ' withfile = 1, MOVE n ' reptest ' to n ' d:datareptest.mdf ', MOV E n ' reptest_log ' to N ' d:datareptest_log.ldf ', NORECOVERY, nounload, STATS = ten gorestore log reptest from DISK = ' d:reptest. Trn ' with NORECOVERY

1. Create a mirror

Performed on 08r205 (Mirror):

--mirror ALTER DATABASE reptest SET PARTNER = ' tcp://192.168.56.102:5022 ';

Execute on 08r202 (Master):

ALTER DATABASE reptest SET PARTNER = ' tcp://192.168.56.105:5022 '; Alterdatabase reptest SET WITNESS = ' tcp://192.168.56.101:5022 '; Alterdatabase reptest SET SAFETY full

By this, the image has been established.

Configuring Replication (transactional Replication)

Replicaiton do not do concrete construction process, only to do a few notes:

1. Both 08r202 and 08r205 need to be built into the distribution of 08r203;

2. The owner of the publishing library on 08r202 and 08r205 must be "SA", otherwise the switchover will appear with the following error: The process could not execute ' sp_replcmds ' on ' win-08r205′.

3. The configuration file on the 08r203 distribution machine needs to be modified as follows (configure failover partner):

--Configure replication failover Parameters--View agent configuration, run execsp_help_agent_profile--agent_type meaning--1 = Snapshot Agent at distributor; 2 = Log Reader Agent; 3 = Distribution Agent; --4 = Merge Agent; 9 = Queue Reader Agent. --For transactional replication, view agent_type=1,2 's profile_id--For transactional replication, you need to configure the Snapshot Agent (Snapshot agent) and the Log Reader Agent Execsp_add_agent_ Parameter @profile_id = 1, @parameter_name = N '-publisherfailoverpartner ', @parameter_value = N ' win-08r205 '--mirror server name exec sp_add_agent_parameter @profile_id = 2, @parameter_name = N '-publisherfailoverpartner ', @parameter_value = N ' win-08r205 '--mirror server name

After the modification is complete, remember to restart the agent service to make it effective.

4. Create a sync chain from 08r202 to 08r204.

Simulate failover and observe results

1. Suspend the 08r202 SQL Server service;

2. On the 08r205 to see if the mirror and the synchronization chain is transferred past;

3. If the synchronization chain is normal, modify the data in a table to see if it can be synced to 08r204;

4. Restart the 08r202 SQL Server service to see if it becomes a mirror machine;

5. If all is well together, congratulations, our test is successful.

SQL Server database Mirroring + replication Scenario