0

I just need your help in understanding of having SQL Server Standard 2012 and 2014 Enterprise version.

We do have CRM application on-prim. which runs based on SQL server. we do have right now 2012 standard version installed. We do have to runs 100's of reports which is based of SSRS and hitting every time to OLTP system.

We are thinking to get Enterprise 2014 which has "High Availability " or something like that option which is I guess live copy of the OLTP database.

If we do this, I believe it will be easier to run all SSRS report from copy as compared to direct OLTP DB.

Please let me know if this is viable option or there are other more practical approaches as compared to this!

Thanks.

  • There are tons of other approaches you could take which have less of a chance of taking down your production system due to a oopsie or at least are much cheaper. Here's the main question. How 'real time' does your data need to be? – Ali Razeghi Sep 22 '16 at 5:15
  • We have 4-5 SSRS report needs on-the-spot and 90% of our reports are ok if it is 1 day old or if it shows result till 6 AM every morning. – user2040021 Sep 22 '16 at 13:39
1

It sounds like you are referring to Always On availability groups with a read only replica. 2012 EE as well as 2014 EE have this as an option and you can route reports to the replica without impacting the primary OLTP database.

Product Documentation

1

There are various ways to have a read-only replica of the live database to offload reporting to, some of which are available in 2012 Standard Edition. If you don't need absolute to-the-second reporting then you could use log shipping with a short shipping interval to keep the copy updated, you could use replication, and so forth.

You may not even need a second instance of SQL Server: if you can install separate physical drives for the secondary database then you could keep the reporting copy on the same instances but different drives to reduce IO contention (though note that this will not help if your main bottlenecks are contention in tempdb or for CPU resource).

Have you researched where the bottlenecks actually are? You may be memory starved at times so thrashing the physical storage, in which case giving the machine more RAM could help a lot (on its own or in combination with having a copy on other drives) without needing a second instance. It may simply be that you can improve the design of those reports so that they get the same result with less effort too.

Of course availability groups will do what you describe and may form part of your uptime assurance & DR plans as well so if you need to upgrade for those reasons anyway by all means take advantage for reporting too, but do a through analysis of the problem to make sure there isn't a solution that isn't all of more optimal, less disruptive, and cheaper.

  • You are abosoultley correct, but the way our infrastructre is everything is sitting on the same server! CRM, SQL, Report Server, Share point all other basic application. We have to send out schedules every day to around 600 people for their next day work. it takes almost 4.5 hours to do this during week days and it takes only 2.5 hours in the weekend. the only major difference between weekdays and weekend is number of user doing operation CRM which needs same tables which I also use for my reporting purposes. – user2040021 Sep 22 '16 at 13:43
0

You will double your licensing cost to jump to Enterprise and then double your server and licensing cost again (to get the secondary).

If your company is willing to spend literally tens of thousands of dollars just to make some reports (maybe) run faster - they might also want to investigate just finding out why they're slow in the first place and addressing that.

  • You are abosoultley correct, but the way our infrastructre is everything is sitting on the same server! CRM, SQL, Report Server, Share point all other basic application. We have to send out schedules every day to around 600 people for their next day work. it takes almost 4.5 hours to do this during week days and it takes only 2.5 hours in the weekend. the only major difference between weekdays and weekend is number of user doing operation CRM which needs same tables which I also use for my reporting purposes. – user2040021 Sep 22 '16 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.