Warm Standby SQL Server/Web Server

Question

Warm Standby SQL Server/Web Server

This question might fall into the IT category but as the lead developer I must come up with a solution to integration as well as pure software issues.

We just moved our web server off site and now I would like to keep a warm standby of both the website and database (SQL 2005) on site. What are the best practices for going about doing this with the following environment?

Offsite: our website and database (SQL 2005) are on one Windows 2003 server. There is a firewall in front of the server which makes replication or database mirroring not an option. A vpn is also not an option.

My thoughts as a developer were to write a service which runs at the remote site to zip up and ftp the database backup to an ftp server on site. Then another process would unzip the backup and restore it to the warm standby database here.

I assume I could do this with the web site as well.

I would be open to all ideas including third party solutions.

Answer 1

I've heard good things about Syncback:

http://www.2brightsparks.com/syncback/sbpro-features.html

Answer 2

If you want a remote standby you probably want to look into a log shipping solution.

This article may help you out. In the past I had to develop one of these solutions for the exact same problem, writing it from scratch is not too hard. The advantage you get with log shipping is that you have the ability to restore to any point in time and you avoid shipping these big heavy full backups around and instead ship light transaction log backups, and occasionally a big backup.

You have to keep in mind that transaction log backups are useless without having both the entire sequence of transaction log backups and a full backup.

Answer 3

Thanks for the link to the article sambo99. Transaction log shipping was my original idea, but I dismissed it because the servers are not in the same domain not even in the same time zone. My only method of moving the files from point A to point B is via FTP. I am going to experiment with just shipping the transaction logs. And see if there is a way to fire off a restore job at given intervals.

Answer 4

You have exactly the right idea. You could maybe write a script that would insert the names of the files that you moved into a table that your warm server could read. Your job could then just poll this table at intervals and not worry about timing.

Forget about that - just found this. Sounds like what you are setting out to do. http://www.sqlservercentral.com/articles/Administering/customlogshipping/1201/

Answer 5

www.FolderShare.com is a good tool from Microsoft. You could log ship to a local directory and then synchronize the directory to any other machine. You could also syncrhronize the website folders as well.

"Set it and forget it" type solution. Setup your SQL jobs to clear older files and you'll never have to edit anything after the initial install.

FolderShare (free, in beta) is currently limited to 10,000 files per library.

Answer 6

For all interested the following question also ties into my overall plan to implement log shipping:

SQL Server sp_cmdshell