Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Say you have a production database and a staging database that you want to have replicate on a semi-hourly basis across the internet. The general intent is to have a stand-by backup, but also to have second read-only database for testing.

How would you go about setting that up and configuring it?

Any notes about performance implications are a plus.

(Answers regarding SQL SERVER 2005 or 2000 also welcome)

share|improve this question
    
Is having the staging database in readonly an acceptable solution? –  Nick Kavadias Jun 12 '09 at 9:47
    
Read-only is acceptable. The main intent is to have a backup that can prevent data loss but also not interfere performance-wise with the production database. –  Matias Nino Jun 12 '09 at 15:34
add comment

4 Answers

You'll want to take a look at log shipping. While the following article is a few years old, it walks you through the why's and hows:

http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx

The SQL Books Online covers it here: http://technet.microsoft.com/en-us/library/bb895393.aspx

share|improve this answer
add comment

if you are looking for database mirroring it is simpler if the machines are on the same domain. If not then you can follow the instructions @ SQL Mirroring for Non-Domain Servers

I should note that this will work for domain member servers as well but there are a few different steps required. It was also written for SQL 2005 but should work almost identically on SQL 2008

share|improve this answer
add comment

Log Shipping will be your technique of choice if you want to replicate every half hour.

A more real time solution would be to use database mirroring (SQL 2005 and up).

Log shipping can be done via network share, or FTP while database mirroring needs to have a socket connection available between the two servers on the port you specified.

Database mirroring requires less bandwidth overall as the transactions are moving in near real time, but with log shipping the logs are moved only when they are backed up, so more bandwidth is needed, but for shorter periods of time.

share|improve this answer
add comment

There are a few more pieces of info you could provide that may change the answer away from log shipping (although with that you've said so far, log shipping is the way to go as everyone's said)

1) what do you want to be able to do with the staging database?

If you just want to have it there as a standby, log shipping is fine. If you want to be able to read from it only, log shipping is also good, but you'll need to restore the logs with STANDBY, and then make a policy decision about what you do to database connections when it's time to restore the next log backup - kick them out or not? If not, you'll need to monitor to make sure the database doesn't get too far out of sync.

If you want to be able to write to the database too (for instance to do some ETL), log shipping is not the answer as it does not allow write access to the database and you may want to consider some form of replication.

Hope this helps

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.