Export (0) Print
Expand All

Chapter 29 - Common Questions in Replication

This chapter provides answers to some common questions that may arise when you plan for Microsoft® SQL Server™ 2000 replication. These questions are by no means all of the issues that need be considered when planning or implementing replication. For more information about replication, see SQL Server Books Online.

Types of Replication and Replication Options 

  • What Type of Replication Should I Use? 

  • What Is the Difference Between Merge Replication and Updatable Subscriptions? 

  • Should I Use SQL Server Queues or Microsoft Message Queuing Services When Using Transactional Replication and Queued Updating? 

Implementing Replication 

  • What Is the Difference Between a Local Distributor and a Remote Distributor? 

  • What Type of Subscription Should I Use: Push or Pull? 

  • If I Am Using Pull Subscriptions, When Should I Specify Them as Anonymous? 

  • What Are the Advantages of Scripting Replication? 

  • Should I Apply the Snapshot Manually or Apply It Automatically? 

  • Can I Replicate Data Between SQL Server and Heterogeneous Databases? 

  • If I Am Using SQL Server 6.5 or SQL Server 7.0 Subscribers, Can I Use the New Features in SQL Server 2000? 

  • Can Microsoft SQL Server Desktop Edition Participate in Replication? 

  • When Upgrading to SQL Server 2000, Do I Need to Upgrade All Servers in Replication at the Same Time? 

Replication and Warm Standby Server Recovery Options 

  • Should I Use Replication, Log Shipping, or Clustering as a Failover Solution? 

  • Does Replication Work on a Cluster? 

Types of Replication and Replication Options

Cc917656.spacer(en-us,TechNet.10).gif Cc917656.spacer(en-us,TechNet.10).gif

When designing a replication topology, it is critical that you understand the types of replication and the options available. This will enable you to design a replication topology that will be appropriate for your business needs and environment.

What Type of Replication Should I Use?

The types of replication are transactional replication and merge replication. With any type, you need to determine if data modifications are going to occur and at which sites they are going to occur.

Use transactional replication when: 

  • Most changes for a given data set are made at one site (for example, the Publisher). With transactional replication, there is an owner with any given data, where most of the data modifications are made. With transactional replication options, data can be modified at other locations, but there is always one, primary owner of the data. 

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

  • Transactions meet the requirements of full ACID properties. 

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

  • When it makes more sense, or it is more practical, to replicate the whole data set, rather than incremental changes (for example, a lot of changes at one time); consider using snapshot replication. Snapshot replication is best used when: a small, whole set of data needs to be replicated; a large set of data where most of the data has been changed needs to be replicated; and when data is very static. 

Use merge replication when: 

  • Data modifications need to be made at multiple Subscribers at various times. 

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

  • Site autonomy is important.

  • You expect data modifications to occur at multiple sites and, therefore, conflicts to occur. Default and custom choices for conflict resolution are available with merge replication. 

  • You want to use join filters to maintain referential integrity between two tables. Join filters allow cross-table relationships to be used in merge replication filters when the filter of one table is based on another table in the publication.

  • You want to use dynamic filters that allow you to filter data from the publishing table that is providing different partitions of data to different Subscribers. Using dynamic filters allows for fewer publications to be stored at the Publisher. In addition, using dynamic filters enables you to filter criteria by using system or user-defined functions. The Subscriber receives only the information needed because the data is filtered based on the connection properties of the Merge Agent for the subscription.

What Is the Difference Between Merge Replication and Updatable Subscriptions?

With merge replication...

With snapshot replication and transactionalreplication with queued updating...

The replicated data is read and updated at either the Publisher or Subscriber.

The replicated data is mostly read-only at the Subscriber.

The Subscriber and Publisher may only be connected occasionally.

The Subscriber, Distributor, and Publisher may be connected most of the time or may only be connected occasionally, when data needs to be dequeued.

Conflicts caused by multiple updates to the same data are automatically handled and resolved.

Conflicts caused by multiple updates to the same data are infrequent but are resolved according to predetermined conflict resolution.

You need updates to be propagated on a row-by-row basis, and conflicts to be evaluated and resolved at the row level or according to a set of business rules, or with default or customized conflict resolution applications.

You need updates to be propagated on a transaction basis, and conflicts to be evaluated and resolved on a transaction basis (the entire transaction is either committed or rolled back).

Should I Use SQL Server Queues or Microsoft Message Queuing Services When Using Transactional Replication and Queued Updating?

Use SQL Server 2000 queues when: 

  • You need to work with SQL Server on various Microsoft Windows® platforms (Windows 98, Windows NT 4.0, and Windows 2000). 

  • No additional components need to be installed. 

Use Microsoft Message Queuing when: 

  • You are already working with Microsoft Message Queuing. 

  • You are working only with the Windows 2000 operating system. 

  • You intend to have a large number of queued updating subscriptions. Using Microsoft Message Queuing is more scalable than SQL Server queues. 

  • You need additional routing and centralized monitoring and centralized queue administration (beyond what is available with SQL Server queues). 

  • You need offline capabilities, such as propagating offline changes to the queue at the Distributor without SQL Server running on the Subscriber (but Microsoft Message Queuing must be running). 

  • You do not require availability of the Publisher when the Subscriber reconnects to the network after updating published data. 

Implementing Replication

Cc917656.spacer(en-us,TechNet.10).gif Cc917656.spacer(en-us,TechNet.10).gif

Implementing replication includes configuring publishing and distribution, creating publications and subscriptions, and applying the initial snapshot of data at the Subscriber.

What Is the Difference Between a Local Distributor and a Remote Distributor?

A local Distributor is a computer that is configured to be both a Publisher and a Distributor of replication. A remote Distributor is a computer that is physically separate from the Publisher and is configured as a Distributor of replication.

Use a local Distributor when: 

  • The computer running the Publisher has the capacity to also absorb the workload of the Distributor residing on the same computer.

  • Typically, merge replication topologies use a local Distributor because the distribution database has a limited role and does not grow large. With merge replication, the Distributor stores history information but it does not store transactions that are propagated during replication. For transactional replication, the transactions are stored temporarily in the distribution database, which requires more processing overhead on the server where the distribution database resides.

Use a remote Distributor when: 

  • You want to offload processing to another computer because of a decrease in performance, either due to an increase in the replication activity or due to constraints on the server or network resources. 

  • Remote Distributors are used more often in transactional replication because of the processing and storage requirements of the distribution database. In transactional replication, the distribution database temporarily stores transactions that are being propagated to other servers.

What Type of Subscription Should I Use: Push or Pull?

Use push subscriptions when: 

  • You want easier administration from a centralized location (the Distributor).

  • When Subscribers are always or almost always connected on the network. 

  • Data needs to be updated at the Subscribers whenever any changes are made at the Publisher. 

Use pull subscriptions when: 

  • Administration of the subscription will take place at the Subscriber. 

  • The publication has a large number of Subscribers (for example, Subscribers using the Internet), and when it would be too resource-intensive to run all the agents at one site or all at the Distributor. You can also use push subscriptions with remote agent activation to offload processing from the Distributor to the Subscriber. 

  • Subscribers are autonomous, disconnected, and/or mobile. Subscribers will determine when they will connect to the Publisher/Distributor and synchronize changes.

If I am Using Pull Subscriptions, When Should I Specify Them as Anonymous?

Specify a pull subscription as anonymous when: 

  • Applications have a large number of Subscribers. 

  • You do not want the overhead of maintaining extra information at the Publisher or Distributor. 

  • Subscribers use the Internet to access data and you do not want to track information about these Subscribers. The necessary privileges and security are still in place and Subscribers are using anonymous subscriptions to have the appropriate permissions.

If you specify that anonymous subscriptions be allowed, SQL Server will generate new snapshots on a regular basis, which can be kept available for new Subscribers. This may be a consideration if generating the snapshot is resource intensive.

What are the Advantages of Scripting Replication?

You can script commonly performed replication functions, such as configuring publishing and distribution, and creating or deleting publications and subscriptions. After you configure or create a replication component, you can automate the creation of a script by using SQL Server Enterprise Manager.

The script contains the Transact-SQL system stored procedures necessary to implement the replication component. Composed primarily of a series of stored procedures, you can view, execute, and/or modify and run the script using SQL Query Analyzer or the osql utility.

You can choose to script the creation or deletion of one or a combination of the following:

  • Distributor properties 

  • Publications and push subscriptions 

  • Pull subscriptions

If you need to delete multiple push subscriptions or a mix of push and pull subscriptions, you can automate the process by creating a script to delete the publication. All subscriptions to the publication will be deleted with the publication. If you are deleting pull subscriptions, you can generate a script that deletes one or more pull subscriptions without deleting the publication.

The number of Subscribers and the number of subscriptions needed at each Subscriber determine whether to script replication or implement replication using SQL Server Enterprise Manager.

Additionally, scripting can aid in recovery from a failure, and it can be used to clone the replication environment.

Should I Apply the Snapshot Manually or Apply It Automatically?

Applying the snapshot automatically saves time because SQL Server propagates the necessary data and schema files to the Subscriber. Apply the snapshot automatically if:

  • You have a reliable and fast network connection. 

  • The publication and the articles within it are of reasonable size. 

  • You want the convenience of SQL Server automatically applying the snapshot. 

Applying the snapshot manually involves saving the snapshot to removable media or to an accessible network location and transferring it to Subscribers. Apply the snapshot manually if:

  • The publication is large. It may be more efficient to load the snapshot manually from a compact disc, or other storage device.

  • You are transferring a large amount of data over the network, or if you have a slow link. 

  • The data already exists at the Subscriber.

When you apply the snapshot manually for transactional publications, custom stored procedures for each article must be created at the Subscriber.

Another way to apply the snapshot manually at Subscribers is to use attachable subscription databases. The attachable subscription databases feature allows you to transfer a database with published data and subscriptions from one Subscriber to another. After the database is attached to the new Subscriber, the database at the new Subscriber will automatically receive its own pull subscriptions to the publications at those Publishers.

For additional considerations when applying the snapshot manually, see "Applying the Initial Snapshot" and "Using Custom Stored Procedures in Articles" in SQL Server Books Online.

Can I Replicate Data Between SQL Server and Heterogeneous Databases?

Methods for implementing snapshot replication or transactional replication published by heterogeneous data sources to your SQL Server 2000 applications are:

  • Building applications with SQL-DMO and the Replication Distributor Interface 

  • Using third-party tools

Microsoft SQL Server 2000 provides a programming framework that enables heterogeneous data sources to become Publishers of snapshot and transactional publications within the SQL Server 2000 replication framework. You can use the Replication Distributor Interface with programmable SQL-DMO objects and third-party tools to publish data incrementally from heterogeneous Publishers.

Several vendors, including BMC and DataMirror, have developed products that support the replication distribution interface included with SQL Server 2000. Using these third-party tools, it is possible to propagate data directly from Oracle or DB2 into a SQL Server distribution database. The Replication Distributor Interface is a public interface that allows the development of custom applications that write transactions and commands to a SQL Server distribution database from virtually any other data source, and then take advantage of SQL Server replication distribution services including the replication monitor in SQL Server Enterprise Manager.

To view a programming sample using the Replication Distributor Interface, see "Programming Snapshot or Transactional Replication from Heterogeneous Data Sources" in SQL Server Books Online.

There is no method within SQL Server 2000 for heterogeneous data sources to publish merge replication data to SQL Server; however, you can use third-party solutions for this type of replication.

If I Am Using SQL Server 6.5 or SQL Server 7.0 Subscribers, Can I Use the New Features in SQL Server 2000?

Features available in SQL Server 2000 are not supported with Subscribers running SQL Server version 7.0 or earlier; however, backward compatibility is assured when you replicate with earlier versions of SQL Server.

For example, if a merge publication contains features valid only in SQL Server 2000, and you use a push subscription to a Subscriber running SQL Server 7.0, backward compatibility is checked, and the Merge Agent will fail and display an error message indicating that the Subscriber does not meet the compatibility level.

If a transactional publication contains features valid only in SQL Server 2000, and you use a push subscription to a Subscriber running SQL Server 7.0, backward compatibility is not checked, and the Distribution Agent may fail with an error message not related to backward compatibility, or the Distribution Agent may succeed, but transactional processing will fail at another point.

If you upgrade a Distributor to an instance of SQL Server 2000, but you have a Publisher running an instance of SQL Server version 7.0, and a Subscriber running an instance of SQL Server version 6.5, you are limited to the replication functionality of SQL Server 6.5 and unable to use features introduced in SQL Server 7.0 or SQL Server 2000. To use the new functionality, upgrade all servers used for replication to SQL Server 2000.

If a publication has active subscriptions to Subscribers running SQL Server version 7.0 or earlier, and you add a feature to the publication that is valid only for SQL Server 2000, the Merge Agents or Distribution Agents for the SQL Server 7.0 subscriptions will fail. Even if the SQL Server 2000 feature is installed, the agents will not run successfully. You must delete the subscription and re-create the publication and subscription.

Can Microsoft SQL Server Desktop Engine Participate in Replication?

Yes, Microsoft SQL Server Desktop Engine can participate in replication. A Desktop Engine installation can be a Subscriber to any type of publication: snapshot , transactional, or merge. An instance of the Desktop Engine can also be a Publisher for snapshot replication or merge replication, but not for transactional replication. A Desktop Engine installation that is a Publisher must use a local Distributor (the Publisher must also be the Distributor and host the distribution database for any publications it creates).

When Upgrading to SQL Server 2000, Do I Need to Upgrade All Servers in Replication at the Same Time?

When upgrading to SQL Server 2000, you can upgrade servers in your organization one at a time; however, when servers are used for replication, you must upgrade the Distributor first, the Publisher second, and then Subscribers. Upgrading servers one at a time following this sequence is recommended when a large number of Publishers and Subscribers exist because you can continue to replicate data even though servers are running different versions of SQL Server. You can create new publications and subscriptions with servers running instances of SQL Server 2000, and still maintain subscriptions created in SQL Server 6.5 or SQL Server 7.0.

When using transactional replication, you can upgrade Subscribers before the Publisher. If you are using immediate updating with snapshot replication or transactional replication, there are additional upgrade recommendations in "Upgrading and Immediate Updating" in this chapter.

You can upgrade replication servers running SQL Server 6.5 or SQL Server 7.0 to SQL Server 2000. If the server is running SQL Server 6.5, you do not need to upgrade it to SQL Server 7.0 before upgrading to SQL Server 2000.

For more information, see "Replication and Upgrading" in SQL Server Books Online.

Replication and Warm Standby Server Recovery Options

Cc917656.spacer(en-us,TechNet.10).gif Cc917656.spacer(en-us,TechNet.10).gif

Although replication is not an out-of-the-box solution as a warm standby server, it can play a role in the need for a warm standby server. If you want to make replication part of a disaster recovery solution, account for the following:

  • Retarget applications to the subscription database in the event of a Publisher failure.

  • Remember that only data is replicated. Actions such as schema changes (beyond add/drop column) and security changes are not replicated. 

  • Have a solution to failback from the Subscriber to the Publisher when the Publisher comes back online.

SQL Server 2000 transactional replication can be configured to work with log shipping as a warm standby server recovery option if the Publisher fails.

SQL Server 2000 merge replication can be configured to work with log shipping to provide a warm standby server recovery option if the Publisher fails. Merge replication also allows Subscribers to synchronize with an alternate Publisher in the event the Publisher at which their subscriptions originated is unavailable.

For more information about using transactional replication or merge replication with log shipping, see SQL Server Books Online.

Should I Use Replication, Log Shipping, or Clustering as a Failover Solution?

  • Use SQL Server clustering when you want a hot, standby, failover support. Clustering offers automatic detection of server failure and automatic failover between nodes participating in the cluster. Client application connections can automatically be redirected to the current active server instance and application data requests will be serviced with little or no interruption.

  • Use log shipping when you want a warm standby solution. Log shipping transfers database dumps and logs from the primary server to the secondary server. As with any warm standby solution, failure detection and failover processing is not automated. When log shipping is employed, the standby database cannot be used for data access. The standby database is unavailable throughout the continual process of loading database and transaction log dumps from the primary server. Because log shipping transfers complete database logs, all logged schema and data changes are transferred from the primary to secondary node. When log shipping is employed, it is not possible to partition the data or transfer only a subset of the objects from the database.

  • Use replication when you want a warm standby solution, and when simultaneous data access on the secondary node (scale out for read) or object or data partitioning is a requirement. 

Clustering is a shared disk, hardware solution protecting against computer failure. The cluster computers are usually in close proximity to one another. Log shipping and replication provide geographically disparate copies of the database. A combination of these solutions can be used to provide protection against different types of failure.

Does Replication Work on a Cluster?

Yes, all types of SQL Server replication work on a cluster server running Microsoft SQL Server 2000 Enterprise Edition. For information about setting up clusters, see "Failover Clustering" in SQL Server Books Online. For more information about configuring replication, see SQL Server Books Online.

Cc917656.spacer(en-us,TechNet.10).gif

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft