Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Best way to replicate DB to Reporting Server

Posted on 2011-03-03
8
Medium Priority
?
339 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
LVL 13

Accepted Solution

by:
dwkor earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

804 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