Replication FAQ'S

Replication FAQ's

1) 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.

2) 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 stocks. 

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.

3) 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. 

4) 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 database
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.

5) 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 replication used.
Medium

6) What type of locking occurs during the Snapshot generation?

 Locking depends on the type of replication used:
In snapshot replication, the snapshot agent locks the object during the entire snapshot generation process.
In transactional replication, locks are acquired initially for a very brief time and then released. Normal operations on a database can continue after that.
In merge replication, no locks are acquired during the snapshot generation process.

7) What options are there to delete rows on the publisher and not on the subscriber?

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.

8) 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.

9) 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 Subscribers:
The table is filtered, and there are no changes to deliver to a given Subscriber.
One or more agents are not running or are failing with an error.
Data is deleted by a trigger, or a trigger includes a ROLLBACK statement.
A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created.
Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.
The INSERT stored procedure used by a transactional article includes a condition that is not met.
Data is deleted by a user, a replication script, or another application.

10) Explain what stored procedure sp_replcounters is used for?

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
Hard

11) 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). For details, please refer tip to this tip: Monitor SQL Server replication latency using tracer tokens
12) 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.
13) 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.  For more information, please read tip: Introduction to SQL Server Heterogeneous Replication
14) 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:  
T-SQL commands. For more details refer msdn article –  http://msdn.microsoft.com/en-us/library/ms147874.aspx    
Microsoft SQL Server Management studio. For more details refer msdn article - http://msdn.microsoft.com/en-us/library/ms152763.aspx    
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 administer replication.
15) 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

16.)Why can't I run TRUNCATE TABLE on a published table?
TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.
17.)What is the effect of running a bulk insert command on a replicated database?
For transactional replication, bulk inserts are tracked and replicated like other inserts. For merge replication, you must ensure that change tracking metadata is updated properly. 

18.)Are there any replication considerations for backup and restore?
Yes. There are a number of special considerations for databases that are involved in replication. For more information, see Back Up and Restore Replicated Databases.
19.)Does replication affect the size of the transaction log?
Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs. Or, set it to run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running. For more information on checking Log Reader Agent status, see View Information and Perform Tasks for the Agents Associated With a Publication (Replication Monitor).
Additionally, if you have set the option 'sync with backup' on the publication database or distribution database, the transaction log is not truncated until all transactions have been backed up. If the transaction log is growing too large, and you have this option set, consider shortening the interval between transaction log backups. For more information on backing up and restoring databases involved in transactional replication, see Strategies for Backing Up and Restoring Snapshot and Transactional Replication.

20)How do I rebuild indexes or tables in replicated databases?
There are a variety of mechanisms for rebuilding indexes. They can all be used with no special considerations for replication, with the following exception: primary keys are required on tables in transactional publications, so you cannot drop and recreate primary keys on these tables.

21.)How do I add or change indexes on publication and subscription databases?
Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

22.)How do I move or rename files for databases involved in replication?
In versions of SQL Server prior to SQL Server 2005, moving or renaming database files required detaching and reattaching the database. Because a replicated database cannot be detached, replication had to be removed from these databases first. Beginning with SQL Server 2005, you can move or rename files without detaching and re-attaching the database, with no effect on replication. For more information about moving and renaming files, see ALTER DATABASE (Transact-SQL).

23.)How do I drop a table that is being replicated?
First drop the article from the publication using sp_droparticle, sp_dropmergearticle, or the Publication Properties - <Publication> dialog box, and then drop it from the database using DROP <Object>. You cannot drop articles from snapshot or transactional publications after subscriptions have been added; you must drop the subscriptions first. For more information, see Add Articles to and Drop Articles from Existing Publications.
24.How do I add or drop columns on a published table?
SQL Server supports a wide variety of schema changes on published objects, including adding and dropping columns. For example, execute ALTER TABLE … DROP COLUMN at the Publisher, and the statement is replicated to Subscribers and then executed to drop the column. Subscribers running versions of SQL Server prior to SQL Server 2005 support adding and dropping columns through the stored procedures sp_repladdcolumn and sp_repldropcolumn. For more information, see Make Schema Changes on Publication Databases.