With the launch of SQL Server 2016, one of the new features you might be interested in is Stretch Databases. It’s basically a data archiving feature which will move your data from your current SQL database to an Azure SQL database for storage, while still allowing the archived data to be queried.
It’s a great idea if you have a very large and growing database and need to keep a history of that data for use later. A perfect example would be a customer orders table. I won’t go in to detail on how to set it up as there is already a great guide on Microsoft for getting started, but I just wanted to highlight some of the pros and cons (in my opinion) of utilizing it:
Pros:
- Great for very large and growing databases as it frees up space on your current SQL server database to record new data.
- Azure automatically backs up databases on its platform by taking snapshots every 8 hours and retains them for 7 days providing you with a range of restore points.
- Azure databases are geo-redundant and replicated several times to reduce the risk of data loss.
- Stretch databases utilize staging tables when moving data to Azure, providing an additional point of redundancy.
Cons:
- Limitations on data that can be stretch-enabled.
- Costs incurred for storing data. (This could potentially be a pro if you are currently paying a high price for data storage.)
- Backups made on stretch enabled databases are shallow backups (i.e. they do not contain the migrated data), and in order to retrieve the data, your database will have to be large enough to accommodate the reverse transfer which also incurs a transfer cost.
In summary, it’s a great idea for a business sites which conduct a lot of business, but maybe not so great for your personal website or blog.