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.
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.
To configure database mirroring:
On XML400DB1, default instance
On XML400DB1/I2 and XML400DB2/I2
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.
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
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.
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
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.