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
A couple of awesome things to note:
- 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).
By the way, as of version 9 our Schema and Data Compare tools support comparing and synchronizing Stretch Databases and stretch tables.