Solved

Best way to replicate DB to Reporting Server

Posted on 2011-03-03
8
331 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL SELECT query help 7 56
length of the password hash sha1:64000 to set sql field property. 13 66
Loops and updating in SQL Query 9 54
Negative isnull? 3 15
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…

828 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