Replication in SQL Server

Replication in SQL Server is a object level high availability feature. Normally in other high availability technologies we replicating entire database but in this we can replicate database objects like tables, stored procedures, triggers, functions etc.

Do you want to replicate your data from one server to another server? Or do you want to migrate data from old version to newer version in your database?

Then SQL Server replication is the perfect solution for your problem.

You can replicate database objects like tables, stored procedures, triggers, cursors etc. from one server to another server.

Replication is mainly used as a data migration technology although it is one of the high availability options. The concept of replication is to migrate data and database objects from one server to another server.

Why is replication a powerful migration technology?

1.    Replication can be used for multiple purposes

High availability

We can maintain 2 servers, both databases are completely synchronized, whatever changes are being done on the primary server goes to the secondary server.

Scalability

Load distribution is possible in replication as both primary and secondary servers are available to users. We can split users among the 2 servers.

Migration

After initial synchronization between 2 servers, database replication tracks the changes, and these changes migrate from source server to subscriber server instead of the entire database.

2.    Replication has a filter mechanism, while moving data from one server to another server we can use a filter or condition like dept equal to sales or emp salary greater than 100, while migrating data replication checks whether data meeting condition or not. If met data will migrate, if not it ignores the data. This is completely lack in other migration technologies like backup and restore, import and export

3.    Replication can be done on object level, we can select tables, stored procedures, functions as well as along with data.

4.    Replication can be configured between heterogeneous databases. We can migrate data from SQL Server to Non-SQL Servers like oracle, DB2, Teradata etc.

5.    Replication has scheduling options; we can define schedules with high latency or low latency.

How Replication works

Replication requires 3 servers and 3 databases along with it require articles, publications, and agents.

Publisher

Publisher is a source server containing primary database called publication database here we can select entire database or few tables, stored procedures of database.

Subscriber

Subscriber is a destination server whatever data coming it stores in subscription database

Distributor

Entire replication process carried in distribution server. It establish connection with publisher identifies data ready for replication copies data to subscriber.

1. Local distributor

Publisher and distributor on same machine

2.Remote distributor

Publisher and distributor on different machines.

Articles

Articles are the database objects that being replicated.

Publication

Publications are collection of articles.

Agents

Agents are executables (jobs) used to perform the replication operations.

Replication process

Replication uses news paper terminology, publisher (News printing person) has publication database in that articles means tables, stored procedures will be available for migration.

Distributor (Delivery person) collect the articles from publication database and forward the same data to subscriber using agents and distribution database

Subscriber (Receiver) receives all the data and keeps in subscription database

When we configure replication, we need to get details

1. Replication model
2. Data flow
3. Latency

Replication models

1. Publisher, subscriber, and local distributor
2. Publisher, subscriber, and remote distributor
3. Central publisher and multiple subscribers
4. Multiple publisher and central subscriber
5. Hybrid (combination of any 2 above models)

Data flow

We need get the details whether flow is one way or two way

One way

Data flows from publisher to subscriber

Two way

If both servers in use need to configure replication. By way as the data moves from publisher to subscriber and get data from subscriber to publisher

Latency

Latency is time interval, schedule the data movement either continuous or hourly or weekly.

Types of replication

1. Snapshot replication
2. Transaction replication
3. Peer to peer replication
4. Merge replication

Snapshot replication

Snapshot is going to make exact copy of an existing database; it is one time replication. 2 agents are created in snapshot replication.

1. Snapshot agent

It perform snapshot generation

• Distribution agent

Snapshot generation process

1. Snapshot agent establishes connection between publisher and distributor.
2. Snapshot agent identifies what are objects participated in replication process applies schema locks on those objects while copying structure.
3. Snapshot generates script of tables and output will be copies as ‘tablename.sch’. Snapshot folder is a storage location

Data will be copied with .bcp

Triggers will be copied with .trg

Indexes will be copies with .idx

Other constraints copy with .pre

If there are 5 tables participated in replication, for each table 5 files will be generated total 25 files will be generated and these files will copy from publisher database to snapshot folder

• Once snapshot generated automatically locks will be removed on objects and breaks connection between publisher and distributor.
• Distribution agent works from here. It goes to snapshot folder identifies tables and creates same files in subscriber. Once files copy done to subscriber the relation between distributor and subscriber also breaks.

When we go for snapshot

1. When source database has constant data
2. When huge latency is accepted
3. When we do not want changes in subscriber regularly.

Transactional Replication

Transaction Replication is one way replication. Initial synchronization will be done using snapshot replication. Snapshot generation process is same in all types of replications. Ongoing changes should be tracked and copy those changes to subscriber continuously using log reader agent.

In transaction replication changes will be tracked through log files. While copying modifications locks will be applied once schema is copies locks will be released, log read will read ongoing changes by scanning log file of the database. From here distribution agent will copy data to subscriber. This we call as concurrent snapshot.

Peer to peer replication

Peer to peer replication is introduced in SQL Server 2005 and enhanced in SQL Server 2008, Each server will act as a publisher and a subscriber. Data will move to all the servers

We have conflicts when one server fails in this replication. In SQL Server 2008 we can add or remove servers without interrupting the other servers. Conflicts handling introduced in SQL Server 2008.

Merge Replication

Merge replication allows publisher and subscriber to make changes to data independently and then merge the results. We have conflicts with merge replication. It uses merge agent.