September 12, 2011 at 2:50 am
Hi
We have recently migrated to a new SQL 2008 R2 enviroment. I have a log shipped db with a restore window that we use for reporting. We also have reports that run from the live db. The growth that we have had in the last 6 months has made it almost impossible to pull any report from the live db without affecting normal day to day front end operation.
I have been tasked with providing a reporting solution that has up to date data but not have down time (log shipping is therefore out). I am allowed to be a max of 1 min behind when reporting, although is is not preferred. The solution should also be functional and dynamic enough for future expansion. The idea behind it would be to have the workload spread over multiple db's. Basically have multiple systems on their own db's, but have that data "replicated" across to the others.
The options that I have looked at are mirroring, replication, ETL.
Couple of things I am not sure about:
1. Would I be able to query the secondary db if I use mirroring. From what I have read is that the mirrored db is in standby mode. And with that would CRUD be possible?
2. I have used replication before in the past (sql 2000) and found this to be very unstable and unreliable. (Possible hardware/network issues) Is this still the case and how resilient is it in 2008?
3.ETL will give me the data but I will have the data gap. This in my opinion is also not viable for the future, but could provide us with a quick fix solution.
Any tips, hints, suggestions are welcome. How do you guys do your offline reporting?
September 12, 2011 at 3:06 am
if the secondary server can be flexible enough(not exactly 1 min behind the prod server then log shipping is still the valid option (as you will not be generating the report after every min of updated data).
So if you can re-consider this then you can configure the log shipping with log backup job running on primary server every n min and restoring on secondary with same frequency.
But if you still need your report to quick with mirroring option then this link http://dcx.sybase.com/1201/en/dbadmin/da-highavailability-s-3807923.html might help you
----------
Ashish
September 12, 2011 at 6:26 am
Downtime is not a option unfortunately. Data is allowed to be a min behind, but never unavailable. It is a financial system so we can allow a delay but never no data.
September 12, 2011 at 6:33 am
data will always be available as long your backup and restore jobs are working fine, if you considering log shipping.
----------
Ashish
September 12, 2011 at 5:13 pm
You have 3 options
1. With Log shipping, the users can't do the reporting during the Log restores..
2. With DB Mirroring, you need to use snapshots for the reporting purposes and again you need to drop and recreate them to have new snapshots every few mins based on the frequency of the reports required.
3. Replication works best in your scenario for the reporting purposes. But sometimes becomes complex and hard to troubleshoot and has some dependencies like PKs needed on the tables etc..
Thank You,
Best Regards,
SQLBuddy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply