Microsoft Azure stretch database     Purpose   Information regarding the new feature in SQL Server 2016. Reading time +/- 20 minutes   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.    

The post Stretch database to cloud. appeared first on SQLTreeo.