sp_addmergearticle @subscriber_upload_options = 1 in C#.NET

Generate ANSI/AIM Code 128 in C#.NET sp_addmergearticle @subscriber_upload_options = 1

This parameter defines restrictions on updates made at a subscriber. The parameter value of 1 is described as download only, but allow subscriber changes and seems equivalent to the -EXCHANGETYPE = 2 setting mentioned previously, but in the SQL Server 2005 case there are no triggers at all on the subscriber table. Another distinction is that this setting is made at the more granular article level rather than set for the entire publication. This means that although the -EXCHANGETYPE and sp_addmergearticle methods are logically equivalent, the implementation has become

Summary

much more sophisticated in SQL Server 2005. Triggers that unnecessarily log metadata at the subscriber are no longer fired; therefore both subscriber data changes and the subsequent synchronization are significantly faster. Put simply, you should replace the use of EXCHANGETYPE with download-only articles! Incidentally, this setting is also implemented by a separate check box in SSMS, as shown in figure 3. This check box does a similar job but sets the value of @subscriber_upload_options to 2, which again makes the changes download-only, but in this case any subscriber settings are prohibited and rolled back.

Figure 3 Merge replication articles can be marked as download-only to prevent subscriber changes and reduce metadata.

Summary

We have looked at many of the lesser-known replication techniques useful in SQL Server 2005. Some of these involve using parameters or procedures that are partially documented but that might help solve a particular issue. Other methods are fully documented, but we have looked at how these methods can be used to replace replication techniques used in SQL Server 2000 and improve our replication implementation and reduce administration.

Paul Ibison is a contractor SQL Server DBA in London. He runs the website www.replicationanswers.com the only site dedicated to SQL Server replication and has answered over 6,000 questions on the Microsoft SQL Server Replication newsgroup. When not working, he likes spending time with his wife and son, Ewa and Thomas, going fell-walking in the Lake District, and learning Ba Gua, a Chinese martial art.

Summary

The purpose of this chapter is to educate DBAs on how to get maximum performance from their high-performance transactional replication topology across all versions and editions. Most DBAs are concerned with latency in other words, how old the transactions are when they are applied on the Subscriber. To set expectations, you should know that the minimum latency of any transactional replication solution will be several seconds (lower limits are between 1 and 2 seconds). Should you need replication solutions which require lower latencies, you should look at products like Golden Gate, which is an IP application that piggybacks off the Log Reader Agent. Focusing solely on latency will not give you a good indication of replication performance. The nature of your workload can itself contribute to larger latencies. For example, transactions consisting of single insert statements can be replicated with small latencies (that is, several seconds), but large batch operations can have large latencies (that is, many minutes or even hours). Large latencies in themselves are not necessarily indicative of poor replication performance, insufficient network bandwidth, or inadequate hardware to support your workload. Consequently, in this study we ll be looking at the following: Throughput The number of transactions and commands SQL Server can replicate per second. These can be measured by the performance monitor counters SQLServer:Replication Dist:Dist:Delivered Trans/sec and SQLServer:Replication Dist:Dist:Delivered Cmds/sec. Worker time How long it takes for SQL Server to replicate a fixed number of transactions and commands. This statistic is logged when the replication agents are run from the command line. Latency How old the transactions are when they arrive at the Subscriber. Latency can be measured using the performance monitor counter SQLServer:Replication Dist:Dist:Delivery Latency.