SQL 2014新功能介绍系列3 - 备份还原篇

大数据催生了云计算和移动互联的世界。微软新的战略方针“cloud first，mobilefirst”也表明公司正在全面转向云计算中转型。在最新发布的SQLServer 2014与微软的Azure云平台联系也更加紧密，方便数据库维护，降低使用、维护成本，提升数据安全。

本文将介绍SQL Server 2014 以下三个有关备份还原的新特性。

1. SQL Server 备份到 URL
2. SQL Server托管备份到Windows Azure
3. 备份加密

首先，我们先简单的介绍SQL Server 备份到 URL这一新特性。

严格来说，从SQL Server 2012 SP1 CU2 就开始支持这一功能，不过只能通过 Transact-SQL、PowerShell和 SMO实现此功能。 

在SQL Server 2014 中，最大的变化就是 SQL Server Management Studio 界面上开始支持备份到 Windows Azure Blob 存储服务或从中还原。“备份”任务和维护计划都可使用该新选项。

把SQL Server 备份到 URL实际上是将数据库备份到Windows Azure Blob 存储服务中。首先需要创建 Windows Azure 帐户。SQL Server 使用 Windows Azure 存储账户名称和主或副访问密钥值来进行身份验证和对存储服务写入和读取 blob。Windows Azure Blob 存储服务中可存储两类 blob：块 blob 和页 blob。 SQL Server 备份将页 Blob 作为 Blob 类型。 Blob 采用以下 URL 格式地址：https://<存储帐户>.blob.core.windows. net/<容器>/<blob>。

下面的SQL语句将 AdventureWorks2012 数据库备份到Windows Azure Blob 存储服务。

1. 创建SQL Server凭据

CREATE CREDENTIAL mycredential

WITH IDENTITY= 'mystorageaccount' --this is the name of the storage account you specified when creating a storage account 

, SECRET = '<storage account access key>' --this should be either the Primary or Secondary Access Key for the storage account

2. 通过SSMS中备份向导或下面query来备份AdventureWorks2012

BACKUP DATABASE AdventureWorks2012 TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012.bak'

WITH CREDENTIAL = 'mycredential' , COMPRESSION, STATS = 5;

限制

• 支持的最大备份大小为 1TB。 
• 不支持创建逻辑设备名称。 因此，不支持使用 sp_dumpdevice 或通过 SQL Server Management Studio 将 URL 添加为备份设备。
• 不支持追加到现有备份blob。 只能使用 WITH FORMAT 选项覆盖到现有 Blob 的备份。
• 不支持在单个备份操作中备份到多个blob。
• 不支持使用BACKUP 指定块大小。 
• 不支持指定
  MAXTRANSFERSIZE。 
• 不支持指定备份集选项 - RETAINDAYS 和 EXPIREDATE。 
• SQL Server 要求备份设备名称最多包含 259 个字符。 对于用于指定以下 URL 所需的元素，BACKUP TO URL 占用 36 个字符：“https://.blob.core.windows.net//.bak”，将其余223 个字符用于帐户、容器和blob 名称总和。

下面，我们来谈谈SQL Server 托管备份到 Windows Azure。

SQL Server 托管备份到Windows Azure 是基于 SQL Server 备份到 URL 这一功能构建的服务，SQL Server 提供这种服务来管理和安排数据库和日志的备份。 在SQL SERVER 2014中，只支持备份到 Windows Azure 存储。 SQL Server 托管备份到 Windows Azure可在数据库和实例级别同时进行配置，从而既能实现在数据库级别的精细控制，又能实现实例级别的自动化。 SQL Server 托管备份到 Windows Azure既可在本地运行的 SQL Server 实例上配置，也可在 Windows Azure 虚拟机上运行的 SQL Server 实例上配置。 建议对在Windows Azure 虚拟机上运行的 SQL Server 实例使用此服务。

目前，自动备份多个数据库需要制定备份策略、编写自定义代码并安排备份。 通过使用SQL Server 托管备份到 Windows Azure，只需提供保持期设置和存储位置。SQL Server 托管备份到 Windows Azure管理并自动执行将 SQL Server 备份到 Windows Azure Blob 存储服务。 SQL Server 托管备份到 Windows Azure使用的备份策略基于保持期和数据库中的事务工作负载。 SQL Server 托管备份到 Windows Azure支持指定保持期的时间点还原。

SQL Server 托管备份到 Windows Azure使用的备份策略：

完整数据库备份：如果以下任意条件成立，SQLServer 托管备份到 Windows Azure代理便会安排一次完整数据库备份。

• 初次为数据库启动SQL Server 托管备份到 Windows Azure，或在实例级使用默认设置启用SQL Server 托管备份到 Windows Azure时。
• 自上次完备数据库备份以来的日志增长等于或超过 1 GB。
• 距上次完整数据库备份以来已超过一周的最大时间间隔。
• 日志链中断。 SQL Server 托管备份到 Windows Azure会定期进行检查，通过比较备份文件的第一个和最后一个 LSN 确定日志链是否完整。 如果日志链因为任何原因而存在中断，则SQL Server 托管备份到 Windows Azure会安排一次完整数据库备份。 日志链断裂的最常见原因可能是使用 Transact-SQL 或通过 SQL Server Management Studio 中的备份任务发出了备份命令。 其他常见的情况包括意外删除了备份文件或意外的备份覆盖。

事务日志备份：如果以下任何情况属实，则SQL Server 托管备份到 Windows Azure安排一次日志备份：

• 找不到日志备份历史记录。 在首次启动SQL Server 托管备份到 Windows Azure时通常存在这种情况。
• 使用的事务日志空间为 5 MB 或更大。
• 距上次日志备份达到 2 小时的最大时间间隔。
• 事务日志备份滞后于完整备份数据库时。 目标是使日志链保持在完整备份之前。

保持期设置

在启用备份时，必须以天为单位设置保持期：最短为 1 天，最长为 30天。

可以通过如下步骤来构建SQL Server 托管备份到 Windows Azure

1. 参考SQL Server 备份到URL中的内容创建Windows Azure 存储帐户以及 SQL 凭据。

2. 为SQL Server实例启用托管备份到 Windows Azure，需要确定保持期。

3. 确保 SQL Server 代理服务启动且正在运行。

Use msdb;

Go

EXEC smart_admin.sp_set_instance_backup

[@enable_backup = ] { 0 | 1}

,[@storage_url = ] 'storageurl'

,[@retention_days = ] 'retention_period_in_days'

,[@credential_name = ] 'sql_credential_name'

,[@encryption_algorithm] 'name of the encryption algorithm'

,[@encryptor_type] {'CERTIFICATE' | 'ASYMMETRIC_KEY'}

,[@encryptor_name] 'name of the certificate or asymmetric key'

4. 如果个别数据库有特殊的备份需求，比如需要保存更长的时间，有更高的安全考虑，需要备份加密，可以对数据库级别设置托管备份到 Windows Azure。

EXEC smart_admin.sp_set_db_backup 

[@database_name = ] 'database name'

,[@enable_backup = ] { 0 | 1}

,[@storage_url = ] 'storage url'

,[@retention_days = ] 'retention_period_in_days'

,[@credential_name = ] 'sql_credential_name'

,[@encryption_algorithm] 'name of the encryption algorithm'

,[@encryptor_type] {'CERTIFICATE' | 'ASYMMETRIC_KEY'}

,[@encryptor_name] 'name of the certificate or symmetric key'

6. 通过运行以下 Transact-SQL 语句验证配置设置

Use msdb;

GO

SELECT * FROM smart_admin.fn_backup_instance_config();

SELECT * FROM smart_admin.fn_backup_db_config('DB Name')

限制

• SQL Server 托管备份到 Windows Azure代理只支持数据库完整备份和日志备份。 不支持文件备份自动化。
• 不支持系统数据库。
• Windows Azure Blob 存储服务是唯一支持的备份存储选项。 不支持备份到磁盘或磁带。
• 当前，Windows Azure 存储中的页 Blob 允许文件大小最大为 1 TB。 备份大于 1 TB 的文件将失败。 为了避免出现这种情况，建议对于大型数据库使用压缩，并在设置SQL Server 托管备份到 Windows Azure之前测试备份文件大小。 可通过备份到本地磁盘或使用 BACKUP TO URL Transact-SQL 语句手动备份到 Windows Azure 进行测试。
• 恢复模式：只支持设置为完整或大容量日志模式的数据库。 不支持设置为简单恢复模式的数据库。

最后，我们来看看备份加密。由于前两个新特性都是将数据库备份到Windows Azure存储中，很多用户非常担心数据的安全性问题。与此同时也有很多数据泄漏问题是由于数据库备份泄漏给第三方导致，所以备份加密也是有很大的市场需求。在SQL Server 2014之前，如果希望实现对备份的加密，会通过如下两种方式实现：

• 使用透明数据加密(TDE)来加密整个数据库，同时备份和事务日志也会被加密;
• 使用第三方备份加密工具。

如果仅仅是为了对备份加密，使用TDE会导致数据库本身和日志被加密，对CPU带来额外开销。此外，使用TDE加密过的数据库如果使用备份压缩，压缩比率会非常低。如果使用SQL Server 2014的加密备份，则无需对整个数据库进行加密，备份仅仅在被写入磁盘之前被加密。原生备份加密如果结合备份压缩功能，则会先进行压缩再加密，从而得到非常高的压缩比率。

如果使用第三方备份加密工具需要额外的费用，并且在执行过程中也会有比较繁琐的操作成本。当需要对Azure中的备份进行加密时，将会变得非常繁琐。而使用原生备份加密可以直接对Azure中的备份进行加密，从而对OFF-SITE备份提供了额外的安全性。

加密算法：支持的加密算法包括 AES 128、AES 192、AES 256 和 Triple DES  

加密程序：证书或非对称密钥

按照以下步骤向本地磁盘创建数据库的加密备份。

1. 创建 master 数据库的数据库主密钥

--Creates a database master key.

--The key is encrypted using the password "<master key password>"

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master key password>';

GO

2. 创建备份证书

Use Master

GO

CREATE CERTIFICATE MyTestDBBackupEncryptCert

   WITH SUBJECT = 'MyTestDB Backup Encryption Certificate';

GO

3. 备份数据库

BACKUP DATABASE [MyTestDB]

TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\MyTestDB.bak'

WITH

  COMPRESSION,

  ENCRYPTION

   (

   ALGORITHM = AES_256,

   SERVER CERTIFICATE = MyTestDBBackupEncryptCert

   ),

  STATS = 10

GO

也可以为“SQL Server 备份到 URL”和“SQL Server 托管备份到 Windows Azure”配置加密选项。

限制：

• 如果使用非对称密钥加密备份数据，则仅支持位于 EKM 提供程序中的非对称密钥。
• SQL Server Express 和 SQL Server Web 不支持在备份期间进行加密。 但是，支持从加密的备份还原到 SQL Server Express 或 SQL Server Web 的实例。
• 旧版 SQL Server 无法读取加密的备份。
• 加密的备份不支持追加到现有的备份集选项。

参考文档：

• https://go.microsoft.com/fwlink/?LinkID=324660
• https://technet.microsoft.com/en-us/library/jj919145.aspx
• https://msdn.microsoft.com/en-us/library/dn449495(v=sql.120).aspx
• https://technet.microsoft.com/en-us/library/jj919149.aspx
• https://msdn.microsoft.com/zh-cn/library/dn449496(v=sql.120).aspx
• https://msdn.microsoft.com/zh-cn/library/dn451009(v=sql.120).aspx
• https://msdn.microsoft.com/zh-cn/library/dn451013(v=sql.120).aspx
• https://msdn.microsoft.com/zh-cn/library/dn449489(v=sql.120).aspx

这就是今天的分享，更多SQL 2014新功能介绍请持续关注本博客。 下周我们将会介绍SQL 2014延迟事务持续性(Delayed Durability Transactions)这个新特性。