SQL Server – When to Use a Sledgehammer and When to use a Screwdriver

Some organizations use SQL Server Always On Availability Groups to help boost the performance of applications where the users are geographically dispersed. They accomplish this by placing the secondary database in the same facility as the remote users. While it is almost always a good idea to place the data near the application, choosing Always On may be using a sledgehammer to do a screwdriver’s job, given the very high cost of Always On from software, hardware, and implementation perspective. Of course, RTO (Recovery Time Objective) and RPO (Recovery Point Objective) are prime factors in deciding whether you need Always On, but these factors are related to recovery and not performance. Many organizations overstate their RTO/RPO requirements as most companies are not under the tight requirements of an airline reservation system or financial trading system. In fact, transactional or merge replication may be just the tool you need to meet both the application performance and recoverability requirements of your organization. Let us learn about When to Use a Sledgehammer and When to use a Screwdriver.

SQL Server - When to Use a Sledgehammer and When to use a Screwdriver Sledgehammer-800x164

I have seen some great examples of companies using SQL Server replication to improve both performance and recoverability. These companies implement the following use cases:

  • A website performance monitoring company uses replication to bring data from points of presence around the world to a central database used for reporting and analysis.
  • An energy company uses replication to move data to and from remote locations which improves performance for end-users and keeps the main office synchronized with its remote locations.
  • A residential security company replicates data to/from residential neighborhoods via SQL Server Express with a central SQL Server to ensure great performance in the neighborhoods while also having the data centrally located for recoverability and reporting purposes.

A common issue when using replication over long distances is that it can fall hopelessly behind. I have seen many companies leverage NitroAccelerator from Nitrosphere to mitigate this issue by attaining near gigabit LAN speeds over these high-latency connections. As a result, they outperform the Always On feature at a fraction of the price. NitroAccelerator installs with no configuration or downtime on each of the replication servers. A cool benefit of NitroAccelerator is it also works on the clients, allowing you to accelerate the end-users’ connections to SQL Server. Therefore, with NitroAccelerator, you can choose the right solution for you in terms of cost and complexity. You can first try directly accelerating the end-user connections, and, if this is not sufficient, then try replication to the remote locations. In both cases, NitroAccelerator will help you ensure the processes are as fast as possible without requiring costly hardware, software, or bandwidth upgrades.

There are always trade-offs when making decisions on performance and recoverability. Replication may be a forgotten tool in your toolbox to address these items at a lower cost and with just as much effectiveness. Perhaps it’s time you use a screwdriver rather than sledgehammer.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Previous Post
SQL SERVER – Script level upgrade for database ‘master’ failed – There is already an object named ‘DatabaseMailUserRole’ in the database
Next Post
SQL SERVER – Using dm_db_stats_properties With InMemory OLTP Tables

Related Posts

4 Comments. Leave new

Leave a Reply