Solved

Best way to replicate DB to Reporting Server

Posted on 2011-03-03
8
326 Views
Last Modified: 2012-05-11
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
Comment
Question by:klittlejohn1
  • 4
  • 4
8 Comments
 
LVL 13

Expert Comment

by:dwkor
ID: 35029534
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
 
LVL 1

Author Comment

by:klittlejohn1
ID: 35029565
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
 
LVL 13

Expert Comment

by:dwkor
ID: 35029630
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
 
LVL 1

Author Comment

by:klittlejohn1
ID: 35029655
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Accepted Solution

by:
dwkor earned 500 total points
ID: 35029703
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
 
LVL 1

Author Comment

by:klittlejohn1
ID: 35029804
Thanks again for the help.  What are your thoughts on transactional replication?
0
 
LVL 13

Expert Comment

by:dwkor
ID: 35029900
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
 
LVL 1

Author Comment

by:klittlejohn1
ID: 35030075
Thanks for the advice.  You were very helpful!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now