February 21, 2011 at 10:25 am
Hi everyone,
I have a client that has stood up a new server to act as their reporting server (they have installed SSRS). I need to get data to that server and keep it fairly up to date (within 15 min or so). I've looked at log-shipping and mirroring but they look like they are more for high-availability. I think I'm looking at replication but would like to stay away from that if possible. I'm only a part-time DBA for this client and my experience with replication hasn't been very good. It seems to have frequent problems, etc.
Anyway -- what is the best way to set up a secondary server with read-only data for reporting?
Let me know if you have any questions or I left out something pertinent.
Thanks,
Brett
February 21, 2011 at 11:38 am
That's one of the things replication was designed for, so staying off of that does limit your options. Personally, I use snapshot replication for that pretty routinely.
One solution I saw someone write about recently was mirroring, and keeping a snapshot on the mirror which could then be used for running reports. I'm not sure how the discussion ended up, but it was mentioned as a possibility. (This might be a new Denali feature, now that I think about it. Might not be available yet. I don't remember.)
Log shipping definitely isn't the way to go on that. To restore the next log, you have to shut down the connections querying the database, and that would mean shutting down reports every 15 minutes. Can work if you're log shipping daily or something on a database with few updates, but not so much for every 15 minutes.
Another option is SSIS synchronizing data from your main to your reporting server. This can put more of a strain on the production OLTP server, because of queries, than either log shipping or snapshot replication, depending on a number of factors. (Snapshots can be a significant performance hit themselves, depending on how frequently data changes.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 21, 2011 at 12:54 pm
May I ask how big is the database?
Also, may we know how much t-log gets generated per minute/hour/day?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 21, 2011 at 1:57 pm
Have you looked into CDC? SQL Server 2008 offers Change Data Capture (basically transactional replication in a way) where you can write your changes to your reporting server.
Might be one option.
Replication is really good, reliable and not all that hard to setup. Its also not that much of a pain. I say you shouldnt seperate yourself from this great feature.
I have also done log shipping to a data warehouse everyday at 4AM through a SQL agent job. Though this meant the reporting was always day -1. That was okay with the business.. so .. its very dependent on the needs as well.
Right now, Im implementing CDC to push changes to our EDW.
Hope this helps.
--
:hehe:
February 22, 2011 at 4:05 am
GSquared (2/21/2011)
One solution I saw someone write about recently was mirroring, and keeping a snapshot on the mirror which could then be used for running reports. I'm not sure how the discussion ended up, but it was mentioned as a possibility. (This might be a new Denali feature, now that I think about it. Might not be available yet. I don't remember.)
It's an existing feature I believe, but not available in editions below Enterprise level
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply