五餅二魚工作室

第一次聽到這個名詞，是在 Techday 2014 的一場 SQL Server 2014 的課程中，當時只有很快的先記錄下來 smart_admin，想說回來再測試一下看看這到底是甚麼東西。

這幾天花了時間查看一下文件和 Source，基本上這個是 SQL Server 2014 搭配 Microsoft Azure 整合的一個功能，以往在使用 SQL Server 的時候，都要自己先設定好資料庫復原模式之後，接著再透過 SQL Agent 的 JOB 或 SSMS 內的維護計畫，來設定相關排程來進行自動備份。雖然在 SQL Server 2012 SP1 CU2 之後的版本，就可以直接在備份的時候使用 URL 參數搭配 Microsoft Azure 來做備份了，而之前的版本也可以透過「Microsoft® SQL Server® Backup to Microsoft Azure®Tool」，間接地將備份放置到 Azure 上。但是在 SQL Server 2014 上面，你還可以有更簡約的方式來進行，你只要設定好「受管理的備份 ( Managed Backup )」之後，SQL Server 會自動產生 System Jobs，預設是每 15mins 檢查一下，按照使用者所設定的 Azure 備份上的相關參數之後，就會自動進行完整備份和交易備份，完全不需要人工的介入和處理，非常的方便。而在以往備份過程當中，我們最擔心當設定好交易紀錄備份時，交易記錄檔有不連續的狀況( 像是有人做了交易備份、交易備份檔移出等 )造成整個備份計畫失效，而在使用 Managed Backup 的時候，這些檢查都可以完全交給他了，當有交易紀錄備份出來的時候他也會自動檢查，因此如果發現有不連續的狀況時，他也會在自動進行一次完整備份，避免後續備份是無效的。

基本上「受管理的備份 ( Managed Backup )」的相關指令都存放在  msdb 內的 smart_admin 的這個 schema 下面，相關物件的說明可以透過 MSDN 上的文件「SQL Server Managed Backup 到 Windows Azure」有完整的說明。當要設定的時候，使用者必須具備對資料庫有 「db_backupoperator」角色，並且有執行「ALTER ANY CREDENTIAL」和預存程序「sp_delete_backuphistory」的權限。

預備工作

要進行測試之前，首先你必須要先有個 Microsoft Azure 的帳號，並且連上 Azure Portal 之後去建立一個儲存體 Storage，因此在這裡我先建立一個 5l2f 的 Storage，並且記錄下該 Storage 的「帳號名稱」和「主要存取金鑰」

接著我們要把記錄下來的這些資訊，利用 SSMS 的管理工具，在 SQL Server 的 Instance 下面，選擇「安全性」→「認證」，按下滑鼠又鍵，來建立一個對應到前面一個步驟所設定的儲存體之「認證」資訊，這樣 SQL Server 在連接 Storage 的時候，就可以透過這個認證內的資訊去做連線了。

在這裡我設定認證名稱是 「ManagedBackupToAzure」，而在這裏的「識別」就是指的 Azure 上面的「帳號名稱」，而「密碼」就是對應到「主要存取金鑰」，確定好之後就完成基本設定了。

接下來選擇「管理」→「受管理的備份」來做啟用，此時只有在 GUI 介面上選擇「啟用 Managed Backup」，並且設定要在 Azure 上檔案保留天數和認證資訊，至於下方的加密選項，就看你有沒有需要了，在這裡我先跳過加密的設定。

而這一段如果您想用指令的話也是可以啦，主要是使用預存程序「smart_admin.sp_set_instance_backup」來進行設定

USE [msdb]

GO

EXEC [smart_admin].[sp_backup_master_switch] @new_state  = 1;

EXEC [smart_admin].[sp_set_instance_backup] @enable_backup = 1, @retention_days = 30, @credential_name = N'ManagedBackupToAzure', @storage_url = N'https://5l2f.blob.core.windows.net/', @encryption_algorithm = N'NO_ENCRYPTION'

GO

備份測試

當完成上述相關設定之後，接下來我們就可以來做測試了，首先我在這裡建立三個資料庫，分別是「Sample_Full」、「Sample_Break」和「Sample_Simple」來做測試，其中我將「Sample_Simple」的資料庫「復原模式」設定為「簡單(SIMPLE)」，其他兩個保持預設值為「完整(FULL)」

此時我們可以利用指令來查看有哪些資料庫目前受管理中，主要是使用「smart_admin.fn_backup_db_config」來查詢，這個 Function 您可以傳入特定的資料庫名稱，或者是傳入空白，就可以查看所有資料庫的狀況了。

-- 可以在函數內填入特定資料庫名稱，空白表示全部

select * from smart_admin.fn_backup_db_config('')

GO

從下面的資訊中我們可以看出來，系統資料庫 ( master、tempdb、model、msdb ) 是不會被納入的，而資料庫「復原模式」要設定為「完整(FULL)」、「大量紀錄(bulk-logged)」才會被受管理備份所接受。

因此當過一陣子之後，我們在 Azure 上面就可以看到有新增一個容器「sql2014-mssqlseerver」，這個容器下也有看到一些備份檔案。其中有針對 Sample_Full 和 Sample_Break 這兩個資料庫在 18:06 的時候先做了完整備份，接下來有做兩次交易紀錄備份，交易紀錄的備份時間間隔是 2 小時。

此時我們想要來了解，要是有 Log 檔案有缺失的話，那麼會發生甚麼狀況呢 ? 因此在這裡我們將 Sample_Break 最後一次的備份透過 SSMS 管理工具給刪除，接下來我們再等一段時間，等下次發生交易紀錄備份之後再來查看。會發現我們以 Sample_Break 這個資料庫來看，在紅色框的部分在 18:21 和 22:22 一共有兩個備份檔，很明顯的看到 20:21 的備份檔已經被刪除了，因此在 Sample_Break 這個資料庫在 22:22 分作了交易紀錄備份之後，SQL Server 會拿這個交易紀錄備份去檢查，因此會發現有中斷的狀況，因此在相隔 15 分鐘之後的 22:37，便會針對 Sample_Break 再做一次完整備份了。

假設當我們完成這些之後，接下來想要把剛剛沒有給加入到受管理的備份內的 Sample_Simple 的資料庫給加入的話，此時需要先調整資料庫的復原模式，在這裡我們是將復原模式設定為大量紀錄

接著要手動將這個資料庫給加入受管理的備份內，在這裡主要是使用預存程序「smart_admin.sp_set_db_backup」來做設定；如果要更改特定資料庫備份到 Azure 上要使用不同的參數或者是天數的話，也是使用這個預存程序來做設定。

-- 查看受管理的備份內有納入那些資料庫

select * from smart_admin.fn_backup_db_config('')

GO

-- 手動加 Sample_Simple 資料庫給加入

EXEC smart_admin.sp_set_db_backup 

  @database_name='Sample_Simple' ,

  @retention_days=14 ,

  @credential_name = 'ManagedBackupToAzure',

  @encryption_algorithm ='NO_ENCRYPTION',

  @enable_backup=1;

GO 

-- 再檢視一下改變後的狀況

select * from smart_admin.fn_backup_db_config('')

GO

從回傳的狀況中可以看出來，此時已經將 Sample_Simple 資料庫給納入到受管理的備份內了

因此再經過約 15mins 的時間，我們再度查看 Azure 上面的儲存體，就可以看到有新增一個資料庫的備份檔案了。

其他資訊

A. 在前面的步驟中我們為了展示的效果，刪除掉一個交易紀錄備份，但如果你真的要去做的話，不論是透過 SSMS 、Azure Storage Explorer 或者是 Azure Portal ，會發現都是無法直接刪除的，如果有特殊狀況需要刪除的話，則必須先使用 PowerShell 的指令取消租約之後，才能透過相關管理工具來刪除檔案。此部分可以參考另外一篇文章「SQL Server 2012 將備份資料放至Microsoft Azure儲存體內」 下的參考資料。

B. 由於 SQL Server 的備份檔是採用 Page Blob 的方式上傳上去的，因此可以達到最大是 1TB 的大小。但相對地來說如果您想透過 Azure Portal 或者是 Azure Storage Explorer 來下載這些備份檔案的話，是會失敗的。因此這個部分是可以利用 「AzCopy」 的命令列工具程式，指定 Blobtype 為「page」才能進行下載。

C. 由於 Managed Backup 是利用 System Jobs，而不是以往的 SQL Agent 下的 User Jobs，因此在 SQL Agent 下是看不到相關的作業，但仍然要啟動 SQL Agent 之後，相關的受管理的備份才會運作。