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

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

匯入及匯出的最佳作法

匯入及匯出資料時,請考量下列最佳做法:

請勿使用 Cloud Storage「要求者付費」值區

您無法使用已啟用付款者為要求者功能的 Cloud Storage bucket,從 Cloud SQL 匯入及匯出資料。

壓縮資料以減少費用

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

Here,

  • 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

Here,

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

詳情請參閱 Microsoft 說明文件。

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

在 Cloud SQL 中匯入及匯出資料的另一種方法,是使用大量複製程式 (bcp) 公用程式。您可以使用 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

Here,

  • 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

Here,

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

詳情請參閱 Microsoft 說明文件。

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

您可以使用大量插入功能,將 Cloud Storage 檔案中的資料匯入 SQL Server 適用的 Cloud SQL 資料庫。

本節說明下列事項:

必要角色和權限

如要設定大量插入,您需要:

  • 您要匯入資料的資料庫具備 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 金鑰,讓金鑰失效。

下列作業可能會不小心轉移存取金鑰和密鑰,並開放使用:

  • 複製執行個體:金鑰和密鑰會出現在複製的執行個體中。
  • 建立唯讀副本:建立的唯讀副本會提供金鑰和密鑰。
  • 從備份還原:從備份還原的執行個體上,可使用金鑰和密鑰。

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

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

效能

建議您採取下列做法,降低使用大量插入功能時對效能的影響:

  • 請測試並為 @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 bucket。建議您為服務帳戶建立 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="cloud sql enable bulk insert"=off
  

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

或者,您也可以執行下列指令,清除所有資料庫旗標:

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

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

使用條帶匯入和匯出

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

驗證匯入的資料庫

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

已知限制

如需已知限制清單,請參閱「匯入及匯出資料的問題」。

安全性風險

從不受信任的來源匯入 SQL Server 備份檔案 (.bak),可能會造成嚴重的安全風險。

雖然保留在信任邊界內的備份檔案通常可以信任,但匯入惡意備份檔案可能會危害整個環境的安全。

惡意備份檔案 (.bak) 可能會讓應用程式面臨下列安全性風險:

  • 匯入的備份檔案可能會占用 SQL Server 執行個體的大部分空間。
  • 匯入的備份檔案可能會提升權限,並取得未經授權的存取權。

這類攻擊會在任何驗證指令碼或安全性檢查執行前發生,因此極為危險。

匯入不受信任的備份檔案,等同於在重要伺服器或虛擬機器上執行不受信任的應用程式,並在環境中執行任意程式碼。

最佳做法

請採用下列安全性最佳做法,降低惡意備份檔案對 SQL Server 適用的 Cloud SQL 環境造成的威脅:

  • 將備份檔案匯入作業視為「高風險」作業。
  • 使用隔離的執行個體,有助於縮小威脅服務範圍。
  • 請只使用信任的備份檔案。請勿匯入不明或外部來源的備份檔案。
  • 請務必使用未離開信任邊界的備份檔案。請確認備份檔案來自信任的範圍內。
  • 切勿為了方便而略過安全控管機制。
  • 啟用伺服器層級稽核功能,擷取備份檔案和匯入事件,並協助降低稽核規避風險。

詳情請參閱「還原不受信任來源的備份資料會造成安全風險」。

自動執行匯出作業

雖然 Cloud SQL 未提供內建方法來自動匯出資料庫,但您可以使用多個 Google Cloud元件建構自己的自動化工具。詳情請參閱這篇教學課程。

疑難排解

以下各節說明匯入和匯出作業的疑難排解。

排解匯入作業問題

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

關閉未使用的作業。檢查 Cloud SQL 執行個體的 CPU 和記憶體用量,確保有足夠的可用資源。如要確保有最多資源可供匯入作業使用,最好的方法是在開始作業前重新啟動執行個體。

重新啟動:

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

匯入前,請先建立資料庫使用者。

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 functions 等 Google Cloud產品,建構自己的自動匯出系統,類似這篇自動備份文章。

後續步驟