匯入及匯出資料的最佳做法

透過集合功能整理內容 你可以依據偏好儲存及分類內容。

匯入及匯出的最佳作法

以下是匯入及匯出資料時的最佳做法：

• 不要使用 Cloud Storage 的「要求者付費」值區
• 壓縮資料以減少費用。
• 減少長時間執行的匯入和匯出程序
• 使用 bcp 公用程式匯入及匯出資料
• 使用大量插入功能匯入資料
• 使用 SqlPackage 匯入及匯出資料
• 使用分割匯入和匯出功能
• 驗證匯入的資料庫

不要使用 Cloud Storage 的「要求者付費」值區

當您從 Cloud SQL 執行匯入和匯出作業時，不能使用已啟用要求者付費的 Cloud Storage 值區。

壓縮資料以減少費用

Cloud SQL 支援匯入及匯出已壓縮及未壓縮的檔案。壓縮可以節省 Cloud Storage 中的大量儲存空間並減少儲存成本，特別是在您要匯出大型執行個體時。

匯出 BAK 檔案時，請使用 .gz 副檔名壓縮資料。匯入副檔名為 .gz 的檔案時，檔案會自動解壓縮。

減少長時間執行的匯入與匯出程序

匯入至 Cloud SQL 和從 Cloud SQL 匯出資料可能需要很長的時間才能完成，具體取決於所處理資料的大小。這可能會造成下列影響：

• 您無法停止長時間執行的 Cloud SQL 執行個體作業。
• 您一次只能為每個執行個體執行一項匯入或匯出作業，且長時間執行的匯入或匯出作業會阻斷其他作業，例如每日自動備份作業。

您可以使用 Cloud SQL 匯入或匯出功能，並使用較小批次的資料，藉此縮短完成每項作業所需的時間。

對於整個資料庫遷移作業，您通常應使用 BAK 檔案，而非 SQL 檔案進行匯入。一般來說，從 SQL 檔案匯入資料的時間會比從 BAK 檔案匯入資料的時間長得多。

使用 SqlPackage 匯入及匯出資料

您可以使用 SqlPackage 在 Cloud SQL 中匯入及匯出資料。您可以將 SQL 資料庫 (包括資料庫結構定義和使用者資料) 匯出至 BACPAC 檔案 (.bacpac)，並將結構定義和資料表資料從 BACPAC 檔案匯入至新的使用者資料庫。

SqlPackage 會使用您的憑證連線至 SQL Server，執行資料庫匯入和匯出作業。讓所有 Cloud SQL 使用者都能進行遷移。如要執行匯入和匯出作業，您必須具備下列條件：

• 已連線至執行個體的工作站，可用於執行 SqlPackage。如要進一步瞭解連線選項，請參閱「關於連線選項」。

• 系統已安裝 SqlPackage。如要進一步瞭解如何下載及安裝 SqlPackage，請參閱 Microsoft 說明文件。

• 用於存取執行個體的憑證。如要進一步瞭解如何設定憑證，請參閱「如何驗證 Cloud SQL」。

範例

c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage
/Action:Import /tsn:myTargetServer /tdn:AdventureWorks2017
/tu:myUsername /sf:mySourceFile
/TargetTrustServerCertificate:True /tp:myPassword

c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage
/Action:Export /TargetFile:"myTargetFile"
/ssn:mySourceServer /su:myUsername /sdn:AdventureWorks2017
/SourceTrustServerCertificate:True /sp:myPassword

使用 bcp 公用程式匯入及匯出資料

使用大量複製程式 (bcp) 公用程式，也是在 Cloud SQL 中匯入及匯出資料的另一種方法。您可以使用 bcp 公用程式，將資料從 SQL Server 資料庫匯出至資料檔案，並將資料從資料檔案匯入 SQL Server 資料庫。bcp 公用程式會使用您的憑證連線至 SQL Server，執行資料庫匯入和匯出作業。讓所有 Cloud SQL 使用者都能進行轉移。如要執行匯入和匯出作業，您必須具備下列條件：

• 可執行 bcp 公用程式，且可連線至 Cloud SQL 執行個體的工作站。如要進一步瞭解連線選項，請參閱「關於連線選項」。

• 系統上安裝的 bcp 公用程式。如要進一步瞭解 bcp 的下載和安裝作業，請參閱 Microsoft 說明文件。

• 設定用於存取執行個體的憑證。如要進一步瞭解如何設定憑證，請參閱「如何驗證 Cloud SQL」。

範例

bcp Person.Person in "person.csv" -d AdventureWorks2017 -U myLoginID -S myServer

bcp Person.Person out "person.dat" -U myLoginID -S myServer -d AdventureWorks2017

使用大量插入功能匯入資料

大量插入功能可讓您從儲存在 Cloud Storage 的檔案中，將資料匯入 Cloud SQL for SQL Server 資料庫。

本節說明下列事項：

• 必要角色和權限
• 使用大量插入功能的注意事項
• 執行大量插入作業
• 查看匯入的資料
• 停用大量插入功能

必要角色和權限

如要設定大量插入作業，您需要：

• 您要匯入資料的資料庫中的 CONTROL 權限。

• HMAC 存取金鑰和密鑰已對應至具有下列權限的 IAM 帳戶：

  • storage.buckets.get
  • storage.objects.create 和 storage.multipartUploads.create 可用於寫入錯誤記錄和錯誤資料範例。

  或者，您也可以使用下列角色：

  • Storage Object Viewer
  • Storage Object Creator 用於寫入錯誤記錄和無效資料範例。

如要使用大量插入功能，您需要具備下列條件：

• msdb.dbo.gcloudsql_bulk_insert 預存程序的 EXECUTE 權限。在執行個體啟用大量插入功能後，Cloud SQL 會建立預存程序。Cloud SQL 預設會將 EXECUTE 權限授予 sqlserver 管理員帳戶。
• 您要匯入資料的物件上的 INSERT 權限。

如要進一步瞭解如何建立大量插入作業的使用者，請參閱「建立及管理使用者」。

使用大量插入作業的注意事項

本節提供使用大量插入作業時，處理執行個體安全性、效能和可靠性的建議。

安全性

Cloud SQL 會將 HMAC 存取金鑰和密鑰加密並儲存在執行個體中，做為資料庫範圍憑證。儲存後就無法存取這些值。您可以使用 T-SQL 指令放棄資料庫範圍憑證，從執行個體中刪除金鑰和密碼金鑰。如果您在金鑰和密鑰儲存在執行個體時進行備份，備份檔案就會包含該金鑰和密鑰。您也可以停用並刪除 HMAC 金鑰，讓金鑰失效。

下列作業可能會不小心傳輸存取金鑰和密鑰，並使其可供使用：

• 複製執行個體：複製的執行個體會提供金鑰和密鑰。
• 建立唯讀備用資源：在建立的唯讀備用資源上提供金鑰和 Secret。
• 從備份還原：從備份還原的執行個體上可使用金鑰和密鑰。

建議您在執行這些作業後，從目標執行個體中刪除金鑰和機密金鑰。

大量插入作業可以將無法剖析的資料寫入儲存在 Cloud Storage 值區中的檔案。如果您想保護大量插入作業可存取的資料，請設定 VPC 服務控管。

成效

建議您採取下列做法，以便在使用大量插入作業時降低對效能的影響：

• 請測試並設定適當的 @batchsize 值，因為根據預設，所有資料都會以單一批次匯入。
• 如果是大量插入作業，請暫時停用索引，加快資料插入作業。
• 盡可能使用 @tablock 選項，因為這可以減少爭用情形，並提高資料載入效能。
• 使用 @ordercolumnsjson 參數指定以叢集索引順序排序的資料。這有助於提升執行個體效能。

可靠性

建議您在使用大量插入作業時，採取下列做法來降低對執行個體可靠性的影響：

• 如果發生錯誤，且使用 @batchsize，可能會導致部分資料載入。您可能需要手動清理執行個體中的這類資料。
• 使用 @errorfile 選項，記錄載入程序中偵測到的錯誤和錯誤資料範例記錄。這樣就能更輕鬆地找出無法載入的資料列。

執行大量插入作業

您可以使用下列儲存程序執行大量插入作業：

msdb.dbo.gcloudsql_bulk_insert

詳情請參閱「使用大量插入作業的儲存程序」。

範例：從 Cloud Storage 中的檔案匯入資料，並指定錯誤檔案

1. 啟用大量插入功能

如要在執行個體上啟用大量插入功能，請啟用 cloud sql enable bulk insert 旗標。

gcloud sql instances patch INSTANCE_NAME --database-flags="cloud sql enable bulk insert"=on

將 INSTANCE_NAME 替換為要用於大量插入作業的執行個體名稱。

詳情請參閱「設定資料庫標記」。

在執行個體上啟用這個旗標後，Cloud SQL 會在執行個體上安裝大量插入預存程序，並授予 sqlserver 管理員帳戶執行權限。

2. 建立 HMAC 金鑰

您需要 HMAC 金鑰才能存取 Cloud Storage 值區。建議您為服務帳戶建立 HMAC 金鑰，並將服務帳戶權限授予要用於大量插入作業的資料夾。如需更多資訊和安全性考量，請參閱「使用大量插入功能時的考量事項」。

3. 建立要匯入的範例資料

1. 使用文字編輯器建立使用 ANSI 或 UTF-16 編碼的檔案，其中包含以下範例資料。將檔案儲存在 Cloud Storage 值區中，並將檔案命名為 bulkinsert.bcp。

   1,Elijah,Johnson,1962-03-21
   2,Anya,Smith,1982-01-15
   3,Daniel,Jones,1990-05-21
   

2. 使用下列範例資料建立格式檔案。將檔案儲存在 Cloud Storage 值區中，並將檔案命名為 bulkinsert.fmt 等。如要進一步瞭解 SQL Server 中的 XML 和非 XML 格式檔案，請參閱「建立格式檔案」。

   13.0
   4
   1       SQLCHAR             0       7       ","      1     PersonID               ""
   2       SQLCHAR             0       25      ","      2     FirstName            SQL_Latin1_General_CP1_CI_AS
   3       SQLCHAR             0       30      ","      3     LastName            SQL_Latin1_General_CP1_CI_AS
   4       SQLCHAR             0       11      "\r\n"   4     BirthDate             ""
   

4. 執行預存程序

1. 使用 sqlserver 使用者連線至執行個體，並建立用於大量插入的範例資料庫和資料表。

   USE MASTER
   GO
   -- create test database
   DROP DATABASE IF EXISTS bulktest
   CREATE DATABASE bulktest
   GO
   
   -- create table to insert
   USE bulktest;
   GO
   CREATE TABLE dbo.myfirstimport(
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );
   

2. 建立資料庫主金鑰、資料庫範圍憑證和外部資料來源。將身分設為 S3 Access Key。

     -- create master key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
   
   -- create database scoped credential
   CREATE DATABASE SCOPED CREDENTIAL GCSCredential
   WITH IDENTITY = 'S3 Access Key',
   SECRET = '<Access key>:<Secret>';
   
   --create external data source
   CREATE EXTERNAL DATA SOURCE GCSStorage
   WITH ( TYPE = BLOB_STORAGE,
   LOCATION = 's3://storage.googleapis.com/bulk-insert-demo/'
   , CREDENTIAL = GCSCredential
   );
   
   CREATE EXTERNAL DATA SOURCE GCSStorageError
   WITH ( TYPE = BLOB_STORAGE,
   LOCATION = 's3://storage.googleapis.com/bulk-insert-demo/'
   , CREDENTIAL = GCSCredential
   );
   

3. 執行大量插入預存程序，匯入樣本資料。

   EXEC msdb.dbo.gcloudsql_bulk_insert
   @database = 'bulktest',
   @schema = 'dbo',
   @object = 'myfirstimport',
   @file = 's3://storage.googleapis.com/bulk-insert-demo/bulkinsert.bcp',
   @formatfile = 's3://storage.googleapis.com/bulk-insert-demo/bulkinsert.fmt',
   @fieldquote = '"',
   @formatfiledatasource = 'GCSStorage',
   @ROWTERMINATOR = '0x0A',
   @fieldterminator = ',',
   @datasource ='GCSStorage',
   @errorfiledatasource = 'GCSStorageError',
   @errorfile = 's3://storage.googleapis.com/oom-data/bulkinsert/bulkinsert_sampleimport.log',
   @ordercolumnsjson =
   '[{"name": "PersonID","order": " asc "},{"name": "BirthDate","order": "asc"}]'
   
   

查看匯入的資料

您可以使用下列任一方法查看匯入的資料：

• 執行以下查詢：

  SELECT * FROM dbo.myfirstimport
  

• Cloud SQL 會將此程序的記錄新增至 SQL 錯誤記錄檔。您可以在 Cloud Logging 中查看這項資訊。您也可以在 SQL Server Management Studio (SSMS) 的 SQL 錯誤記錄資料中查看這項資訊。

停用大量插入

如要停用大量插入功能，請移除 cloud sql enable bulk insert 標記：

  gcloud sql instances patch INSTANCE_NAME --database-flags="cloudsql enable bulk insert"=off
  

將 INSTANCE_NAME 替換為要移除大量插入作業的執行個體名稱。

或者，您也可以執行下列指令來清除所有資料庫標記：

  gcloud sql instances patch INSTANCE_NAME --clear-database-flags
  

將 INSTANCE_NAME 替換為要移除大量插入作業的執行個體名稱。

使用分割匯入和匯出

執行分割匯入或匯出作業，可縮短作業完成所需的時間，並允許匯入和匯出超過 5 TB 的資料庫。詳情請參閱「使用 BAK 檔案匯出及匯入」一文。

驗證匯入的資料庫

匯入作業完成後，請連線至資料庫並執行適當的資料庫指令，確保內容正確無誤。例如連結並列出資料庫、資料表和特定項目。

已知限制

如需已知限制的清單，請參閱匯入和匯出資料的問題。

自動化匯出作業

雖然 Cloud SQL 並未提供內建方式自動匯出資料庫，但您可以使用多個 Google Cloud元件自行建構自動化工具。詳情請參閱 這份教學課程。

疑難排解

匯入作業疑難排解

排解匯出作業問題

後續步驟

• 瞭解如何使用 BAK 檔案匯入及匯出資料。
• 瞭解如何使用 SQL 傾印檔案匯入資料。
• 瞭解如何啟用自動備份。
• 瞭解如何從備份還原。