March 28, 2011 at 8:43 am
Can you help me understand the requirements a little more. Are you looking for an update to your reporting server once an hour that makes the reporting server look like the live server as of that hour? So if you update at 10am, 11am, 12pm, that the reporting server from 10am-11am looks like the live server at 10am?
Log shipping can work, but while you perform the update, the users will be kicked out of the reporting server. That can be an issue for many places, especially if you are going to knock off connections for 5 minutes at the top of each hour.
Transactional Replication, which can work, typically operates continuously but you can set a schedule. Most of the setup and admin can be done from SSMS using the GUI, but there is potentially the need to do some T-SQL, especially if you have issues and need to debug things. You also need to be aware of how replication can affect your production t-log.
My advice would be to engage a consultant for a few hours to help you understand the impact here and design a solution. This isn't that hard, but it would help to have someone work with you to understand how to do this.
I can recommend someone that will work remotely if you don't know anyone locally that you want to use.
March 28, 2011 at 9:06 am
Idea is to have 2 node sql cluster on principal DB and separate DB that is mounted on sql reporting server for OLAP, analitical tools. Second DB does not have to be instant but no more than 1 hour old, less would be preferable. If it is possible no clients would be disconnected, sessions would be eventually locked for 10 seconds or so.. What do you think about Database Mirroring in Enterprize edition? We are trying to avoid it since cost is huge but it looks like gentlemens solution :Whistling:
March 28, 2011 at 9:22 am
With mirroring, you cannot access the Mirror. With Enterprise, you can create snapshots, but updating those requires them to be dropped and recreated. There is no "freezing" of the connections. They will be dropped and must reconnect.
Replication sounds like your best option, but I would engage someone to make sure it is set up and they can help you understand what is happening. It shouldn't take a lot of time, but it would be good for someone to walk you through it, help you document it, and give you a resource that can help you if things go sideways.
March 28, 2011 at 2:16 pm
srdjan.katic (3/28/2011)
Idea is to have 2 node sql cluster on principal DB and separate DB that is mounted on sql reporting server for OLAP, analitical tools. Second DB does not have to be instant but no more than 1 hour old, less would be preferable. If it is possible no clients would be disconnected, sessions would be eventually locked for 10 seconds or so.. What do you think about Database Mirroring in Enterprize edition? We are trying to avoid it since cost is huge but it looks like gentlemens solution :Whistling:
I guess I'd push back on the requirement of 1 hour old data for a reporting server. Most of the time, people want it just because they know that it can be done. It's not very often where people actually need the capability.
I agree with Steve... if the 1 hour old data is a real requirement, then replication is probably the best way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2011 at 6:14 pm
chetanr.jain (3/28/2011)
I would prefer Log SHipping in this case.As the primary server is in Clustering mode and Secondary server is in stand-alone mode.
Why would you go for log shipping? Every time the log is applied the database is put into recovery and all connetions are broken. I've seen users go mad because just as they are expecting results from their reports the queriy is killed for the restore. Even using NetApp Snapshots kills connetions.
Transactional replication provides a robust low impact solution AND allows for better indexing on the reporting database, so you can have a pure OLTP database and a decent reporting database with decent indexes. It's not a DW but it's better than reporting off an OLTP database.
With a bit of planning you can restict the replicated articles to only those required for reporting and save space on the reporting database side.
Replication can be setup via the GUI, it's fairly intuative but go here for a step by step guide: http://www.sql-server-performance.com/articles/per/transactional_replication_2008_r2_p1.aspx
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
March 29, 2011 at 1:45 am
We will do some brainstorming and see what is best for us.
Tnx to everyone for support and ideas!
Srdjan
March 29, 2011 at 5:29 am
IMHO log shipping is probably your easiest option here and built in to SQL Server, it's also fairly easy to manage. User disconnections should be minimal if you get the schedules right
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply