Avatar of usmmsupport
usmmsupportFlag for United States of America

asked on 

SQL Reporting Server

I am running SQL 2012 Standard (so i do not believe Always On is an option for me) we have a need to have an almost real time (Within an hour) copy of our production DB for reporting purposes.  Some of our reports cause severe lag in production so there is a desire to seperate reporting onto its own server.

I was planning to use log shipping for this, but now find that if any report is running when the next import tries, it will fail.  Mirroring I don't think allows the 2nd copy to be active at the same time ... What is the best way to setup a secondary instance of SQL for report (read only) access to the production data without impacting production ?
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft Server OS

Avatar of undefined
Last Comment
Henrik Staun Poulsen
Avatar of Aneesh
Flag of Canada image

>Mirroring I don't think allows the 2nd copy to be active at the same time .
If you have space in the server, you can create snapshots every one hour, at any point, you need to have two snapshots of the same database one created recently and the otherone created before this one; you need to make changes in the application, so that the new connections will connect to the most recent snapshot; By having the two snapshots, you are not killing the existing connections whenever you create a newer one. The existing connections continue to work on the same database while the newer connections on the newer snapshot
you could also use Read Commited SnapShot

Open in new window

It requires quite a bit of extra space in TempDB, but it means that writes do not block readers and vice-versa.
Avatar of usmmsupport
Flag of United States of America image


So if I understand this right, I can use log shipping to create a read only standby database, and by executing the above cmd, it will still be able to update trans logs even if reports are being ran?  Space is not an issue for us.
I think so. But you have to try it out.

You could also consider letting your report users run their reports on the production server, i.e. only have one server for the database.

Then you may want to set Max degrees of parallelism to something different than zero, say 4 or 8, depending on how many users you have that run reports at the same time, and how many cores your have in your server.

The saved cost for the additional server and SQL Server license may also buy you some fast storage for some or all of your disks. SSDs are expensive, but fast!

Best regards,
Avatar of usmmsupport
Flag of United States of America image


Yea that is not an option for us.  Our SQL server already runs on pure flash from Violin Memory, there is no faster storage system in existence, it blows away SSD's in terms of performance.

The production server has the fastest hardware it can possibly have, so reporting off it is simply not possible we have to have a reporting server.
Avatar of Henrik Staun Poulsen
Henrik Staun Poulsen
Flag of Denmark image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo