Setting up a SQL Server Reporting Database

By:   |   Updated: 2022-07-14   |   Comments (1)   |   Related: More > Database Administration


Please do not scroll away - stay informed.
Dear Database Professional,

Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

We know your day is hectic and you don't necessarily have time to research new topics and solutions every day, but we can keep you informed.

Take 30 seconds to register for our newsletter and look for free educational content to help you grow your career. >> REGISTER HERE <<

Thank you,
Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)
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:

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:

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:

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:

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:

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!



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Eric Blinn Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

View all my tips


Article Last Updated: 2022-07-14

Comments For This Article




Friday, July 15, 2022 - 8:12:34 PM - Jeff Moden Back To Top (90268)
How does SQL Server licensing work for the production replicas? Don't they also need to be licensed by core count?


download














get free sql tips
agree to terms