Replication

By UCertify Team

Replication is a process that copies and distributes data and database objects from one database to another and then synchronizes information between the databases for consistency.



SQL Server 2000 replication is a set of solutions that allow the copying, distribution, and modification of data across an enterprise. SQL Server 2000 includes several methods and options for replication design, implementation, monitoring, and administration that provide the functionality and flexibility needed for distributing data and maintaining data consistency.



Replication can be used to distribute data to different locations and to remote or mobile users over a local area network, a dial-up connection, and the Internet. Replication can also be used to enhance application performance, physically separate data based on how it is used (e.g., to separate online transaction processing (OLTP) and decision support systems), or distribute database processing across multiple servers.



Replication provides several benefits depending on the type of replication and the options chosen. However, the most common benefit that replication provides is the availability of data when and where it is needed. Replication allows multiple sites to keep copies of the same data. This is useful when multiple sites need to read the same data or need separate servers for reporting applications.


Replication also allows users to work with copies of data while disconnected from the database and to later propagate the changes made to other databases when they get connected. Replication can also be used as a choice for standby server strategy.


Replication Model


SQL Server 2000 replication uses a publishing industry metaphor to represent the components and processes involved in a replication topology. The replication model is composed of the following components:


Publisher


The Publisher is a server that makes data available for replication to other servers. The Publisher can have one or more publications, each of which represents a logically related set of data. It detects which data has changed during transactional replication and maintains information about all publications at the site.

Distributor

The Distributor is a server that hosts the distribution database and stores history data, and/or transactions and meta data. The Distributor can perform different tasks depending on which type of replication has been implemented.



The Distributor can be a local Distributor or a remote Distributor. A local Distributor is a server that is configured to be both a Publisher and a Distributor of replication. However, a remote Distributor is a server that is separate from the Publisher and is configured as a Distributor of replication.

Subscribers

Subscribers are servers that receive the replicated data. Subscribers subscribe to publications, not to individual articles within a publication. Also, they subscribe only to the required publications, not all of the publications available on the Publisher. Depending on the type of replication and replication options chosen, the Subscriber can propagate data changes back to the Publisher or republish the data to other Subscribers.

Publication

A publication is a collection of one or more articles from a database. Grouping multiple articles together makes it easier to specify a logically related set of data and database objects that need to be replicated together.

Article

An article is a table, a partition of a table, or a database object that is specified for replication. An article can be an entire table, certain columns of the table (using a vertical filter), certain rows of the table (using a horizontal filter), a stored procedure or view definition, the execution of a stored procedure, a view, an indexed view, or a user-defined function.

Subscription

A subscription is a request for a copy of data or database objects that are to be replicated. A subscription defines what publication will be received, where, and when. Synchronization or distribution of a subscription can be requested either by the Publisher (a push subscription) or by the Subscriber (a pull subscription). Push and pull subscriptions are the two types of subscription. A push subscription is a subscription created and administered at the Publisher. A pull subscription is a subscription created and administered at the Subscriber. A publication can support a mixture of push and pull subscriptions.

Types of Replication

SQL Server 2000 supports the following three types of replication:

Snapshot Replication

Snapshot replication is the process of copying and distributing data and database objects exactly as they appear at a point in time. As changes made to the published data are not propagated to the Subscriber incrementally, snapshot replication does not require continuous monitoring of data changes. In snapshot replication, Subscribers are updated with a completely refreshed data set and not individual transactions. Since snapshot replication replicates an entire data set at a time, it takes a long time to propagate data changes to Subscribers. Snapshot publications are typically replicated less frequently than other types of publications.



Options available with the snapshot replication allow the filtering of published data and the transformation of data as it is published. They also allow the Subscribers to make modifications to the replicated data and propagate the changes to the Publisher and other Subscribers.

Snapshot replication is used when:

Data is mostly static and changes rarely.

It is acceptable to have copies of data that are obsolete for a period of time.

Replicating small volumes of data.

Sites are mostly disconnected and high latency (the amount of time between when data is updated at one site and when it is updated at another) is acceptable.

Transactional Replication

In transactional replication, an initial snapshot of data is propagated to Subscribers, and when data changes are made at the Publisher, the individual transactions are replicated and propagated to the Subscribers.

In transactional replication, a primary key is required because each transaction is replicated individually. SQL Server 2000 stores the transactions affecting replicated objects and propagates the changes to the Subscribers continuously or at scheduled intervals.

Transactional replication is typically used when:

Data modifications need to be propagated to Subscribers often within seconds of when they occur.

The transactions need to be atomic (either all or none applied at the Subscriber).

Subscribers are mostly connected to the Publisher.

High latency is not acceptable.

Merge Replication

Merge replication allows various sites to work independently (online or offline) and merge data modifications made at multiple sites into a single, uniform result later on. The initial snapshot is applied to Subscribers and SQL Server 2000 tracks changes made to the published data at the Publisher and at the Subscribers. The data is synchronized between Publisher and the Subscribers either at a scheduled time or when demanded.



The data to be replicated are updated independently (i.e., without no commit protocol) at multiple servers. As a result, the same data may have been updated by the Publisher or by more than one Subscriber. Therefore, conflicts may occur when data modifications are merged.



Merge replication includes default and custom choices for conflict resolution that can be defined while configuring a merge publication. When a conflict occurs, the Merge Agent invokes a resolver to determine which data will be accepted and propagated to other sites.



Merge replication provides options for filtering published data horizontally and vertically, including dynamic filters and join filters, using alternate synchronization partners, optimizing synchronization to improve merge performance, validating replicated data to ensure synchronization, and using attachable subscription databases.



Merge replication is useful when:

Multiple Subscribers need to update data at different times and propagate the changes to the Publisher and to other Subscribers.

Subscribers need to receive data, make changes offline, and synchronize changes later with the Publisher and other Subscribers.

The application latency requirement is either high or low.

Site autonomy is critical.

About the Author: uCertify was formed in 1996 with an aim to offer high quality educational training software and services in the field of information technology to its customers. uCertify provides exam preparation solutions for the certification exams of Microsoft, CIW, CompTIA, Oracle, Sun and other leading IT vendors. To know more about uCertify, please visit http://www.ucertify.com/


Source: www.isnare.com