将 Access 2002 数据库迁移到 SQL Server(二)-先飞网电脑教程文章

>> 我们从网络和杂志上收集了近100000余篇各类电脑技术、网络技术、软件技术等方面的文章教程,我们的收录原则:不是精华拒不收录!

将 Access 2002 数据库迁移到 SQL Server(二)

［作者：佚名转贴自：本站原创阅读次数：160 更新时间：2005-12-4 12:52:00 录入：刘光勇］热

更可靠的查询

•	当 Access 开发人员尝试运行查询、窗体或基于查询的报表时，可能会遇到 Out of Memory（内存不足）或 Query too Complex（查询太复杂）错误。这通常是因为您要执行的查询中包含的表联接数超出了 Access 的处理能力。为了解决此问题，Access 开发人员通常不得不花费大量资源重新设计查询以及重建表结构。

SQL Server 已被重新设计，可以支持更灵活的查询。在一个查询中，最多可以：

•	在 SELECT 语句中使用 256 个表
•	使用约 256 KB 的查询文本
•	在 SELECT 语句中使用 4096 个列

还有一点要注意，Access 最多支持 50 个嵌套的子查询，但 SQL Server 最多只能支持 32 个。

使用数据

在 Access 和 SQL Server 中创建数据查询的方式并不一样。不同之处在于使用的查询语言和查询设计器。SQL Server 还支持存储过程（一种灵活有效的数据查询存储方式）和用户定义的函数（允许您重用业务逻辑）。而且，SQL Server 还提供了比 Access 功能更强大的故障恢复模式。

查询数据

查询优化

在 Access 中远程查询数据时，所有数据都将返回客户端，而且筛选和排序也是在客户端完成的。因为 SQL Server 数据查询通常通过网络从客户端进行，所以可能会发生严重的网络带宽问题。因此，将后端系统迁移到 SQL Server 时，重要的是要重新设计查询，以便只将所需的数据集返回客户端（而不是整个数据集）。例如，某个 Access 窗体暗含的查询可能是：

SELECT * FROM Customers

打开该窗体时，上面的查询将返回整个 Customers 表。在 SQL Server 中，必须优化该查询，以便只返回当前记录。对应的 SQL 查询应为：

SELECT * FROM Customers WHERE CustomerID = 'C00010'

这样将只返回一个行/记录。每当用户导航到该窗体中的下一个或上一个记录时，CustomerID 就会发生变化，而数据库就需要重新执行查询以检索当前记录。

这种服务器端筛选方法在数据库服务器上执行筛选和排序，并且只返回最少数量的所需的数据，从而有助于减少网络通信量。

查询类型

Access 为查看和设计数据查询提供了多种方法。表 5 列出了将内置的 Access 查询类型迁移到 SQL Server 时可以使用的选项。

表 5：将 Access 查询转换为 SQL Server 查询的选项
Access 查询类型	SQL Server 迁移选项
Select	SELECT 语句可以在 T-SQL 文件、存储过程或视图中使用。还可以使用内置的 SQL Server 查询设计器来设计 SELECT 语句，该设计器与 Access 查询设计器类似（参阅图 24）。
Crosstab	Crosstab 可以作为 T-SQL 文件、存储过程或视图来实现。可以使用临时表来查询内存中的 Crosstab 所需的数据集。然后可以联接和查询临时表，以检索所需的 Crosstab 数据。将 Access Crosstab 数据转换为可以在 SQL Server 中使用的数据可能很耗时。您可以考虑使用第三方应用程序自动执行某些步骤。要处理 Crosstab 查询，一种更灵活有效且可扩展的解决方案是使用 SQL Server 分析服务。使用分析服务可以创建联机分析处理 (OLAP) 多维数据集，以生成复杂的动态报表。有关使用 SQL Server 分析服务处理数据的详细说明，请参阅 sql/evaluation/bi/bianalysis.asp" target="_blank">Analysis Services。
Make table	Make table 可以作为 T-SQL 语句实现，该语句使用 SELECT INTO 子句将数据从一个表复制到另一个表中。
Update	Update 语句可以作为使用 UPDATE 子句的 T-SQL 语句或存储过程进行存储。
Append	Append 语句可以作为使用 INSERT INTO 子句的 T-SQL 语句或存储过程进行存储。
Delete	Delete 语句可以作为使用 DELETE FROM 子句的 T-SQL 语句或存储过程进行存储。

查询语言功能

表 6 总结了 Access 和 SQL Server 在支持的查询语言功能方面的主要区别（摘录自《Access 2002 Desktop Developer's Handbook》，Paul Litwin 等著，SYBEX Inc. 2001 年出版）。

表 6：Access 和 SQL Server 在数据查询方面的区别
功能	是否受带有 Jet 4 SQL-92 扩展的 Access SQL 支持	是否受 SQL Server 2000 T-SQL 支持
安全性（GRANT、REVOKE 等）	是	是
事务支持（COMMIT、ROLLBACK 等）	是	是
视图 (CREATE VIEW)	是	是
临时表	否	是
FROM 子句中的联接	是	是
UPDATE 和 DELETE 语句中的联接	是	否
支持 FULL OUTER JOIN 和 UNION JOIN	否	是
支持在 UPDATE 语句的 SET 子句中使用子查询	否	是
支持在 DELETE 语句中使用多个表	是	否
SELECT DISTINCTROW	是	否
SELECT TOP	是	否
游标（DECLARE CURSOR、FETCH 等）	否	是
域支持（CREATE DOMAIN、ALTER DOMAIN 等）	否	是
支持检查约束	是	否
声明（CREATE ASSERTION、DROP ASSERTION 等）	否	否
行值构造函数	否	否
CASE 表达式	否	是
在 CREATE TABLE 语句中实现完全的引用完整性支持	否	是
标准化的系统表和错误代码	否	否
标准数据类型	是	是
标准字符串运算符	否	是
标准通配符	是	是
支持 VBA 函数	是	否
其他聚合函数	是	否
TRANSFORM 语句	是	否
在查询或存储过程中使用参数	是	是
SELECT INTO 语句	是	是

有关在 SQL Server 中设计 Access 查询的详细信息，请参阅 sql7/html/access2sql.asp" target="_blank">Migrating Your Access Database to Microsoft SQL Server 7.0。（注意：这篇文章是针对 SQL Server 7.0 编写的，尚未进行更新。）

为对象编写脚本的能力

结构化查询语言 (SQL) 是 Access 和 SQL Server 进行数据访问和数据处理时使用的标准语言。SQL 语言的最新修订版称为 SQL-92，以完成修订的年份命名。Microsoft 在基本 SQL 语言中添加了自己的某些扩展，这些扩展在两个 DBMS 解决方案中是不同的。

Access 支持带有 Jet 4 ANSI-92 扩展的 SQL-92，这使您可以使用 SQL 来管理事务。

Jet 4 ANSI-92 扩展还使您可以更轻松地管理数据库安全性。但它不支持某些功能，例如，设置和更改数据库对象所有权。

在 SQL Server 2000 中，Microsoft 在基本 SQL-92 语言中添加了一些自定义扩展。这些扩展增加了对某些重要功能的脚本支持，例如：

•	存储过程
•	分布式事务
•	操作系统函数
•	更灵活的子查询
•	在查询中使用别名
•	备份和恢复数据

T-SQL 语言是对标准 SQL 命令集的有力扩展。它提供了执行以下操作所需的所有功能：

•	在数据库表中检索、修改、删除和添加数据
•	接受和返回参数
•	执行计算
•	运行内置函数和用户定义的函数
•	在服务器之间复制数据

T-SQL 就像 Access 查询和 VBA 之间的桥梁，因为它使数据查询可以与条件逻辑和计算组合在一起。

请注意，SQL Server 完全支持 SQL-92 标准，因此不需要使用扩展。

表变量：对复杂查询很有用

要在 Access 中对一组联接表执行计算，您需要创建一个定义联接的查询。在使用该数据的应用程序中，每次在 SQL SELECT 语句中使用该查询时，所有表都需要重新联接，这可能就是一项耗费资源的操作（尤其是在多用户环境中）。

例如，要删除名字以字母 A 开头的所有客户并删除所有客户订单和订单历史记录，在 Access 中，您需要：

创建一个 SELECT 查询，以获得所有需要的客户 ID：

SELECT Customers.CustomerID
            FROM Customers
            WHERE Customer.FirstName LIKE 'A%'

将上面的 SELECT 查询包括在三个 DELETE 查询中，以删除所有需要的客户、订单和订单历史记录：

DELETE FROM Orders
            WHERE Orders.CustomerID IN
            (
            SELECT Customers.CustomerID
            FROM Customers
            WHERE Customer.FirstName LIKE 'A%'
            )
            And
            DELETE FROM OrderHistory
            WHERE OrderHistory.CustomerID IN
            (
            SELECT Customers.CustomerID
            FROM Customers
            WHERE Customer.FirstName LIKE 'A%'
            )
            And
            DELETE FROM Customers
            WHERE Customer.FirstName LIKE 'A%'

以这种方式执行该操作的效率非常低，因为每个删除操作都需要对 Customers 表运行耗费资源的 LIKE 筛选。如果 Customers 表中的记录增加到数百万条，执行此类通配符 WHERE 筛选就会产生严重的性能问题。

执行该操作的更有效的方式是使用表变量，这是 SQL Server 提供的一项功能。表变量的使用方式类似于 SQL 语法中的常规表。但是，表变量与常规表的不同之处在于表变量暂时存储在内存中，而不是存储在硬盘上。因为内存访问要比硬盘访问快得多，因此，在对同一个筛选或联接的数据集执行多个操作时，表变量会很有用。

要使用表变量实现上面的示例，您需要：

声明表：

DECLARE @tmpCustomerIDs TABLE (CustomerID nvarchar(50))

获得筛选的记录集并将它们存储在表变量中：

INSERT INTO @tmpCustomerIDs (CustomerID)
            (SELECT CustomerID FROM Customers WHERE Customers.ContactName LIKE 'A%')

使用表变量中的值，对客户、订单和订单历史记录执行所有删除操作：

DELETE FROM Orders
            WHERE Orders.CustomerID IN
            (
            SELECT CustomerID
            FROM @tmpCustomerIDs
            )
            And
            DELETE FROM OrderHistory
            WHERE OrderHistory.CustomerID IN
            (
            SELECT CustomerID
            FROM @tmpCustomerIDs
            )
            And
            DELETE FROM Customers
            WHERE Customers.CustomerID IN
            (
            SELECT CustomerID
            FROM @tmpCustomerIDs
            )

临时表是 SQL Server 为了有效地对动态数据集执行操作所提供的另一个机制。与表变量不同的是，临时表停留在内存中的时间较长，因此可能需要更多的数据控制和日志记录资源。

系统故障恢复

在尝试打开已损坏的数据库时，大多数 Access 开发人员都遇到过 Unrecognized database format（无法识别的数据库格式）错误，如图 25 所示。如果系统出现故障（例如操作系统故障或停电），您可以选择以下解决方法：

•	使用 Access 压缩和修复工具尝试从损坏的 .mdb 文件中恢复数据，然后将恢复的数据导入到空数据库中，以便尽量减少受损坏的记录数量。这并不是一个万无一失的措施，数据仍然可能会丢失。
•	从最近的备份恢复。由于需要重新输入丢失的数据，这可能会浪费资源。
•	运行 Jet 压缩工具 Jetcomp.exe。通常，这比运行压缩和修复工具更有效。但是，仍然无法保证所有数据都完好无损。
•	将损坏的数据库提交给第三方数据库恢复专家，由他们使用专有方法从数据库中提取数据。这种方法的费用可能很高，而且由于让外部人员处理您的数据，还可能会带来安全隐患。

SQL Server 使您可以更好地控制数据恢复过程。您可以为每个 SQL Server 数据库选择三种恢复模式之一，以确定如何备份数据，以及在丢失数据时采取哪些措施。这三种恢复模式包括：

•	简单恢复。可以恢复最近的备份。
•	完全恢复。使数据库恢复到出现故障之前的状态。这种模式需要的系统资源和磁盘空间最多（用于日志记录）。
•	批日志恢复。使数据库恢复到最后一次备份日志时的状态。这种模式需要的系统资源和磁盘空间比完全恢复模式少，但很可能需要手动重新输入数据。
•	这些恢复模式使您可以根据可用的系统资源灵活地选择进行系统故障恢复的最佳方式。

提示：与 Access 备份相比，SQL Server 数据备份的主要优点是，用户可以在数据库运行时完成备份，而不需要从数据库中注销。这就增加了数据库对用户的可用性，并能保证更长的正常运行时间。

比较数据库

在 Access 中，根据最新的结构更改实时更新生产数据库是一项持续的工作。您需要快速断开数据库连接以进行结构更改和数据转换，但是，如果用户依赖于该系统，则很难完成这项工作。由于可能添加了新的字段和关系，因此数据转换也需要时间。

要对 Access 数据库进行结构更改，通常需要：

1.	由开发人员处理应用程序数据库，对包含数据的数据库进行结构更改。
2.	跟踪对包含数据的数据库所做的更改，并编写用于执行更新的更新查询、DAO 或 ADO 代码。
3.	完成开发后，需要在手动更新时断开数据库连接。

可以使用第三方应用程序（例如 SSW Data Renovator）自动完成上述过程中的某些操作，以帮助您尽量减少系统的不可用性，降低发生错误的可能性。SSW Data Renovator 可以将新数据库与生产数据库进行比较，然后针对二者之间的所有差异生成报表，并提供向导样式的界面，以便自动将数据迁移到新的结构中。

尽管 SQL Server 具有不需要断开数据库连接就能进行结构更新的优点，数据库管理员仍然必须：

•	分析所有数据库架构，并针对结构更改而修改日志。
•	手动创建迁移脚本，以便将更改推入目标数据库。

可以使用第三方工具（例如 Red-Gate SQL Compare 或 sqldeploy/" target="_blank">SSW SQL Deploy）帮助您自动执行此任务，步骤如下：

•	比较数据库中的所有对象，包括存储过程、关系、表、视图和用户定义的函数
•	报告所有差异
•	生成可以直接在目标数据库上运行的迁移脚本

结论

Microsoft SQL Server 2000 是企业级数据库解决方案，与 Microsoft Access 2002 相比，它极大地改进了可伸缩性、维护性和数据库恢复功能。由于 SQL Server 基于客户端/服务器体系结构，所以在通过远程连接处理和发送数据的方式上与 Access 有很大的不同。SQL Server 还提供了许多功能，使数据查询、业务逻辑重用和数据备份等任务变得更简单，更灵活。

术语

ADO.NET

Microsoft .NET Framework 附带的一种数据访问模型。它是专门为需要可伸缩性、无状态和 XML 的 Web 而设计的。

客户端/服务器体系结构

一种软件体系结构，它允许多个客户端向中央服务器或服务器组发出请求，并从这些服务器接收结果，从而改进了可伸缩性。这种体系结构的处理负载由客户端和服务器共同分担。

群集

一种直接在硬盘上为数据建立索引和排序的方法，大大提高了数据查询的速度。

数据转换服务

SQL Server 附带的工具，用于在使用基于 OLE DB 体系结构的多种数据源（例如 Microsoft Excel）中导入和导出数据。

OLAP

联机分析处理。一种数据存储模型，可以帮助您从不同的角度分析业务数据。例如，您可以使用 OLAP 查看某个时段以超过某个价格的价格在某个地区销售的所有产品。

SQL Server 企业管理器

SQL Server 附带的工具，使您可以轻松地管理数据库对象、用户、备份和数据库权限。

SQL Server 事件探查器

SQL Server 附带的工具，它可以识别特别耗时或耗费资源的数据库事务，从而帮助您优化查询。

SQL Server 查询分析器

SQL Server 附带的工具，用于编写和调试数据库查询。

T-SQL

Transact-SQL。对 SQL-92 标准查询语言的扩展，它提供了 SQL Server 中的扩展功能，例如存储过程、数据备份和恢复以及分布式事务。

UDDT

用户定义的数据类型。SQL Server 中的一项功能，它允许您基于现有的 SQL Server 基本数据类型创建您自己的数据类型。UDDT 使您能够对数据应用更严格的业务规则。

UDF

用户定义的函数。自定义的 T-SQL 语句块，它使您能够轻松地在整个数据库应用程序中重用业务逻辑。

Visual Studio .NET

一种集成的开发环境 (IDE)，它使开发人员能够以可视方式开发各种与 Microsoft .NET 相关的应用程序。它为设计、编译、测试和部署支持 .NET 的 Web 和 Windows 应用程序提供了功能强大的工具。

XML

可扩展标记语言。一种广泛采用的标准方式，它以不需要太多人工或计算机操作即可处理的格式来表现文本和数据。

有关详细信息，请访问

sql/default.mspx" target="_blank">Microsoft SQL Server 产品页

关于作者

Adam Cogan 是 SSW 的主要体系结构设计师。SSW 是一家 Microsoft 认证合作伙伴，专门提供基于 Office 和 .NET 的解决方案。在 SSW，Adam 使用 Microsoft 技术（例如 SQL Server 2000、.NET 和 Office 2003）开发了适用于各种业务领域的自定义解决方案。Adam 还组建了悉尼的 Microsoft .NET 用户组，并积极参与该地区的 INETA 管理过程。

上一篇:将 Access 2002 数据库迁移到 SQL Server(一) 下一篇: 将ACCESS转化成SQL2000需要注意的几个问题