7 questions found
What is SQL Server replication, and what problem does it help solve?
Beginner
Replication is a set of technologies that automatically copies and synchronizes data from one database, called the publisher, to one or more other databases, called subscribers, letting you distribute data to multiple locations for purposes like reporting, offloading read traffic, or keeping remote offices synchronized with a central database.
-- Simplified concept: a publisher sends changes
-- to one or more subscriber databases automatically
EXEC sp_addpublication @publication = 'SalesPublication';
Real-world example
A retail chain uses replication to automatically copy sales data from each store's local database to a central reporting database, giving head office near real time visibility into sales across every location.
Common follow-ups: What are the different types of replication available in SQL Server?;Does replication require a constant network connection between servers?
Always On Availability Groups;Backup & Recovery
What are the three main types of replication in SQL Server, and how do they differ?
Beginner
Snapshot replication periodically copies an entire fresh snapshot of the published data, transactional replication continuously sends individual data changes as they happen for near real time synchronization, and merge replication allows changes to be made independently at both the publisher and subscriber, later merging them together, which is useful for scenarios with occasionally connected clients.
-- Transactional replication continuously sends changes
-- as they occur in near real time
EXEC sp_addarticle @publication = 'SalesPublication', @article = 'Orders';
Real-world example
A company uses transactional replication to keep a reporting database nearly synchronized in real time with their main operational database, choosing it over snapshot replication because near real time data is important for their dashboards.
Common follow-ups: When would snapshot replication be a better choice than transactional replication?;What kind of conflicts can occur with merge replication, and how are they resolved?
Always On Availability Groups;Change Data Capture (CDC)
What roles do the publisher, distributor, and subscriber play in a transactional replication setup?
Intermediate
The publisher is the source database where the original data changes happen, the distributor is a special role, sometimes a separate server, that manages and forwards the captured changes, and the subscriber is the destination database that receives and applies those changes, together forming the complete replication pipeline from source to destination.
-- Configure the distributor first
EXEC sp_adddistributor @distributor = 'DistributorServer';
-- Then configure the publisher
EXEC sp_adddistpublisher @publisher = 'PublisherServer', @distributor = 'DistributorServer';
Real-world example
A company sets up a dedicated distributor server separate from both the publisher and subscriber servers, reducing the processing load on their primary production database that acts as the publisher.
Common follow-ups: Can the distributor role run on the same server as the publisher?;What happens if the distributor server experiences downtime?
SQL Server Architecture & Editions;Backup & Recovery
How do you monitor the health and latency of a transactional replication setup to ensure subscribers are staying reasonably up to date?
Intermediate
You can use Replication Monitor, a built-in graphical tool in SQL Server Management Studio, which shows the current status of each publication and subscription, including how much latency exists between when a change happens at the publisher and when it is applied at the subscriber, helping you quickly spot replication delays or failures.
-- Replication Monitor is accessed through SSMS
-- under Replication > Launch Replication Monitor
Real-world example
A database administrator uses Replication Monitor to notice a subscriber has fallen significantly behind the publisher, investigating and discovering a network issue that was slowing down the delivery of replicated changes.
Common follow-ups: What typical latency is considered acceptable for transactional replication?;What actions can you take if a subscriber falls too far behind?
Query Optimization & Plans;SQL Server Agent & Job Scheduling
How would you troubleshoot a transactional replication setup where a subscriber has stopped receiving new changes entirely?
Advanced
You would check the status of the relevant replication agents, such as the log reader agent and distribution agent, in Replication Monitor or through their job history, review any error messages logged, verify network connectivity between the involved servers, and check whether a specific data change caused a replication conflict or error that halted the process entirely.
-- Check the job history for the distribution agent
EXEC sp_help_agent_default;
-- Review recent errors
SELECT * FROM MSrepl_errors ORDER BY time DESC;
Real-world example
A database administrator discovers that a subscriber stopped receiving updates due to a distribution agent job failing repeatedly because of a network timeout, resolving the issue by adjusting the agent's timeout settings and restarting the job.
Common follow-ups: What are the most common causes of a replication agent failing?;How do you safely reinitialize a subscription if it has fallen too far out of sync to recover normally?
SQL Server Agent & Job Scheduling;Error Handling with TRY CATCH
How does replication compare to Always On Availability Groups for keeping data synchronized across multiple servers, and when would you choose one over the other?
Advanced
Always On Availability Groups are designed primarily for high availability and disaster recovery, keeping an entire database synchronized for failover purposes, while replication is more flexible for selectively distributing specific tables or subsets of data to multiple locations, often for reporting or integration purposes, making the right choice depend heavily on your actual goal, whether that is failover protection or targeted data distribution.
-- Availability Groups replicate the entire database
-- Replication can selectively publish just specific tables
EXEC sp_addarticle @publication = 'SalesPublication', @article = 'Orders'; -- only this table
Real-world example
A company uses an Always On Availability Group for full database failover protection on their primary transactional database, while separately using transactional replication to distribute just a subset of reporting relevant tables to a dedicated analytics server.
Common follow-ups: Can replication and Always On Availability Groups be used together in the same environment?;What are the licensing differences between these two features?
Always On Availability Groups;SQL Server Architecture & Editions
What steps are involved in setting up transactional replication for the first time between a publisher and a new subscriber?
Intermediate
You configure a distributor if one does not already exist, create a publication on the source database specifying which articles, meaning tables or other objects, should be included, create a subscription on the target database pointing back to that publication, and then perform an initial synchronization, often through a snapshot, before ongoing transactional changes begin flowing automatically.
EXEC sp_addpublication @publication = 'SalesPublication';
EXEC sp_addarticle @publication = 'SalesPublication', @article = 'Orders';
EXEC sp_addsubscription @publication = 'SalesPublication', @subscriber = 'SubscriberServer';
Real-world example
A team sets up a brand new reporting subscriber for their sales publication, following the standard sequence of configuring the distributor, defining the publication and its articles, and finally creating the subscription before replication begins flowing.
Common follow-ups: What happens during the initial snapshot synchronization step?;How long does the initial setup typically take for a large publication?
SQL Server Architecture & Editions;Backup & Recovery