SQL Server Replication

What is SQL Server Replication?

SQL Server Replication is a set of tools that allow data to be automatically moved from one server to another. There are three distinct types of replication available within Microsoft SQL Server:

SQL Server Replication

Transactional Replication

In this type of replication information is moved from one server to the other as the transactions occur. This is a good choice for large databases because once it is set up and an initial snapshot is applied it only moves the changes to data between the servers. By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that allow updates at the Subscriber.

When to use Transaction Replication:
• You want incremental changes to be propagated to Subscribers as they occur.
• The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
• The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
• The Publisher has a very high volume of insert, update, and delete activity.
• The Publisher or Subscriber is a non-SQL Server database, such as Oracle.

Snapshot Replication

In Snapshot replication an entire copy of the data to be replicated is moved to the target server. This is a good choice for smaller databases where it is acceptable for the data to be perhaps a day old. Since Snapshot replication is copying entire tables it doesn’t need to have primary keys on tables. The snapshot process is commonly used to provide the initial set of data and database objects for transactional and merge publications.

When to use Snapshot Replication:
• The data is not changing frequently.
• If you want to replicate small amount of data.
• To replicate look-up tables which are not changing frequently.
• It is acceptable to have copies of data that are out of date with respect to the publisher for a period of time. For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended.

Merge Replication

Merge Replication allows users on the target system to modify data and have that data “merged” back onto the primary source. This is commonly used in applications where someone, perhaps a Sales Rep, needs to download information, make updates to that information while disconnected from the primary database then apply the updates to the primary when reconnected. Because changes could occur to the same record on both the primary and target machines this type of replication requires rules be created to resolve conflicts. Merge replication also requires that a GUID (Globally Unique Identifier) field be added to each table that will be replicated.

When to use Merge Replication:
• Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.
• Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
• Each Subscriber requires a different partition of data.
• The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row will change only once at the Publisher to reflect the net data change (that is, the fifth value).

Replication Components

SQL Server replication is based on the “Publish and Subscribe” Machanism. However, below is the list of each individual components of SQL Server Replication.

• It is a source database where replication starts. It makes data available for replication.
• Publishers define what they publish through a publication.

• Articles are the actual database objects included in replication like tables, views, indexes, etc.
• An article can be filtered when sent to the subscriber.

• A group of articles is called publication.
• An article can’t be distributed individually. Hence publication is required.

• It is intermediary between publisher and subscriber.
• It receives published transactions or snapshots and then stores and forwards these publications to the subscriber.
• It has 6 system databases including distribution.

• It is the destination database where replication ends.
• It can subscribe to multiple publications from multiple publishers.
• It can send data back to publisher or publish data to other subscribers.

• It is a request by a subscriber to receive a publication.
• We have two types of subscriptions – push and pull.

Push Subscriptions
• With this subscription, the publisher is responsible for updating all the changes to the subscriber without the subscriber asking those changes.
• Push subscriptions are created at the Publisher server

Pull Subscriptions
• With this subscription the subscriber initiates the replication instead of the publisher.
• The subscriptions are created at the Subscriber server.

Replication Agents

• We know that replication process works in the background with the help of jobs.
• These jobs are also called as agents. These jobs internally uses respective .exe files present in …………….. \110\COM folder.
All the agents information is present in Distribution db in the following tables.
• dbo.MSxxx_agents
• dbo.MSxxx_history

Snapshot Agent
• It is an executable file that prepares snapshot files containing schema and data of published tables and db objects.
• It stores the files in the snapshot folder, and records synchronization jobs in the distribution database.

Distribution Agent
• It is used with snapshot and transactional replication.
• It applies the initial snapshot to the Subscriber and moves transactions held in the Distribution db to Subscribers.
• It runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.

Log Reader Agent
• It is used with transactional replication, which moves transactions marked for replication from the transaction log on the publisher to the distribution db.
• Each db has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.

Merge Agent
• It is used with merge replication.
• It applies the initial snapshot to the Subscriber and moves incremental data changes that occur.
• Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both.
• It captures changes using triggers.

Queue Reader Agent
• It is used with transactional replication with the queued updating option.
• It runs at the Distributor and moves changes made at the Subscriber back to the Publisher.
• Unlike Distribution Agent and Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution db.

Who Uses SQL Server Replication and Why?

A business organization that needs to offload reporting to a separate server
SQL Server replication can reduce the stress on your production environment caused by user reports. Replication allows your users to run their reports against a replicated copy of your database. This copy can be near real-time (transactional replication) or if there isn’t a need for real-time a copy of production data can be moved to a reporting server off-hours (snapshot replication). In either case it frees up your production server for the important work of serving your customers.

A business organization that wants to build distributed system
Data distribution is a tool that helps companies put necessary data in the hands of local decision-makers yet maintain firm central control over the data. With SQL Server replication, data can be shared and replicated between databases, allowing system designers to put the data where it’s needed.

A business organization that wants to have additional backup strategy
In conjunction with backup, SQL Server replication strategies seek to complement traditional approaches by providing alternative levels of data protection and integrity, while minimizing user disruptions. replication creates a point-in-time copy of the data to be used as the backup source.

A business organization who wants to set up failover system
SQL Server replication maintains a near real-time “warm standby” database to which applications can switch with virtually no downtime if the primary site fails. You can manage planned downtime such as routine maintenance, software upgrades and etc. It protect during unplanned downtime due to machine/network outage, for example. It also provides disaster recovery. Any system providing HA services should provide continuous availability of data in any scenarios.

A business organization that needs to replicate data from Oracle to SQL Server
SQL Server Enterprise edition supports heterogeneous replication from Oracle to SQL Server and from SQL Server to Oracle.

<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.