Implementing data synchronization updates with SQL Server replication technology

The concept of SQL Server 2000 replication: A set of techniques for replicating and distributing data and database objects between databases and synchronizing them to ensure their consistency.

With replication, you can distribute data to different locations, over a local area network, using a dial-up connection, and distributing to remote or mobile users over the Internet. Replication also enables users to improve application performance by physically separating data based on how the data is used (for example, separating online transaction processing (OLTP) and decision support systems), or processing across multiple server distribution databases.

The basic elements of SQL replication include:

Publishers, Subscribers, distributors, publications, projects.

Publishing Server

A publisher is a server that provides data for replication to other servers. The publisher can have one or more publications, each of which represents a set of logically related data. In addition to specifying which of these data needs to be replicated, the Publisher detects data that has changed during transactional replication and maintains information about all publications on this site.

Distribution server

A Distributor is a server that hosts and stores historical data and/or transactions and metadata as a distribution database. The role of the distributor differs depending on the type of replication being performed. For more information, see Replication Types.

A remote Distributor is a server that is independent of the publisher and configured as a Distributor for replication. A local Distributor is a server that is configured both as a replication publisher and as a Distributor for replication.

Subscriber server

A Subscriber is a server that receives replicated data. Subscribers subscribe to publications that are not separate from publications, and subscribers subscribe only to the publications they want, not to all available publications on the publisher. Depending on the type of replication and the replication options you choose, Subscribers can also propagate data changes back to the publisher or redistribute data to other subscribers.

Release

A publication is a collection of one or more items in a database. This grouping of multiple items makes it easier to specify a set of logically related data and database objects to replicate together.

Project

An item is a data table, data partition, or database object that you specify to replicate. A project can be a complete table, a few columns (using a vertical filter), a few rows (using horizontal filters), a stored procedure or view definition, a stored procedure's execution, a view, an indexed view, or a user-defined function.

Subscription

A subscription is a request for a copy of a data or database object. The subscription defines when and where the publication and reception will be received. A subscription's synchronization or data distribution can be requested by the Publisher (push subscription) or subscriber (pull subscription). A publication can support a mix of push and pull subscriptions.

How SQL Replication works

SQL SERVER handles replication primarily with publications, subscriptions. The server on which the source data resides is the publishing server, responsible for publishing the data. The publisher copies copies of all changes to the published data to the Distributor Distributor, which contains a distribution database that receives all changes to the data and saves the changes and distributes the changes to subscribers.

SQL Server replication technology type, three replication technologies, respectively, are

1. Snapshot replication

2. Transactional replication

3. Merge replication