Link to home
Start Free TrialLog in
Avatar of klittlejohn1
klittlejohn1

asked on

Best way to replicate DB to Reporting Server

We are using SQL Server 2005 SP3.  We currently have a production database that we want to replicate to another SQL server.  The purpose for the second SQL server is to run reports and testing so it doesn't affect the production environment performance.  My question is what would be the best method to do this? The production DB is around 280 GB with logs around 170 GB.  We were thinking of doing log shipping but not sure if that is the best method.  Data doesn't need to be up to date and can be at least 24 hours behind production environment.  Thanks in advance for your help!
Avatar of dwkor
dwkor
Flag of United States of America image

The biggest question is if reporting (secondary) database is read-only. In such case log shipping would probably be the simplest solution for you. If you need to be able to modify data, most likely you will need to use replication.
Avatar of klittlejohn1
klittlejohn1

ASKER

The second DB will need to be read-only.  Going with log shipping will kick users off the DB when the DB updates correct?
As far as I remember this (user disconnect) is configurable. But if you keep users in DB, reporting would be inconsistent at least.

One other option you can consider if you want to minimize this time and if you have enterprise edition, you can use mirroring and report against database snapshot from the mirrored db. This also gives you the benefit of the automatic client failover if you need it. Both ways have pros and cons.
We currently have the production DB in a cluster environment.  We also looked at mirroring and weren't sure if that would be good or not.  I am still investigating
ASKER CERTIFIED SOLUTION
Avatar of dwkor
dwkor
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks again for the help.  What are your thoughts on transactional replication?
It would work but it's more complicated from implementation and maintenance standpoints than log shipping. I would use it in either of 3 cases:
1. If you need to modify the data in reporting db (you will have to implement conflict resolution)
2. if you want to replicate only subset of the data.
3. If you need reporting database to be up to date all the time

In your case (read-only data with up to 24 hours delay), I'd probably start with log shipping. Because of simplicity.
Thanks for the advice.  You were very helpful!