Enable Stretch Database for a databaseEnable Stretch Database for a database

适用对象: 是SQL Server(仅限从 SQL Server 2016 起的 Windows 版)否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server (Windows only starting with 2016) noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

若要为现有数据库配置 Stretch Database,请在 SQL Server Management Studio 中为数据库选择“任务 | Stretch | 启用” ,以打开“为数据库启用 Stretch” 向导。To configure an existing database for Stretch Database, select Tasks | Stretch | Enable for a database in SQL Server Management Studio to open the Enable Database for Stretch wizard. 你也可以使用 Transact-SQL 来为数据库启用 Stretch Database。You can also use Transact-SQL to enable Stretch Database for a database.

如果你为单个表选择“任务 | Stretch | 启用” ,且尚未为数据库启用 Stretch Database,向导将会为数据库配置 Stretch Database,并在此过程中让你配置表。If you select Tasks | Stretch | Enable for an individual table, and you have not yet enabled the database for Stretch Database, the wizard configures the database for Stretch Database and lets you select tables as part of the process. 请执行本主题中的步骤,而非为表启用 Stretch Database中的步骤。Follow the steps in this article instead of the steps in Enable Stretch Database for a table.

在数据库或表上启用 Stretch Database 需要 db_owner 权限。Enabling Stretch Database on a database or a table requires db_owner permissions. 在数据库上启用 Stretch Database 也需要 CONTROL DATABASE 权限。Enabling Stretch Database on a database also requires CONTROL DATABASE permissions.

备注

之后如果要禁用 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.

开始操作之前Before you get started

先决条件:在服务器上启用 Stretch DatabasePrerequisite: Enable Stretch Database on the server

必须先在本地服务器上启用 Stretch Database,然后才能在数据库或表上启用它。Before you can enable Stretch Database on a database or a table, you have to enable it on the local server. 此操作需要 sysadmin 或 serveradmin 权限。This operation requires sysadmin or serveradmin permissions.

  • 如果你拥有必要的管理权限,“为数据库启用 Stretch” 向导将会为服务器配置 Stretch。If you have the required administrative permissions, the Enable Database for Stretch wizard configures the server for Stretch .

  • 如果你没有必要的权限,在你运行向导之前,管理员必须通过运行 sp_configure 来手动启用该选项,否则便需由管理员运行该向导。If you don't have the required permissions, an administrator has to enable the option manually by running sp_configure before you run the wizard, or an administrator has to run the wizard.

若要在服务器上手动启用 Stretch Database,请运行 sp_configure 并打开 remote data archive 选项。To enable Stretch Database on the server manually, run sp_configure and turn on the remote data archive option. 下列示例通过将 remote data archive 选项的值设置为 1 来启用它。The following example enables the remote data archive option by setting its value to 1.

EXEC sp_configure 'remote data archive' , '1';  
GO

RECONFIGURE;  
GO  

有关详细信息,请参阅配置远程数据存档服务器配置选项sp_configure (Transact-SQL)For more info, see Configure the remote data archive Server Configuration Option and sp_configure (Transact-SQL).

使用向导在数据库上启用 Stretch DatabaseUse the wizard to enable Stretch Database on a database

有关为数据库启用延伸向导的信息,包括必须输入的信息以及必须做出的选择,请参阅 Get started by running the Enable Database for Stretch Wizard(通过运行“启用数据库延伸向导”入门)。For info about the Enable Database for Stretch Wizard, including the info that you have to enter and the choices that you have to make, see Get started by running the Enable Database for Stretch Wizard.

使用 Transact-SQL 在数据库上启用 Stretch DatabaseUse Transact-SQL to enable Stretch Database on a database

必须先在数据库上启用 Stretch Database,然后才能在单个表上启用它。Before you can enable Stretch Database on individual tables, you have to enable it on the database.

在数据库或表上启用 Stretch Database 需要 db_owner 权限。Enabling Stretch Database on a database or a table requires db_owner permissions. 在数据库上启用 Stretch Database 也需要 CONTROL DATABASE 权限。Enabling Stretch Database on a database also requires CONTROL DATABASE permissions.

  1. 开始之前,请为 Stretch Database 迁移的数据选择现有 Azure 服务器,或创建新的 Azure 服务器。Before you begin, choose an existing Azure server for the data that Stretch Database migrates, or create a new Azure server.

  2. 在 Azure 服务器上,以 SQL Server 的 IP 地址创建能让 SQL Server 与远程服务器通信的防火墙规则。On the Azure server, create a firewall rule with the IP address range of the SQL Server that lets SQL Server communicate with the remote server.

    可以轻松找到所需值,并且通过尝试从 SQL Server Management Studio (SSMS) 中的对象资源管理器连接 Azure 服务器来创建防火墙规则。You can easily find the values you need and create the firewall rule by attempting to connect to the Azure server from Object Explorer in SQL Server Management Studio (SSMS). SSMS 可帮助你通过打开以下对话框(已包括所需的 IP 地址值)来创建规则。SSMS helps you to create the rule by opening the following dialog box which already includes the required IP address values.

    Stretch 的防火墙规则

  3. 若要为 SQL Server 数据库配置 Stretch Database,该数据库必须拥有数据库主密钥。To configure a SQL Server database for Stretch Database, the database has to have a database master key. 数据库主密钥能保护 Stretch Database 用来连接到远程数据库的凭据。The database master key secures the credentials that Stretch Database uses to connect to the remote database. 下面是创建一个新数据库主密钥的示例。Here's an example that creates a new database master key.

    USE <database>; 
    GO  
    
    CREATE MASTER KEY ENCRYPTION BY PASSWORD='<password>'; 
    GO
    

    有关数据库主密钥的详细信息,请参阅 CREATE MASTER KEY (Transact-SQL)创建数据库主密钥For more info about the database master key, see CREATE MASTER KEY (Transact-SQL) and Create a Database Master Key.

  4. 当你为数据库配置 Stretch Database 时,必须为 Stretch Database 提供凭据,以供本地 SQL Server 与远程 Azure 服务器之间进行通信使用。When you configure a database for Stretch Database, you have to provide a credential for Stretch Database to use for communication between the on premises SQL Server and the remote Azure server. 您有两种选择:You have two options.

    • 你可以提供管理员凭据。You can provide an administrator credential.

      • 如果通过运行向导启用 Stretch Database,则可以在那时创建凭据。If you enable Stretch Database by running the wizard, you can create the credential at that time.

      • 如果计划通过运行 ALTER DATABASE启用 Stretch Database,则必须在运行 ALTER DATABASE 以启用 Stretch Database 之前手动创建凭据。If you plan to enable Stretch Database by running ALTER DATABASE, you have to create the credential manually before you run ALTER DATABASE to enable Stretch Database.

      下面是创建一个新凭据的示例。Here's an example that creates a new credential.

      CREATE DATABASE SCOPED CREDENTIAL <db_scoped_credential_name>  
          WITH IDENTITY = '<identity>' , SECRET = '<secret>' ;
      GO   
      

      有关凭据的详细信息,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)For more info about the credential, see CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL). 创建凭据需要 ALTER ANY CREDENTIAL 权限。Creating the credential requires ALTER ANY CREDENTIAL permissions.

    • 你可以使用联合服务帐户,让 SQL Server 与远程 Azure 服务器在下列条件全数成立时进行通信。You can use a federated service account for the SQL Server to communicate with the remote Azure server when the following conditions are all true.

      • 用来运行 SQL Server 实例的服务帐户为域帐户。The service account under which the instance of SQL Server is running is a domain account.

      • 域帐户属于其 Active Directory 与 Azure Active Directory 联合的域。The domain account belongs to a domain whose Active Directory is federated with Azure Active Directory.

      • 远程 Azure 服务器已配置为支持 Azure Active Directory 身份验证。The remote Azure server is configured to support Azure Active Directory authentication.

      • 用来运行 SQL Server 实例的服务帐户在远程 Azure 服务器上必须配置为 dbmanager 或 sysadmin 帐户。The service account under which the instance of SQL Server is running must be configured as a dbmanager or sysadmin account on the remote Azure server.

  5. 若要为数据库配置 Stretch Database,请运行 ALTER DATABASE 命令。To configure a database for Stretch Database, run the ALTER DATABASE command.

    1. 对于 SERVER 参数,请提供现有 Azure 服务器的名称,包括名称的 .database.windows.net 部分 — 例如 MyStretchDatabaseServer.database.windows.netFor the SERVER argument, provide the name of an existing Azure server, including the .database.windows.net portion of the name - for example, MyStretchDatabaseServer.database.windows.net.

    2. 提供具有 CREDEMTIAL 参数的现有管理员凭据,或指定 FEDERATED_SERVICE_ACCOUNT = ON。Provide an existing administrator credential with the CREDENTIAL argument, or specify FEDERATED_SERVICE_ACCOUNT = ON. 下面的示例提供现有的凭据。The following example provides an existing credential.

    ALTER DATABASE <database name>  
        SET REMOTE_DATA_ARCHIVE = ON  
            (  
                SERVER = '<server_name>' ,  
                CREDENTIAL = <db_scoped_credential_name>  
            ) ;  
    GO
    

后续步骤Next steps

另请参阅See Also

通过运行 Stretch Database 顾问标识适用于 Stretch Database 的数据库和表 Identify databases and tables for Stretch Database by running Stretch Database Advisor
ALTER DATABASE SET 选项 (Transact-SQL)ALTER DATABASE SET Options (Transact-SQL)