SQL Server 2000 makes many improvements to replication, its service for automatically copying data among database servers. Replication allows companies to distribute or roll up information across servers (e.g., collect financial data from branch offices for central reporting) and to support mobile users who need offline database access (e.g., sales reps who take orders on a laptop). SQL Server provides numerous options for replication, which administrators choose among depending on how many servers contribute replicated data, the volume of data, how frequently the data change, and the quality of connections among servers.

Replication Background

SQL Server replication copies a replicated data set (called a publication) from a central server (the publisher) to a set of subscriber servers. Initially, the subscriber receives a file called a snapshot with a complete copy of the publication. Thereafter, publishers update subscribers’ copies by one of three methods: send entire database copies (snapshot replication), send a list of transactions that ran against the database since the last update (transactional replication), or send changed database rows (merge replication).

SQL Server also allows subscribers to update the publisher's copy of a publication, making replication two-way. In snapshot and transactional replication, subscribers can execute a transaction that simultaneously updates both the publisher's copy and the subscriber's (immediate updating). In merge replication, subscribers can update their copies of a publication and then send those changes to the publisher during synchronization. Merge replication can produce conflicts; for example, a subscriber and the publisher might set a field to two different values. SQL Server resolves these conflicts with an administrator-determined conflict resolution policy (for example, accept all subscriber changes).

Highlights

SQL Server 2000 includes too many new replication features to list here; the major improvements simplify setup of new subscribers, simplify maintenance of publications, and make two-way replication more robust and flexible.

Simplified Setup

SQL Server 2000 provides several features to speed setup of replication on new subscribers.

Attachable subscription databases. In SQL Server 2000, administrators can set up a new subscriber by copying subscription information from an existing subscriber. In previous versions, this required manual work by an administrator, or a fairly complex script.

Better snapshot generation and delivery. SQL Server 2000 locks much less data than previous versions when generating snapshots, which reduces its impact on the publisher’s performance. Subscribers can load snapshots from local file systems and CD-ROMs rather than over the network from a distributor server as in previous versions, and snapshot files can be compressed to reduce download time and disk space.

Reducing Maintenance Work

SQL Server 2000 provides several features that make it easier for administrators to maintain replicated publications, including the following:

Column add and delete. Administrators can add or delete columns in a published table rather than delete the publication, change the table, and then re-create the publication, as previous versions required. However, SQL Server 2000 still does not support other kinds of schema changes (e.g., changing a column's data type) for replicated data.

Replicated synchronization scripts. A publication can include a script that's replicated from the publisher to subscribers and then runs on each subscriber when it synchronizes. Such scripts are useful for housekeeping chores such as rebuilding indexes.

Improvements to Two-Way Replication

SQL Server 2000 makes many improvements to two-way replication, including a new form of replication called queued updating.

Queued updating. Earlier versions of SQL Server allow snapshot and transactional subscribers to update their own copies of a publication only if they immediately update the publisher's as well. This requires the publisher to be online and connected to the subscriber. SQL Server 2000, in contrast, allows subscribers to update data in a publication at any time. If the publisher is unavailable, the update transaction is stored in a queue and run on the publisher later.

More merge conflict resolution policies. SQL Server 2000 introduces new merge conflict resolution choices, which determine how to reconcile conflicts between changes made by a subscriber and a publisher. For example, SQL Server 2000 can ask users to resolve conflicts interactively, it can resolve conflicts in favor of the most recently changed version of a publication, or it can sum or average conflicting values in numeric fields.

Identity column management. For both queued updating and merge, SQL Server 2000 can manage identity columns to avoid conflicts. Identity columns are special database columns that are used to automatically assign unique IDs to data items; for example, a sales application might use an identity column to automatically assign new unique customer IDs. With previous versions of SQL Server, all identity column values must be assigned by the publisher, to ensure they are unique. SQL Server 2000 removes this restriction, allowing subscribers to assign identity values without conflict even when disconnected from the publisher.

Back to associated article: SQL Server 2000 Steers Toward Enterprise