In this series you will understand the different components of SQL Server Replication.

This is part 1 of the series which gives the user an overview of Replication with the components and the scenarios where replication can be the ideal choice.

S 1. SQL server Replication
S 2. Scenarios in which we can use replication
S 3. Replication Components

Replication is a Microsoft SQL Server offering which copy and distribute the database objects and data from one database to another. This synchronizing between databases is done to maintain consistency in the data.

SQL Server replication is more than just copying data across the servers. Replication can be setup continuously to synchronize data between source server and secondary server or set it up to synchronize the data at scheduled intervals.

Additionally, Replication supports both way synchronization - single-directional and bi-directional which keeps several datasets in sync with each other.

There are several scenarios in which a user can opt to move with SQL Server replication.

1. We can use replication to distribute the data to a different server and provide a source for reporting or reading requests.
This benefits in two ways-

    a. Routing expensive reporting queries from source to reporting\subscriber server.
    b. It can be scheduled to run at certain intervals.

2. SQL Server doesn't offer synchronous replication, but it can be set up transactionally to replicate data in near real-time to servers. Latency depends on a lot of factors, but generally it is for few seconds or minutes.

3. A third scenario in which merge replication is often utilized is in sales profile. Sales profile update data throughout the day which needs to be fed into the database, but that same data can sometimes be updated from different places which creates conflicts. With replication, SQL Server provides a replication topology that was built for exactly this type of scenario.

4. Replication can also be used for high availability purposes. You can maintain a copy of database on a second server which can accept read requests.

5. Replication is more flexible than other SQL HA offering. Single table\Set of tables, filtered rows, and selected columns can be replicated instead of whole range of data or columns or tables.


Replication Components

Publications: A publication is a collection of articles grouped together as one unit.

Articles: Article are the objects that are set to replicate. These objects can be tables, views, or stored procedures.

Publisher: The Publisher is the server that makes a publication available for replication

Distributor: The Distributor is a server that identifies changes to the articles.

Distribution databases: It contains several objects that store replication metadata as well as replicated data.

Subscriber: Server that subscribes to a publication is called a Subscriber.

Subscriptions: A subscription is the counterpart of the publication. Each subscription creates a link, or contract, between one publication and one Subscriber.

Subscription databases: A target database of a replication subscription is called a subscription database.

Snapshot Agent: Snapshot Agent provides the data required to perform the initial synchronization of the publication database with the subscription database.

Log Reader Agent : The Log Reader Agent reads the transaction log of the publication database. If it finds changes in published objects, it records those changes to the distribution database.

Distribution Agent: The Distribution Agent applies the changes recorded in the distribution database to the subscription databases.

Continued in Part 2