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

本頁面提供使用 Cloud SQL 匯入及匯出資料的最佳做法。如需將資料匯入 Cloud SQL 的逐步操作說明，請參閱「匯入資料」。如需匯出資料的逐步操作說明 (無論資料位於 Cloud SQL 或您管理的執行個體)，請參閱「匯出資料」。

匯入及匯出的最佳作法

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

請勿使用 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 進行驗證」。

範例

匯入

如要將資料匯入資料庫 AdventureWorks2017，請執行下列指令：

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

在此，

  • mySourceFile 是您要用做本機儲存空間動作來源的來源檔案。如果使用這個參數，其他來源參數將無效。
  • myTargetServer 是代管目標資料庫的伺服器名稱。
  • myUsername 是您要用來存取目標資料庫的 SQL Server 使用者名稱。
  • myPassword 是憑證中的密碼。

詳情請參閱 Microsoft 說明文件。

匯出

如要從資料庫 AdventureWorks2017 匯出資料，請執行下列指令：

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

在此，

  • myTargetFile 是您要使用的目標檔案 (.dacpac 檔案)，而非資料庫。如果使用這個參數，其他目標參數將無效。這項參數不適用於僅支援資料庫目標的動作。
  • myUsername 是您要用來存取來源資料庫的 SQL Server 使用者名稱。
  • mySourceServer 是代管來源資料庫的伺服器名稱。
  • myPassword 是憑證中的密碼。

詳情請參閱 Microsoft 說明文件。

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

您也可以使用大量複製程式 (bcp) 公用程式，在 Cloud SQL 中匯入及匯出資料。使用 bcp 公用程式，您可以將資料從 SQL Server 資料庫匯出至資料檔案，也可以將資料從資料檔案匯入 SQL Server 資料庫。bcp 公用程式會使用您的憑證連線至 SQL Server，執行資料庫匯入和匯出作業。所有 Cloud SQL 使用者皆可使用這項功能。 如要執行匯入和匯出作業，您必須具備下列條件：

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

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

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

範例

匯入

如要將 person.csv 檔案中的資料匯入 AdventureWorks2017 資料庫的 Person 資料表，請執行下列指令：

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

在此，

  • myLoginID 是用來連線至 SQL Server 的登入 ID。
  • myServer 是您要連線的 SQL Server 執行個體。如果未指定伺服器，bcp 公用程式會連線至本機電腦上的 SQL Server 預設執行個體。

詳情請參閱 Microsoft 說明文件。

匯出

如要將 AdventureWorks2017 資料庫的 Person 資料表資料匯出至 person.dat 檔案，請執行下列指令：

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

在此，

  • myLoginID 是用來連線至 SQL Server 的登入 ID。
  • myServer 是您要連線的 SQL Server 執行個體。如果未指定伺服器，bcp 公用程式會連線至本機電腦上的 SQL Server 預設執行個體。

詳情請參閱 Microsoft 說明文件。

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

大量插入功能可讓您從儲存在 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，瞭解如何撰寫錯誤記錄，以及查看無效資料的範例。

如要使用大量插入功能，您需要：

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

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

使用大量插入的注意事項

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

安全性

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

下列作業可能會在無意間轉移存取金鑰和密鑰，並讓這些金鑰和密鑰可供使用：

  • 複製執行個體：複製的執行個體會提供金鑰和密鑰。
  • 建立唯讀副本：金鑰和密鑰會顯示在建立的唯讀副本中。
  • 從備份還原：金鑰和密碼會儲存在從備份還原的執行個體中。

執行這些作業後，建議您從目標執行個體捨棄金鑰和密碼。

如果無法剖析資料，大量插入作業會將資料寫入儲存在 Cloud Storage bucket 中的檔案。 如要保護大量插入作業可存取的資料，請設定 VPC Service Controls。

成效

建議您採取下列做法，在使用大量插入功能時減輕對效能的影響：

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

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

  • 如果發生故障並使用 @batchsize，可能會導致資料載入不完整。您可能需要在執行個體上手動清除這項資料。
  • 使用 @errorfile 選項記錄載入程序中偵測到的錯誤和不良資料範例。這樣就能輕鬆找出無法載入的資料列。

執行大量插入作業

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

msdb.dbo.gcloudsql_bulk_insert

詳情請參閱「Stored procedure for using 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 金鑰，並授予服務帳戶權限，存取要用於大量插入作業的 bucket。如需更多資訊和安全性考量事項，請參閱「使用大量插入作業時的考量事項」。

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

  1. 使用文字編輯器建立 ANSI 或 UTF-16 編碼的檔案，並加入下列範例資料。將檔案儲存在 Cloud Storage bucket 中，並命名為 bulkinsert.bcp (例如)。

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

  2. 使用下列範例資料建立格式檔案。將檔案儲存在 Cloud Storage bucket 中，並命名為 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元件建構自己的自動化工具。詳情請參閱 這篇教學文章。

疑難排解

排解匯入作業問題

問題 疑難排解
HTTP Error 409: Operation failed because another operation was already in progress。 您的執行個體已有待處理的作業。系統一次只能執行一項作業。請待目前的作業完成後再提出要求。
匯入作業耗時過長。 有效連線過多可能會干擾匯入作業。

關閉未使用的作業。檢查 Cloud SQL 執行個體的 CPU 和記憶體用量，確保有充足的可用資源。如要確保匯入作業有最多資源可用，請先重新啟動執行個體，再開始執行作業。

重新啟動：

  • 關閉所有連線。
  • 終止可能消耗資源的任何工作。
如果傾印檔案中有一或多位參照的使用者不存在，匯入作業就會失敗。 匯入傾印檔案前，擁有物件或已取得傾印資料庫中物件存取權的所有資料庫使用者，都必須存在於目標資料庫中。否則匯入作業無法以原始擁有權或權限重新建立物件。

匯入前請先建立資料庫使用者。
LSN 不符 交易記錄備份的匯入順序有誤，或交易記錄鏈中斷。
按照備份集資料表中的順序，匯入交易記錄備份。
StopAt 過早 這項錯誤表示交易記錄檔中的第一筆記錄晚於 StopAt 時間戳記。舉例來說，如果交易記錄檔中的第一筆記錄是在 2023-09-01T12:00:00，而 StopAt 欄位的值為 2023-09-01T11:00:00，Cloud SQL 就會傳回這項錯誤。
請務必使用正確的StopAt時間戳記和交易記錄檔。

排解匯出作業問題

問題 疑難排解
HTTP Error 409: Operation failed because another operation was already in progress. 您的執行個體已有待處理的作業。系統一次只能執行一項作業。請待目前的作業完成後再提出要求。
HTTP Error 403: The service account does not have the required permissions for the bucket. 請確認值區存在，且 Cloud SQL 執行個體 (執行匯出作業) 的服務帳戶具備 Storage Object Creator 角色 (roles/storage.objectCreator)，以便將匯出內容載入值區。請參閱「Cloud Storage 的 IAM 角色」。
您希望系統自動匯出資料。 Cloud SQL 不提供自動匯出功能。

您可以使用 Cloud Scheduler、Pub/Sub 和 Cloud Run 函式等產品，自行建構自動匯出系統，類似於這篇 自動備份文章。 Google Cloud

後續步驟