SQL Server Replication

What is Replication?

Replication is a set of solutions for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

It provides flexibility for a range of scenarios, from distributing data to remote locations to increasing data availability and scalability.

Key Concepts

  • Publisher: The source database that makes data available.
  • Subscriber: The destination database that receives data.
  • Distributor: An optional server that manages data flow.
  • Articles: Individual tables, stored procedures, or other objects being replicated.

Common Use Cases

  1. Distributing read-only data to reporting servers.
  2. Synchronizing data across geographically dispersed locations.
  3. Providing high availability for critical applications.

Quick Start

Use the wizard in SQL Server Management Studio or run the following script to create a basic snapshot replication:

-- Enable publishing
EXEC sp_replicationdboption @dbname='MyDB', @optname='publish', @value='true';

-- Create a publication
EXEC sp_addpublication @publication='MyPublication',
    @publication_type='snapshot';

-- Add an article (table)
EXEC sp_addarticle @publication='MyPublication',
    @article='dbo.Customers',
    @source_owner='dbo',
    @source_object='Customers';

-- Create a subscription
EXEC sp_addsubscription @publication='MyPublication',
    @subscriber='SubscriberServer',
    @destination_db='MyDB_Subscriber',
    @subscription_type='push';