Interview Questions
1. What are types of replication?
- Snapshot replication – As the name implies snapshot replication takes a snapshot of the published objects and applies it to a subscriber. Snapshot replication completely overwrites the data at the subscriber each time a snapshot is applied. It is best suited for fairly static data or if it’s acceptable to have data out of sync between replication intervals. A subscriber does not always need to be connected, so data marked for replication can be applied the next time the subscriber is connected. An example use of snapshot replication is to update a list of items that only changes periodically.
- Transactional replication – As the name implies, it replicates each transaction for the article being published. To set up transactional replication, a snapshot of the publisher or a backup is taken and applied to the subscriber to synchronize the data. After that, when a transaction is written to the transaction log, the Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. Only committed transactions are replicated to ensure data consistency. Transactional replication is widely applied where high latency is not allowed, such as an OLTP system for a bank or a stock trading firm, because you always need real-time updates of cash or stocks.
- Merge replication – This is the most complex types of replication which allows changes to happen at both the
publisher and subscriber. As the name implies, changes are merged to keep data consistency and a uniform set of
data. Just like transactional replication, an initial synchronization is done by applying snapshot. When a transaction
occurs at the Publisher or Subscriber, the change is written to change tracking tables. The Merge Agent checks these
tracking tables and sends the transaction to the distribution database where it gets propagated. The merge agent has
the capability of resolving conflicts that occur during data synchronization. An example of using merge replication
can be a store with many branches where products may be centrally stored in inventory. As the overall inventory is
reduced it is propagated to the other stores to keep the databases synchronized.
Submit an interview question
Submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of w3Sniff.
Get Started
Comments
Leave a Comment