SQL Server 2005 Replication Services

SQL Server 2005’s replication services are a set of technologies used for copying and distributing data and database objects from one database to another and then synchronizing between databases to ensure that the data in each database is consistent and durable.

Note 

SQL Server 2005 can subscribe to a SQL Server 2000 publication, but not to a SQL Server 7.0 (version 7 of the database engine) publication. If you need to replicate from a version 7.0 database to a version 9.0 database, you need to get your data replicated to SQL Server 2000 first.

Microsoft, obsessed with the publishing business, has built the replications services around a publishing industry metaphor to represent the components and processes in a “replication topology.” The model is composed of the following components:

• Publisher   The Publisher is a SQL Server database that makes its data available to other databases that can receive from it. The Publisher can have more than one publication, and each publication can represent a logically related set of data. The publisher server is the source of the data to be replicated, but it is more than just a sender of data. It can also detect which data has changed during transactional replication, and it keeps track of all the publications it is responsible for.

• Distributor   The Distributor is a SQL Server database (possibly the same host as the publisher) that hosts the distribution database and stores history data, and/or transactions and metadata. The role of the Distributor varies, depending on which type of replication you implement. A remote Distributor is a SQL Server system that is maintained separate from the Publisher and is configured as a Distributor of replication. As mentioned, you can also have a server that is configured to be both a Publisher and a Distributor of replication data-a so-called local distributor.

• Subscribers   Subscribers are database servers that receive the replicated data. Subscribers subscribe to publications, and not to individual articles within a publication. They also subscribe only to the publications that they need, not all of the publications available on a Publisher, which means they get only the data they need to have, and not data from every database on the server. The Subscriber can also propagate data changes back to the Publisher or republish the data to other Subscribers. This depends on the replication option chosen, which allows the replication to be a two-way street.

• Publications   A publication is defined as a collection of one or more articles that originate from one database. This grouping of multiple articles makes it easier to specify a logically related set of data and database objects that you want to replicate as a unit.

• Articles   The article is not something you read in the New York Times magazine. It is a table of data, or a partition of data, or a database object that can be designated for replication. The article can be an entire table; a collection of columns (using the vertical filter) or certain rows (using the horizontal filter); a stored procedure or view definition; or the execution of a stored procedure, a view, an indexed view, or a user-defined function.

• Subscriptions   The subscription is a request for a copy of data or database objects to be replicated. A subscription defines what publication will be received, where it will be received, and when it will be received. Synchronization of the data distribution of a subscription can be requested either by the Publisher-in what is termed a push subscription-or by the Subscriber-in what is termed a pull subscription. A publication can support a concoction of both push and pull subscriptions.

There are also several replication “agency” processes that are responsible for copying and moving data between the Publisher and the Subscriber. These are the Snapshot Agent, Distribution Agent, Log Reader Agent, Queue Reader Agent, and Merge Agent. We’ll get to these shortly.