Replication Enhancements

SQL Server 2000 enhances snapshot replication, transactional replication, and merge replication by adding:

• Alternate snapshot locations, which provide easier and more flexible methods for applying the initial snapshot to Subscribers. You can save (and compress) the snapshot files to a network location or removable media, which can then be transferred to Subscribers without using the network.
  
  
• Attachable subscription databases, which allow you to transfer a database with replicated data and one or more subscriptions from one Subscriber to another SQL Server. After the database is attached to the new Subscriber, the subscription database at the new Subscriber will automatically receive its own pull subscriptions to the publications at the specified Publishers.
  
  
• Schema changes on publication databases, which allow you to add or drop columns on the publishing table and propagate those changes to Subscribers.
  
  
• On demand script execution, which allows you to post a general SQL script that will be executed at all Subscribers.
  
  
• Pre- and post-snapshot scripts, which allow you to run scripts before or after a snapshot is applied at the Subscriber.
  
  
• Remote agent activation, which allows you to reduce the amount of processing on the Distributor or Subscriber by running the Distribution Agent or Merge Agent on one computer while activating that agent from another computer. You can use remote agent activation with push or pull subscriptions.
  
  
• Support of new SQL Server features, which includes user-defined functions, indexed views, new data types, and multiple instances of SQL Server.
  
  
• The ActiveX Snapshot Control, which makes programmatic generation of snapshots easier.
  
  
• More snapshot scripting options, which support transfer of indexes, extended properties, and constraints to Subscribers.

Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the changes between sites when they are connected. Enhancements to merge replication include:

• Greater parallelism of the Merge Agent for improved server-to-server performance.
  
  
• Optimizations for determining data changes relevant to a partition at a Subscriber.
  
  
• Dynamic snapshots, which provide more efficient application of the initial snapshot when using dynamic filters.
  
  
• Vertical filters for merge publications.
  
  
• More powerful dynamic filtering with user-defined functions.
  
  
• The ability to use alternate synchronization partners when synchronizing data. Using alternate synchronization partners, a Subscriber to a merge publication can synchronize with any specified server that has the same data as the original Publisher.
  
  
• Automated management of identity ranges. In merge replication topologies where a publication contains an identity column, and where new rows can be inserted at Subscribers, automated management of identity ranges at the Subscriber ensures the same identity values are not assigned to rows inserted at different subscription databases, and that primary key constraint violations do not occur. This feature is also available when queued updating is used with snapshot replication or transactional replication.
  
  
• Support for timestamp columns in published tables.
  
  
• Improved management of the growth of merge tracking data.
  
  
• Several new merge replication conflict resolvers including interactive resolvers that provide a user interface for immediate, manual conflict resolution, priority based on a column value, minimum/maximum value wins, first/last change wins, additive/average value, and merge by appending different text values.
  
  
• New options to validate permissions for a Subscriber to upload changes to a Publisher (check_permissions) and security enhancements including code signing of conflict resolvers included with Microsoft SQL Server 2000.
  
  
• New COM interfaces that support heterogeneous data sources as Publishers within a SQL Server replication topology.
  
  
• Validation of replicated data per subscription or on a publication-wide basis. Validation is also available through SQL Server Enterprise Manager.
  
  
• Reinitialization to allow uploading of changes from the Subscriber before the application of a new snapshot.

For more information, see Merge Replication and Replication Options.

With transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers. Enhancements to transactional replication include:

• Concurrent snapshot processing so that data modifications can continue on publishing tables while the initial snapshot is generated.
  
  
• Improved error handling and the ability to skip specified errors and continue replication.
  
  
• Validation of replicated data at the Subscriber, including validation on vertical partitions. Validation is also available through SQL Server Enterprise Manager.
  
  
• Publishing indexed views as tables.
  
  
• The option to store data modifications made at the Subscriber in a queue (queued updating).
  
  
• The option to transform data as it is published to Subscribers (transforming published data).
  
  
• The ability to restore transactional replication databases without reinitializing subscriptions or disabling and reconfiguring publishing and distribution. You can also set up transactional replication to work with log shipping, enabling you to fail over to a warm standby server without reconfiguring replication. For more information, see Strategies for Backing Up and Restoring Transactional Replication.

For more information, see Transactional Replication.

Queued updating allows snapshot replication and transactional replication Subscribers to modify published data without requiring an active network connection to the Publisher.

When you create a publication with the queued updating option enabled and a Subscriber performs INSERT, UPDATE, or DELETE statements on published data, the changes are stored in a queue. The queued transactions are applied asynchronously at the Publisher when network connectivity is restored.

Because the updates are propagated asynchronously to the Publisher, the same data may have been updated by the Publisher or by another Subscriber and conflicts can occur when applying the updates. Conflicts are detected automatically and several options for resolving conflicts are offered.

For more information, see Queued Updating.

Transformable subscriptions (available with snapshot replication or transactional replication) leverages the data movement, transformation mapping, and filtering capabilities of Data Transformation Services (DTS).

Using transformable subscriptions in your replication topology allows you to customize and send published data based on the requirements of individual Subscribers, including performing data type mappings, column manipulations, string manipulations, and use of functions as data is published.

For more information, see Transforming Published Data.

There have been several improvements in SQL Server Enterprise Manager that provide for easier implementation, monitoring, and administration of replication. Enhancements to replication usability include:

• A centralized Replication folder in the SQL Server Enterprise Manager tree, which organizes all subscriptions and publications on the server being administered.
  
  
• The ability to browse for and subscribe to publications (when permission is allowed) using Windows Active Directory.
  
  
• The ability to see multiple Distributors in a single monitoring node in SQL Server Enterprise Manager.
  
  
• Standard and advanced replication options separated in the Create Publication, Create Push Subscription, and Create Pull Subscription Wizards. You can choose to show advanced options in these wizards on the Welcome page of each wizard.
  
  
• New wizards for creating jobs that create dynamic snapshots for merge publications that use dynamic filters (Create Dynamic Snapshot Job Wizard), and for transforming published data in snapshot replication or transactional replication (Transform Published Data Wizard).