Share via

SQL Server 2016 新功能搶先看 - 受管理的備份

  • Article

SQL Server 2016 搶先看第七波，這次要帶大家認識強化過後的「受管理的備份」功能！

回顧系列專文：　

  1. SQL Server 2016 新功能搶先看 - Stretch Database / Managed Backup (內含 Demo 畫面)
  2. SQL Server 2016 新功能搶先看 - Live Query Statistics / JSON
  3. SQL Server 2016 新功能搶先看 - 動態資料遮罩 / 多重 tempdb 資料檔
  4. SQL Server 2016 新功能搶先看 - Stretch Database 詳解
  5. SQL Server 2016 新功能搶先看 - Always Encrypted
  6. SQL Server 2016 新功能搶先看 - Temporal Tables

本文將帶大家了解 SQL Server 2016 受管理的備份以下功能：

說明

SQL Server 2014 推出了多項讓企業可以輕鬆實現異地備份的需求，其中包括受管理的備份（Managed Backup），這是一項不需訂定備份計畫就能讓 SQL Server 依照工作負載的狀況，自動化將資料庫備份到 Microsoft Azure 儲存體的功能。

對 DBA 而言，受管理的備份是一項相當方便又實用的功能，很高興可以看到 SQL Server 2016 加強受管理的備份功能，增加了下列幾項令人振奮的改良，包括：

  1. 支援客製化備份排程（完整與交易紀錄備份）。
  2. 支援系統資料庫。
  3. 支援簡單復原模式的資料庫。
  4. 更豐富的使用者介面。

SQL Server 2016 受管理的備份功能，從所使用的結構描述到相關的系統預存程序，甚至底層的安全性機制與存放方式都與前一版截然不同，兩者比較如下表：

版本

SQL Server 2014

SQL Server 2016 CTP

結構描述

smart_admin

managed_backup

系統預存程序

sp_set_db_backup

sp_set_instance_backup

sp_backup_config_basic

sp_backup_config_advanced

sp_backup_config_schedule

安全性

SQL Server認證

Azure儲存體帳戶和存取金鑰

SQL Server認證

Azure共同存取簽章（SAS）Token

基礎存放

Microsoft Azure 儲存體的 page blobs

Microsoft Azure 儲存體的 block blobs

準備工作

在開始使用受管理的備份前，您必須具備 Microsoft Azure 訂用帳戶，本文假設您已經啟用 Azure 訂用帳戶，並且熟悉如何取得 Azure 訂用帳戶檔案和下載 Azure PowerShell 公用程式。

建立儲存體帳戶及容器

首先必須建立儲存體帳戶和容器，您可以使用 Azure 管理入口網站或 Azure PowerShell 來完成。

下列 PowerShell 指令碼示範在 Azure 位於東亞的資料中心建立名稱為 managedbackupstr 的儲存體帳戶，接著在該儲存體帳戶中建立名稱為 bakcontainer 的容器。

#在東亞資料中心建立名稱為managedbackupstr的儲存體帳戶

New-AzureStorageAccount -StorageAccountName "managedbackupstr" -Location "East Asia"

 

#建立儲存體帳戶的內容(Context)

$context = New-AzureStorageContext -StorageAccountName managedbackupstr -StorageAccountKey (Get-AzureStorageKey -StorageAccountName managedbackupstr).Primary

 

#建立容器

New-AzureStorageContainer -Name bakcontainer -Context $context

執行結果如下圖所示：

 

此時開啟 Azure 管理入口網站可以看到儲存體帳戶已經正確建立並且狀態顯示為線上，就可以接著建立用來儲存 SQL Server 備份媒體集所需的共用存取簽章（Shared Access Signature，SAS）。

建立共用存取簽章

為了讓 SQL Server 有權限存取儲存體帳戶，並且將備份媒體放在指定的容器，必須建立共用存取簽章，除了使用下列的 PowerShell 指令碼建立之外，您也可以透過一些工具來產生共用存取簽章的 token。

下列 PowerShell 指令碼用來建立儲存體帳戶的共用存取簽章 Token ，您可以依照您的環境自行修改訂用帳戶、儲存體及容器名稱。

#訂用帳戶名稱

$SubscriptionName='MSDN - VS Premium'

#儲存體帳戶名稱

$StorageAccountName='managedbackupstr'

#容器名稱

$ContainerName='bakcontainer'

Select-AzureSubscription -SubscriptionName $subscriptionName

$accountKeys = Get-AzureStorageKey -StorageAccountName $storageAccountName

$storageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $accountKeys.Primary

$container = Get-AzureStorageContainer -Context $storageContext -Name $ContainerName

$cbc = $container.CloudBlobContainer

$permissions = $cbc.GetPermissions();

$policyName = 'policy1'

$policy = new-object 'Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPolicy'

$policy.SharedAccessStartTime = $(Get-Date).ToUniversalTime().AddMinutes(-5)

$policy.SharedAccessExpiryTime = $(Get-Date).ToUniversalTime().AddYears(5)

$policy.Permissions = "Read,Write,List,Delete"

$permissions.SharedAccessPolicies.Add($policyName, $policy)

$cbc.SetPermissions($permissions);

$policy = new-object 'Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPolicy'

$sas = $cbc.GetSharedAccessSignature($policy, $policyName)

Write-Host 'Shared Access Signature= '$($sas.Substring(1))''

Write-Host 'Credential T-SQL'

$TSQL = "CREATE CREDENTIAL [{0}] WITH IDENTITY='Shared Access Signature', SECRET='{1}'" -f $cbc.Uri,$sas.Substring(1)

Write-Host $TSQL

執行結果如下圖所示，請先把紅色方框處的 T-SQL 指令碼複製下來。

回到 SQL Server Management Studio 貼上複製的內容，接著按 F5 執行查詢。

CREATE CREDENTIAL [https://managedbackupstr.blob.core.windows.net/bakcontainer]

WITH IDENTITY = 'Shared Access Signature',

SECRET = 'sv=2014-02-14&sr=c&sig=s23DlV2WhTCtyelR1NdBr30V5ehKwgPKUzNm9qaNBxU%3D&se=2015-07-23T03%3A39%3A30Z&sp=rwdl'

建立完成後可以在物件總管看到如下圖的結果。

啟用受管理的備份

截至本文撰寫為止，SQL Server 2016 CTP 2.2 尚未支援以圖形化介面建立或設定受管理的備份，暫時只能使用 T-SQL 來啟用受管理的備份。在啟用受管理的備份前，因為受管理的備份相依於 SQL Server Agent 服務，因此請先確定該服務為啟動狀態並且啟動模式設定為自動。

SQL Server 2014 在 msdb 系統資料庫的 smart_admin 結構描述中，提供用來管理受管理的備份的系統預存程序，SQL Server 2016 改採用 managed_backup 結構描述，並新增多個系統預存程序來讓您客製化設定備份的加密及頻率。 

您可以使用 sp_backup_config_basic 系統預存程序來啟用受管理的備份，並指定備份媒體保留期間和儲存體帳戶的 URL 等設定，設定的對象可以是單一資料庫或整個執行個體。可使用的參數包括：

(1) @enable_backup：用來設定啟用或關閉受管理的備份。

(2) @database_name：用來設定要啟用受管理備份的資料庫名稱。

(3) @container_url：用來指定儲存體帳戶的容器 URL。

(4) @retention_days：則是設定備份保留週期。

下列 T-SQL 指令碼示範使用 sp_backup_config_basic 系統預存程序為 ManagedBakDB 資料庫啟用受管理的備份，並將備份媒體存放在前面所建立的容器之中，以及設定保留備份媒體 3 天。

EXEC msdb.managed_backup.sp_backup_config_basic

 @enable_backup = 1,

 @database_name = 'ManagedBakDB',

 @container_url = [https://managedbackupstr.blob.core.windows.net/bakcontainer],

 @retention_days = 3

 

SELECT *

FROM msdb.managed_backup.fn_backup_db_config('ManagedBakDB')

執行結果如下圖所示：

 

下列 T-SQL 指令碼示範使用 sp_backup_config_basic 系統預存程序為執行個體層級啟用受管理備份，並指定備份媒體保留天數及備份 URL ，設定完畢後新建立或附加至執行個體的資料庫會自動套用該設定。

EXEC msdb.managed_backup.sp_backup_config_basic

 @container_url = [https://managedbackupstr.blob.core.windows.net/bakcontainer],

 @retention_days = 1,

 @enable_backup = 1

 

設定受管理的備份

除了前一節介紹的 sp_backup_config_basic 系統預存程序用來設定受管理的備份之基本組態外，SQL Server 2016 還提供下列系統預存程序可以為備份進行加密或自訂備份頻率與時間，說明如下：

1. managed_backup.sp_backup_config_advanced

sp_backup_config_advanced 系統預存程序可以用來為受管理的備份設定備份媒體所使用的加密方式。所需參數包括：

(1) @database_name：用來指定啟用受管理備份的資料庫名稱。

(2) @encryption_algorithm：用來設定用來加密備份媒體的演算法。

(3) @encryptor_type：用來設定加密方式，可以選擇使用憑證或非對稱金鑰。

(4) @encryptor_name：用來設定加密所使用的憑證或非對稱金鑰名稱。

下列 T-SQL 示範使用 sp_backup_config_advanced 系統預存程序為 msdb 系統資料庫啟用受管理的備份，並使用自我簽署憑證來為備份媒體加密，接著搭配 sp_backup_config_basic 系統預存程序來啟用受管理的備份及指定備份 URL 和保留天數。

USE master

GO

 

CREATE CERTIFICATE ManagedBakCert

   ENCRYPTION BY PASSWORD = 'p@ssw0rd'

   WITH SUBJECT = 'Managed Backup Cert'

GO

 

EXEC msdb.managed_backup.sp_backup_config_advanced

    @database_name = 'msdb'

    ,@encryption_algorithm = 'AES_256'

    ,@encryptor_type = 'CERTIFICATE'

    ,@encryptor_name = 'ManagedBakCert'

 

EXEC msdb.managed_backup.sp_backup_config_basic

  @enable_backup = 1,

  @database_name = 'msdb',

  @container_url = [https://managedbackupstr.blob.core.windows.net/bakcontainer],

  @retention_days = 1

 

SELECT * FROM msdb.managed_backup.fn_backup_db_config ('msdb')

執行結果如下圖所示：

 

2. managed_backup.sp_backup_config_schedule

sp_backup_config_schedule 系統預存程序可以用來為受管理的備份指定完整備份、交易記錄備份的時間及頻率。相關參數包括：

(1) @database_name：用來設定要啟用受管理備份的資料庫。

(2) @scheduling_option：用來設定備份資料庫的排程是由系統控制或自訂。

(3) @full_backup_freq_type：用來指定完整備份的頻率。

(4) @days_of_week：當@full_backup_freq_type設定為Weekly，可指定要在星期幾進行備份。

(5) @backup_begin_time：用來設定備份開始時間。

(6) @backup_duration：用來設定備份期間，但不保證可在指定的時間內完成。

(7) @log_backup_freq：用來設定交易記錄備份的頻率。

下列 T-SQL 指令碼示範使用 sp_backup_config_schedule 系統預存程序為 master 系統資料庫啟用受管理的備份，排定於每個星期日進行備份，備份頻率及週期則由系統決定，並使用 sp_backup_config_basic 系統預存程序來啟用受管理的備份及指定備份 URL 和保留天數。 

EXEC msdb.managed_backup.sp_backup_config_schedule

    @database_name =  'master'   

        ,@scheduling_option =  'System'

    ,@days_of_week =  'Sunday'

 

EXEC msdb.managed_backup.sp_backup_config_basic

  @enable_backup = 1,

  @database_name = 'master',

  @container_url = [https://managedbackupstr.blob.core.windows.net/bakcontainer],

  @retention_days = 1

 

SELECT * FROM msdb.managed_backup.fn_backup_db_config ('master')

執行結果如下圖所示：

下列 T-SQL 指令碼示範使用 sp_backup_config_schedule 系統預存程序，為 model 系統資料庫啟用受管理的備份，指定每週五的 23:00 進行備份，備份週期為 20 分鐘，及每 4 個小時進行交易記錄備份，並使用 sp_backup_config_basic 系統預存程序來啟用受管理的備份及指定備份 URL 和保留天數。

EXEC msdb.managed_backup.sp_backup_config_schedule

    @database_name =  'model'   

        ,@scheduling_option =  'Custom'

    ,@full_backup_freq_type =  'Weekly'

    ,@days_of_week =  'Friday'

    ,@backup_begin_time =  '23:00'

    ,@backup_duration =  '00:20'

    ,@log_backup_freq =  '04:00'  

 

EXEC msdb.managed_backup.sp_backup_config_basic

  @enable_backup = 1,

  @database_name = 'model',

  @container_url = [https://managedbackupstr.blob.core.windows.net/bakcontainer],

  @retention_days = 1

 

SELECT * FROM msdb.managed_backup.fn_backup_db_config ('model')

執行結果如下圖所示：

 

3. managed_backup.sp_backup_on_demand

針對已經啟用受管理的備份之資料庫，可以使用 sp_backup_on_demand 系統預存程序可用來執行臨機操作（ad hoc）的備份，使用這個方式備份交易記錄並不會破壞記錄連結（log chain）。相關參數包括：

(1) @database_name：用來設定欲進行備份的資料庫名稱。

(2) @type：用來設定備份的種類。

下列 T-SQL 指令碼示範使用 sp_backup_on_demand 系統預存程序為使用者資料庫 db3 進行手動的交易記錄備份。

EXEC msdb.managed_backup.sp_backup_on_demand

@database_name = 'db3'

,@type = 'Log'

執行結果如下圖所示：

診斷受管理的備份

受管理的備份讓資料庫備份更加具有彈性，且不需要訂定複雜的備份策略，只須考慮備份媒體要保留的天數，就能夠輕鬆達到異地備份的目的，但也因為 SQL Server 都自動且智慧的幫您處理這些繁瑣的資料庫備份細節，當備份異常或失敗時，該如何診斷問題發生原因進而排除問題？

您可以使用 sp_get_backup_diagnostics 系統預存程序來查詢備份到 Azure 儲存體的擴充事件（Extended Events）內容，進而監控受管理備份的執行狀況。相關參數包括：

(1) @xevent_channel：用來指定擴充事件通道名稱，包括Admin、Analytic、 Operational和Debug。

(2) @begin_time：用來指定欲查詢的擴充事件起始時間。

(3) @end_time：用來指定欲查詢的擴充事件結束時間。

下列 T-SQL 指令碼示範使用 sp_get_backup_diagnostics 系統預存程序取得受管理備份的執行狀況。

EXEC msdb.managed_backup.sp_get_backup_diagnostics

執行結果如下圖所示：

 

下列 T-SQL 指令碼示範使用 sp_get_backup_diagnostics 系統預存程序查詢Admin通道於 2015-07-22T08:31:18.089Z 到 2015-07-22T08:36:28.099Z 之間的擴充事件記錄。

EXEC msdb.managed_backup.sp_get_backup_diagnostics @xevent_channel = 'Admin',

  @begin_time = '2015-07-22T08:31:18.089Z', @end_time = '2015-07-22T08:36:28.099Z'

執行結果如下圖所示：

下列 T-SQL 指令碼示範使用 sp_get_backup_diagnostics 系統預存程序取得過去 30 分鐘 Analytic 通道的資料。

EXEC msdb.managed_backup.sp_get_backup_diagnostics

@xevent_channel = 'Analytic'

執行結果如下圖所示：

 

除了使用上述方法查看備份的相關擴充事件外，受管理的備份還提供健全狀態通知的功能，能夠在受管理的備份發生問題時以電子郵件通知系統管理員，使用這項功能必須先設定 Database Mail 及 SQL Server Agent 警示系統（如下圖）。

 

接著才可以 sp_set_parameter 系統預存程序來啟用電子郵件通知。

下列 T-SQL 指令碼示範使用 sp_set_parameter 系統預存程序設定電子郵件通知，請將 @parameter_value 參數改成想要收到通知的電子郵件位址。

EXEC msdb.managed_backup.sp_set_parameter

@parameter_name = 'SSMBackup2WANotificationEmailIds',

@parameter_value = '電子郵件位址'

當事件發生時，您會收到如下圖的診斷報告，幫助隨時掌握目前的備份狀況。

您所收到的診斷報告其實背後是透過 fn_get_health_status 系統函數來取得資料。除了等待電子郵件通知外，若有需要也可以利用上述系統函數，手動查詢特定時間區間的健全狀態報告。

下列 T-SQL 指令碼示範使用取得 2015 年 7 月份的健全狀態報告。

SELECT *

FROM msdb.managed_backup.fn_get_health_status('20150701','20150731')

執行結果如下圖所示：

 

另外 sp_set_parameter 系統預存程序也可以用來啟用（或關閉）與受管理的備份相關的擴充事件。

下列 T-SQL 指令碼示範使用 managed_backup.sp_set_parameter 系統預存程序啟用 Debug 事件，並以 fn_get_current_xevent_settings 系統函數查詢設定狀況。

EXEC msdb.managed_backup.sp_set_parameter 'FileRetentionDebugXevent', 'true'

 

 

SELECT * FROM msdb.managed_backup.fn_get_current_xevent_settings()

執行結果如下：

 

此外，使用 fn_available_backups 系統函數可以查詢備份到 Azure 儲存體的詳細資訊，例如下列的 T-SQL 指令碼：

SELECT *

FROM msdb.managed_backup.fn_available_backups('db3')

執行結果如下圖所示：

 

停用受管理的備份

若因為問題排除等原因需要暫時停用受管理的備份，可以使用 sp_backup_master_switch 系統預存程序來暫停自動備份到 Azure 儲存體的工作排程，例如下列的 T-SQL 指令碼：

EXEC msdb.managed_backup.sp_backup_master_switch

@new_state=0

反之，要使受管理的備份繼續執行，可以使用下列 T-SQL 指令碼：

EXEC msdb.managed_backup.sp_backup_master_switch

@new_state=1

而在前一版本用來查詢受管理備份的設定狀態的系統函數 fn_backup_db_config，在 SQL Server 2016 依然可以使用，您可以使用 fn_backup_db_config 來取得所有資料庫或單一資料庫受管理備份的設定狀況。 

SELECT *

FROM msdb.managed_backup.fn_backup_db_config (NULL)

執行結果如下圖所示，從 is_managed_backup_enabled 資料行可以看出，該資料庫是否有啟用受管理的備份。

 

您也可以使用 fn_backup_instance_config 系統函數來查詢執行個體層級，有關受管理的備份之設定結果，例如下列 T-SQL 指令碼：

SELECT *

FROM msdb.managed_backup.fn_backup_instance_config()

停用單一資料庫的受管理備份也很簡單，可以前面所介紹的 sp_backup_config_basic 系統預存程序來停用個別資料庫的設定。

下列 T-SQL 指令碼示範停止 DWQueue 資料庫的受管理備份功能。

EXEC msdb.managed_backup.sp_backup_config_basic

                @database_name = 'DWQueue'

                ,@enable_backup = 0

GO

或是利用資料指標（Cursor）來停用所有受管理備份的資料庫。

DECLARE cur_dbs CURSOR FOR

SELECT db_name

FROM msdb.managed_backup.fn_backup_db_config (NULL)

WHERE is_managed_backup_enabled = 1

 

DECLARE @db_name varchar(50)

OPEN cur_dbs

 

FETCH NEXT FROM cur_dbs INTO @db_name

WHILE @@FETCH_STATUS = 0

           BEGIN

                      EXEC ('EXEC msdb.managed_backup.sp_backup_config_basic 

                @database_name= '''+'' + @db_name+ ''+''',

                @enable_backup=0')

                     FETCH NEXT FROM cur_dbs INTO @db_name

           END

 

CLOSE cur_dbs

DEALLOCATE cur_dbs

Comments

  • Anonymous
    August 07, 2015
    The comment has been removed
  • Anonymous
    September 24, 2015
    The comment has been removed
  • Anonymous
    September 29, 2015
    The comment has been removed
  • Anonymous
    October 04, 2015
    SQL Server 2016 搶先看系列第十彈 ！

    本系列文已經介紹 SQL Server 2016 在安全性、混合雲應用、效能調校及開發等方面所推出的嶄新功能或改良， SQL Server