SQL Server Performance Forum – Threads Archive

log-shipping to reporting databases

moring all,
Not sure that this is the right forum – if it isn’t please point out which one is. I am planning to set up a reporting database to off load non-operational reports to another database. Essentially this will give me 6 months data and I will then be able to keep only 2 months on the production database. I want to use log shipping to do this, but I have a number of questions that I have no answers for yet.
Not sure which method of log shipping I will use (MS or a home grown one) but the questions deal with the report database. Once I have log shipping setup and working – what state will the report database be after I have done the inital restore of the backup and started restoring the log files? Obviously when the next log restore happens can I have users still doing stuff on the database or do I have to kcik them off? I am using SQL Server2000 NOT 2005. What stat do I leave the database in after the log restore? so that user can run reports using report services? thanks
john

If the second database is constantly used for reporting purpose then log shipping will not be a viable solution. YOu can take help of replication by selecting required tables onto the reporting database. In log shipping during the log restore all the users must be kicked out and database will be unusuable until the log is restored. If the reports can sustain 5 minutes delay in accessing databases then log shipping is an ideal solution. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I agree with Satya. I had a similar issue, and I am doing snapshot restores everyday.
(Though Trans. replication is the better option) The existing users will be kicked out in theory, but, when I was testing it still did the restores, which i am connected to that db. Also, only ‘sa’ can access this db. How are you going to give permissions ? In both modes (no-recovery & standby) once the initial snapshot is restored, your existing logins/users will be out of sync. dont know how to fix that. What I learned is LS is for hot/warm standby solution, not for parallel access to your production db. bj

… rather I say LS is not an uninterrupted parralled access to your production db. [8D]. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>