Setting up MSSQL Replication for High Availability & Redundancy

Solution

The customer was running on-Premises SQL Server 2014 Database Server (Standard Edition) for hosting one of their Key Application’s database. They wanted to setup replica of their Application’s Production Database to another SQL Server running same 2014 standard edition for high availability and redundancy. The replication process required everything from Primary Database including All Tables, Views, Stored Procedures & User Defined Functions if any.

Our team provided a complete solution that involved

• Initial Assessment of Application Database revealed following facts
  • – There were almost 30% of tables without primary keys
    • – There were many Stored Procedures, User Defined Functions & Views present in Production Database instance which needs to be replicated to
      Dev Node.

• Find & rectify any threats or errors present while setting up replication.

• Schedule Replication Job on regular basis/ configure it to run continuously to keep Development SQL Server instance up to date with primary one.

• Since Transactional Based MSSQL Replication can only replicate tables with primary keys We decided to go with setting up Snapshot based Replication.

• Setup MSSQL Snapshot based Replication by configuring Production SQL Server as Distributor, Publisher & Adding Development SQL Node as a subscriber in Replication Topology.

• Also, Validation of Successful Snapshot generation & application to Subscriber Dev SQL Node along with creating any missing views, user defined functions manually wherever required.

Technical Challenges

Our team figured out that there are many tables without primary keys in Application database, replication of such articles is not supported with transaction-based SQL replication & decided to go ahead with snapshot based one. We rectified all errors / warnings with Snapshot generation till application of same to Development Subscriber Node along with provisioning few of Views & User Defined Functions manually on Dev SQL Server.

Technologies Used

Accomplishment

• Setup MSSQL Replication using appropriate method (Transactional or Snapshot based) between Production & Development SQL Server satisfying key need to replicate all database objects.

• Identify & correct any warnings or errors encountered while setting up replication.

• Schedule Replication Job on regular basis or configure it to run continuously to keep Development SQL Server instance up to date with primary one.