Replication: SQL Server 2000 - Part 1 - Entities Further Explained...

(Page 5 of 7 )

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.

Replication Types

Microsoft SQL Server supports the following types of replication:

• Snapshot Replication
• Transactional Replication
• Merge 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

Next: Implementing Replication >>
 

More SQL Server Articles
More By Mahesh Kodli

Comments On: Replication: SQL Server 2000 - Part 1

· Perfect way of explaining.ThanksNarendar Piryani    · Such a beautiful way of explaining the whole repliation topic.Great Post!!!   >>> Post your comment now!