AlwaysOn Availability Groups, a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring, was introduced in Microsoft SQL Server version 2012. Always on is much like Mirroring, but with added features that make it a robust solution for high-availability and disaster-recovery.

Having said that, I assume you are charged with managing a SQL 2005 or SQL 2008 server farm where you are replicating data from a single “master” or Publisher to multiple subscribers and there is no budge to upgrade to SQL 2012 or the vendor application beings supported are not certified on SQL 2012, yet.

If you happen to be a DBA managing a SQL farm that still uses traditional replication or you wanted to implement replication on your existing SQL 2005 or SQL 2008 farm, the below guidelines may be worth your testing time.

Let’s assume you were recently hired and are now in charge of the company’s SQL server 2005 installations that replicate data around the country.  Periodically, you get calls from users asking when the data will be available to their app that connects to a replica of the data.  After the smoke clears, it would be helpful if you, the DBA presented to upper management a plan to improve the data availability performance at a fraction of the cost of upgrading.

Server & Network

  • MIN and MAX Memory:  Configure SQL Server MAX and MIN memory settings to control the amount of memory allocated to the Database Engine. Having these configured properly allows better buffer pool management and the associated performance improvements that include;

o   Avoids low memory availability during replication activities, use the min server memory option to set the minimum available memory.

o   Avoids having the operating system page to disc for memory, you can also set a maximum amount of memory with the max server memory option.

  • A faster performing server ultimately means faster data availability.  Read more here.
  • Proper allocation of database files:  Use separate disk drives for the transaction log for EACH database participating in replication as this will decrease the time it takes to write transactions to log.

o   Mirror the transaction log drive, RAID 0+1

  • Server Memory: Whenever possible, add as much memory to the servers as your budget will allow.
  • Use a Fast Network: Propagation of changes to Subscribers can be significantly enhanced by using a 100Mbps network or faster.
  • Database Design:  Follow best practices for your application.
  • Indexes at the Subscriber:  This best practice rule can cause frustration when the intended purpose of the replicated data is reporting and additional indexes, which are not allowed on the Publisher, are a source of the data bottleneck to the clients. The primary key at the Subscriber should be indexed, of course, but note that additional indexes, though they may speed up the reporting queries, can and will affect the replication insert, update and delete functions.
  • READ_COMMITTED_SNAPSHOT Database Option:  To help reduce the contention between user activity and the replication agent process activities, set this option at the Publisher and Subscriber databases.
  • Application Logic Triggers:  Business logic in user defined triggers at the Subscriber can impact data availability.

Database Design

o   Transactional Replication:  Consider using custom stored procedures to apply the replicated commands.

o   Merge Replication:  Consider using business logic handlers. Read more here.

  • Limit Use of Large Object (LOB) data types:  LOBs require more storage space and processing than other column data types.  If you must include them, it is generally recommended that you use varchar(max), nvarchar(max), varbinary(max) in place of text, ntext and image data types which are deprecated.

o   Distribution Profile for OLEDB streaming: For transactional replication, consider using the Distribution Agent Profile called Distribution Profile for OLEDB streaming.  Read more here.

Publication Design

During the initial design phase of replication, it is a typical approach to simply replicate the entire table and only use a few columns on the reporting side.

  • Publish Only the Data Required:  If your data is being replicated for reporting purposes and not a warm-standby, consider publishing only the data that is required and not the entire table.
  • Minimize conflicts:

o   Publication design

o   Application behavior

  • Use Row Filters Carefully:  Both Transactional and MERGE Replication can be affected.

o   Transactional Replication: The Log Reader Agent can be affected if many updates occur because the agent must apply filter to each row affected by an update.

o   Merge Replication: The agent has be evaluate each changed or deleted row to determine which subscribers should receive them.

Subscription Considerations

It is quite easy to build push subscriptions, however, the additional overhead at the publisher associated with push subscriptions can be devastating.

  • Pull Subscriptions:  With a pull subscription, the Subscriber requests changes made at the Publisher. Pull subscriptions allow the user at the Subscriber to determine when the data changes are synchronized.

o   The Distribution Agent or the Merge Agent runs at the Subscriber. Read more here.

  • Out of Synch Subscribers: When large amounts of data are out synch, it could be faster to reinitialize with a new snapshot than using replication to move the individual changes.  You would have to know your environment as this approach is destructive.

Agent Parameters

Several parameters exist to enhance replication performance.  Below are a few;

  • Reduce Verbose Levels: Reduce these levels for prod release but keep them on during initial testing and debugging.

o   HistoryVerboseLevel and OutputVerboseLevel:  These reduce the number of new rows inserted to tack agent history and output.

o   –MaxBCPThreads parameter of the Snapshot Agent, Merge Agent and Distribution AgentNOTE:  The number of threads should not exceed the number of processors on the computer.

o   –UseInprocLoader:  The Distribution and Merge Agents use this parameter to BULK INSERT data when the snapshot is being applied.  NOTE:  Cannot be used if published tables include XML columns.

I have had a lot of success implementing and tuning Microsoft SQL Server Replication.  I hope that this article affords you some of the same success.

Via con Dios, DBA!


Wordpress Code Snippet by Allan Collins