When planning replication applications, consider the following:
- Design your application to minimize conflicts. If the Subscribers need to read data and do not need to update data, conflicts will be 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 online/offline applications where you expect conflicts can and will occur, merge replication is usually the best choice for your application. Merge replication allows for a variety of conflict detection and resolution policies, evaluates updates row by row, and results in data convergence.
- Snapshot replication or transactional replication with the immediate updating or queued updating option is recommended for applications that are mostly read with occasional updates. Immediate updating uses two-phase commit (2PC). Queued updating provides policies for conflict resolution and evaluates updates and conflicts on a transaction basis.
- When using merge replication, or when using snapshot replication or transactional replication with the queued updating option, determine the conflict resolution policy before implementing replication.
- Research how disconnecting from the database will affect mobile or disconnected users. What happens if users do not immediately see the updates they make at the Subscriber?
- How fast is data synchronization? How long does it take to apply the initial snapshot and how long does it take for periodic updates? Test the initial snapshot by applying it over the actual network that will be used. Consider applying the initial snapshot manually using a CD-ROM or removable media device if transferring it over the network takes too long.
- Manage identity values by using identity ranges when using merge replication or when using snapshot replication or transactional replication and allowing queued updating subscriptions. If you create data partitions and assign different identity ranges to the partitions, conflicts will be avoided because different sites will be working with different subsets of data.
- Ensure that your applications use column names in INSERT statements before enabling merge replication or transactional replication with immediate updating or queued updating options, because these types of replication may add columns to your publishing table. If you do not list the column names in INSERT statements for these types of replication, an error will occur.
- If you are using transactional replication with the immediate updating or queued updating option, Subscribers will not be able to update values with the text or image data types. The publication can contain text or image columns, but those columns may be updated only at the Publisher.
- Be aware of maximum column and row sizes. A table used in snapshot replication or transactional replication can have a maximum of 255 columns and a maximum row size of 8,000 bytes. A table used in a merge publication can have a maximum of 246 columns and a maximum row size of 6,000 bytes. The reason the restriction for merge replication is stricter than the restriction for transactional replication is because conflict tables have the same structure with additional columns that store information about the origin of the conflict and the specific reason for the conflict. Because additional space is needed to record this conflict information, the maximum row size is less than the maximum row size for transactional replication.
- If you will have a high volume of transactions, always design your application to use stored procedures to modify data at the Publisher and publish the execution of stored procedures.