Home arrow SQL Server arrow Page 5 - Replication: SQL Server 2000 - Part 1

Replication: SQL Server 2000 - Part 1

In this first installment of a 2-part series, Mahesh describes and explains what Replication is, its features and benefits, and how you can put it to use.

Author Info:
By: Mahesh Kodli
Rating: 4 stars4 stars4 stars4 stars4 stars / 210
November 10, 2003
  1. · Replication: SQL Server 2000 - Part 1
  2. · Replication Benefits
  3. · SQL Server Platform for Replication
  4. · Entities for the SQL Server Replication Model
  5. · Entities Further Explained...
  6. · Implementing Replication
  7. · Implementing Replication, Cont'd

print this article

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

blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials