Browse All Articles > Overview SQL Server Replication
In 2012 SQL Server, Microsoft introduced a new feature ALWAYS-ON as the flexible solution for high availability. This feature only allows us to copy a one-to-one database. Howerver, have you ever asked yourself: How will I copy some tables for reporting or schedule transferring data from my system to another? Have you ever encountered some real situations as below:
1- You have a website that is selling mobile devices; your customers can view and buy them directly from your website. Your director wants a report of sales revenue hourly along with several analysis reports. To avoid impacting the current system, another system called Reporting was built and you need to transfer sales data from website to Reporting system.
2- You have separate systems in different places tjat are connected to a central system with many people are working on them. The changes are updated to the central system at scheduled intervals. This means that many people can update the same data at same time in different places.
These examples are two of many situatuions you face when you have to copy and distribute your data to other systems for different purposes. There are many techniques to help us and one of them is REPLICATION. The solution is a set of techniques for copying or distributing data and database objects from one database to another database then synchronizing between databases to maintain consistency.
![OverviewReplication.png]()
Publication database
A database that contains objects that need to been replicated. A publication database are protected againts being dropped.
Articles
Articles are SQL Server objects that are allowed to replicated. SQL Server objects are often tables, views and store procedures.
Publications
A publication is a colllection of articles grouped together as one unit.
Distribution database
A database that contains replication metadata for replicated objected. Each Distributor has at least one distribution database.
Replication agents
Copying and distributing data processes are executed by a set of replicaton agents that are independent Windows executables. There are four replication agents: Distribution Agent, Snapshot Agent, Log Reader Agent, Merge Agent and Queue Agent. After installation, you can see the programs in installed folder. The executable programs are usually installed in Distributor. However, you can dertemine where to install them depending on your architecture.
![ReplicationAgents.png]()
Distribution agent -> distrib.exe
Log reader agent -> logread.exe
Merge agent -> replmerg.exe
Snapshot agent -> snapshot.exe
Distribution agent
The Distribution agent is reponsible for applying the changes from the distribution database to the subscription database. Only transactional replication uses the Distribution agent.
Log reader agent
The Log Reader agent reads the transaction log information of the publication database and write the changes to the distribution database. Only transactional replication uses the Log Reader agent.
Merge agent
The Merge agent is responsible for synchronizing changes between the publication database and the subscription database. It will handle changes in both the publication database and the subscription database and can synchronize those changes bi-directionally. Only merge replication uses the Merge agent.
Snapshot agent
At the initial synchronization between the Publication database and the Subscription database, Snapshot Agent is required for the process to perform. If you use Snapshot Replication type, the agent will be required. However, Transactional or Merge replications will use other agent types to synchronize data except when you request a fresh synchronization after intial synchronization. On the other hand, Snapshot Agent copy all data every time it is required.
Subscriptions
A Subscription is simply a collection of objects to receive the changes from Publication. Each Subcription is linked to a Publication. There are two type of subcriptions: PUSH and PULL subscriptions.
PUSH: Distributor is responsible for updating changes from a Pulication to a Subcription
PULL: Subcriber requests Ditributor to update changes regularly.
Subscription databases
A subscription database is a database which contains the replicated objects from the publication. SQL Server does not prevent a subscription database from being dropped.
(Source: http://technet.microsoft.com/en-us/library/ms151198(v=sql.110).aspx and Fundamentals of SQL Server 2012 Replication By Sebastian Meine, Ph.D.)
1- You have a website that is selling mobile devices; your customers can view and buy them directly from your website. Your director wants a report of sales revenue hourly along with several analysis reports. To avoid impacting the current system, another system called Reporting was built and you need to transfer sales data from website to Reporting system.
2- You have separate systems in different places tjat are connected to a central system with many people are working on them. The changes are updated to the central system at scheduled intervals. This means that many people can update the same data at same time in different places.
These examples are two of many situatuions you face when you have to copy and distribute your data to other systems for different purposes. There are many techniques to help us and one of them is REPLICATION. The solution is a set of techniques for copying or distributing data and database objects from one database to another database then synchronizing between databases to maintain consistency.
Overivew architecture
Replication Components
This section will explain the basic concepts of replication components in the replication architecture.Publisher
Publisher is a SQL Server instance that contains Publication databases for replication.Publication database
A database that contains objects that need to been replicated. A publication database are protected againts being dropped.
Articles
Articles are SQL Server objects that are allowed to replicated. SQL Server objects are often tables, views and store procedures.
Publications
A publication is a colllection of articles grouped together as one unit.
Distributor
Distributor is often a SQL Server instance that is responsible for controlling Replication mechanism. It identifiers the changes of articles and stores them in Distribution database. Sometime, it might also notify the Subscriber depending on the replication setup. Depending on your selected architecture, Distributor can reside on the same server as Publisher or separate server.Distribution database
A database that contains replication metadata for replicated objected. Each Distributor has at least one distribution database.
Replication agents
Copying and distributing data processes are executed by a set of replicaton agents that are independent Windows executables. There are four replication agents: Distribution Agent, Snapshot Agent, Log Reader Agent, Merge Agent and Queue Agent. After installation, you can see the programs in installed folder. The executable programs are usually installed in Distributor. However, you can dertemine where to install them depending on your architecture.
Distribution agent -> distrib.exe
Log reader agent -> logread.exe
Merge agent -> replmerg.exe
Snapshot agent -> snapshot.exe
Distribution agent
The Distribution agent is reponsible for applying the changes from the distribution database to the subscription database. Only transactional replication uses the Distribution agent.
Log reader agent
The Log Reader agent reads the transaction log information of the publication database and write the changes to the distribution database. Only transactional replication uses the Log Reader agent.
Merge agent
The Merge agent is responsible for synchronizing changes between the publication database and the subscription database. It will handle changes in both the publication database and the subscription database and can synchronize those changes bi-directionally. Only merge replication uses the Merge agent.
Snapshot agent
At the initial synchronization between the Publication database and the Subscription database, Snapshot Agent is required for the process to perform. If you use Snapshot Replication type, the agent will be required. However, Transactional or Merge replications will use other agent types to synchronize data except when you request a fresh synchronization after intial synchronization. On the other hand, Snapshot Agent copy all data every time it is required.
Subscriber
A Subscriber is also a SQL Server instance that receives the changes of Publisher. Depending on your replication setup, Subscriber can receive data passively or actively. For example, the Subscriber might be disconnected from the Publisher but data would re-synchronize after it reconnects.Subscriptions
A Subscription is simply a collection of objects to receive the changes from Publication. Each Subcription is linked to a Publication. There are two type of subcriptions: PUSH and PULL subscriptions.
PUSH: Distributor is responsible for updating changes from a Pulication to a Subcription
PULL: Subcriber requests Ditributor to update changes regularly.
Subscription databases
A subscription database is a database which contains the replicated objects from the publication. SQL Server does not prevent a subscription database from being dropped.
Replication types
There are main replication types: Snapshot replication, Transaction Replication and Merge replication.Conclusion
Replication provides us the flexible solution to replicate (copy) database objects to another SQL Server for many purposes. This covers the basic concepts and terms of the replication technique and you should get acquainted with them before moving to next article.(Source: http://technet.microsoft.com/en-us/library/ms151198(v=sql.110).aspx and Fundamentals of SQL Server 2012 Replication By Sebastian Meine, Ph.D.)
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
Commented: