Using Transactional Replication to support REPORTING and DIASTER RECOVERY

Here are some thoughts on using Transactional (or Peer-2-Peer) Replication Subscriber to support both REPORTING and DISATER RECOVERY.

Normally “subscriber” target server would have different set of non-clustered indexes to support REPORTING capabilities. The Publisher’s indexes, check-constraints, triggers, etc, would only be added when Publisher goes down and the Subscriber comes online as the new Publisher.  These Publisher objects would normally already have “Not for Replication” enabled.

Transactional Replication Disaster Recover Setup

1. 1. Create Publication and setup scriber via Snapshot Replication.  If you are setting up the Subscriber using Backup/Restore, then DROP any objects not needed to support the REPORTING functionality.  For example, drop any non-clustered indexes or triggers added to support Publisher workload but is not needed on the Subscriber.
   2. Create Subscriber Non-Clustered indexes to support Reporting capability.
   3. Script the Publisher INDEXES, Check Constraints, Triggers, SPs, etc. Don’t apply these to Subscriber but copy to local subscriber.
   4. Backup the Publisher MSDB or Script out any SQL Agent Jobs
   5. Create and execute a SSIS job to move Logins to the Subscriber.  Consider running the job at regular intervals to ensure subscriber has most recent logins.

When Publisher goes down

1. 1. Remove the Non-Clustered indexes from Subscriber which were added to support the REPORTING capabilities
   2. Run Publisher script to match the Subscriber schema to the original Publisher
   3. Run SQL Agent job script.
   4. Rename Subscriber to Publisher’s name (optional)