Search

Blog

  Blog
Techemistry Blog

Poor Man's SQL Log Shipping (Kinda)

12/23/2016 9:54:00 PM -- Ted Krapf

<p>Hi all,<br /> <br /> </p> <p>A Very Happy Holidays to everyone out there! &nbsp;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!<br /> <br /> </p> <p>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. &nbsp;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. &nbsp;But, they were looking for some ingenuity so they could leave current systems in place as is.<br /> <br /> </p> <p>Here was the setup. &nbsp;A GoDaddy VPS SQL Server Express up in the cloud and an in-office/on-premise SQL Server Express. &nbsp;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.<br /> <br /> </p> <p>After some trial and error this is how I got it done for them:<br /> <br /> </p> <p>1.) Installed a licensed copy of Pranas.NET's SQLBackupAndFTP app on the VPS server<br /> 2.) Setup the above app to do a SQL Differential backup every hour and send it to an on-premise FTP server. &nbsp;Also does a full backup daily<br /> 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. &nbsp;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.<br /> 4.) Wrote a C#.NET Console app for the on-premise server, setup in Task Scheduler that:<br /> <br /> <br /> a.) monitors the ZIP files that SQLBackupAndFTP sends to the local FTP site<br /> b.) leverages two local SQL Express servers (one for monitoring/logging of the process, one that is the hot spare)<br /> c.) utilizes SQL's RESTORE and WITH RECOVERY features<br /> d.) determines if a full or full w/ differential restore is necessary (depending on the time of day and files received from the VPS)<br /> e.) restores the backups<br /> <br /> <br /> 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.<br /> <br /> <br /> Interested in the C# code or examples? &nbsp;Send me a message for an inexpensive quote.<br /> <br /> <br /> Merry Christmas and Happy New Year everyone!<br /> <br /> ~Ted</p>

[return to articles list]