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.
Table of Contents
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.
Question: How to display subscriber status using T-SQL for transactional publications
Answer: Use the below T-SQL command
sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 0
Question: How to display subscriber status for snapshot publications:
Answer: Use the below T-SQL command
sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 1
Question: How to display subscriber status for merge publications:
Answer: Use the below T-SQL command
sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 2
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.
Replication Interview Questions with T-SQL Queries
Question: How do you manage if a snapshot fails in transactional replication in SQL Server?
Answer: In Transactional replication configuration, we must configure a snapshot for initial data sync to the subscriber. If the snapshot fails, we need to follow below steps:
Replication initialization will not be completed.
The subscription must be reinitialized after the snapshot issue has been resolved.
We can reinitialize a subscription with the below T-SQL command:
EXEC sp_reinit_subscription
@publisher = 'OurPublisherName',
@publication = 'OurPublicationName',
@subscriber = 'OurSubscriberName';Question: How do you monitor replication performance in an SQL server?
Answer: To monitor replication performance, we can use the Replication Monitor tool in SQL Server Management Studio for a deep dive and detailed statistics.
We can also use the below T-SQL command to get the Replication Status.
EXEC sp_replmonitorhelppublication;Question: How can we handle conflicts in merge replication in SQL Server?
Answer: Using a conflict resolver, we can manage conflicts in Merge Replication. In Merge Replication, the default resolver is based on the priority, but in the custom resolvers, we can review & resolve the conflict. We can review conflicts using the below T-SQL command:
EXEC sp_enumconflictpublications;Question: How can we manually reinitialize a replication subscription in SQL Server?
Answer: You can reinitialize a subscription using the below T-SQL command:
EXEC sp_reinit_subscription
@publication = 'OurPublicationName',
@subscriber = 'OurSubscriberName';Question: Can you give some common causes of replication latency in SQL Server?
Answer: There are a few common causes of replication latency in SQL Server like
- Network specific issues.
- Insufficient resources on the server, such as CPU, memory, disk I/O, etc.
- High volume of data transactions on the server.
- Bottlenecks at log reader or distribution agent.
Question: What are the steps to stop replication?
Answer: To stop or remove replication in SQL Server, we need to follow below steps:
Drop the Subscriptions:
EXEC sp_dropsubscription
@publication = 'OurPublicationName',
@subscriber = 'OurSubscriberName';Drop the Publication:
EXEC sp_droppublication @publication = 'OurPublicationName';Disable the Distributor:
EXEC sp_dropdistributiondb @database = 'DistributionDB';
EXEC sp_dropdistributor;Question: How can we replicate schema changes to a subscriber in SQL Server transactional replication?
Answer: In SQL Server, transactional replication can automatically replicate schema changes if this feature is enabled. To enable schema change replication in SQL Server, we need to follow the below step:
EXEC sp_changepublication
@publication = 'OurPublicationName',
@property = 'allow_anonymous',
@value = 'true';Question: What are the steps to troubleshoot a failed replication agent in SQL Server?
Answer: to troubleshoot a failed replication agent in SQL Server, check the Replication Monitor for error details.
Query Agent Errors
EXEC sp_replcmds;If the database is not part of the replication, the above command will return the below error:
Msg 18757, Level 16, State 3, Procedure sp_replcmds, Line 1 [Batch Start Line 0]
Unable to execute the procedure. The database is not published. Execute the procedure in a database that is published for replication.
Try to run it in the database that has already been published.
Restart the Agent:
EXEC sp_start_job @job_name = 'ReplicationAgentName';
Question: Can we change the publisher of an existing subscription in SQL Server? If Yes, how?
Answer: To change the publisher of an existing subscription in SQL Server, you need to reconfigure the replication by dropping the existing subscription and creating a new one with the new publisher.
Question: Can we validate the consistency between publisher and subscriber in SQL Server?
Answer: Yes, we can validate the consistency between publisher and subscriber in SQL Server. We need to use the sp_publication_validation procedure:
EXEC sp_publication_validation @publication = 'OurPublicationName';Question: Can we set up the replication in an Always On Availability Group environment in SQL Server?
Answer: Yes, we can set up the replication in an Always On Availability Group environment in SQL Server. We need to follow below steps:
- Add the publisher database to the availability group.
- Ensure the distributor is outside the availability group.
- Configure replication agents to use a listener name instead of instance names.
Review the articles below also.
Dbcc Freeproccache: A powerful command
Extended Events in SQL Server: A Deep Dive
Understand Deadlocks in SQL Server
SQL Server Pivot: Top 5 Concepts