There is a freely available white paper publish by Paul Randall and Microsoft here http://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
- 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.
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
