CODEWITHSUNDEEP
sql-serversql-server-2016
Jason Clark
Jason Clark

Reputation: 1425

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

SQL Server 2016 has a new feature, stretch database, that allow you to archive your historical data transparently. but i want to know which tables are good candidates for this feature?

Upvotes: 2

Views: 294

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175696

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 that's 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 can't 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

Upvotes: 2

Related Questions