Replication: SQL Server 2000 - Part 2

Filtering Published Data

Replication can be tuned to yield better performance, and one of the ways to achieve this is filtering the data before publishing; in other words, give subscribers what they want to see and play with.

Replication facilitates this by splitting it vertically/horizontally.  By distributing partitions of data to different Subscribers, you can:

• Minimize the amount of data sent over the network.
• Reduce the amount of storage space required at the Subscriber.
• Customize publications and applications based on individual Subscriber requirements.
• Reduce conflicts because the different data partitions can be sent to different Subscribers.

In addition, the following are some tips which will help to yield better performance:

• When running SQL Server replication on a dedicated server, consider setting the minimum memory amount for SQL Server to use from the default value of 0 to a value closer to what SQL Server normally uses.
• Don’t publish more data than you need. Try to use Row filter and Column filter options wherever possible as explained above.
• For best performance, avoid replicating columns in your publications that include TEXT, NTEXT or IMAGE data types.
• Avoid creating triggers on tables that contain subscribed data.
• Applications that are updated frequently are not good candidates for database replication.
• Distribute the workload into more than one SQL server using replication.
• Plan for the type of replication to be used before the database design, because the type of replication used will, to a certain extent, guide your database design.