In this article, we look at stretch enabled tables that one should be aware of including key constraints
Stretch Database is a SQL Server feature which allows users to migrate cold data securely and transparently to Azure cloud. Users can even pause these migrations during connection errors, which help in easier transfer of data.
Stretch Database directly targets transactional databases containing a large amount of cold data, which is usually stored in multiple tables in a database. And these tables might contain data way more than one billion rows.
Why is Stretch Database used?
• It allows users to store cold data in a separate table, or a database to migrate to Azure cloud.
• Users can use its filter function to separate or select cold or hot data, in the rows whichever they want to migrate.
Stretch Database is an amazing SQL Server feature which allows users to migrate their data safely and transparently to Microsoft Azure, however, it also comes with some limitations, which disables the users from implementing or enabling stretch on their databases. Here is a list of few of its limitations. Keep them in mind while using stretch if you want to migrate your data to the Azure cloud.
Limitations for Stretch Database enabled Tables
These are some of the conditions which prevent Stretch Database from enabling in your tables, make sure to keep them in mind next time you work with Stretch Database.
1. Constraints
• While using stretch database Uniqueness does not enforce on PRIMARY KEY and UNIQUE constraints in Microsoft Azure tables which contains any form of migrated data.
2. DML operations
• In any Stretch enabled table user is not allowed to DELETE or UPDATE any migrated rows or rows which are still eligible for migration.
• Users are also not allowed to INSERT rows in any Stretch-enabled table from a linked server.
3. Indexes
• Stretch-enabled tables do not allow users to create an index for the view.
• Any Filters on indexes in SQL Server are not propagated from the Stretch-enabled table to remote table.
4. Limitations that prevent users from enabling Stretch Database in a table
Users cannot enable Stretch Database for tables that have or come under the following conditions:
5. Table properties
• Tables with more than 998 indexes or over 1,023 columns
• Any FileTables or tables containing FILESTREAM data
• Tables that have an active usage of Change Data Capture or Change Tracking
• Any tables that were memory optimized
6. Data types
• Text, image, and ntext
• timestamp
• sql_variant
• XML
• CLR data types like geometry, hierarchyid, CLR or geography user-defined types.
7. Constraints
• Check constraints along with Default constraints
• Any Foreign key constraints which reference the table. We can explain this using the parent-child relationship in which (for example, Order (parent) and Order_Detail (child)), a user can enable Stretch Database Table for the table of his child (Order_Detail) but cannot change the setting of the parent table (Order).
8. Indexes
• Indexes with Full Texts
• XML indexes
• Spatial indexes
• Any Indexed views which provide a reference to the table
While stretch databases should be actively considered, companies must also invest in a tool that can recover sql server database files to keep their data safe during contingencies.
Author Introduction:
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com