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

8/22/2022 - Mon

>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
Henrik Staun Poulsen

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.

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Henrik Staun Poulsen

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,

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.
Henrik Staun Poulsen

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question