SQL Server Replication Interview Questions & Answers
Today i am going to write about SQL Server Replication Interview questions and answers. Read another set of interview questions and answers on other SQL Server features attached later in this article.
SQL Server Replication Interview Questions & Answers
Question – How many Replication Agents support SQL Server Replication and what are their roles?
Answer – There are total five Replication Agents in SQL Server Replication. Here is the list of all Replication Agents with their description.
- Snapshot Agent – This agent is used by all types of replication whether its Snapshot Replication, Merge Replication or Transactional Replication. Snapshot Agent prepares initial data files of published objects, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent runs at the Distributor.
- Log Reader Agent – This replication agent is used by Transactional replication only. This Agent is very important to run transactional replication smoothly. Log Reader Agent replicates all transactions that are marked for replication from Publisher to Distributor. Every database that is marked for replication has its own log reader agent and it runs on distributor.
- Distribution Agent – This agent is responsible to move transactions from distributor to subscriber. Distribution agent is used by Snapshot and Transactional Replication. This agent runs on distributor for PUSH subscription and runs on Subscriber for PULL subscription.
- Merge Agent –The Merge Agent applies the initial snapshots to the Subscribers. It also merges incremental data changes that occurred at the Publisher or Subscribers after the initial snapshot was created, and detects and resolves any conflicts according to rules you configure. This agent also 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.
Question – What is PUSH and PULL subscription?
Answer – These are subscription modes that defines where should your distribution, merge and queue reader agent will run.
When you choose PUSH subscription, data will be replicated from Publisher to Subscriber. Subscriber will act as passive and will receive data based on publisher and distributor configurations. Distribution and Merge Agent will run on distributor in this type of subscription.
While in PULL subscription, subscriber play a key role. Subscriber can control when it will receive data from publisher in pull subscription. Distribution and Merge Agents will also be running on subscriber in PULL subscription.
Question – What is merge replication and how does it work?
Answer – Merge Replication a type of SQL Server Replication that can be used to synchronize data from publisher to subscriber or vice versa. Merge replication also starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred. Merge replication is implemented by the SQL Server Snapshot Agent and Merge Agent. The Merge Agent applies the initial snapshots to the Subscribers. It also merges incremental data changes that occurred at the Publisher or Subscribers after the initial snapshot was created, and detects and resolves any conflicts according to rules you configure.
Question – How many Replication Agents are used in Merge Agent?
Answer – Merge Replication uses Snapshot Agent and Merge Agent to replicate the data.
Question – How Merge Replication tracks data changes post initial snapshot creation?
Answer – Merge replication tracks changes through triggers and metadata tables. Merge replication adds the column rowguid to every table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set. The newid() function is provided as a default for the rowguid column, however customers can provide a guid for each row if needed.
Question – What is the actual location where the distributor agent runs?
Answer – Distribution agent runs on distributor instance for PUSH subscription and runs on Subscriber for PULL subscription.
Question – Can we Rename Publication Database?
Answer – No, we cannot rename publication database directly but if we want to rename any publication database then we need to drop the publications, rename the database and re-configure replication all over again. We cannot directly rename publication database.
Question – What is the difference between Publisher and Publication?
Answer – Publisher is the database Instance where replicated databases are hosted. Publication is the set of database objects commonly known as articles that are marked for replication.
Question – Can we prevent DELETE commands to replicate on Subscriber that has executed on Publisher?
Answer – We can exclude DELETE commands to replicate on subscriber.
Question – Why Subscribers are not getting data from Publisher?
Answer – There are several reasons for data not being replicated to Subscribers. Some of the reasons are given below.
- Replication Agents jobs are not running or failing during execution.
- The table is filtered.
- Data might be deleted by a trigger.
- Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.
- Data is deleted by a user, a replication script, or another application.
Question – How do you check Replication Latency?
Answer – We can check replication latency either by using Replication monitor or using system stored procedure called sp_replcounters. This stored procedure gives 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. Similar information we can get in replication monitor as well.
Question – I want to add a column in a table that has been marked for replication. I don’t want to replicate this column to subscribers. How can we do that?
Answer – To add a new column to a table and not include that column in an existing publication, we need to disable the replication of schema changes and then execute ALTER TABLE <Table_Name> ADD <Column_Name> statement.
Question – 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.
- SQL Server DBA Interview Questions & Answers
- SQL Server Interview Questions & Answers on Indexes
- Always On Availability Group Interview Questions & Answers
- SQL Server Backup & Recovery Interview Questions
- SQL Server Architecture Interview Questions & Answers
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023