SQL 问题与解答I/O 错误、数据库镜像及更多内容

Paul S. Randal

问:我一直在对自己管理的数据库运行常规一致性检查,甚至还添加了一些 SQL 代理警报以捕捉用户查询遇到的任何 I/O 错误。但是,我不知道自己针对检查和警报实现的逻辑是否起作用,因为所有数据库都没有出现过损坏问题。我如何在测试数据库中创建一些损坏,以便确认做出的每项设置都能正常工作?此外,是否还应使用其他方法来检测 I/O 错误?

答:在 SQL Server® 2000 中,创建供测试用的损坏数据库的传统方法是手动从测试数据库的 sysindexes 表中删除一行。但在 SQL Server 2005 中,以这种方式损坏系统表非常困难。自己损坏测试数据库的最佳方式是在数据库关闭时,使用十六进制编辑器更改数据文件。操作步骤如下:

  • 关闭数据库,以解除对数据文件的锁定。(但请注意不要拆离数据库,因为如果损坏的页面出错,可能无法将其还原。)
  • 在文件中选取若干页的偏移(例如 100 页,至少 819200 个字节),但要确保它不超过 8192 字节的边界(页边界)。这样就不会波及重要的元数据页面和分配位图,使您能够启动数据库并对其运行 DBCC CHECKDB。
  • 在文件中所选的偏移处写入几个字节,内容全部为零。通常使用这种方法来确保引入一些页眉损坏错误。

也就是说,创建损坏的测试数据库的最快捷方式就是使用别人已创建好的数据库。我的博客(网址是 go.microsoft.com/fwlink/?LinkId=115151)上提供了损坏的 SQL Server 2000 和 SQL Server 2005 数据库的示例(包括说明)。

您的第二个问题是应执行哪些操作来检测 I/O 错误 — 应启用页面校验和。SQL Server 2005 中引入了此功能,以防止整个数据库页出现由子系统带来的 I/O 错误。

基本上,将某页写入磁盘后,SQL Server 要做的最后一件事就是计算整个 8KB 页面的校验和并在该页上标记此校验和。从磁盘中读取某页时,如果该页具有页面校验和,则会重新计算校验和,并将此值与该页上存储的值进行比较。如果二者不符,则表示此页在 SQL Server 外部被损坏,并会引发 824 错误。此错误将在读取此页的连接过程中出现,并记录到 SQL Server 错误日志和 Windows® 应用程序事件日志中。

默认情况下,在 SQL Server 2005 和 SQL Server 2008 上创建的所有数据库的页面校验和功能都处于启用状态。但对于从 SQL Server 早期版本升级而来的数据库,则必须手动启用。您可以使用以下代码来启用页面校验和:

ALTER DATABASE mydb SET PAGE_VERIFY CHECKSUM;

提示:更改默认的 SQL Server 端口

默认情况下,为 SQL Server 实例配置的端口是 1433。一旦此端口被某个实例占用,则无法再供其他实例使用。因此,如果您安装了另一个(已命名)实例用于侦听使用 tcp 的网络,则还需要另外一个端口。而且在某些情况下,管理员可能为了避免混乱而更改端口(尽管这种混乱微不足道,可以通过端口扫描程序轻松解决)。当然,此后您必须将客户端配置为使用其他端口。常用的方法有以下三种。

第一种方法,假设管理员将某个实例的端口更改为 5555,您可以轻松地使用语法 MyServername,5555 在要连接到的机器名中指定此实例的端口号。如果端口再次更改,则客户端也必须再次更改其 connectionStrings。

第二种方法是使用客户端上配置的 SQL Server 别名。除指定别名外,还必须指定服务器名称、端口名称和协议。配置好后,即可使用此别名连接数据库实例,连接方法与使用服务器名方式相同。此方法的好处在于域管理员可以部署服务器配置更改,因为这些设置都存储在注册表中。

第三种方法是使用 SQL Server Browser 服务,适用于已命名实例。在这种情况下,用户只知道实例名称,并在 connectionString 中使用 MachineName\InstanceName 指定该名称。此方法在 SQL Server 2000 中已作为运行服务的一部分实现。但在 SQL Server 2005 中,SQL Server Browser 服务作为一项单独服务构建。除了对计算机执行实例发现外,它还会在端口 1434 上使用所请求实例的相应端口号应答传入用户数据报协议 (UDP) 请求,从而实现客户端的重定向并支持透明连接。

—Jens K. Suessmeyer,Microsoft 的数据库顾问

问:为了消除数据库中的所有碎片,我设置了一个夜间维护计划,重新构建在 SQL Server 2005 Enterprise Edition SP2 上运行的生产数据库中的所有索引。我注意到此方法会导致数据库过度增长,因此添加了一个缩减所有额外空间的步骤,因为磁盘上剩余的空间已不足。但现在,重新生成操作似乎不起作用了。这是怎么回事?

答:这是人们设置维护计划时经常遇到的一个问题。您陷入了“收缩-增长-收缩-增长”循环。

在重新生成索引时,会在原有索引删除前创建该索引的一个新副本。此过程需要占用数据库文件中的额外空间 — 通常与当前索引占用的空间量相同。在 SQL Server 2000 中,排序索引行也需要额外的空间(大约为索引大小的 20%),但在 SQL Server 2005 中仅重新生成索引已不再有此项要求。

有时,管理员希望删除重新生成索引时产生的额外空间,因此会在维护计划中的重新生成步骤后添加一个收缩操作。但是,人们并不知道此收缩操作会由于算法本身的原因导致索引碎片。这意味着,最近重新生成且经过碎片整理的索引会立即变成碎片,从而使之前重新构建索引的努力毁于一旦。

假设数据库文件只在下次重新生成索引时才会再次增长,则最好允许数据库中留有额外空间并完全避免运行收缩操作。(此外,连续增长和收缩数据库文件还会导致操作系统级别的文件碎片 — 此类碎片会像索引碎片一样降低系统性能。)

最后,您还可能要考虑降低重新生成索引的频率,甚至可能需要尝试使用其他方法,例如使用我为 SQL Server 2000 编写的旧 DBCC INDEXDEFRAG,或 SQL Server 2005 和 SQL Server 2008 中较新的 ALTER INDEX REORGANIZE 语法。

有一本白皮书用途很大,其中介绍了索引碎片并针对何时消除碎片提供了指导(网址是 go.microsoft.com/fwlink/?LinkId=115154)。虽然此书是针对 SQL Server 2000 编写的,但其中的概念仍然适用。

问:我们一直在组织内尝试灾难恢复策略,并且认为数据库镜像非常适合我们的情况。我尝试保护的服务器中有很多无关数据库(由前期服务器合并项目导致),但是希望对所有这些数据库使用数据库镜像。我想知道,在保证性能的前提下最多能镜像多少个数据库?

答:这个问题的答案就是我常说的:视情况而定!已发布的指南中指出,对每个实例进行镜像的数据库不得超过 10 个,这只是根据大部分用户的情况所做的粗略估计。您需要针对硬件设置考虑以下因素:

  • 主实例和镜像实例的内存多大?(理想情况下,二者应该相同。)
  • 主实例和镜像实例的处理能力如何?(二者也应相同。)
  • I/O 子系统在镜像实例上的带宽是多少?(应与在主实例上的带宽相同。)
  • 每个数据库上的工作负荷生成的事务日志量是多大?
  • 主实例和镜像实例之间有多少可用网络带宽?

最后两个因素是最重要的。如果两个实例之间的可用网络带宽不足,无法处理要镜像的所有数据库每秒生成的所有事务日志,则主数据库的性能将下降。SQL Server 2008 可使用日志流帮助减轻部分负担。

接下来要考虑的重点是镜像的内存和线程要求。每个镜像数据库都会占用一个线程和部分内存。如果服务器的功耗比较低,则众多镜像数据库与常规工作负荷加起来,会导致服务器负载过重。

您还需要考虑运行数据库镜像的方式。在同步模式下,只有将所有事务日志记录复制到镜像数据库的事务日志后,才能提交主数据库上的事务。因此,由网络超负载引起的任何延迟都会导致主数据库出现工作负荷性能问题。

在异步模式下,主数据库上的事务不必等待即可提交,但是网络延迟可能会导致等待发送到镜像的事务日志量增加。这会导致事务日志大小方面的问题。更糟糕的是,如果出现失败,尚未发送的所有事务日志都将丢失。因此,未发送的事务日志越多,在恢复过程中丢失数据的可能性就越大。

应用方案可能相差很大,我曾在实际生产环境中看到过一些有趣的例子。例如,我曾见过包含 150 个数据库的环境,每个数据库中的活动量较少,而且并非始终同时活动。这 150 个数据库都能够成功进行镜像。

相反,我还曾看到过只包含三个高负载数据库的设置,但网络连接不好。在该方案中,只能对一个数据库进行镜像,否则就会由于网络带宽不足而导致工作负荷下降。

成功镜像的关键在于先计算出日志生成量。如果计算结果表明可用网络带宽能够支持要镜像的数据库数目,则可能会成功。在投入生产前需测试您的配置,确保包括可能生成事务日志的所有操作,尤其是您可能执行的所有数据库维护操作。

Paul S. RandalSQLskills.com 的主管,也是 SQL Server 的 MVP 之一。从 1999 年到 2007 年,他一直在 Microsoft 的 SQL Server 存储引擎团队工作。Paul 曾编写 DBCC CHECKDB 来修复 SQL Server 2005,并在 SQL Server 2008 开发过程中负责核心存储引擎部分。Paul 是灾难恢复、高可用性和数据库维护方面的专家,经常出席一些会议。他的博客地址是 SQLskills.com/blogs/paul

© 2008 Microsoft Corporation 和 CMP Media, LLC。保留所有权利;未经允许不得复制本文的部分或全部内容.