Setting up a SQL Server Reporting Database

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


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?


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:


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!

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

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

Comments For This Article

Tuesday, March 26, 2024 - 8:33:38 PM - Eric R Blinn Back To Top (92128)

Unless the load on the production server is crazy high during these 15-30 minutes and interrupts business, I would not use a reporting copy of the database in this instance.

Tuesday, March 26, 2024 - 6:27:42 AM - Muzafar Back To Top (92118)
I understand the need of the secondary node or replication of the database where we do a lot of ad-hoc queries and even for regular reports if they run on SQL procedures so every time a report is refreshed it puts load on to the production database. However if you have a DWH in place and you only batch process once in 24 hours, the batch runs for half an hour or so, the volumes and the compute on ETL is also moderate that whole ingestion pipeline can be completed within 15 minutes. What is the justification in this case to have a reporting database a replica of production. Thanks

Wednesday, October 18, 2023 - 2:00:57 PM - Eric Blinn Back To Top (91681)

I apologize for not seeing your comment earlier. Any replica from which you read data needs to be licensed -- almost certainly by core count.

Wednesday, October 18, 2023 - 1:59:23 PM - Eric Blinn Back To Top (91680)

While a database snapshot creates a DB in SSMS and marks a point in time, it does not create a copy of the data. When querying the snapshot, the bulk of the reads will go right through the snapshot and to the original DB.

In most cases, this defeats the purpose of the second database.

Tuesday, October 17, 2023 - 3:45:24 PM - DMITRI BOBKOV Back To Top (91676)
You forgot about DB snapshots.
Just schedule a period when your reports are not refreshing, drop previous DB snapshot and re-created a new one.
It takes seconds and after that you can read data from that for reports.

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?

get free sql tips
agree to terms