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