asked on 9/5/2013

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 Server Microsoft SQL Server 2008 Microsoft Server OS

Last Comment

Henrik Staun Poulsen

9/6/2013

Aneesh

9/5/2013

>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

9/6/2013

you could also use Read Commited SnapShot

ALTER DATABASE yourdb SET READ_COMMITTED_SNAPSHOT ON;

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.

usmmsupport

9/6/2013

ASKER

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.

Henrik Staun Poulsen

9/6/2013

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,
Henrik

usmmsupport

9/6/2013

ASKER

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.

ASKER CERTIFIED SOLUTION

Henrik Staun Poulsen

9/6/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

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