Stretch Database to the Cloud

20 Aug 2019 29826 views 0 minutes to read Contributors

Purpose

  Information regarding the new feature in SQL Server 2016.

References

Microsoft documentation: https://docs.microsoft.com/en-us/

Contents

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      

What is a stretch database?

  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/  

      

When should you stretch a 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:  

  • Keep Cold/historical data available and online.
  • Require longer retention periods.
  • Massive tables that number in millions/billions.
  • Due to large table/database sizes adding/switching storage is a common occurrence.
  • Maintenance jobs/tasks take longer due to large size of databases and tables (e.g. creating index, index rebuilds, database integrity checks)
  • Want to save expenses on storage
  • SLA requires you to have your data restored within a certain time limit, but due to large sizes it is impossible to achieve.

  These are a few examples of certain scenarios; any similar scenarios can also indicate the possibility of stretching your database.     

Implementing a stretch database

  Before implementing a Stretch database on SQL Server there are certain prerequisites that need to be completed first.    

  • SQL Server version 2016 or higher.
  • Microsoft Azure subscription.
  • Microsoft Azure account
  • Check limitations on tables (constraints, DML operations, Indexes, Properties)
  • Check limitations on datatypes (column types, keys)

  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.     

Microsoft Azure stretch database Security

  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         

Limitations in a stretch database

  Limitations for Stretch-enabled tables Stretch-enabled tables have the following limitations.   Constraints  

  • Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data.

DML operations

  • You can't UPDATE or DELETE rows that have been migrated, or rows that are eligible for migration, in a Stretch-enabled table or in a view that includes Stretch-enabled tables.
  • You can't INSERT rows into a Stretch-enabled table on a linked server.

Indexes  

  • You can't create an index for a view that includes Stretch-enabled tables.
  • Filters on SQL Server indexes are not propagated to the remote table.
  • Limitations that currently prevent you from enabling Stretch for a table

  The following items currently prevent you from enabling Stretch for a table. Table properties

  • Tables that have more than 1,023 columns or more than 998 indexes
  • FileTables or tables that contain FILESTREAM data
  • Tables that are replicated, or that are actively using Change Tracking or Change Data Capture
  • Memory-optimized tables
  • Data types
  • text, ntext and image
  • timestamp
  • sql_variant
  • XML
  • CLR data types including geometry, geography, hierarchyid, and CLR user-defined types.

  Column types  

  • COLUMN_SET
  • Computed columns
  • Constraints
  • Default constraints and check constraints
  • Foreign key constraints that reference the table. In a parent-child relationship (for example, Order and Order_Detail), you can enable Stretch for the child table (Order_Detail) but not for the parent table (Order).

  Indexes  

  • Full-text indexes
  • XML indexes
  • Spatial indexes
  • Indexed views that reference the table

 Monitoring a Stretch database

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.

Report a Bug

In this article