SQL Server 2016 introduces Stretch Databases, a beautiful feature that by itself makes migrating to SQL 2016 worth it. What is it? A stretch database is basically a database with an automated cloud archiving mechanism. Here’s how it works in a few words:
- You enable stretch database for your database and the tables you’re interested in “archiving”
- You decide to “archive” either a whole table, for example a history table, OR just certain rows, for example all transactions older than 12 months for a transaction table.
- SQL Server will then silently migrate (archive) your cold data based on the criteria you defined, from the live database to Azure
- No need to change queries or applications – if the data a query is pulling happens to be “cold” then you may notice some latency but other than that the whole thing is completely transparent.
- Your data is encrypted end to end (in the live database, on the way, and in the target)
- Faster backups, faster queries, overall significantly better performance (of course the improvement depends heavily on how you define the criteria that separates cold and hot data).