如何利用SQL Server 2014將資料庫檔案存放於Microsoft Azure Storage(下)

如何利用SQL Server 2014將資料庫檔案存放於Microsoft Azure Storage(下)

說明

在前兩篇文章中介紹到使用SQL Server 2014建立資料庫可以直接把資料庫檔案放到Microsoft Azure Storage(以下簡稱Storage),以及如何透過卸離/附加或是將現有企業內部部署的資料庫檔案移轉到Storage。本文將繼續介紹SQL Server 2014跟Storage的整合上,還可以怎麼做應用?

將資料庫還原到Storage

首先使用下列T-SQL進行資料庫完整備份:

--完整備份
BACKUP DATABASE [Northwind]
 TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Northwind.bak' 
GO

備份成功之後,您可以自行決定要使用SSMS提供的GUI或是以T-SQL來還原資料庫,下列示範的是將前一步驟進行完整備份的Northwind資料庫還原成NorthwindOnAzure,並將資料庫檔案存放在Storage。

image

您可以在還原資料庫視窗中,點選左側的檔案,接著勾選【將所有檔案重新放置到資料夾】,接著輸入容器的URL,最後按確定就可以開始將資料庫還原到Storage。

image

或是以下列T-SQL來還原資料庫,跟過去您還原資料庫的差異只有在資料庫檔案的位置,從磁碟機路徑改為容器的URL,其他部分均沒有什麼不一樣。

USE [master]
RESTORE DATABASE [NorthwindOnAzure] 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Northwind.bak' 
WITH  
MOVE N'Northwind' TO N'https://sqlhastorage.blob.core.windows.net/dbfiles/Northwind.mdf',  
MOVE N'Northwind_log' TO N'https://sqlhastorage.blob.core.windows.net/dbfiles/Northwind.ldf'

GO

特別一提的是,容器的URL必須使用https,若您使用CREATE DATABASE、RESTORE DATABASE來將資料庫檔案放在Storage時,容器的URL是使用http,可能會遇到下圖的錯誤訊息,

image

而且使用GUI方式來還原時,會發生輸入的資料夾URL會被帶錯的狀況。

image

因而導致還原時發生下圖的錯誤訊息,原因是當您輸入http的容器URL時,SSMS幫您產生的資料庫檔案URL格式錯誤所致。

image

為放置在Storage中的資料庫啟用資料庫透明加密(TDE)

若您希望為放置在Storage中的資料庫檔案提供更多的安全保護機制,您也可以像在過去企業內部部署的資料庫一樣,啟用TDE加密來保護您的資料庫檔案,您可以使用下列T-SQL指令碼來建立TDE所需要的資料庫金鑰和憑證,並且務必在建立完成後將之匯出妥善保存,以免將來無法為資料庫解密。

-- 建立資料庫主要金鑰
USE master 
GO 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQL PASS Taiwan'; 
GO 

--建立憑證
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'TDECert')
	DROP CERTIFICATE TDECert
GO

CREATE CERTIFICATE TDECert 
WITH SUBJECT = 'TDE Certification' 
GO 

--匯出憑證及私密金鑰
BACKUP CERTIFICATE TDECert 
TO FILE = 'TDECert.cer' --憑證檔案名稱
WITH PRIVATE KEY 
( 
FILE = 'TDECert_PK.pvk', --私密金鑰檔案名稱
ENCRYPTION BY PASSWORD = 'SQL PASS Taiwan'  
)

匯出憑證及私密金鑰時,若未指定路徑預設將會放在SQL Server資料檔案的所在位置,也就是C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA,因此當您執行上述指令碼之後,您將會在C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA路徑下看到您所匯出的憑證及私密金鑰檔案。

image

接下來切換到您要啟用TDE加密的資料庫之後,以上一步驟建立的憑證來產生資料庫加密金鑰,最後啟用TDE加密,詳如下列的T-SQL指令碼。

--切換到您想要啟用TDE加密的資料庫
USE NorthwindOnAzure
GO 

--使用憑證為建立資料庫加密金鑰
IF EXISTS (SELECT * FROM sys.dm_database_encryption_keys WHERE database_id = DB_ID('NorthwindOnAzure'))
	DROP DATABASE ENCRYPTION KEY
GO 

CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = TRIPLE_DES_3KEY  
ENCRYPTION BY SERVER CERTIFICATE TDECert 
GO 
 
 --啟用TDE資料庫加密
ALTER DATABASE NorthwindOnAzure 
SET ENCRYPTION ON 
GO 

附加啟用TDE的資料庫

要附加啟用TDE的資料庫時,您必須跟啟用TDE功能時相同,先建立資料庫主要金鑰,然後匯入上一步驟所匯出的憑證及私密金鑰,如此一來您才可以順利將已經加密的資料庫附加上來,相關T-SQL指令碼如下:

-- 建立資料庫主要金鑰
USE master 
GO 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQL PASS Taiwan'; 
GO 

--建立憑證
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'TDECert')
	DROP CERTIFICATE TDECert
GO

--匯入憑證及私密金鑰
CREATE CERTIFICATE TDECert 
FROM FILE = 'TDECert.cer'
WITH PRIVATE KEY 
(
    FILE = 'TDECert_PK.pvk',
    DECRYPTION BY PASSWORD = 'SQL PASS Taiwan'
);
GO


--附加資料庫
USE [master]
GO
CREATE DATABASE NorthwindOnAzure ON 
(FILENAME = N'https://sqlhastorage.blob.core.windows.net/dbfiles/Northwind.mdf')
 LOG ON
(FILENAME = N'https://sqlhastorage.blob.core.windows.net/dbfiles/Northwind.ldf')
 FOR ATTACH
GO

 

參考資料

Windows Azure 中的 SQL Server 資料檔案

Azure Storage Explorer

Create a SQL Server 2014 Database directly on Azure Blob storage with SQLXI

SQL Server 2014 Management Topic I-directly create SQL Server database on Azure Storage

Scaling-out SQL Server disks and data files on Windows Azure Virtual Machines…a real-world example

Performance Guidance for SQL Server in Windows Azure Virtual Machines

教學課程:Windows Azure 儲存體服務中的 SQL Server 資料檔案

KB 2907705

如何利用SQL Server 2014將資料庫檔案存放於Microsoft Azure Storage(上)

如何利用SQL Server 2014將資料庫檔案存放於Microsoft Azure Storage(中)

CREATE MASTER KEY (Transact-SQL)

CREATE CERTIFICATE (Transact-SQL)

BACKUP CERTIFICATE (Transact-SQL)

CREATE DATABASE ENCRYPTION KEY (Transact-SQL)

SQL Server Backups and Restores Directly with Windows Azure Blob Storage Service