Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features

Three types of replications are available with SQL Server 2005: snapshot replication, transactional replication, and merge replication. SQL Server 2005 also permits a variation of the transactional replication allowing updatable subscriptions. Here the Publisher streams transactions to SQL Server Subscribers after they receive an initial snapshot of the published data. However, transactions that originate at the Subscriber are replicated back to the Publisher. Each replication option has its strengths and weaknesses; however, each is suitable only for a specific reason.

Snapshot Replication

Snapshot replication is the process of copying and distributing data and database objects in the exact state they appear in at a certain moment in time. Snapshot replication does not require continuous monitoring of changes because changes made to published data are not propagated to the Subscriber incrementally; rather, they are periodically replicated in a single effort. The subscribers are thus replicated to with every complete refresh of the dataset. They are not replicated to on a transaction-by-transaction basis.

Snapshot replication can take longer to propagate data modifications to Subscribers than the other forms of replication because the snapshot replication replicates an entire dataset at one point in time. Snapshots are typically replicated less frequently than other types of publications. Once a day in the evening is a good time to engage in snapshot replication. A data warehouse or a hot stand-by server is well suited to obtain snapshot replication data because the data does not need to be propagated to the replication database on the heel of every transaction. However, the dataset should be small because trying to replicate petabytes of data at one time might cause an entire city to cave in on itself as you suck up the entire neighborhood’s Internet access getting data from A to B.

Options available with snapshot replication allow you to filter published data and allow Subscribers to make modifications to replicated data and propagate those changes to the Publisher and then to other Subscribers. This filtering mechanism thus lets you transform data as it is published. Snapshot replication is useful for

• Data that is mostly static (seldom changes), such as warehouse data replicating to a data mart

• Cases in which it is acceptable to have copies of data that are out of date for a certain period of time

• Small volumes of data

• Sites that are often disconnected, where 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

With transactional replication, an initial snapshot of data is propagated to Subscribers, so that the subscriber has what is called an initial load, something to start with. Then when data modifications are made at the Publisher, these individual transactions are immediately captured and replicated to the Subscriber.

Under transactional replication, SQL Server 2005 monitors INSERT, UPDATE, and DELETE statements, as well as changes to stored procedure executions and indexed views. It stores the transactions affecting replicated objects and then propagates those changes to Subscribers continuously or at scheduled intervals. Transaction boundaries are preserved. If, for example, 100 rows are updated in a transaction, either all 100 rows of the transaction are propagated or none of them are. When all changes are propagated, all Subscribers will have the same values as the Publisher.

Options available with transactional replication allow you to filter published data and allow users at the Subscriber to make modifications to replicated data and propagate those changes to the Publisher and to other Subscribers. You can also transform data as it is published.

Transactional replication is typically used when

• You want data modifications to be propagated to Subscribers, often within seconds of when they occur.

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

• Subscribers are connected to the Publisher much of the time.

• Your application will not tolerate high latency for Subscribers receiving changes.

Merge Replication

Merge replication allows a collection of sites to work autonomously, online or offline. The data is then merged-with updates and insertions made at multiple sites replicated into a single, uniform result at a future time. The initial snapshot is applied to Subscribers as the initial load. Then SQL Server 2005 tracks the changes to the published data at the Publisher and at the Subscribers. The data is synchronized between servers at a predetermined or scheduled time or on demand using rowguid values (32-bit GUIDs) in an identity column that is added to each database table. Updates are then made independently (with no commit protocol) at more than one server. This means that the same data may be updated by the Publisher or by more than one Subscriber, and thus conflicts can occur when data modifications are merged.

If merge replication can introduce conflicting data, then why use it? You would use merge replication when

• Multiple subscribers need to update data at various times and the data has to be propagated to the Publisher and to other Subscribers.

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

• The application latency requirement is either high or low.

• Site autonomy is critical.

Merge replication requires diligence on the part of the DBA. It includes default and custom choices for conflict resolution that you define when you configure a merge replication solution. So when a conflict occurs, a resolver is invoked by the Merge Agent to determine which data will be accepted and propagated to other data centers.

You have several options available to you when you configure merge replication. These include the following:

• Filtering published data horizontally and vertically (rows or columns), including join filters and dynamic filters

• The ability to use alternate synchronization partners

• The ability to optimize synchronization to improve merge performance

• The ability to validate replicated data to ensure synchronization

• The ability to use attachable subscription databases