【IT168 技术】在推出了 Windows8 消费者预览版与Visual Studio 11 之后，微软在3月7日发布了其最新的数据库服务器软件 SQL Server 2012 版。虽然说SQL Server 2012在云、大数据、安全以及高可用性方面都有不俗的表现，但短时间内，众多企业考虑到投资及管理的成本，SQL Server 2008仍然是主流，一周时间内就有三位朋友向小编询问SQL Server 2005及SQL Server2008管理问题。

　　但不管是SQL 2005、2008还是2012，就其数据库的基本知识以及常规管理维护还是一样的，今天我们就通过一篇文章介绍一下如何对SQL Server数据库进行常规的管理维护。其实在SQL Server数据库中创建数据库是一个很简单的工作，不论是用图形界面还是命令，所以如果咱们只是创建一般应用的数据库就足够了，但是要创建一个高性能的，有良好扩展性的数据库便不是一件简单的事情。在创建之前就要有一个比较详细的规划。那么本文就介绍到的内容有SQL Server的数据库结构、 规划SQL Server数据库及 编辑数据库文件的属性、使用RAID提高数据库性能、 使用文件组提高数据库性能和管理性能。

　　数据库结构：

　　大家看到下面这个图就明白了，在SQL Server 中任何一个数据库都有两种类型的文件组成：

　　其中数据文件，存放的是数据库中的具体数据，扩展名为 mdf和.ndf分别代表主数据文件和次数据文件。日志文件，简单来说存放的是对数据库所进行的操作，扩展名是 .ldf。关于日志和事务，后期会有专门的文章介绍。但扩展名不是强制的，只是为了便于文件类型的识别。

　　大家从这个图中可以看到所谓数据文件，里面存放的是数据库的一些对象，如表、索引等。而日志文件存放的是对数据库进行插入、删除、修改等一些操作的历史信息。记录的是对数据库的操作。那么大家想想，如果一个正在运行的数据库，是数据文件大，还是日志文件大?正常情况下来说，日志文件会远远大于数据文件。原因是日志文件记录了对数据库的所有的操作信息，如果一个表有1000条记录，现在咱们通过语句修改了这1000记录的信息，那么日志文件中就会记录1000次操作，时间长了，日志文件会变得越来越大。所以，咱们会经常看到一个数据文件只有几十M，而日志文件会高达几个G。

　　表是咱们数据文件中一个重要对象，它在存放的时候，是按页为单位进行存放的。每个数据页是8KB，每8个数据页又构成一个扩展(区)。但SQL Server有一个规定，行不能跨页，是什么意思呢?也就是一条记录的最大容量是8KB，不能超过8KB，也就是一条所有的字段宽度加在一起，不能超过8KB.，也就是不能超过8060字节。页的大小是8KB，即8192字节，故行的大小不会超过8192字节，最大值是8060字节，因为8192-96(标头信息)- 2(页尾维护的行指针)- 34(保留字节) = 8060。

　　一般情况下，一行也不会超过8KB， 8060/10 也可以放800多个字段。但这里面会涉及到读取性能的问题。一会再说。那万一超过了，怎么办?在 SQL Server 2005 中，行不能跨页，但是行的部分可以移出行所在的页，因此行实际可能非常大。页的单个行中的最大数据量和开销是 8,060 字节 (8 KB)。但是，这不包括用 Text/Image 页类型存储的数据。在 SQL Server 2005 中，包含 varchar、nvarchar、varbinary 或 sql_variant 列的表不受此限制的约束。当表中的所有固定列和可变列的行的总大小超过限制的 8,060 字节时，SQL Server 将从最大长度的列开始动态将一个或多个可变长度列移动到 ROW_OVERFLOW_DATA 分配单元中的页。每当插入或更新操作将行的总大小增大到超过限制的 8,060 字节时，将会执行此操作。将列移动到 ROW_OVERFLOW_DATA 分配单元中的页后，将在 IN_ROW_DATA 分配单元中的原始页上维护 24 字节的指针。如果后续操作减小了行的大小，SQL Server 会动态将列移回到原始数据页。

　　那么咱们来看一个问题：

　　一个表有10000行，每行占5000字节，则此表占多少K字节?如果每行占4000字节呢?

　　5000： 10000*8000 每一页浪费了3000字节

　　4000： 5000*8000 二行可以共用一页

　　好处肯定是第二种方式，占用磁盘空间少了，但这不是主要好处，因为现在磁盘空间也大了，也便宜，这里面还有一个最主要的好处是读取性能增加了。也就是说一页包含尽量多的行

　　如果现在希望检索这1万行记录的话。如果一页中存放一行，就会发生1万次的I/O操作。才能把所有的数据读出来。

　　如果一页中有两行，只要5000次，那如果一页中存放4行。这效率是很明显的。

　规划数据库及编辑库属性

　　当用户在数据库时，需要考虑到用户的权限以及对此数据的规划，如下所示：

　　1 权限：必须是系统管理员或被赋予create database的权限

　　2 规划：如数据库名称，数据文件以及日志文件的属性等。

　　数据库名称，满足命名规范，有意义 数据库文件的位置 指定盘符路径， 数据库文件的属性：大小，增长率，最大容量。排序规则： 这个数据库是否和服务器使用同样的排序规则，早期2000的时候，一旦数据库创建成功后，排序规则就被订死了。2008里，这个排序规则也可以修改了。

　　Demo: 创建数据库：

　　基本配置就算了。只是大家在最大文件大小设置时不要使用“文件增长不受限制”

　　如果选择了文件增加不受限制，则是说文件的增长只会受到磁盘空间的限制，但如果真的是磁盘空间满足了，不仅SQL Server不能用，而且还会影响到系统的运行。所以不要选择“不受限制”。

　　数据文件和日志文件的默认存放位置的更改方法 ：服务器属性----数据库设置

　　在创建完数据库之后，可能还会根据需要对数据库的一些选项进行修改，因为默认的选项是拷贝模板数据库的选项，默认可以满足一般的应用。下面咱们来看一下SQL 2008里的一些数据库选项：

　　排序规则：排序规则根据特定语言和区域设置的标准指定对字符串数据进行排序和比较的规则。系统怎么知道你存放的是中文，日文，法文等不同的字符，并且它们之间的排序和比较的规则是什么呢。所以这里需要决定排序规则。

　　恢复模式：指定下列模式之一来恢复数据库：“完整”、“大容量日志”或“简单”。恢复模式主要用于控制事务日志维护。此处的选项不同，对于事务日志的处理方法就不同。

　　调整恢复模式命令也很简单： alter database DB_NAME set recovery full、simple、Bulk_logged

　　兼容级别：指定数据库支持的最新 SQL Server 版本。对于所有 SQL Server 2008 安装，默认的兼容级别都为 100。除非 model 数据库有更低的兼容级别，否则 SQL Server 2008 中创建的数据库会设置为该级别。将数据库从 SQL Server 的任何早期版本升级到 SQL Server 2008 时，如果数据库的兼容级别不在 80 以下，则该数据库将保留其现有的兼容级别。升级兼容级别低于 80 的数据库会将数据库的兼容级别设置为 80。这既适用于系统数据库，也适用于用户数据库。使用 ALTER DATABASE 可更改数据库的兼容级别。

　   执行某一存储过程时，该存储过程将使用定义它的数据库的当前兼容级别。在更改某一数据库的兼容性设置时，该数据库的所有存储过程都将随之自动重新编写。

　数据库其他辅助选项

　　Service Broker: 默认是启用的。其作用是Service Broker 可帮助数据库开发人员生成可靠且可扩展的应用程序。在 SQL Server 实例之间提供可靠的消息传递服务。这个服务不属于咱们这个系列的讨论范围。

　　页验证：默认是检验和，由于I/O或突然断电等 原因，都有可能会发生数据而残缺的情况，会影响数据的一致性。所以，如果我们启用了页验证，系统会进行一些修复工作。对应于2000的时候，叫残缺页!

　　CHECKSUM 当PAGE_VERIFY选项被设置为CHECKSUM时，SQL Server根据每个页面的内容计算出一个校验和，并在页面被写入磁盘时将该值保存在页面头部。当从磁盘读出该页面时，会重新计算出一个校验和并与保存在页面头部的值相比较。如果这两个值并不匹配，错误信息824(表示校验和错误)会被报告出来。

　　TORN_PAGE_DETECTION 当PAGE_VERIFY选项被设置为TORN_PAGE_ DETECTION时，无论何时只要有页面被写入磁盘，一个数据库页面(8KB)的每512字节的扇区就会有一个比特位被反转。它允许SQL Server探测由于电源故障或其他系统故障造成的不完整的I/O操作。如果以后SQL Server从磁盘读取数据时发现一个比特位的状态不正确，那意味着该页面没有被正确地写入磁盘(探测到了一个损坏的页面)。

　　NONE(没有页面校验选项)指定该值后，当一个页面被写入时，CHECKSUM 和TORN_PAGE_DETCTION 值都不会产生，并且当页面被读取时也不会校验这些值。

　　校验和与损坏页面错误都产生错误信息824，该错误信息会被写入SQL Server错误日志和Windows 事件日志。当读取任意一个页面时，如果产生了824错误，SQL Server都会在msdb数据库的系统表suspect_pages中插入一行。

　　所以，我们可能通过查看此表，来查看有没有相应的错误。

　　SQL Server将会对任何校验和、损坏页进行四次重试操作。如果在这些尝试中有一次是成功的，那么就会向错误日志中写入一条记录，并且触发该读取操作的命令将会继续执行。如果所有的尝试都是失败的，那么该命令将会失败并给出824错误信息。应该考虑将它更改为CHECKSUM。虽然TORN_PAGE_DETECTION使用的资源较少，与CHECKSUM相比它提供的保护也较少。

　　解决方法：

　　我们可以通过还原数据，或者当错误仅限于索引页面时重建索引来“修复”这个错误。如果我们遭遇一个校验和错误，可以运行DBCC CHECKDB来确定受到影响的数据页类型和数据页。我们还应该确定错误的根本原因并尽快解决它以防止额外的或正在发生的错误。发现问题的根本原因需要调查硬件、固件驱动程序、BIOS、过滤器驱动程序(例如反病毒软件)和其他I/O路径组件。

　　游标：

　　CURSOR_CLOSE_ON_COMMIT{ON|OFF} 如果该选项设置为ON，那么当一个事务被提交或被回滚时所有打开的游标都会被关闭(遵守SQL-92标准)。如果OFF(默认值)被指定，那么在事务提交后游标仍然保持开放。回滚一个事务会关闭除了被定义为INSENSITIVE或STATIC之外的所有游标。

　　CURSOR_DEFAULT {LOCAL|GLOBAL} 当这个选项被设置为LOCAL且游标在创建时没有被指定为GLOBAL时，任何游标的作用域都是创建它的本地批处理、存储过程或触发器。游标名称只在它的作用域内才有效。游标可以被批处理、存储过程或触发器，或者一个存储过程的输出参数中的本地游标变量所引用。当这个选项被设置为GLOBAL且游标在创建时没有被指定为LOCAL时，游标的作用域就是整个连接。游标的名称能够被任何使用该连接执行的存储过程或批处理所引用。

　　限制访问 ：

　　指定哪些用户可以访问该数据库。可能的值有：

　　多个 ：生产数据库的正常状态，允许多个用户同时访问该数据库。

　　单个 ：用于维护操作，一次只允许一个用户访问该数据库。系统数据库还原的时候，会用到

　　限制 ：只有 db_owner、dbcreator 或 sysadmin 角色的成员才能使用该数据库。

　　已启用加密：

　　下图显示了 TDE 加密体系结构：

　　若要使用 TDE，需要按照一定的步骤操作，如创建主密钥、创建或获取由主密钥保护的证书、创建数据库加密密钥并通过此证书保护该密钥、将数据库设置为使用加密。

　　下面的示例演示如何使用安装在名为 MyServerCert 的服务器上的证书加密和解密 AdventureWorks 数据库。

　　自动：

　　自动创建统计信息：数据库是否自动创建缺少的优化统计信息。这个统计信息有点类似于咱们地图的索引信息，从北京的昌平到海淀，可以坐什么公交车，但如果是一个过期的地图，那么就可能不准确。所以在此一般会启用这两项。

　　自动更新统计信息：指定数据库是否自动更新过期的优化统计信息。可能的值包括 True 和 False。如果设置为 True，则将在优化过程中自动生成优化查询需要但已过期的所有统计信息。

　　自动关闭：当一个数据库的最后一个用户退出时，该数据库会被正常关闭，并因此释放所有的资源。所有的文件句柄都会被关闭，所有在内存中的结构都会被删除以便该数据库不再使用任何内存。当一个用户尝试重新使用该数据库时，它会重新打开该数据库。也就是说，自动关闭的好处是可以释放一部分内存给操作系统，但坏处是，下一次再用用户打开数据库的时候，所花费的时候会比较长，现在内存也大，为了性能来说，一般不启用此功能。所以对于生产型的数据库来说，就把此功能关闭了。

　　自动收缩：一个数据库的所有文件都会成为周期性收缩的对象。数据文件和日志文件都能够被SQL Server自动地收缩。SQL Server会定期的去检查有没有过多的空间被浪费掉。但有一个缺点：当SQL Server 在收缩的时候，可能会影响性能，其他用户在此时访问数据库的时候，会感觉到很慢。所以生产型数据库此项也是不启用的。

　　自动异步更新统计信息：如果设置为 True，则启动过期统计信息的自动更新的查询在编译前不会等待统计信息被更新。后续查询将使用可用的已更新统计信息。如果设置为 False，则启动过期统计信息的自动更新的查询将等待，直到更新的统计信息可在查询优化计划中使用。

　　收缩数据库或数据库文件

　　应用场合：如下图所示，我们需要将多余的空间释放，但此操作会影响到业务系统的性能，所以在进行数据库收缩时，一定要选择好时机。

　　我们现在希望把多余的空间450M还给操作系统。那么就可以对数据库进行收缩。

　　自动收缩的方法：数据库选项中允许“自动收缩” 或者是命令。如图所示：

　　设置好自动收缩后，数据库就会对空间进行检查：自动收缩会在未用空间超过25%开始，如数据文件的大小是500M,而数据量是400M，未用空间是100M 达到了100/400=25%，则自动收缩开始。自动收缩在后台运行，以减少对用户活动的运行。但只能是减少，影响还是有的。那咱们一般就不启用此选项，那怎么收缩呢，我们可以手动收缩的方式进行。

　　语法： DBCC shrinkdatabase 数据库名称[,目标百分比] [,notruncate|truncateonly]

　　目标百分比：希望收缩后，未用空间占整个空间的百分比。但不能收缩到比较初始创建更小。如初始是50M，收缩后数据文件必须是大于50M的。其中Notruncate指的是将分散在数据库不同位置的剩余空间收缩，数据库收缩后空间位于文件尾，但不交还给操作系统，还留着，只是把数据做了一个集中。文件大小不变。Truncateonly指的是尽可能的收缩，收缩后的空间还给操作系统。加上这个选项则目标百分比失效。只适用于数据文件。日志文件不受影响。Truncateonly选项是尽可能的去收缩，而不管你的百分比是多少。只适用于数据文件。日志文件不受影响。没有notruncate或truncateonly的话，则收缩之后，将空间交换给操作系统。

　　数据库的物理规划

　　对数据库进行物理规划时，主要考虑到数据文件和日志文件位置规划。这也是考验数据库管理员的一项基本技术，进行数据库的物理规划的目的有支持高性能和容错，分散数据文件以提高I/0并发，日志文件和数据文件在不同的磁盘上，目的也是为了获得更高的I/0并发，临时数据库在单独的磁盘上。因为临时数据库一般用于存放临时数据，单独放在一个磁盘上是为了防止和其他数据库争用I/O资源。

　　在此会使用到RAID技术，RAID是英文Redundant Array of Inexpensive Disks的缩写，中文简称为廉价磁盘冗余阵列。简单的说，RAID是一种把多块独立的硬盘(物理硬盘)按不同的方式组合起来形成一个硬盘组(逻辑硬盘)，从而提供比单个硬盘更高的存储性能和提供数据备份技术。组成磁盘阵列的不同方式成为RAID级别(RAID Levels)。数据备份的功能是在用户数据一旦发生损坏后，利用备份信息可以使损坏数据得以恢复，从而保障了用户数据的安全性。在用户看起来，组成的磁盘组就像是一个硬盘，用户可以对它进行分区，格式化等等。总之，对磁盘阵列的操作与单个硬盘一模一样。不同的是，磁盘阵列的存储速度要比单个硬盘高很多，而且可以提供自动数据备份。如图所示：

　　此外，还需要用到文件组技术，文件组是SQL Server一个或多个数据文件的命名集合，它构成分配或用于数据库管理的单个单元。但文件不能跨文件组。有点类似于文件夹的意思。文件组分为主文件组和用户自定义文件组，主文件组有且仅有一个，用于存放mdf文件和ndf文件，用户定义文件组;可以有多个，仅用于存放ndf文件。

　　文件组对组内的所有文件都使用按比例填充策略，当将数据写入文件组时，SQL 根据文件中的可用空间将一定比例将数据写入到文件组的每个文件，而不是将所有的数据先写满第一个，再写第二个。如果先写满一个，再写第二个，就没有必须存放在不同磁盘了。

　　有了文件组之后，管理员就可以备份文件或文件组而不是整个数据库，如一个数据库有上G的容量，备份一次时间太长了，所以我们就可以把数据分散到多个文件组中，每天备份一个文件组。如果某个表需要单独的维护，把其放到一个单独的文件组：一个数据库中可能有很多的表，但经常操作的表可能只有几个，那么我们就可以将这些表单独放到一个文件组中，10%表的天天使用，30%有时候使用到，60%，很久不使用，那备份的时候，是天天都备份呢，还是一个小时备份一次呢，可以创建不同的文件组，对应不同使用频率的表。实现不同的备份策略。如果仅仅是为了性能，应使用RAID而不是文件组 文件组的性能不如RAID，但RAID也仅仅是为了提供性能，不能实现一些维护。指定一个用户定义的文件组为默认文件组 我们有一个指导思想，系统数据和用户数据应该分开存放。将某个文件组设置为默认文件组后，那么用户数据就默认存放到用户文件组中。以实现系统数据和用户数据分开存放。文件组不能提供容错：使用文件组主要是为了实现特定的维护，不能提供容错。