Introducing Replication Options

SQL Replication

Replication

Introducing 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
Filtering Published Data

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

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

Filtering data horizontally allows you to publish only the data that is needed, partition data to different sites, avoid conflicts (because Subscribers will be viewing and updating different subsets of data), and manage publications based on user needs or applications.

Additionally, you have the option of employing user-defined functions in your static and dynamic filters and leveraging the power of customized functions.

Merge replication provides the added functionality of join filters and dynamic filters. Join filters enable you to extend filters created on one table to another. For example, if you are publishing customer data based on 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. Dynamic filters allow you to create a merge publication and then filter data from the publishing table.. The filter value can be the user ID or login retrieved based on a Transact-SQL function, such as SUSER_SNAME() or HOSTNAME().

Publishing Database Objects

You can publish 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 (snapshot replication, transactional replication, and merge replication).

Publishing Schema Objects

In addition to database objects, you can also specify if you want schema objects to be published such as declared referential integrity (primary key constraints, reference constraints, unique constraints), clustered indexes, nonclustered indexes, user triggers, extended properties, and collation. You can also change destination table owner names and data formats to optimize for SQL Server 2000 or heterogeneous Subscribers.

Updatable Subscriptions

Data at the Subscriber can be modified if you use merge replication or if you use snapshot replication or transactional replication with an updatable subscription option.

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. Updatable subscription options include immediate updating, queued updating, and immediate updating with queued updating as a failover.

Immediate updating allows Subscribers to update data only if the Publisher will accept them immediately. 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.

Queued updating allows Subscribers to modify data and store those data modifications in a queue while disconnected from the Publisher for a period of time. 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 store data modifications in a SQL Server 2000 queue or use Microsoft Message Queuing.

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

When using merge replication, data at the Subscriber is automatically updatable.

Transforming Published Data

With snapshot replication or transactional replication, you can leverage the transformation mapping and scripting capabilities of Data Transformation Services (DTS) when building a replication topology. Replication integrated with DTS allows you to customize and distribute data based on the requirements of individual Subscribers. For example, a Subscriber might need to have different table names, column names, or compatible data types.

By transforming 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. With static partitions, you need to create and filter separate publications for each Subscriber based on the needs of the Subscriber.

Alternate Synchronization Partners

Subscribers to merge publications can synchronize with servers other than the Publisher at which the subscription originated. Synchronizing with alternate partners allows Subscribers to synchronize data even if the primary Publisher is unavailable. This feature is also useful when mobile Subscribers have access to a faster or more reliable network connection with an alternate Publisher.

See Also

Alternate Synchronization Partners

Filtering Published Data

Merge Replication or Updatable Subscriptions

Publishing Data and Database Objects