The Cost of Log Shipping SQL Server to Amazon S3

Log Shipping, SQL Server

Want to know how much it’ll cost to back up your database transaction logs to Amazon S3?  I’ll show you with a snippet of T-SQL.

Note the parameters at the top for retention periods.  The defaults assume one full backup weekly.  DBAs would normally want to do full backups more often, but when you’re paying by the upload, you may want to consider only uploading one full backup per week and then uploading transaction log backups the rest of the time.  You can still take full and differential backups locally for faster local recovery times as long as you set your backup parameters correctly so that you don’t break your log chain.

The @months_to_retain_full_backups parameter says we’re storing two months of full backups in S3.  This gives you some offsite flexibility while minimizing your upload costs.

The upload cost is set at $.10 per gig because that’s Amazon’s long-term price.  There’s a temporary sale going on for $.03 per gig for uploads, but you can’t set your budget by that.

With no further ado, here’s the query.  If it doesn’t work on your system, let me know – this is a really rough draft that I’ve only used on a dozen or so systems, and it wouldn’t surprise me if it’s got bugs.

This query revolves around transaction log backups, but if you only do incrementals, you could change that bs.type = “L” filter in the query to be “I” instead, and you’ll get the cost on incrementals.  I don’t have a query for databases that only do full backups, but frankly, if you’re only doing fulls, your data probably isn’t important enough to get internet-based online backups available.

This query is good for more than just gauging costs, too: keep in mind that your internet connection will need to be able to handle uploading your backups out to Amazon S3.

This is only a ballpark estimate. There are other costs that this query does not cover, and Amazon’s pricing drops periodically.  For example, Amazon charges you for some types of requests, like checking directory contents.  These costs can vary, but they’re generally very small relative to the bandwidth and storage costs.  This query also doesn’t check for backup compression, but if you’re paying by the byte for bandwidth and storage, you should seriously consider using backup compression.  The ROI is extremely high: software like Quest LiteSpeed pays for itself here in no time.

Learn more about our SQL Server in Amazon cloud resources page.