How to Identify Candidate Tables for the new feature of SQL Server 2016 Stretch Databases?

How to Identify Candidate Tables for the new feature of SQL Server 2016 Stretch Databases?

According to documentation

What kind of databases and tables are candidates for Stretch Database?

Stretch Database targets transactional databases with large amounts of
historical data, typically stored in a small number of tables. These
tables may contain more than a billion rows.

In SQL Server 2016 Community Technology Preview 2 (CTP2), Stretch
Database migrates entire tables. This assumes that you already move
historical data into a table thats separate from current data.

Use Stretch Database Advisor, a feature of SQL Server 2016 Upgrade
Advisor, to identify databases and tables for Stretch Database. For
more info, see Identify databases and tables for Stretch Database
by running Stretch Database Advisor.

Stretch Database Advisor

Current limitation (may change in future):

Table properties

    More than 1,023 columns

    More than 998 indexes

    Tables that contain FILESTREAM data

    FileTables

    Replicated tables

    Tables that are actively using Change Tracking or Change Data Capture

    Memory-optimized tables

    You cant enable Stretch for a table that has a column named [batchID--N] 
   or an index named [idx--batchID--N] where N is the object ID of the table.

Data types and column properties

    timestamp

    sql_variant

    XML

    geometry

    geography

    hierarchyid

    CLR user-defined types (UDTs)

    Columns that are Always Encrypted

Column types

    COLUMN_SET

    Computed columns

Constraints

    Check constraints

    Foreign key constraints that reference the table

    Default constraints

Indexes

    XML indexes

    Full text indexes

    Spatial indexes

    Clustered columnstore indexes

    Indexed views that reference the table

How to Identify Candidate Tables for the new feature of SQL Server 2016 Stretch Databases?

Leave a Reply

Your email address will not be published. Required fields are marked *