澳门金沙vip 2

压缩技术给SQL Server备份文件瘦身

可是,其体积仍然很庞大。所以,在日常工作中,如何给SQL
Server的备份文件瘦身,就是很多数据库管理员所关心的问题了。
也许微软的数据库产品设计专家听到了众多数据库管理员的呼声了吧。在最新的2008版本的SQLServer数据库中,提出了备份压缩的概念。其基本原理跟利用RAR等压缩工具压缩文件一样,可以让原有的备份文件体积更小。这直接带来的好处,就是可以节省服务器的备份空间。另外,若SQLServer数据库配置了异地备份的话,那么也可以节省网络带宽,缩短异地备份的时间,等等。
笔者前不久刚把数据库升级到了2008,并重新更改了备份配置,让数据库支持备份压缩。下面笔者就把备份压缩的管理心得跟大家分享一下。希望笔者这些经验能够帮助大家做好SQL
Server数据库备份压缩的管理。简单的来说,如果要采用备份压缩技术,那么数据库管理员要弄明白几个问题。
问题一:备份压缩技术的限制条件。
由于备份压缩技术是2008版本中才提出来的,所以其兼容性可能就会收到一些限制。根据官方的说法是,从2008以后的数据库版本,都会支持这个备份压缩技术。故向后兼容应该问题不大。数据库管理员关心的应该是,从低版本升级到高版本的数据库时的一些限制条件。掌握这些限制条件,可能会让数据库升级少遇到一些问题。根据笔者的了解,这里至少有二个限制条件。
一是压缩的备份和未压缩的备份不能够共存于一个媒体集中。在SQL
Server数据库中,如果要对数据集进行备份,则首先需要建立一个媒体集。笔者升级完成之后,先对数据库进行了一个完全备份,这个备份没有采用压缩技术。后来笔者在测试压缩备份的时候,却发现怎么都不成功。后来根据错误提示查询了相关资料并进行亲自测试,才发现压缩的备份和未压缩的备份不能够共存于一个媒体集中。笔者后来重新建立了一个媒体集后,备份压缩技术就可以起作用了。
二是早期版本的SQL
Server数据库无法读取压缩的备份。为了测试备份压缩技术的向前兼容性,笔者特意利用备份压缩后的数据库文件,去恢复2005版本的数据库。注意,这个数据库文件是升级到2008后马上备份的,也就是说,除了这个压缩技术外,没有采用2008的新技术与新对象。但是,却发现2005版本的数据库根本不认账,不认识这个压缩后的备份文件。可见,早期版本的SQL
Server数据根本无法读取压缩后的备份文件。
这是笔者测试后发现的两个限制条件。不过笔者查询了一些官方资料后发现,还有一个重要的限制。如NTBACKUP工具无法共享含压缩的数据库备份磁带。不过由于笔者用不到这方面的内容,所以也没有测试是否如此。
问题二:压缩的效果到底如何?
如果采用了压缩备份技术,那么备份文件到底可以瘦下来多少呢?这主要跟数据库有关。根据笔者的了解,如下一些因素会直接影响到最终的压缩效果。
首先是跟数据类型有关。如果数据库中大部分是字符型的数据,则其压缩效果会比较好。而如果数字类型比较多的话,那么采用压缩备份技术后,备份文件并不能够小多少。这也给数据库管理元是否要采用压缩备份技术提供了一个判断的标准。
其次是数据是否加密。正常情况下,如果数据库中的数据未加密,则其压缩的效果会比较明显。相反如果数据库的数据加密了,则其压缩的程度就会小很多。如数据库管理员利用透明数据加密方法来加密整个数据库,则采用压缩备份技术之后,压缩备份并不会将数据库减小多少,甚至根本不会减小。
再者,跟数据表设计也有关系。一般情况下,如果表设计比较合理,则其压缩的效果就会好许多。如某页中包含多个行,而其中的某个字段包含相同的值,则该值就可以获得比较大的压缩率。与之相反,如果字段中的数据大部分是随机数据(即使只有稍微的差别),则其压缩备份的大小几乎与未压缩的备份相同。这也就是说,要想取得比较好的压缩效果,则在数据库设计时,就需要考虑。如可以采用一些列表字段供用户选择,就可以提高最终备份文件的压缩效果。
问题三:压缩备份对于性能的影响如何?
数据库采用压缩备份之后,对于数据库的影响是双方面的,即有利也有害。
利是直接跟上面所说的数据库压缩效果相关。因为同一个数据库的压缩备份文件要比原来的备份文件要小,所以压缩备份所需要的设备输入输出通常比较少,所以可以大大提高备份速度。而且,数据库进行异地备份的话,还可以大大缩短网路传输的时间。所以,当数据库的压缩效果越好,则对于数据库的性能,也会有很大的改善。
不利之处就在于资源的消耗方面。如果采用了压缩备份技术,则压缩会显著增加CPU的使用率。而压缩进程所占用的额外CPU可能会对兵法操作产生消极的影响。为了尽量减少这个不利影响,可以采取的措施就是调整SQL
Server数据库的备份策略。如把备份时间放在午夜时分。那时候,基本上没有用户使用数据库,或者数据库的使用几率会大大降低。此时,就是多一些额外的CPU消耗,用户也很难察觉到。
另外在数据库中,也可以通过降低优先级的方式,来降低压缩备份对数据库的不利影响。如当发生CPU争用时,此备份的CPU使用就会受到资源控制其的限制。通过将特定的用户会话映射到限制CPU使用的资源调控器工作负荷来实现。不过这个实现起来比较复杂,以后若有机会,笔者将会专题讲述。对于大部分企业来说,数据库的使用都有很明显的高发期与低潮期。只需要稍微调整一下备份策略,在数据库使用低潮期进行压缩备份,就可以很轻松的避免压缩备份所带来的负面影响。而完全不需要吃力不讨好,采用这么复杂的解决方案。即使像银行类这些金融机构,在晚上12点之后用户也会大量的减少。此时他们释放出来的CPU给压缩备份使用已经足够了。
还好笔者以前采取的备份策略,就是在晚上12点之后让数据库进行自动备份。所以这次采用了压缩备份之后,对于性能的影响可以忽略。
问题四:如何启用压缩备份?
默认情况下,数据库在执行备份的时候,是不采用压缩备份的。如果数据库管理员出于特定的需要要启用压缩备份的话,就需要管理员去手工启动。压缩备份的默认行为是数据库系统中的备份压缩默认选项服务器级配置来决定的。
如需要启用压缩备份策略,只需要经过简单的三个步骤即可。
第一步:打开数据库对象资源管理器,右键单击需要启用压缩备份策略的那个服务器,然后打开属性对话框。
第二步:单击数据库设置节点。找到备份和还原选项卡。在压缩备份页签中显示了备份压缩默认设置的当前配置。这个“压缩备份”选项决定了数据库在备份的时候是否要才用压缩备份策略。如果选中的话,默认情况下数据库将启用压缩备份。
第三步:建立新备份媒体。笔者在上面提到过,压缩备份与未压缩备份不能够存储在同一个媒体集中。如果数据库管理员是中途启用这个压缩备份策略的。即在原先的备份媒体中已经有未压缩的备份文件,那么数据库管理员要么需要删除原有的备份文件,要么就是重新建一个备份媒体。笔者的意见是重新建立别分媒体,而保留原有的备份文件。这主要是出于安全的考虑。万一压缩备份因为某些原因不成功,则仍然可以有补救措施。
压缩备份是SQLServer数据库推出的一个新技术。笔者以为,如果企业数据库容量比较小的话,没有必要采用这个压缩备份。只有数据库容量比较大,或者要进行异地备份的情况下,采用压缩备份的效果才会显现出来。由于压缩备份有比较大的限制条件和管理难点,数据库管理员还是需要在性能、压缩效果等方面评估压缩备份可能会给企业带来的效果。评估之后再进行取舍,是否要采用压缩备份。

在SQL
Server安全系列专题月报分享中,我们已经分享了:如何使用对称密钥实现SQL
Server列加密技术、使用非对称密钥实现SQL
Server列加密、使用混合密钥实现SQL
Server列加密技术、列加密技术带来的查询性能问题以及相应解决方案、行级别安全解决方案和SQL
Server 2016 dynamic data
masking实现隐私数据列打码技术这六篇文章,文章详情可以参见往期月报。本期月报我们分享使用证书做数据库备份加密的最佳实践。

  通常情况下,快照复制有如下特点。

谈及数据库安全性问题,如何预防数据库备份文件泄漏,如何防止脱库安全风险,是一个非常重要的安全防范课题。这个课题的目的是万一用户数据库备份文件泄漏,也要保证用户数据的安全。在SQL
Server中,2014版本之前,业界均采用的TDE技术来实现与防范脱库行为,但是TDE的原理是需要将用户所有的数据进行加密后落盘,读取时解密。这种写入时加密,读取时解密的行为,必然会导致用户查询性能的降低和CPU使用率的上升(具体对性能和CPU影响,可以参见这片测试文章SQL
Server Transparent Data Encryption Performance
Comparison)。那么,我们一个很自然的问题是:有没有一种技术,既可以保证备份文件的安全,又能够兼顾到用户查询性能和CPU资源的消耗呢?这个技术就是我们今天要介绍的数据库备份加密技术,该技术是SQL
Server
2014版本首次引入,企业版本和标准版支持备份加密,Web版和Express版支持备份加密文件的还原。

  一是从快照这个名词中,我们也可以看出,照片是一个静态的过程,即只反映数据库某个时刻的状态,而不会反映数据库的一个变化过程。快照复制知识对数据库某个设定的时间的数据进行复制,而不会对一段时间内的数据更改进行连续监视。这跟其另一个兄弟“事务复制”有本质的区别。若把快照复制比喻成完全备份的话,则事务复制就是一个差异备份。

创建测试数据库

  二是快照复制所需要的资源比较大。快照复制是对整个数据库进行复制,而不管其是否发生了变化。无疑,这复制的时间、传输的时间都会比较长,其耗费的服务器与网络资源也会比较多。所以,快照复制的成本是比较高的。但是,在某些特定的情况下,快照复制反而可以提高效率。如对于不经常更改当数据,或者不要求保持高同步性的数据的情况下,利用快照复制来协调多个数据库之间的数据一致性反而使比较合理的选择。

为了测试方便,我们专门创建了测试数据库BackupEncrypted。

  快照复制虽然是一门比较先进的技术,但是,并不是在所有应用场景中,其都可以起到很好的效果。若使用不当,快照复制技术反而会拖数据库管理员的后腿。根据笔者的经验,一般在以下情况下,最好采用快照复制。

-- create test databaseIF DB_ID('BackupEncrypted') IS NOT NULL DROP DATABASE BackupEncryptedGOCREATE DATABASE BackupEncryptedON PRIMARY(NAME = BackupEncrypted_data, FILENAME = N'E:\SQLDATA\DATA\BackupEncrypted_data.mdf', SIZE = 100MB, FILEGROWTH = 10MB),FILEGROUP SampleDB_MemoryOptimized_filegroup CONTAINS MEMORY_OPTIMIZED_DATA ( NAME = BackupEncrypted_MemoryOptimized, FILENAME = N'E:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized')LOG ON ( NAME = BackupEncrypted_log, FILENAME = N'E:\SQLDATA\DATA\BackupEncrypted_log.ldf', SIZE = 100MB, FILEGROWTH = 10MB)GO

  一是当数据更改量大但是不经常发生时,可以采用快照复制。如笔者有一个客户,在全国各地都有服装门市店,各个店面的销售价格基本一致。由于经济危机的影响,在年底为了尽可能的收回现金,决定在12月24日到1月3日之间下调各个门市店的销售价格。由于他们的销售系统都是全国联网的。价格的下调需要总部的系统来完成。此时,就可以采用快照技术,让这个价格表跟各个地方的数据库同步。在价格更改完成后,复制完成的数据库快照。一般来说,某个表的数据主要是静态数据,不经常更改。当数据在一个时间发生更改时,将一个全新的数据库快照发布到服务器上,其效果会更好。

创建测试表

  二是在某些决策支持系统中,采用快照复制技术的比较多。因为决策支持系统,他们只需要查询数据,而很少对数据进行更改。同时,往往对数据库的及时性要求也不高。如一个销售决策系统,他们可能只需要到上个月月底的销售数据,而不需要到当天的销售数据。在这种情况下,采用快照复制技术,比起其他的数据同步技术,如事务复制技术,效果会更加的好。通常情况下,在某个时期,如果允许有过时的数据副本,也就是说,对数据的及时性要求不怎么高的系统,可以考虑采用快照技术。

在测试数据库下,创建一张用于测试的表testTable,并插入一条随机数据。

  三是复制数据比较少的情况下,可以采用快照复制技术。因为快照复制是一次复制整个数据库,如果数据量比较大的话,则每次复制、传输的时间都会比较长。此时,采用事务复制会比较合理。其实,这跟完全备份与差异备份类似。当数据库容量比较小时,采用完全备份反而比差异备份更加实际。因为维护起来方便。

USE [BackupEncrypted]GO-- create test table and insert one recordIF OBJECT_ID('dbo.testTable', 'U') IS NOT NULL DROP TABLE dbo.testTableGOCREATE TABLE dbo.testTable( id UNIQUEIDENTIFIER default NEWID(), parent_id UNIQUEIDENTIFIER default NEWSEQUENTIALID;GOSET NOCOUNT ON;INSERT INTO dbo.testTable DEFAULT VALUES;GOSELECT * FROM dbo.testTable ORDER BY id;

  四是可以利用快照复制来实现对SQL Server数据库的异地备份。SQL
Server服务器本身没有异地备份功能。往往是先在本地进行备份,然后把备份文件拷贝到其他主机上。有了快照复制的话,可以解决这个问题。即先建立一个分发服务器,然后每天晚上在空余的时候,对发布服务器进行快照复制,传输给分发服务器。如此的话,分发服务器的内容就跟发布服务器上的内容保持一致。再对分发服务器执行备份,就可以完成异地备份的策略了。

该条数据内容如下截图:

  所以,通常情况下,如果企业的应用场景符合上面四种情况的话,采用快照复制的效果会比较好。反之,则就需要考虑采用事务复制等其他的数据同步技术。

澳门金沙vip 1

  在使用快照复制技术的时候,笔者还有几个善意的提醒。

创建Master Key和证书

  一是快照复制技术可以在一定程度上实现网络数据库的功能。SQL
Server数据库到现在的版本为止,都不支持数据库横向的扩展。也就是说,不主张采用多个服务器来分担主服务器的负担。微软一直强调的是服务器的纵向拓展,如增加服务器的CPU数、增加内存等等,来改善服务器的性能。使用快照复制技术可以使得数据的分布过程实现自动化,让多个服务器之间自动实现数据的同步,以适应企业不同程度的需要。当企业扩展时,可以增加额外的数据库服务器,来减轻主服务器的工作负荷并提高数据的使用效率。这也就在一定程度上实现了多服务器(网络数据库)的功能。不过这个功能有个限制,请看下面一个注意点。

澳门金沙vip,创建Master Key和证书,用于加密数据库备份文件。

  二是快照复制技术基本上是单向的。也就是说,只可以从主服务器上对数据进行快照复制,然后传递给其他的数据库服务器。其他的数据库服务器只能够被动的接收主服务器过来的快照。而不能够把自己服务器的数据的变化情况反馈给主服务器。这就跟我们通常所说的网络服务器有一个本质的区别。网络服务器的话,各个子服务器上更改的数据,可以在相互的服务器上进行同步。所以,若要利用快照服务器来实现网络服务器的功能,以减轻主服务器的负荷,这里往往有一个部署上的技巧。就是对于子服务器来说,只接受查询的功能。也就是说,把一些报表生成、数据查询的任务交给子服务器来做。而把数据更新等任务则仍然交给主服务器来完成。

USE masterGO-- If the master key is not available, create it. IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%') BEGIN CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey*'; END GOUSE masterGO-- create certificateCREATE CERTIFICATE MasterCert_BackupEncryptedAUTHORIZATION dboWITH SUBJECT = 'Backup encryption master certificate',START_DATE = '02/10/2017',EXPIRY_DATE = '12/30/9999'GO

  三是对一些数据突然变更的情况,可以采用强制复制与强制订阅。如在发布服务器上(发布服务器是快照复制中对于主服务器的专业称呼)数据发生了变更,则不管数据库管理员设置的时间有没有到,可以强制对发布服务器执行快照复制。并且把它传输到分发服务器上,强制采用更新后的数据。这就是强制订阅。强制订阅通常用在发生数据更改时必须立即发送更改到订阅服务器的这种类型的应用中。如在08年1月1日早上1点某个超市要对所有的商品价格进行调价。此时,调价完成后,就必须采用强制订阅业务,同步其他的一些价格查询服务器上的数据。当客户在查询机上查询价格的时候,查到的是其调整后的价格。这就是强制订阅的一种典型应用。但是,若数据变更比较频繁,则采用这种强制订阅的话,很可能造成系统或者网络的瘫痪。数据库管理员在这一点上要引起重视。

备份证书

  四是从2005以后的版本中,包括刚推出的2008版本,引入了对Internet技术的支持。通过复制,可以把数据发布到Internet上,提高了数据库中数据的使用效率。不过在Internet上复制数据时,要注意几个问题。一是要保证发布服务器和分发服务器在网络防火墙的同一端,而不能够位于防火墙的两侧,负责的话,数据复制将不会成功。二是要保证发布服务器和分发服务器有直接的连接,而并发只有互联网这一种连接方式。三是目前它支持TCP/IP协议。只有这个协议正常运行,才可以进行互联网上的复制。同时要注意的问题就是互联网的带宽问题。带宽的多少,直接影响着数据复制的效率问题。

首先,将证书和证书密钥文件备份到本地,最好它们脱机保存到第三方主机,以免主机意外宕机,导致证书文件丢失,从而造成已加密的备份文件无法还原的悲剧。

  总之,快照复制的出现,为SQL
Server各个数据库之间数据的同步提供了一种捷径,是保障SQL
Server数据一致性的法宝。

USE masterGOEXEC sys.xp_create_subdir 'C:\Tmp'-- then backup it up to local pathBACKUP CERTIFICATE MasterCert_BackupEncrypted TO FILE = 'C:\Tmp\MasterCert_BackupEncrypted.cer'WITH PRIVATE KEY ( FILE = 'C:\Tmp\MasterCert_BackupEncrypted.key', ENCRYPTION BY PASSWORD = 'aa11@@AA');

加密完全备份

创建完Master Key和证书文件后,我们就可以做数据库完全备份加密操作。

USE master;GO-- do full backup database with encryptionBACKUP DATABASE [BackupEncrypted] TO DISK = N'C:\Tmp\BackupEncrypted_FULL.bak' WITH COMPRESSION, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = MasterCert_BackupEncrypted), STATS = 10;GO

加密差异备份

数据库差异备份加密,备份操作前,我们插入一条数据,以供后续的测试数据校验。

USE [BackupEncrypted]GO-- insert another recordSET NOCOUNT ON;INSERT INTO dbo.testTable DEFAULT VALUES;GOSELECT * FROM dbo.testTable ORDER BY id;USE master;GO--Differential backup with encryptionBACKUP DATABASE [BackupEncrypted]TO DISK = N'C:\Tmp\BackupEncrypted_DIFF.bak'WITH CONTINUE_AFTER_ERROR,ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = MasterCert_BackupEncrypted), STATS = 10, DIFFERENTIAL;GO

差异备份操作前,校验表中的两条数据如下图所示:

澳门金沙vip 2

加密日志备份

数据库事物日志备份加密,备份前,我们照样插入一条数据,以供后续测试数据校验。