Top 50 Replication Interview Questions

Explore the top 50 SQL Server Replication Interview Questions to better understand.

Replication is SQL Server’s main feature. It provides a facility to create a mechanism to copy data/database objects from one SQL instance to another while ensuring consistency. It is commonly used to distribute data across multiple servers or databases, ensuring that changes in one area are replicated in others. This can improve performance, provide load balancing, and increase availability.

Basic Replication Interview Questions

Question: What is transactional Replication, and when should you utilize it?

Answer: Transactional ReplicationReplication enables near-real-time data distribution from a publisher to subscribers. It is most commonly utilized when good data consistency and minimal latency are required between databases.

Question: What are the significant steps in implementing transactional ReplicationReplication?

Answer: Configure a distributor.
We need to create the publication in the Instance with the publisher.
Identify articles for publication.
Set up subscribers to subscribe to the publication.
Synchronize the initial snapshot and start duplicating transactions.

Question: How does a snapshot function in transactional Replication?

Answer: A snapshot sets up the subscriber by copying the schema and data from the publisher, and future modifications are propagated progressively.

Question: What is snapshot replication, and when should it be used?

Answer: Snapshot replication creates a complete copy of the data and distributes it to the subscribers. It is often utilized when changes occur infrequently, and some latency in updating is acceptable.

Question: What are the significant disadvantages of using snapshot replication?

Answer: High resource utilization due to repeated data copying.
There are no incremental updates; all data is refreshed at the subscriber level.

Question: What is merge replication in SQL Server, and how is it commonly used?

Answer: Merge ReplicationReplication enables the publisher and subscribers to update data separately, with changes combined periodically. It is beneficial in scenarios where data changes occur in multiple locations and must be integrated.

Question: How are disputes managed during merge replication?

Answer: Conflicts arise when the same row is modified by both the publisher and the subscriber. SQL Server handles conflicts according to established rules, such as the subscriber consistently winning, the most recent modification winning, or custom conflict resolvers.

Question: What is peer-to-peer transactional Replication?

Answer: Peer-to-peer Replication is transactional Replication in which each node serves as a publisher and a subscriber, enabling bidirectional Replication Replication. It is mainly utilized in high-availability applications.

Question: Can we replicate the stored procedures and views in SQL Server?

Answer: Stored methods and views can be replicated as articles in a publication. The replication method will carry out the stored procedures at the subscriber.

Question: What are the significant limits of SQL Server replication?

Answer: We can not clone all object types like triggers and full-text indexes.
Specific replication methods can be resource-intensive.
Merge Replication Replication may be delayed owing to conflict resolution.

Question: How will you resolve the replication latency issues?

Answer: Check for network issues or performance bottlenecks.
Keep a close eye to ensure the distribution agent is running and not stuck.
Replication delay can be monitored using performance counters or system-stored procedures such as sp_replcounters.
Check the transaction log’s size and ensure it is truncated.

Question: In SQL Server, how will you monitor the health of Replication?

Answer: Use tools like Replication Monitor to track replication agent status, check for latency concerns, and display complete replication performance statistics. You can also query system views, such as msdb.dbo.sysreplicationalerts.

Question: What are the frequent difficulties that can emerge in SQL Server replication?

Answer: Network latency can cause delays in the Replication of updates.
Agent failures: Distribution or replication agents can fail due to incorrect configurations or permission issues.
Large initial snapshots may cause performance issues, especially for large databases.
Transaction logs can expand in transactional Replication if replication agents are slow or stuck.

Question: In SQL Server, What are the replication agents?

Answer: Replication agents are jobs that conduct the tasks required for data replication. There are several sorts of agents, including the Log Reader Agent, Snapshot Agent, Distribution Agent, and Merge Agent.

Question: What are the primary distinctions between Replication and Always On Availability Groups in SQL Server?

Answer: Replication distributes data across servers for reporting, scaling out, or offline access. It enables fine-grained control over which tables or columns to replicate.
Always-on Availability Groups ensure high availability and catastrophe recovery by maintaining entire copies of databases in real-time with automatic failover.

Performance-Based Replication Interview Questions

Question: What factors affect SQL Server replication performance?

Answer: network bandwidth and latency.
The total size of the duplicated dataset.
Changes at the publisher are regular.
The publisher, Distributor, and subscriber each have limited resources.
The frequency of snapshot generation.

Question: How do you boost replication performance?

Answer: Filter replicated articles to send only relevant information.
Schedule snapshots at off-peak hours.
Use a different distributor to outsource replication processing.
Monitor and adjust replication agent profiles.

Scenarios-Based Replication Interview Questions

Question: What would you do if a subscriber went down for an extended period due to transactional Replication?

Answer: Even if a subscriber is not online, the Distributor will continue to record transactions in its database. Depending on the retention period, the distribution database could expand dramatically. Transactions will be processed after the subscriber is back online. If necessary, you can adjust the retention period or reactivate the subscription.

Question: How would you handle large-scale data updates in a replicated database?

Answer: Consider stopping ReplicationReplication during the bulk update (or using a different approach such as BULK INSERT or the BCP utility), then reinitializing ReplicationReplication or taking a new snapshot to guarantee that all changes are captured.

Question: You notice that one of the subscriber databases in a transactional replication system is out of sync with the publisher. How would you handle this scenario?

Answer: Verify the replication agents. First, check the status of the Distribution Agent to ensure it is operational.

Check for latency: Use Replication Monitor to identify any delays in data delivery to subscribers.

Resynchronize: If the subscriber is severely behind, you can resynchronize them by:

Reinitializing the subscription: This will apply a new snapshot to the subscriber, putting it back in sync with the publisher.

Alternatively, restarting the Distribution Agent may suffice if the amount of data to resynchronize is small.

Check for changes in data or schema. Investigate whether any schema or direct data alterations were done at the subscriber’s end that may have caused the disparity.