September 14, 2011 at 12:46 pm
Hello -
We currently have SQL 2005 SP4 cluster running and use SSRS to pull reports from these databases. These reports are taxing the main production server. We would have to created a view that would pull that data into a new database on a different server that the reports can be pulled or data can be accessed from there.
I'm looking for a best practice or suggestions on getting this done. Any more questions please let me know. I'm sure I'm leaving something off.
Thanks,
David
September 14, 2011 at 12:52 pm
this might be a silly question, but are you running the IIS and SSRS on the same server as the database?
If so, that's not helping.
A datawarehouse server is quite common as a solution to overtaxing an OLTP system with reporting. You're on the right path.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 14, 2011 at 12:57 pm
David,
Is real-time access to the production data a requirement? If not how about snapshot replication? Or how about database mirroring with a database snapshot every few hours/days? If real-time access is required what about transactional replication on those tables that need to be reported off of. You could code your own solution using T-SQL and Linked server or SSIS but I wouldn't try to reinvent the wheel for a problem that maybe already solved with the out of the box tool set.
Those are my thoughts
Steve
Steve
http://stevefibich.net
http://utilitydb.codeplex.com/
September 14, 2011 at 1:06 pm
Hi - No question is silly just my answer might be. 🙂
The IIS and SSRS are separate from the DB server.
September 14, 2011 at 1:11 pm
Steve -
No real-time access to the production data is not a requirement. That is the good news out of all this. 🙂
The snapshot replication looks to be the way I might want to go. Do you know any good doc's on how to do it? Also do you know can I go from a SQL server 2005 to new SQL 2008 server with it?
Thanks,
David
September 14, 2011 at 1:20 pm
David,
No sorry I don't have know of any good doc's on how to do this, I normally turn to Books Online for this kind of thing. Snapshot Replication is very easy to setup and can be done from within the GUI of SSMS. There is a wizard if you right click on Replication that will walk you through it. I would do this on test/development server first just so you can get the hang of it. As for going between 2005 and 2008 I don't know off hand, but I'm sure its out on Google somewhere and you could always give it a shot and see.
Steve
Steve
http://stevefibich.net
http://utilitydb.codeplex.com/
October 27, 2011 at 11:49 am
david.ostrander (9/14/2011)
No real-time access to the production data is not a requirement. That is the good news out of all this. 🙂
The easiest way - which wouldn't even requiere to touch current reporting code is to build a "Reporting Database" in a separate server, on a nightly basis, just by restoring the last backup of your Production Database - provided there is a daily full backup of it.
Once you do this and OLTP vs. Reporting concurrency is not longer an issue you can plan for a more elegant solution like building a star schema datawarehouse to replace the "Reporting Database".
Hope this helps.
_____________________________________
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.Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply