事务复制发布故障转移到镜像

There is a freely available white paper publish by Paul Randall and Microsoft herehttp://sqlcat.com/sqlcat/b/whitepapers/archive/2008/09/02/sql-server-replication-providing-high-availability-using-database-mirroring.aspx about configuring database mirroring for a transaction replication publisher. I setup a Hyper-V environment and walked through the whitepaper, step by step, documenting and testing at each stage. This is meant more as a self training strategy than a recommndation and clearly references the source of the idea/plan.

Objectives

Configure a Transaction Replication topology that supports failing a Publisher over to a Mirror Standby, then back, without disrupting a continous pull subscriber or a mirror failover/'transparent redirection' aware application that is reading/writing to the published database. This training project is effectively a walkthrough and test of the WhitePaper published by Paul Randal and Microsoft.

Summary

Normal Operations Configuration

The LAB 2A diagram below shows the start state of both replication and database mirroring on two virtual server each running two instances of SQL.

• The default instance on XML400DB1 is serving as the replication publisher.
• The XML400DB1/I2 instance is on the same server is a subscriber
• The XML400DB2 default instance on XML400DB2 is serving as the distributor for replication and the witness for mirroring.
• The XML400DB2/I2 instance is a mirror standby.

Failover Operations Configuration

The LAB 2B diagram below shows the state post failover of the primary to the mirror, which then assumes the publisher role.

• The default instance on XML400DB1 is now serving as the standby for database mirroring.
• The XML400DB1/I2 instance is still a subscriber, but of the XML400DB2/I2 instance
• The XML400DB2 default instance on XML400DB2 still serves as the replication distributor and mirror witness.
• The XML400DB2/I2 instance is now the primary database for mirroring and the publisher for replication.

Database Mirroring

To configure database mirroring:

On XML400DB1, default instance

• Switch the database into FULL recovery mode on the primary. XML400DB1 Default instance
• Take a full and transaction backup

On XML400DB1/I2 and XML400DB2/I2

• Restore the full and transaction backups on the standby with NORECOVERY

Use the wizards to configure database mirroring, XML400DB2/I2 will server as witness. You cannot installing the mirroring witness on the same server as the primary. Ideally, database mirroring would use certificates to authorize endpoints/users and be scripted for proper maintenance. The test here though is on failover and transparent client redirection so I used the wizard.

These are the settings in the database mirroring setup wizard.

Replication

1. Configure Replcation Distribution on XML400DB2

2. Configure Replication Publication on XML400DB1 - Use remote Distributor

3. Configure Replication Subscription on XML400DB1/I2 - Use remote Distributor to push publication to the subscriber

4. Override Snapshot and Log Reader Agent Profile parameters as described here :http://msdn.microsoft.com/en-us/library/ms147893.aspx

Test Summary

The test involves running SQL batches (SELECT, INSERT, UPDATE, DELETE) against the replication publisher using SQLStress, manually failing over to the mirror standby which automatically becomes replication publisher, then back while monitoring database IO to gauge the impact on users. SQLStress is a transparent client redirection aware application and continues running during the failover.

19:05 - Start PERFMON

19:10 - Start SQLStress load simulation

19:15 - Failover Database Mirror Primary/Replication Publisher to Standby

19:20 - Failback Database Mirror Primary/Replication Publisher to original Primary

19:25 - Stop SQLStress

19:30 - Stop Perfmon

The SQLStress load simulation tool (the client application in this scenario) is mirror aware, as is the replication subscriber once the Agent -PublisherFailoverPartner parameters are set.

Notice the 'Failover Partner' parameter. SQLStress is transparent client redirection aware.

Results

The failover/failback worked exactly as hoped. The Replication Subscriber blipped as the failover occurred but quickly recovered and began syncronizing with the new publisher.

XML400DB1/I2 - Subscriber Database Write Activity

The load simulation was running from 19:10 - 19:25. Two blips are apparent at 19:15 and 19:20 when the failover/failback occurred.

XML400DB1 - Mirror Primary Write Transactions/sec

The SQLStress load simulation, running from 19:10-19:25, was clearly busy somewhere else between 19:15-19:20

?

XML400DB2/I2 - Mirror Standby, - Write Transactions/sec

The 'Transparent Clint Redirection' aware SQLStress tool has recongnized the failover and moved operations to this instance between 19:15-19:20

 

Conclusion

Many organizations already use this feature which is actually easy to configure and works well. The test lab environment used here was simply and the load simulation gentle but results were good.

References

http://sqlcat.com/sqlcat/b/whitepapers/archive/2008/09/02/sql-server-replication-providing-high-availability-using-database-mirroring.aspx  - Ideas for lab
http://www.sqlstress.com/  - Load simulation