Considerations for All Types of Replication

  • Makale

This section of the documentation includes areas you should take into consideration before implementing replication, such as identity columns, constraints, and data types. This topic covers areas that affect all types of replication, and the following topics provide additional information:

In addition to these topics, the topic for each replication feature describes considerations for implementing the feature.

SQL Server Agent

Replication uses stand-alone programs, called agents, to perform the tasks associated with tracking changes and distributing data. By default, replication agents run as jobs scheduled under SQL Server Agent. SQL Server Agent must be running for the jobs to run. By default, the SQL Server Agent service is disabled when SQL Server 2005 is installed unless you explicitly choose to autostart the service during installation. For more information about starting the SQL Server Agent service, see Starting SQL Server Agent.

Security Considerations

It is important to understand how to secure a replication environment to protect the data and business logic in your application. Fundamentally, securing your replication environment is a matter of understanding your authentication and authorization options, understanding appropriate uses of replication filtering features, and learning specific measures for securing each piece of the replication environment (including the Distributor, Publisher, Subscribers, and the snapshot folder). For more information, see Security Considerations for Replication.

Administrative Considerations

After you have configured replication, it is important to understand how to administer a replication topology. For information about best practices for administering replication, see Best Practices for Replication Administration. This topic provides basic best practice guidance in a number of areas with links to more information for each area. In addition to following the best practice guidance presented in this topic, consider reading through the frequently asked questions topic to acquaint yourself with common questions and issues: Frequently Asked Questions for Replication Administrators.

Network and Performance Considerations

There are a number of performance-related best practices to follow in areas such as:

  • Server and network hardware
  • Database design
  • Distributor configuration
  • Publication design and options
  • Filter design and use
  • Subscription options
  • Snapshot options
  • Agent parameters
  • Maintenance

For more information, see Enhancing Replication Performance.

If you are replicating over a slow connection, the profiles for the agents involved in replication can be customized. The configuration options vary with the particular agent whose profile is being configured. For more information, see Replication Agent Profiles and A Slow Network Is Causing Problems.

Network speed is often the most important issue when applying the initial snapshot. The volume of incremental data changes might be low, but the volume of data initially distributed might be high. Possible solutions include:

Specifying Server Names in Replication Topologies

When specifying server instance names participating in replication, you must supply the name in the format of the SQL Server registered server instance name. For example, you must use the SQL Server instance name when specifying Publisher or Subscriber parameters to replication stored procedures or the replication agent connection settings on the command line. If the network name for the SQL Server instance differs from the registered instance name, replication connections by agents will not succeed. If the network name of the instance and the SQL Server instance name differ, consider adding the SQL Server instance name as a valid network name. One method to set an alternative network name is to add it to the local hosts file. The local hosts file is located by default at WINDOWS\system32\drivers\etc or WINNT\system32\drivers\etc. For more information, see the Windows documentation.

For example, if the computer name is comp1 and the computer has an IP address of 10.193.17.129, and the instance name is inst1/instname, add the following entry to the hosts file:

10.193.17.129 inst1

Data Integrity

For topologies that allow updates at Subscribers, you must ensure that application integrity is preserved at each Subscriber. All controls used to validate data changes at the Publisher, such as constraints, should be present at the Subscriber or should be handled in the application. For information about replication agents and constraints at the Subscriber, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

Data Conflicts

For topologies that allow updates at Subscribers, design your application to minimize conflicts. If Subscribers need to read data and do not need to update data, conflicts are avoided. Partitioning data logically according to geographic locations or business uses can also prevent users from updating the same data values, thus avoiding conflicts. For more information about partitioning data, see Filtering Published Data.

Large Object (LOB) Data Types

The data types text, ntext, and image are deprecated. We recommend that you use the data types varchar(max), nvarchar(max), varbinary(max), respectively. If you use text, ntext, and image data types, see the "Large Object (LOB) Data Types" section in the following topics for more information: Considerations for Transactional Replication and Considerations for Merge Replication.

Character mode snapshots are used by non-SQL Server Publishers, non-SQL Server Subscribers, and Subscribers running Microsoft SQL Server 2005 Compact Edition. If you use a character mode snapshot, LOB types are converted to Unicode, which causes the LOB data in the snapshot file to be four times larger than the same data in the publication database. When the data is copied to the subscription database, it is converted again, with the net result that it is the same size as the data in the publication database.

Data Type Mapping

If a replication topology includes previous versions of SQL Server, SQL Server 2005 Compact Edition, non-SQL Server Publishers, or non-SQL Server Subscribers, data types are mapped between databases. For a list of data type mappings, see:

Identity Columns

To use identity columns in a replication topology that has updates at more than one node, each node in the replication topology must use a different range of identity values, so that duplicates do not occur. Replication handles identity columns across all publication and subscription types, allowing you to manage the columns manually or have replication manage them automatically. For more information, see Replicating Identity Columns.

Computed Columns

Replication copies only the definition of computed columns to the Subscriber. The value for each row at the Subscriber depends on the data at the Subscriber and whether the computation for the column uses nondeterministic functions. If the data at the Publisher and Subscriber are identical at a given point in time and the computation uses only deterministic functions, the computed values for a row are identical. If the data is different or the computation uses one or more nondeterministic functions, the values at the Publisher and Subscriber could be different. For more information on the determinism of functions, see Deterministic and Nondeterministic Functions.

See Also

Other Resources

Implementation Considerations for Replication

Help and Information

Getting SQL Server 2005 Assistance