SQL Server Replication

This SQL Server Replication guide will provide a high level overview of the various options available to database administrators for replicating data between SQL database servers.  SQL replication is essential to any IT shop with a need to distribute database objects and data to remote locations whether it be for synchronous transaction processing or disaster recovery purposes.  Replication involves copying and distributing data from one database to another and keeping it in synch to maintain consistency.  Once a replication topology is set up, data can be distributed to different locations across the globe no matter how remote.  This provides a mechanism for world wide access to consistent data even though it may be dynamically changing.

SQL Server ReplicationThere are 3 main replication technologies used by SQL Server.  These are Transactional replication, Merge replication, and Snapshot Replication.  All have their strengths and weaknesses and are best suited for different SQL Server deployment scenarios.  Review these below to help you best architect your replication topology.

Transactional Replication – (Applies transactions from the Publishing server to the Subscriber server in real time)

Best for:

  • High Volume of transactions
  • Integrating heterogeneous data
  • Server to Server scenarios
  • Low Latency Networks
  • High Throughput Networks
  • Increased  Scalability
  • Higher Availability
  • Data Warehousing
  • Offloading batch processing
  • Integrating data from multiple sites
  • Integration with non-SQL servers like Oracle or MySQL

Merge Replication – (offline Subscribing servers synchronize with Publisher when connected)

Best for:

  • Mobile Apps
  • Distributed Server Apps
  • Server to Client scenarios
  • Point of sale systems (POS)
  • High Latency networks
  • Integrating data from multiple sites
  • System prone to data conflicts

Snapshot Replication – (Publishing server distributes snapshots of data to subscribers at regular intervals)

Best for:

  • Initial data sets for transactional replication
  • Initial data sets for merge replication
  • Total Refreshes of data
  • Substantial but infrequent changes of data
  • Synchronizing data across many distributed systems

Comments are closed.