SQL Server Replication Interview Questions & Answers

In this article, we will discuss a number of interview questions that cover the SQL Server Replication concept, components, types, and troubleshooting scenarios.

Preparing to attend an interview for a SQL Server Database Administrator position? Make sure that you are ready to answer multiple questions about the SQL Replication feature, which is considered as an example of the High Availability and Disaster Recovery solutions provided by Microsoft SQL Server.

Q1: What is the SQL Server Replication feature?

SQL Server Replication is a data distribution technique that is used to copy and synchronize the database data and objects from one database to another, hosted in the same SQL Server or in a remote SQL Server instance, keeping the data in a consistent state.

Q2: What is the difference between the Transactional Replication and the Snapshot Replication?

Both the Transactional replication and Snapshot replication start the initial synchronization by creating a full copy of the publication database articles and apply this copy to the subscription database.

In the Transaction replication, all changes that occurred at the Publisher side after taking the snapshot will be copied and applied to the Subscriber side continuously.

This is not the case with the Snapshot replication, where the next synchronization will occur when a new snapshot is taken from the Publisher side to be applied to the Subscriber again.

Q3: What is special with the Merge Replication?

In the Merge replication, the Subscriber will download a copy of the Publisher database data and objects at first connect. When the Subscriber connects to the network again, it will upload all changes to the Publisher database then download all changes from the Publisher again, that are performed by all Subscribers, to keep the data synchronized and consistent.

Q4: What is the difference between the Peer to Peer replication and the Bidirectional replication?

Both Peer to Peer and Bidirectional replications are special types of Transactional replication.

In the Peer to Peer replication, the Publisher distributes the changes to more than one Subscriber at the same time.

But in the Bidirectional replication, the two servers can exchange data between each other.

Q5: What is common between all replication types?

The initial synchronization in all SQL replication types starts by taking a snapshot from the Publisher database then copy and apply it to the Subscriber database.

Q6: What are the main four components of the SQL Server Replication site?

The Articles, in the form of database tables, views, and stored procedures
The Distributor, the SQL Server instance where the Distribution database is hosted
The Publisher, where the source database is located
The Subscriber, where the target database is located

Q7: List the four SQL Replication agents and what is the purpose of each one?

Replication Snapshot Agent, responsible for generating a copy of the Publisher database that will be used for the initial synchronization in all SQL replication types
Log Reader Agent, responsible for monitoring the changes of the Publisher databases and copying the transactions from the transaction log into the distribution database
Distribution Agent, responsible for applying the initial snapshot and the continuous data changes to the Subscriber database
Merge Agent, responsible for synchronizing the changes from the Publisher and the Subscriber in both ways

Q8: Which component should be installed in the SQL Server instance before configuring the replication?

The SQL Replication component should be installed in servers that will participate in the SQL replication site, including the Publisher, the Subscriber, and the Distributor.

This component can be installed from the SQL Server installation media during the SQL Server installation process or added later to an existing instance.

Q9: Why we need to have free space on both the Publisher and Subscriber sides before configuring the SQL Server Replication?

We need to have free space:

On the server where the initial snapshot will be created
On the Subscriber where the initial snapshot and the continuous transactions will be applied
The Publisher database to handle the transaction log growth when a large number of transactions are replicated

Q10: Will the SQL Server Replication site work if the SQL Server Agent Service is not running?

Not for sure. This is because each replication agent will be running under a SQL Agent job that needs the SQL Server Agent service to be up and running.

Q11: What are the network requirements for setting up the SQL Replication between two servers?

Sufficient network bandwidth between the Publisher, Distributor and Subscriber servers to avoid any network latency issue
The Publisher, Distributor and Subscriber servers are able to reach (ping) and connect (telnet) to each other

Q12: What should be previously defined on each database table in order to participate in the SQL Replication site?

A primary key on each table that will be replicated from the Publisher to the Subscriber, to maintain the uniqueness of the data within these tables.

Q13: What permissions required for the account that is used to create the replication site and run the replication agents?

The account used to enable the Distributor, Publisher and Subscriber should be a member of the sysadmin fixed server role
The account used to create the publication should be a member of the db_owner on the publication database
The account used to create the subscription should be a member of the db_owner on both the publication and subscription databases

Q14: What is the difference between the Push and Pull subscriptions?

Push subscription: All replication agents will run at the Distributor side, providing centralized administration for the replication site
Pull subscription: Each agent will run at its own Subscriber, reducing the overhead caused by the agents’ operations, where this overhead will be distributed over the subscribers

Q15: Which window is used to monitor the SQL Server Replication components and synchronization status?

The SQL Replication Monitor window.

Q16: From where we can get information about the SQL Server Replication agents’ failures?

The SQL Server Agent Activity Monitor window.

Q17: How could we fix a replication issue when trying to update a record that is deleted from the subscription database mistakenly?

By inserting this record again to the subscription database or ignore this consistency issue, based on the project requirements, by configuring the subscription agent profile to continue synchronizing if a data consistency issue is detected.

Q18: What does “Uninitialized Subscription” error means?

This indicates that the current subscription is pending to be initialized and provided a new initial synchronization snapshot or the subscription is expired due to synchronization failure for multiple days.

Q19: How could we troubleshoot that the user configured to write on the subscription database does not have permission on the subscription database?

The records will be read from the Publisher to the Distributor with no issue, but it will be stuck while trying to write it to the Subscriber. Where we will see that there is a subscription error message from the Replication Monitor, and the error message can be checked from the Agent Activity Monitor under the Log Reader Agent job history.

We can fix this issue by providing the db_owner permission for the account that is configured for the Subscriber or use an authorized account.

Q20: Which of these actions are supported in the SQL Server Replication?

Configure SQL Server Replication on a database with a simple recovery model. (Yes)
Filters the rows that will be replicated from the Publisher to the Subscriber. (Yes)
Truncate a table which is used as an article in a SQL Replication site. (No)
Configure SQL Replication on a database that already participates in an Always-on Availability Group. (Yes)

Author
Recent Posts

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Related posts: