• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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!
0
klittlejohn1
Asked:
klittlejohn1
  • 4
  • 4
1 Solution
 
dwkorCommented:
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.
0
 
klittlejohn1Author Commented:
The second DB will need to be read-only.  Going with log shipping will kick users off the DB when the DB updates correct?
0
 
dwkorCommented:
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
klittlejohn1Author Commented:
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
0
 
dwkorCommented:
with log shipping you will add the load to production server during log backups. with the mirroring it would be some additional overhead for every transaction especially in synchronous mode.

With log shipping your data would be up to date based on the shipping schedule. Downtime would depend on the schedule (time when log is restored). With the mirroring you have control how often do you drop/re-create snapshots and the process itself is almost instant.

I would probably go with the shipping, but again, it depends on your requirements.
0
 
klittlejohn1Author Commented:
Thanks again for the help.  What are your thoughts on transactional replication?
0
 
dwkorCommented:
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.
0
 
klittlejohn1Author Commented:
Thanks for the advice.  You were very helpful!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now