07 Aug 2019 0 minutes to read Contributors
Information regarding the new feature in SQL Server 2016.
Microsoft documentation: https://docs.microsoft.com/en-us/
What is a stretch database? When should you stretch a database? Implementing stretch database. Microsoft Azure stretch database Security. Limitations stretch database. Monitoring Stretch database. Contact
A database stretch is migrating your COLD data from your SQL Server to Microsoft Azure cloud. Unlike typical COLD data that is offline, when stretching a database your COLD data is still online and available for querying. Stretch database can be a solution for several common issues if they are present/experienced. https://azure.microsoft.com/nl-nl/services/sql-server-stretch-database/
As mentioned, stretching a database can be a benefit in several scenarios: If any of the situations apply then you can consider stretching your database to Microsoft Azure cloud:
These are a few examples of certain scenarios; any similar scenarios can also indicate the possibility of stretching your database.
Before implementing a Stretch database on SQL Server there are certain prerequisites that need to be completed first.
The plans differ based on the speed starting from 100 DSU up to 2000 DSU, prices vary upon each DSU plan chosen.
After the above prerequisites are completed you can continue to implement a stretch database on your SQL Server. When implementing a stretch database there are several factors that need to be accounted for as the initial setup might affect your production performance, and depending on table size it can take up a considerable amount of time before it is completed.
When stretching a database to Azure cloud this will be done only over a Secure channel (from Source: SQL Server to Destination: Azure cloud) Any sensitive column is converted to ciphertext so no one will be able to retrieve any information. Security can be extended with Always Encrypted: Data that is sent to Azure cloud is encrypted, Client-side data is encrypted by keys, the keys are created when configuring the stretch database on each row(row-level). When querying data that is in the cloud it will also be decrypted by the client driver. A key is used between SQL Server and Azure Cloud that will not be visible in the database, this key is used to make the connection from SQL to Azure cloud. https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine
Monitoring your SQL to Azure stretched table can be done by a build in dashboard in SSMS. When opening the dashboard, you can find detailed information regarding status, e.g. how many rows are still in the on-premise database and how many have been already transferred to Azure.