Poor Man's SQL Log Shipping (Kinda)
Date: 12/23/2016 9:54:00 PM
Hi all,
A Very Happy Holidays to everyone out there! It's been a very exciting and busy year helping clients with unique needs and solutions. I'm looking forward to what 2017 will bring!
I recently was tasked by a client to come up with a simple (as elegant as possible), and inexpensive Log Shipping solution for Microsoft SQL Server Express. Yes, I know SQL Server Express doesn't have Log Shipping, and the solution would just be to purchase a license of SQL -- well two licenses in the client's case. But, they were looking for some ingenuity so they could leave current systems in place as is.
Here was the setup. A GoDaddy VPS SQL Server Express up in the cloud and an in-office/on-premise SQL Server Express. They wanted to have the cloud server log shipped at least every couple of hours to their office so they had a hot spare SQL server in case they lost their cloud copy for some reason.
After some trial and error this is how I got it done for them:
1.) Installed a licensed copy of Pranas.NET's SQLBackupAndFTP app on the VPS server
2.) Setup the above app to do a SQL Differential backup every hour and send it to an on-premise FTP server. Also does a full backup daily
3.) The above app is nice because it can be configured to auto delete backup copies (on the FTP server) that are x number of days old. With this we always have hourly SQL backups running back as far as we want, but without soaking up too much disk space and having to manage those files elsewhere.
4.) Wrote a C#.NET Console app for the on-premise server, setup in Task Scheduler that:
a.) monitors the ZIP files that SQLBackupAndFTP sends to the local FTP site
b.) leverages two local SQL Express servers (one for monitoring/logging of the process, one that is the hot spare)
c.) utilizes SQL's RESTORE and WITH RECOVERY features
d.) determines if a full or full w/ differential restore is necessary (depending on the time of day and files received from the VPS)
e.) restores the backups
So while, yes, this isn't log shipping, the client got away fairly inexpensively with having an up to the hour hot spare of their SQL Server Express instance.
Interested in the C# code or examples? Send me a message for an inexpensive quote.
Merry Christmas and Happy New Year everyone!
~Ted