Problem
If you are looking for job change as a SQL Server DBA, then you should prepare with SQL Server interview questions and answers on the most used features. SQL Server Replication is one topic that most interviewers evaluate your expertise. Keep reading this tip to learn SQL Server Replication interview questions and answers.
Solution
SQL Server Replication is a technology that is used to replicate or distribute data from one server to another server or even within the same server. We can also make available one set of data from one location to another for reporting purpose using replication. Have a look at the set of replication questions and answers for your next job interview.
SQL Server Replication Interview Questions and Answers
Question – Briefly define SQL Server Replication and its components.
Answer – Replication is used to replicate data from one server to another. SQL Server replication has very mature features that can be used to offload reporting transactions from your OLTP system and run them on your replicated database that is hosted on either the same instance or another instance. There are three types of replication we can configure depending on the requirements.
- Snapshot Replication
- Transactional Replication
- Merge Replication
The important components involved in replication are listed below.
- Publisher – The SQL Server instance that is replicating the data is known as the Publisher in a replication configuration.
- Subscriber – The SQL Server instance that is receiving the data is known as Subscriber.
- Articles – An article is an individual database object like a table, view or stored procedure, etc. that will be replicating to another server.
- Publications – Publications are a set of articles that are being replicated to a Subscriber.
- Distributor – The Distributor is mandatory database that stores replication specific data associated with one or more Publishers.
- Agent Jobs – There are several replication processes also known as agents involved in copying and moving data between the Publisher and Subscribers.
Question – How many Replication Agents are involved in SQL Server Transactional Replication?
Answer – There are four replication agents used in transactional replication. Here is the list of all four replication agents.
- Snapshot Agent
- Log Reader Agent
- Distribution Agent
- Queue Reader Agent
Question – What is the role of the SQL Server Log Reader Agent in Replication?
Answer – This replication agent is used by Transactional replication only. This agent is very important to run transactional replication smoothly. The Log Reader Agent replicates all transactions that are marked for replication from the Publisher to the Distributor. Every database that is marked for replication has its own log reader agent and it runs on the Distributor.
Question – What is role of the Merge Agent in SQL Server Replication?
Answer – As its name suggests, the Merge Agent is used by Merge Replication. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both.
Question – What is the role of the Distribution Agent in SQL Server Replication?
Answer – This agent is responsible to move transactions from the distributor to subscriber. The Distribution agent is used by Snapshot and Transactional replication. This agent runs on the distributor for PUSH subscriptions and runs on the Subscriber for PULL subscriptions.
Question – Can we restrict or filter few columns from a publication to not replicate on the subscriber?
Answer – When tables are published as articles, filters can be used to restrict the columns and rows of the data sent to the Subscribers.
Question – Can we rename a Subscriber database? If yes, describe the steps.
Answer – Yes, we can rename a subscriber database by running an ALTER statement, but we should follow some additional steps post renaming the subscriber database.
The command below can be used to rename a subscriber database.
USE master; GO ALTER DATABASE DBNAME Modify Name = DBNAME_NEW;
Once you rename the subscriber database you might see errors like “The process could not access the database” in the replication monitor in the ‘Distributor to Subscriber History’. To fix this error you need to create another subscription for this renamed database and drop the older subscription.
Question – Can we configure SQL Server Replication for a database that is running in SIMPLE recovery model?
Answer – Yes, you can configure Replication for any database irrespective of their recovery model. The recovery model is not a prerequisite to configure replication for a database.
Question –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 transactional replication.
- 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.
Question – 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 publication database.
- You have small network bandwidth to replicate your large set of data.
- Poor resource configuration like storage, RAM, etc.
Question – 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.
Question – Can we configure SQL Server Replication for a database that is part of an Availability Group?
Answer – Yes, you can configure replication for a database that is in an Availability Group.
Question – Can you tell me where the SQL Server Merge Agent will run for a PULL subscription?
Answer – If you configure a PULL subscription, the Merge Agent will be configured to run on the Subscriber server.
Question –How can we add an article to an existing SQL Server publication?
Answer – Follow the below processes to add an article to existing publication.
Launch Publication Properties then choose your Publication in which you want to add the identified article. Uncheck the “Show only objects”. Now you will be able to see all database objects from the publication database. Here, you can select the check box next to the object that you want to add in to existing publication. Then click OK to proceed. Similarly, you can uncheck the box if you want to remove any article from a publication.
Next Steps
- Read more articles on SQL Server Replication.
- You can also read more articles for SQL Server DBA Interview Questions.
- Explore more knowledge with these SQL Server Database Administration Tips.
