Understanding the Replication Options

Options available with the types of replication allow you more replication solutions and greater flexibility and control in your applications. Replication options are

• Filtering published data

• Publishing database objects

• Publishing schema objects

• Updatable subscriptions

• Transforming published data

• Alternate synchronization partners

Table 8–1 presents the replication options and the replication types that support them.

Table 8–1: Replication Options

Replication Option	Replication Type
Filtering Published Data	Snapshot, Transactional, Merge
Updatable Subscriptions (immediate queuing and updating)	Snapshot, Transactional
Updatable Subscriptions	Merge
Transforming Published Data	Snapshot, Transactional
Alternate Synchronization Partners	Merge, Transactional with Updatable Subscribers
Optimizing Synchronization	Merge

Filtering Published Data

Filtering data during replication allows you to publish only the data or partitions of data that are needed by the Subscriber. You can filter data and thus create partitions that include only columns and/or only rows that you specify for replication.

With all types of replication, you can choose to copy and distribute complete tables, or you can filter your data horizontally or vertically with static filters. The merge replication is especially strong in filtering options, and you can use dynamic filters to customize the filter to correspond to a property of the Subscriber receiving the data.

When you filter data horizontally, you have the option of publishing only the data that is needed. You can also partition data to different sites and avoid conflicts that arise out of the situation where subscribers are viewing and updating different subsets of data. You can also manage publications in accordance with user needs or applications.

You also have the option of using user-defined functions in your static and dynamic filters, and you can even make use of customized functions. Merge replication also lets you use join filters and dynamic filters. The join filters enable you to extend filters created on one table to another. Let’s say you are publishing customer data according to the state where the customer resides; you may want to extend that filter to the related orders and order details of the customers in a particular state. The dynamic filters, on the other hand, allow you to create a merge publication and then filter data from the publishing table. The filter value can be the user ID or a login retrieved through a T-SQL function.

Publishing Database Objects

You also have the option of publishing database objects, including views, indexed views, user-defined functions, stored procedure definitions, and the execution of stored procedures. You can include data and database objects in the same publication or in different publications. Publishing database objects is available with all types of replication- transactional, snapshot, and merger replication.

Publishing Schema Objects

You can also specify schema objects to be published. These might include such objects as declared referential integrity, primary key constraints, reference constraints, unique constraints, clustered indexes, nonclustered indexes, user triggers, extended properties, collations, and so on. You can also change destination table owner names and data formats to optimize for SQL Server 2005, SQL Server 2000, and non-SQL Server subscribers.

Updatable Subscriptions

An updatable subscription allows data at the Subscriber to be modified. Updatable subscriptions are possible with all three replication types, but the algorithms used differ from replication type to replication type. When using merge replication, data at the Subscriber is automatically updatable.

Specifically, the updatable subscription options available with snapshot replication and transactional replication allow you to make changes to replicated data at the Subscriber and propagate those changes to the Publisher and to other Subscribers. Such updatable subscription options include the ability to force immediate updating, queued updating, and immediate updating with queued updating as a fail-over.

Immediate updating also allows Subscribers to update data only if the Publisher will accept them immediately. This is a configuration option in the Publisher. If the changes are accepted at the Publisher, they are propagated to other Subscribers. The Subscriber must be continuously and reliably connected to the Publisher to make changes at the Subscriber, and such a reliable connection would be a dedicated and high-bandwidth WAN backbone.

Queued updating lets the subscribers modify data and store the modifications in a queue. The queue builds while the subscriber remains disconnected from the Publisher. When the Subscriber reconnects to the Publisher, the changes are propagated to the Publisher. If the Publisher accepts the changes, normal replication processes occur and the changes are propagated to other Subscribers from the Publisher. You can then store data modifications in a SQL Server 2005 queue or use Microsoft Message Queuing (MSMQ).

If you use immediate updating with the queued updating option, SQL Server lets you use immediate updating with the option of switching to queued updating if a connection cannot be maintained between the Publisher and the Subscribers. After switching to queued updating, reconnecting to the Publisher, and emptying the queue, you can switch back to immediate updating mode.

Transforming Published Data

A huge benefit of snapshot replication or transactional replication is the feature that lets you leverage the transformation mapping and scripting capabilities of SSIS. These can come into play big time when building a replication topology for almost any situation. Replication integrated with SSIS packages allows you to customize and distribute data according to the requirements of individual Subscribers. Your Subscriber, for example, might need to have different table names, different column names, or compatible data types.

By transforming the published data, you can filter data and simulate dynamic partitions of data so that data from one snapshot or transactional publication can be distributed to Subscribers that require different partitions of data. You also have the option of static partitions. These are created and filter separate publications for each Subscriber in accordance with the needs of each Subscriber.

Alternate Synchronization Partners

I alluded to the Alternate Synchronization Partners feature earlier. It allows Subscribers to merge publications to synchronize with servers other than the Publisher at which the subscription originated. This means that Subscribers can synchronize data even if the primary Publisher is unavailable; they essentially synchronize with alternate partners instead of the Publisher. This feature is also useful when mobile Subscribers connect to a faster or more reliable network connection that can give them access to an alternate Publisher, if the primary is inaccessible.