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 ?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
>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.
usmmsupportAuthor Commented:
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.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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,
usmmsupportAuthor Commented:
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.
I did not know Violin Memory, but I had a look at their homepage. PCI bus => speed.

Our SAN does 800k io/s and 8 GB/s and our main DB in our Data Warehouse is +30 TB.
We did try Fusion IO, but only 500 GB, and we could not easily move the hot stuff onto those, hence we did not see any benefits.

But Read Commited SnapShot did help a lot against blocking.

We also have a problem with rebuilding index, and partitioning has helped here. It was a lot of work though. Table partitioning is only available in Enterprise Edition, but View partitioning is in all versions.

Best regards,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.