By: Eric Blinn | Updated: 2022-07-14 | Comments (6) | Related: More > Database Administration
Problem
We run a lot of reports against our production SQL Server database and now it is starting to cause performance and blocking issues. What can we do to move that reporting workload somewhere else to free up resources on the production OLTP server?
Solution
Luckily, there are several ways to approach this problem. Choosing an option will depend on several factors including how up-to-date the report data needs to be, licensing implications, and what format the data needs to be in.
The goal of this tip is to only offer suggestions on which approach makes the most sense. There won't be much in the way of actual implementation advice. Once a selection is made, follow the links to the companion articles for tips on how to implement the solutions described.
Availability Groups
SQL Server Always On Availability Groups (AG) creates 2 or more copies of a group of databases for the purpose of high availability. Each copy of a database is called a replica. There is a feature available where read-only connections that come into the AG listener can be automatically routed to a non-primary replica of the AG. This means that all reporting traffic can be marked as read-only and routed to an entirely different server to take load off of the primary replica.
This method provides a real-time or near-real-time copy of the entire database for reporting purposes. The issue with this approach is that it requires 2 or more fully-licensed Enterprise Edition SQL Servers. The read-only routing feature is not available with the Basic Availability Groups option that comes with SQL Server Standard Edition.
| Latency | Format | Licensing |
|---|---|---|
| Real-time or near real-time | Entire database with all tables, indexes, procedures, etc | Enterprise Edition with primary replica and all readable secondary replicas licensed |
Implementation Tips:
- Configure an AG with a Replica for Reporting Workloads
- Configure Read-Only Routing on an Existing AG
- Connecting to a Read-Only Replica in SSMS
Replication
Next on the list is replication. Replication is a SQL Server feature where portions of a database are copied to another database and kept up to date by the database engine. This can be done in near real-time with transactional replication or on a schedule with snapshot replication.
This feature is especially good for reporting because the databases involved do not have to have the same indexes. The OLTP database can have indexes that work for daily processing while the reporting copy can have an entirely different set of indexes that work best with reports. Also, the database that is the replication target can have other tables/procedures in it to better support the reports. It does not need to exactly mirror the principal database.
The downside to replication is that it isn't meant to copy an entire database. It is meant to operate on a subset of tables and/or columns. Could the DBA check every single box on the replication wizard to try to copy every column in every table of the database? Technically yes, but this is not recommended.
| Latency | Format | Licensing |
|---|---|---|
| Near real-time or updated on a schedule | Subset of the tables and columns of a database. No procedures, indexes, etc | No special considerations |
Implementation Tips:
- SQL Server Replication Overview (both Transactional and Snapshot)
- Automate Replication Monitoring
- Adding a Table to an Existing Replication Installation
Backup and Restore
One of the most popular approaches this author has seen in his career is a daily backup and restore. This method leverages existing processes and doesn't require any new technologies or upgraded licenses. Odds are that the principal database is already being backed up every day either in full or as a differential. What if a second step was added to that scheduled task? Step 2 could restore that database to a target server for reporting purposes.
This is one of the easiest ways to move the reporting workload to another machine, but will only work if the latency of up to whole day is acceptable.
| Latency | Format | Licensing |
|---|---|---|
| On a schedule, usually daily | Entire database with all tables, indexes, procedures, etc | No special considerations |
Implementation Tips:
- SQL Server Backup Commands Tutorial
- SQL Server Restore Tutorial
- This tip talks about automatically restoring a database from one server to another for the purpose of running DBCC CheckDB, but it could be easily modified to do the restore for reporting purposes.
Log Shipping
Log shipping is usually used as a DR routine, but can sometimes be applied to build a reporting copy of a database. The restore steps of the log shipping target database can be set to put the database into a read-only state after every restore. The main downside to log shipping for reporting purposes is that the database will be unavailable during the restore tasks. If log backups (and eventual restores) are occurring every 15 minutes, then the reporting database would essentially go down 4 times each hour for a minute or so each time. This can be a tough sell to the user base.
| Latency | Format | Licensing |
|---|---|---|
| On a schedule, usually every hour or more with short outages during the restore step | Entire database with all tables, indexes, procedures, etc | No special considerations |
Implementation Tips:
- Step by Step Log Shipping
- Change Secondary Log Shipping DB to Read-Only
- Change Restore Method of Log Shipping to Read-Only
ETL Programs
Another option available is to create an Extract Transform Load (ETL) program to copy the desired data out of the principal server and into another database where it can be reported upon. There are many tools available to write such programs with SQL Server Integration Services (SSIS) being the one that is delivered natively with SQL Server.
The latency of the data for this approach can vary widely depending on which tools are utilized and how often they are run. In some cases, it can be combined with other features such as replication, Change Tracking, or Change Data Capture to reduce latency.
The major variable in the ETL approach is how much "transforming" is done. The ETL program could be written to do little or no transforming and just copy the data in the same format as the principal system. This can make sense when there is already a large reporting library that depends on the table structure of the principal system.
Another option is to do much transforming to build the target reporting database. Often this will include steps such as denormalizing certain objects. The idea of building a data warehouse or similar reporting environment can be daunting. Check out this tip which can be a great starting point for designing a data warehouse. Pay close attention to the Next Steps section at the bottom as it will deliver even more content for the project.
In either situation, since the target database is going to be different than the principal, different table and index structures are available and should be used. Consider the use of ColumnStore indexes in these environments as they can be very useful when querying for aggregates over large data sets.
| Latency | Format | Licensing |
|---|---|---|
| Varies widely | At the discretion of the ETL developer | No special considerations, unless a 3rd party ETL tool is involved |
Implementation Tips:
- SSIS Tutorial
- Using Change Data Capture (Enterprise Only until SQL 2016 SP1)
- Using Change Tracking (Standard Edition)
What doesn't work?
Database mirroring will create a complete copy of a database on another server, but much like a Basic Availability Group, it will not allow a readable secondary. Always On Failover Cluster Instances don't create a second copy of the data so that feature will not help either.
Next Steps
- I hope that one of these options will work for you. If you have any other methods to suggest or questions about these options, please put them in the comments below!
About the author
Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-07-14
