Enable Stretch Database for a tableEnable Stretch Database for a table

本主题适用于:yesSQL Server(仅从 2016 开始的 Windows)noAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库THIS TOPIC APPLIES TO: yesSQL Server (Windows only starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

若要为 Stretch Database 配置表,请在 SQL Server Management Studio 中为表选择“Stretch | 启用”,以打开“为 Stretch 启用表”向导。To configure a table for Stretch Database, select Stretch | Enable for a table in SQL Server Management Studio to open the Enable Table for Stretch wizard. 还可以使用 Transact-SQL 在现有表上启用 Stretch Database,或创建已启用 Stretch Database 的新表。You can also use Transact-SQL to enable Stretch Database on an existing table, or to create a new table with Stretch Database enabled.

  • 如果在单独的表中存储冷数据,则可以迁移整个表。If you store cold data in a separate table, you can migrate the entire table.

  • 如果表中同时包含热数据和冷数据,则可以指定筛选器函数以选择要迁移的行。If your table contains both hot and cold data, you can specify a filter function to select the rows to migrate.

    先决条件Prerequisites. 如果你为表选择“Stretch | 启用”且尚未为数据库启用 Stretch Database,则向导将首先为 Stretch Database 配置数据库。If you select Stretch | Enable for a table, and you have not yet enabled Stretch Database for the database, the wizard first configures the database for Stretch Database. 请执行通过运行“启用数据库延伸向导”开始中的步骤,而非本文中的步骤。Follow the steps in Get started by running the Enable Database for Stretch Wizard instead of the steps in this article.

    权限Permissions. 在数据库或表上启用 Stretch Database 需要 db_owner 权限。Enabling Stretch Database on a database or a table requires db_owner permissions. 在表上启用 Stretch Database 还需具有表的 ALTER 权限。Enabling Stretch Database on a table also requires ALTER permissions on the table.

    备注

    之后如果要禁用 Stretch Database,请记住,禁用表或数据库的 Stretch Database 不会删除远程对象。Later, if you disable Stretch Database, remember that disabling Stretch Database for a table or for a database does not delete the remote object. 如果希望删除远程表或远程数据库,则需要使用 Azure 管理门户进行删除。If you want to delete the remote table or the remote database, you have to drop it by using the Azure management portal. 远程对象会继续产生 Azure 成本,直到手动删除它们。The remote objects continue to incur Azure costs until you delete them manually.

使用向导在表上启用 Stretch DatabaseUse the wizard to enable Stretch Database on a table

启动向导Launch the wizard

  1. 在 SQL Server Management Studio 的对象资源管理器中,选择要在其上启用 Stretch 的表。In SQL Server Management Studio, in Object Explorer, select the table on which you want to enable Stretch.
  1. 右键单击并选择“Stretch”,然后选择“启用”,以启动向导。Right-click and select Stretch, and then select Enable to launch the wizard.

    简介Introduction
    查看向导和必备组件的用途。Review the purpose of the wizard and the prerequisites.

    选择数据库表Select database tables
    确认已显示并选定你要启用的表。Confirm that the table you want to enable is displayed and selected.

    你可以迁移整个表,或在向导中指定一个简单的筛选器函数。You can migrate an entire table or you can specify a simple filter function in the wizard. 如果想要使用不同类型的筛选器函数来选择要迁移的行,请执行以下操作之一。If you want to use a different type of filter function to select rows to migrate, do one of the following things.

  • 退出向导并运行 ALTER TABLE 语句以对表启用 Stretch 并指定筛选器函数。Exit the wizard and run the ALTER TABLE statement to enable Stretch for the table and to specify a filter function.

  • 运行 ALTER TABLE 语句以在退出向导后指定筛选器函数。Run the ALTER TABLE statement to specify a filter function after you exit the wizard. 有关所需步骤,请参阅 运行向导后添加筛选器函数For the required steps, see Add a filter function after running the Wizard.

    ALTER TABLE 语法将在本文的后面进行介绍。The ALTER TABLE syntax is described later in this article.

    摘要Summary
    查看你输入的值和你在该向导中选择的选项。Review the values that you entered and the options that you selected in the wizard. 然后选择“完成” 以启用 Stretch。Then select Finish to enable Stretch.

    结果Results
    查看结果。Review the results.

使用 Transact-SQL 在表上启用 Stretch DatabaseUse Transact-SQL to enable Stretch Database on a table

可以为现有表启用 Stretch Database,或通过使用 Transact-SQL 创建已启用 Stretch Database 的新表。You can enable Stretch Database for an existing table or create a new table with Stretch Database enabled by using Transact-SQL.

“常规”Options

运行 CREATE TABLE 或 ALTER TABLE 时使用以下选项来在表上启用 Stretch Database。Use the following options when you run CREATE TABLE or ALTER TABLE to enable Stretch Database on a table.

  • 根据需要,如果表中同时包含热数据和冷数据,则使用 FILTER_PREDICATE = <function> 子句指定一个函数来选择要迁移的行。Optionally, use the FILTER_PREDICATE = <function> clause to specify a function to select rows to migrate if the table contains both hot and cold data. 该谓词必须调用内联表值函数。The predicate must call an inline table-valued function. 有关详细信息,请参阅 通过使用筛选器函数选择要迁移的行For more info, see Select rows to migrate by using a filter function. 如果未指定筛选器函数,则将迁移整个表。If you don't specify a filter function, the entire table is migrated.

    重要

    如果提供的筛选器函数性能不佳,则数据迁移性能也不佳。If you provide a filter function that performs poorly, data migration also performs poorly. Stretch Database 通过使用 CROSS APPLY 运算符将筛选器函数应用到表。Stretch Database applies the filter function to the table by using the CROSS APPLY operator.

  • 指定 MIGRATION_STATE = OUTBOUND 以立即开始数据迁移,或指定 MIGRATION_STATE = PAUSED 以推迟数据迁移的开始时间。Specify MIGRATION_STATE = OUTBOUND to start data migration immediately or MIGRATION_STATE = PAUSED to postpone the start of data migration.

启用现有表的 Stretch DatabaseEnable Stretch Database for an existing table

若要为 Stretch Database 配置现有表,请运行 ALTER TABLE 命令。To configure an existing table for Stretch Database, run the ALTER TABLE command.

下面是迁移整个表并立即开始数据迁移的示例。Here's an example that migrates the entire table and begins data migration immediately.

USE <Stretch-enabled database name>;
GO
ALTER TABLE <table name>  
    SET ( REMOTE_DATA_ARCHIVE = ON ( MIGRATION_STATE = OUTBOUND ) ) ;  
GO

下面是仅迁移由 dbo.fn_stretchpredicate 内联表值函数标识的行并推迟数据迁移的示例。Here's an example that migrates only the rows identified by the dbo.fn_stretchpredicate inline table-valued function and postpones data migration. 有关筛选器函数的详细信息,请参阅 通过使用筛选器函数选择要迁移的行For more info about the filter function, see Select rows to migrate by using a filter function.

USE <Stretch-enabled database name>;
GO
ALTER TABLE <table name>  
    SET ( REMOTE_DATA_ARCHIVE = ON (  
        FILTER_PREDICATE = dbo.fn_stretchpredicate(),  
        MIGRATION_STATE = PAUSED ) ) ;  
 GO

有关详细信息,请参阅 ALTER TABLE (Transact-SQL)For more info, see ALTER TABLE (Transact-SQL).

创建已启用 Stretch Database 的新表Create a new table with Stretch Database enabled

若要创建已启用 Stretch Database 的新表,请运行 CREATE TABLE 命令。To create a new table with Stretch Database enabled, run the CREATE TABLE command.

下面是迁移整个表并立即开始数据迁移的示例。Here's an example that migrates the entire table and begins data migration immediately.

USE <Stretch-enabled database name>;
GO
CREATE TABLE <table name>
    ( ... )  
    WITH ( REMOTE_DATA_ARCHIVE = ON ( MIGRATION_STATE = OUTBOUND ) ) ;  
GO

下面是仅迁移由 dbo.fn_stretchpredicate 内联表值函数标识的行并推迟数据迁移的示例。Here's an example that migrates only the rows identified by the dbo.fn_stretchpredicate inline table-valued function and postpones data migration. 有关筛选器函数的详细信息,请参阅 通过使用筛选器函数选择要迁移的行For more info about the filter function, see Select rows to migrate by using a filter function.

USE <Stretch-enabled database name>;
GO
CREATE TABLE <table name> 
    ( ... )  
    WITH ( REMOTE_DATA_ARCHIVE = ON (  
        FILTER_PREDICATE = dbo.fn_stretchpredicate(),  
        MIGRATION_STATE = PAUSED ) ) ;  
GO  

有关详细信息,请参阅 CREATE TABLE (Transact-SQL)For more info, see CREATE TABLE (Transact-SQL).

另请参阅See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)