What is SQL Server replication?
- Replication is subset of SQL
Server that can move data and database objects in an automated way from
one database to another database. This allows users to work with the same
data at different locations and changes that are made are transferred to
keep the databases synchronized.
What are the different types of SQL Server replication?
- Snapshot replication - As the name implies snapshot replication takes
a snapshot of the published objects and applies it to a subscriber.
Snapshot replication completely overwrites the data at the subscriber each
time a snapshot is applied. It is best suited for fairly
static data or if it's acceptable to have data out of sync between
replication intervals. A subscriber does not always need to be connected,
so data marked for replication can be applied the next time the subscriber
is connected. An example use of snapshot replication is to update a
list of items that only changes periodically.
- Transactional replication - As the name implies, it replicates each
transaction for the article being published. To set up transactional
replication, a snapshot of the publisher or a backup is taken and applied
to the subscriber to synchronize the data. After that, when a transaction
is written to the transaction log, the Log Reader Agent reads it from the
transaction log and writes it to the distribution database and then to the
subscriber. Only committed transactions are replicated to ensure data
consistency. Transactional replication is widely applied where high
latency is not allowed, such as an OLTP system for a bank or a stock
trading firm, because you always need real-time updates of cash or
- Merge replication - This is the most complex types of replication
which allows changes to happen at both the publisher and subscriber.
As the name implies, changes are merged to keep data consistency and a
uniform set of data. Just like transactional replication, an initial
synchronization is done by applying snapshot. When a transaction occurs at
the Publisher or Subscriber, the change is written to change tracking
tables. The Merge Agent checks these tracking tables and sends the
transaction to the distribution database where it gets propagated.
The merge agent has the capability of resolving conflicts that occur
during data synchronization. An example of using merge replication
can be a store with many branches where products may be centrally stored
in inventory. As the overall inventory is reduced it is propagated to the
other stores to keep the databases synchronized.
What is the difference between Push and Pull Subscription?
- Push - As the name implies, a push subscription pushes data
from publisher to the subscriber. Changes can be pushed to subscribers on
demand, continuously, or on a scheduled basis.
- Pull - As the name implies, a pull subscription requests
changes from the Publisher. This allows the subscriber to pull data
as needed. This is useful for disconnected
machines such as notebook computers that are not always connected and when
they connect they can pull the data.
What are different replication agents and what's their purpose?
- Snapshot Agent- The Snapshot Agent is used with all types of
replication. It prepares the schema and the initial bulk copy files of
published tables and other objects, stores the snapshot files, and records
information about synchronization in the distribution database. The
Snapshot Agent runs at the Distributor.
- Log Reader Agent - The Log Reader Agent is used with transactional
replication. It moves transactions marked for replication from the
transaction log on the Publisher to the distribution database. Each
published using transactional replication has its own Log Reader Agent
that runs on the Distributor and connects to the Publisher (the
Distributor can be on the same computer as the Publisher)
- Distribution Agent - The Distribution Agent is used with snapshot
replication and transactional replication. It applies the initial snapshot
to the Subscriber and moves transactions held in the distribution database
to Subscribers. The Distribution Agent runs at either the Distributor for
push subscriptions or at the Subscriber for pull subscriptions.
- Merge Agent - The Merge Agent is used with merge replication. It
applies the initial snapshot to the Subscriber and moves and reconciles
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. The Merge Agent runs at either the Distributor for push
subscriptions or the Subscriber for pull subscriptions.
- Queue Reader Agent - The Queue Reader Agent is used with transactional
replication with the queued updating option. The agent runs at the
Distributor and moves changes made at the Subscriber back to the
Publisher. Unlike the Distribution Agent and the Merge Agent, only one
instance of the Queue Reader Agent exists to service all Publishers and
publications for a given distribution database.
Does a specific recovery model need to be used for a replicated database?
- Replication is not dependent on
any particular recovery model. A database can
participate in replication whether it is in simple, bulk-logged, or full.
However how data is tracked for replication depends on the type of
1) What type of locking occurs during
the Snapshot generation?
- Locking depends on the
type of replication used:
snapshot replication, the snapshot agent locks the object during the entire snapshot
transactional replication, locks are acquired initially for a very brief time and then released. Normal
operations on a database can continue after that.
merge replication, no locks
are acquired during the snapshot generation process.
What options are there to delete rows on the publisher and not on the
- One option is to replicate
stored procedure execution instead of the actual DELETE command. You
can create two different versions of the stored procedures one on the
publisher that does the delete and the other on the subscriber that
does not do the delete.
- Another option is to not replicate DELETE commands.
Is it possible to run multiple publications and different type of publications
from the same distribution database?
- Yes this can be done and there are no
restrictions on the number or types of publications that can use the same
distribution database. One thing to note though is that all publications
from a Publisher must use the same Distributor and distribution database.
Data is not being delivered to Subscribers, what can be the possible reasons?
- There are a
number of possible causes for data not being delivered to
- The table is filtered, and there are no
changes to deliver to a given Subscriber.
or more agents are not running
or are failing with an error.
- Data is deleted by a
or a trigger includes a ROLLBACK statement.
transactional subscription was initialized without a snapshot, and changes have occurred on the
Publisher since the publication was created.
of stored procedure execution for a transactional publication produces
different results at the Subscriber.
INSERT stored procedure used
by a transactional article includes a condition that is not met.
is deleted by a user, a replication script, or another application.
Explain what stored procedure sp_replcounters is
- Sp_replcounters is a system
stored procedure that returns information about the transaction rate,
latency, and first and last log sequence number (LSN) for each publication
on a server. This is run on the publishing server. Running this stored
procedure on a server that is acting as the distributor or subscribing to
publications from another server will not return any data
How will you monitor replication latency in transactional replication?
- Tracer tokens
were introduced with SQL Server 2005 transactional replication as a way to monitor the latency of delivering
transactions from the publisher to the distributor and from the
distributor to the subscriber(s).
If I create a publication with one table as an article, and then change the
schema of the published table (for example, by adding a column to the table),
will the new schema ever be applied at the Subscribers?
- Yes. Schema changes to tables
must be made by using Transact-SQL or SQL Server Management Objects (SMO).
When schema changes are made in SQL Server Management Studio, Management Studio
attempts to drop and re-create the table and since you cannot drop a published objects, the schema change will fail.
Is it possible to replicate data from SQL Server to Oracle?
- Yes this can be done using heterogeneous replication. In
SQL Server 2000, publishing data to other databases such as DB2 or Oracle
was supported; however, publishing data from other databases was not
supported without custom programming. In SQL Server 2005 and later
versions, Oracle databases can be directly replicated to SQL Server in
much the same way as standard SQL Server replication.
How will you monitor replication activity and performance? What privilege do
you need to use replication monitor?
- The easiest way to monitor
replication activity and performance is to use replication monitor, but
you can also use the below tools to monitor replication performance:
- To monitor replication, a user
must be a member of the sysadmin
fixed server role at the Distributor or a member of the replmonitor fixed database role in the distribution
database. A system administrator can add any user to the replmonitor role, which allows that user to view
replication activity in Replication Monitor; however, the user cannot
Can you tell me some of the common replication DMV's and their use?
- sys.dm_repl_articles -
Contains information about each article being published. It returns data
from the database being published and returns a row for each object being
published in each article.
- sys.dm_repl_schemas -
Contains information about each table and column being published. It
returns data from the database being published and returns one row for
each column in each object being published
- sys.dm_repl_traninfo -
Contains information about each transaction in a transactional replication
6) What are the main prerequisites you should
plan before configuring SQL Server Transactional Replication?
Answer – Below are the things you should follow before setting up
- Make sure your servers that will
act as a Publisher, Distributor or Subscriber can communicate with each other.
- Make sure all tables that need
to be replicated have a primary key.
- Verify you have enough space in the log file and
the drive where the database log file is stored.
- Network bandwidth
is a crucial point you should keep in mind while planning replication
otherwise your replication will face latency issues.
7) What are the possible reasons for a SQL
Server Replication lag or Replication latency issues?
Answer – There could be multiple reasons for replication latency issues.
Some of the reasons are given below.
- One of the Replication Agent jobs is not running or failing during execution.
- A huge data load or large transactions occurred on your
- You have small network
bandwidth to replicate your large set of data.
- Poor resource configuration like storage, RAM, etc.
8) Can we truncate a table that is marked as
an article in SQL Server Replication?
Answer - No, you cannot truncate a table that is marked for replication.
we configure SQL Server Replication for a database that is part of an
Answer - Yes, you can configure replication for a database that is in an
Can you tell me where the SQL Server Merge Agent will run for a PULL
Answer – If you configure a PULL subscription, the Merge Agent will
be configured to run on the Subscriber server.
11) How can we drop an article from publication?
Answer – We can drop any article from
publication if needed. We need to launch Publication properties page then we
need to select the publication from which we need to drop the identified
article. To delete an article, clear the check box next to each article you
want to delete then click OK.
12) Adding/dropping article(s)
from an existing publication by invalidating the snapshot agent
1. First, change the allow_anonymous property
of the publication to FALSE
disable Change immediate_sync
EXEC sp_refreshsubscriptions @publication = N'Adventureworks2016-Pub'
start Snapshot Agent using Replication monitor
start log reader agent
the disabled properties, first, immediate_sync
and then Allow_anonymous options
you can verify the article on all your subscribers