Thursday, May 12, 2016

Stretch Database is a nice feature of SQL Server 2016

Source: MSFT Blog

Why do you need this?

Well you want to archive data (historical) but you do not want to store it locally as it is not frequently accessed and your resources are inadequate. However you do want to access. No problem, a query in SSMS can access that data (remotely stored in Azure) as easily as the on premises data. It is possible there may be a bit of latency from the remote site.

SSMS 2016 has a very good wizard to take care of the Stretch Database.  The Wizard is accessible via the Object Explorer. You need to select the database you want to extend using the Stretch Database feature. It is not necessary that you choose an entire database to extend; you can use the filter to include what you need to go into the Stretch database. Of course, since the remote is on Azure, you should have an account and proper credentials. The Stretch database is always online and querying should have no problem.

Stretch database is compatible with the other features; Transparent Data Encryption and Always Encrypted and Row-Level Security features.

You do require a Azure account but a free trial is available. There is a price for everything including the new Stretch database feature.

More here:
https://blogs.technet.microsoft.com/dataplatforminsider/2016/04/13/keep-data-virtually-forever-with-stretch-database-in-sql-server-2016/