SQL On The Edge #4 – SQL 2016 Stretch Database

As the Microsoft SQL Server team continues to build very interesting integrations between the full SQL Server box product we all know and the Azure cloud platform. Stretch database is another integration that will be included as part of SQL Server 2016 and it will help customers solve these issues:

– Running out of space in your own data center?
– Have long archival requirements for your data?
– Users still query data from many years ago on occasion?
– You don’t want to do code changes to deal with these scenarios?

Stretch database attacks those problems by allowing us to “stretch” tables from on-premises (or full VM) SQL Servers into a table living in Azure SQL Database. Your users and applications will not be aware of where the data is coming from, they just have to query and let SQL Server handle the behind the scenes magic to either go to the local data files or go to the cloud to get the records.

So if you’re running out of space, you can stretch to a Standard tier db and get 250GB instantaneously or 500GB on Premium or even 1TB if you get a P11! Of course you will have to pay for the Azure SQL database cost but considering it’s a fully managed service, in some scenarios this can easily offset the cost of the on-premises storage plus managing all the archival if your system is busy and large enough.

The feature also works transparently with the backup and restore operations. When you backup your SQL Server database it will contain the metadata for the Stretch database and if you have the proper credentials, you can re-enable the connection to the Azure SQL database on RESTORE. If you just want to RESTORE as let’s say a DEV copy then you can either not reconnect the stretch database at all or reconnect it to some other DEV Azure copy for example.

As is usually the case, this feature does come with some limitations. For example, it can’t be used on replicated tables, In-Memory tables or tables using the CLR based data types (hierarchy, XML, spatial types, etc). The main limitation now is that UPDATE and DELETE are not supported so it’s very much for ‘append-only’ type of data. However, for many scenarios these limitations can be more than acceptable. You can see the full list here.

To see how we enable this feature and how it works, let’s jump to the video, enjoy!

Discover more about our expertise in SQL Server.