Replication is a server-based tool that you can use to synchronize data between two or more databases. Replication can send data from one SQL Server instance to another
There are 3 types of Replication
Snapshot Replication
Snapshot replication is also known as static replication. Snapshot replication copies and distributes data and database objects exactly as they appear at the current moment in time.
Characteristics of Snapshot Replication
- The changes to data at the subscriber are not updated to the subscriber continuously
- Subscribers are updated with complete modified data and not by individual transactions
- Propagating the changes to the subscribers takes more time as it is a one time process or scheduled process.
When Do I use Snapshot Replication?
Following are some of the scenarios where snapshot replication fits in ideally:
- Data/Db objects are static or do not change frequently
- Replicate Look Up tables that do not change frequently
- The amount of data to be replicated is small
- Users often work in disconnected mode, and are not always interested in the latest data.
Transactional Replication
Transactional replication is also known as dynamic replication. In transactional replication, modifications to the publication at the publisher are propagated to the subscriber incrementally.
Characteristics of Transactional Replication
- Publisher and the subscriber are always in synchronization.
- Transaction boundaries are preserved; i.e. if there are modifications to 5 rows of data, either all the 5 modified rows are propagated to the subscriber or none are propagated.
- The publisher and the subscriber should always be connected.
When do I use Transactional replication?
- Replicating Database with rollup information, Database with regional, central sales or inventory database that is updated and replicated to different sites.
- Subscribers always need the latest data for processing.
Merge Replication
Merge replication provides advantages of both Snapshot replication and Transactional replication. The initial snapshot applied to the subscribers and then SQL Server tracks changes to the data at publisher and subscriber levels. The data is synchronized on a scheduled basis or on demand. Since data modifications are made independently at publisher and subscriber levels, conflicts are likely to occur during synchronization.
Characteristics of Merge Replication:
- Updates to the data are made independently at more than one server.
- Data is merged on a scheduled basis or on demand.
- Allows users to work online/offline and synchronize the publisher and subscriber on a scheduled basis or on demand.
When Do I use Merge Replication?
- Site autonomy is very critical.
- Multiple subscribers need to update the data either at the same time or at different times and propagate the changes to the publisher
Entities for the SQL Server Replication Model
Publisher
Publisher is a server that makes the data available for subscription to other servers. In addition to making data available for replication, a publisher also identifies what data has changed at the subscriber during the synchronizing process. Depending on the type of replication, changed data is identified at different instances.
Distributor
Distributor maintains the Distribution Database. The role of the distributor varies depending on the type of replication. Two types of Distributors are identified: Remote distributor and Local distributor. Remote distributor is separate from publisher and is configured as a distributor for replication. Local distributor is a server that is configured as a publisher and a distributor.
Agents
Agents are the processes that are responsible for copying and distributing data between Publisher and subscriber. There are different types of Agents supporting different replication types.
Subscriber
Subscriber is a server that receives and maintains the published data. Modifications to the data at the subscriber-level can be propagated back to the publisher; in some cases Subscriber may re-publish the data to the other subscribers.
Articles
An article can be any database object, viz. Tables (Column filtered or Row filtered), Views, Indexed views, Stored Procedures, User defined functions.
Publication
Publication is collection of articles.
Subscriptions
Subscription is a request for copy of data or database objects to be replicated.
Subscription Types
Changes to the subscriptions at the publisher can be replicated to subscribers via PUSH subscription or PULL subscription.
With Push subscription the publisher is responsible for synchronizing all the changes to the subscriber without the subscriber asking for those changes.
With Pull subscription the subscriber initiates the replication instead of the publisher.
In fact, if you are not a DBA, you will hardly do something like this.
