Optimizing Replication

Depending on the number of servers in your topology, SQL Server replication can be taxing on your system. You have several methods for tuning performance, and some of the important considerations include the following:

  • Never publish more data than you need. Filter each article horizontally and vertically to only filter needed data. By doing this you'll save processor time as well as network bandwidth.

  • Ensure that the columns you filter on are indexed properly. Not doing this will cause SQL Server to perform table scans continuously.

  • Don't use immediate updates if you can avoid it. Use of these takes its toll on the server and should be avoided if immediate updates are not required. If this is not required, schedule the agent to execute once an hour or whatever will fill the requirement.

  • Keep the distribution components on a different server. This spreads the burden of transferring data to the subscribers to another server so your production server does not have to spend any I/O sending data to multiple subscribers.

  • There should be a high-bandwidth network between the publisher and distributor. The distributor should be doing nothing other than sending data to the subscribers.

  • Schedule snapshot replication during off-peak times on the server. Snapshot replication has to create shared locks on the tables it's replicating and transfer the data to the distributor. This can be very I/O intensive.

  • If you're using transactional or merge replication, the transaction log is constantly being read for new transactions. You will want to make this as fast as possible by using a RAID 1 or separate drive array.

  • There is a huge overhead if you want to replicate columns with the image, text, or ntext data types. Try to avoid replicating these columns if you can.

  • In merge replication, create nonclustered indexes on the ROWGUID column. This will speed up comparisons as the merge agent resolves conflicts.

  • Increase the size of batches in merge replication being passed from the publisher if you have a large amount of transactions.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net