Lightening The Load On SQL Server

If you’re running SQL Server, you or your employees are probably writing reports and queries to pull important information from the data your applications and websites collect. At the very least, the applications you run likely include built-in reports. Do you find, though, that when reports run, especially certain reports, everything else that depends on SQL server slows down? It could be that you’re simply asking too much of a single SQL instance.

As companies collect ever-increasing data (whoever actually deletes data these days?) and in the age of Business Intelligence, Big Data, and data-driven strategy and management, our database servers are burdened with more and more reporting load. When this happens, we ask a lot of our SQL servers. We expect them to continue gathering data from application input at blistering speed, while also serving up reams of historical data for reports. Eventually, performance degrades. In addition, if individuals are allowed to query directly against the data, there will be times when they accidentally or naïvely issue queries that block all other activity. A typical and very effective solution to this is to create a copy of the data to be used exclusively for reporting only. SQL Server provides many ways to create and maintain reportable copies of data. Here is a quick rundown:

• Simple backup and restore at regular intervals
  • For small databases that don’t have to have up-to-the-minute data, this can work and may seem like an intuitive approach, but it will require manual or scripted and scheduled work and is likely to be error-prone.
• Log Shipping
  • Provides more frequent data sync to the reporting copy, depending on log frequency and size, but is not ideal for reporting because the secondary databases, while readable, are made briefly unavailable each time a log file is applied. In addition, the secondary schema must match the primary in its entirety, with means no additional indexes can be added to facilitate faster reporting
• Replication.
  • Can provide near real-time data sync, without service interruption on the secondary. Also, does not have to include all database objects and will allow the addition or changes of index strategies. However, it can add significant maintenance overhead to schema changes.
• Mirroring with snapshots
  • Mirroring is being deprecated, so this approach should be avoided. However, it is possible to use the powerful mirroring capability along with snapshots to create readable data. This scenario is best considered only as an augmentation to a failover strategy.
• AlwaysOn Availability Groups with readable replica
  • This is a very powerful way to get both failover capabilities and reporting offloading. However, it requires clustered Windows and very careful patch management (see this little patch snafu).
• (new with v. 2017) Availability Groups without clustering
  • This is an exciting new feature that will allow us to take advantage of the convenience and performance of readable mirrors without having to trouble with AlwasyOn clustering. It is expressly not suitable for failover, though it could function as a warm-backup in a pinch.

Each of these approaches has different strengths, weaknesses, and costs. Deciding among them is a matter of assessing your business requirement, hardware and license resources, and ability to take on or outsource the additional planning and maintenance. If you suspect that reporting load is the root cause of your SQL performance issues, Thrive can assess the activity on your server to determine the most effective solution. Contact Thrive today!