Enable Stretch Database for a databaseEnable Stretch Database for a database

適用於: 是SQL Server 2016 與更新版本 (僅限 Windows) 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲APPLIES TO: yesSQL Server 2016 and later (Windows only) noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

若要設定現有的資料庫以使用 Stretch Database,請在 SQL Server Management Studio 中為資料庫選取 [工作 | 延展 | 啟用] ,開啟 [啟用資料庫的延展功能精靈] 。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 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 Database Advisor 以識別符合延展資格的資料庫及資料表。Before you configure a database for Stretch, we recommend that you run the Stretch Database Advisor to identify databases and tables that are eligible for Stretch. Stretch Database Advisor 也能識別封鎖問題。The Stretch Database Advisor also identifies blocking issues. 如需詳細資訊,請參閱 執行 Stretch Database Advisor 以識別 Stretch Database 的資料庫和資料表For more info, see Identify databases and tables for Stretch Database by running Stretch Database Advisor.

  • 檢閱 Stretch Database 的限制Review Limitations for Stretch Database.

  • Stretch Database 會將資料移轉至 Azure。Stretch Database migrates data to Azure . 因此,您必須擁有 Azure 帳戶和計費用的訂閱。Therefore you have to have an Azure account and a subscription for billing. 若要取得 Azure 帳戶,請 按一下這裡To get an Azure account, click here.

  • 擁有建立新的 Azure 伺服器或選取現有的 Azure 伺服器所需的連線和登入資訊。Have the connection and login info you need to create a new Azure server or to select an existing Azure server.

必要條件:在伺服器上啟用 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.

  • 如果您已經有必要的系統管理權限,則 [啟用資料庫的延展功能精靈] 會設定伺服器以使用「延展」。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 並開啟 [遠端資料封存] 選項。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

如需 [啟用資料庫的延展功能精靈] 的相關資訊,包括您必須輸入的資訊及必須決定的選擇,請參閱 開始執行啟用資料庫的延展功能精靈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. 以 CREDENTIAL 引數提供現有的系統管理員認證,或指定 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 Advisor 以識別 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)