将 Access 数据库迁移到 SQL Server

开始之前

以下部分提供背景信息和其他信息来帮助你入门。

关于拆分数据库

所有 Access 数据库对象可以位于一个数据库文件中，也可以存储在两个数据库文件中：前端数据库和后端数据库。这称为拆分数据库，旨在促进网络环境中的共享。后端数据库文件只能包含表和关系。前端文件只能包含所有其他对象，包括窗体、报表、查询、宏、VBA 模块和指向后端数据库的链接表。迁移 Access 数据库时，它类似于拆分数据库，因为 SQL Server 充当现在位于服务器上的数据的新后端。

因此，你仍然可以使用 SQL Server 表的链接表来维护前端 Access 数据库。实际上，你可以获得 Access 数据库提供的快速应用程序开发的优势，以及 SQL Server 的可伸缩性。

SQL Server 优势

仍需要一些令人信服的迁移至 SQL Server？以下是需要考虑的一些其他好处：

更多并发用户 SQL Server 可以处理比 Access 更多的并发用户，并在添加更多用户时最大程度地减少内存要求。
提高了可用性 使用 SQL Server，可以在数据库正在使用时动态备份数据库（增量备份或完整备份）。因此，不必强制使用户退出数据库即可备份数据。
高性能和可伸缩性 SQL Server 数据库的性能通常优于 Access 数据库，尤其是对于 TB 大小的大型数据库。此外，SQL Server 通过并行处理查询（在单个进程中使用多个本机线程来处理用户请求）来更快高效地处理查询。
提高了安全性 使用受信任的连接，SQL Server 与 Windows 系统安全性集成，以提供对网络和数据库的单个集成访问，并采用这两种安全系统的最佳方法。这使得管理复杂的安全方案变得更加容易。 SQL Server 是敏感信息（例如社会安全号码、信用卡数据和机密地址）的理想存储。
可立即恢复性 如果操作系统崩溃或断电，SQL Server 可以在几分钟内自动将数据库恢复到一致状态，无需数据库管理员干预。
VPN 的使用 访问和虚拟专用网络 (VPN) 无法相处。但对于 SQL Server，远程用户仍可以使用桌面上的 Access 前端数据库和位于 VPN 防火墙后面的 SQL Server 后端。
Azure SQL Server 除了 SQL Server 的优点外，还提供无停机的动态可伸缩性、智能优化、全局可伸缩性和可用性、消除硬件成本以及减少管理。

选择最佳 Azure SQL Server 选项

如果要迁移到 Azure SQL Server，有三个选项可供选择，每个选项都有不同的优势：

单一数据库/弹性池 此选项具有通过 SQL 数据库服务器管理的资源集。单个数据库类似于 SQL Server 中的包含数据库。还可以添加弹性池，该池是数据库集合，其中包含通过 SQL 数据库服务器管理的共享资源集。最常用的 SQL Server 功能通过内置备份、修补和恢复提供。但不能保证确切的维护时间，从 SQL Server 迁移可能很困难。
托管实例 此选项是具有共享资源集的系统数据库和用户数据库的集合。托管实例类似于与本地 SQL Server 高度兼容的 SQL Server 数据库的实例。托管实例具有内置备份、修补、恢复功能，并且可以轻松地从 SQL Server 迁移。但是，有少量 SQL Server 功能不可用，并且无法保证确切的维护时间。
Azure 虚拟机 此选项允许在 Azure 云中的虚拟机内运行 SQL Server。你可以完全控制 SQL Server 引擎和简单的迁移路径。但需要管理备份、修补程序和恢复。

有关详细信息，请参阅选择到 Azure 的数据库迁移路径和什么是 Azure SQL？。

第一步

在运行 SSMA 之前，可以提前解决一些有助于简化迁移过程的问题：

添加表索引和主键 确保每个 Access 表都有一个索引和一个主键。 SQL Server 要求所有表至少具有一个索引，并且如果表可以更新，则要求链接表具有主键。
检查主键/外键关系 确保这些关系基于数据类型和大小一致的字段。 SQL Server 不支持在外键约束中具有不同数据类型和大小的联接列。
删除“附件”列 SSMA 不会迁移包含 Attachment 列的表。

在运行 SSMA 之前，请执行以下第一步。

关闭 Access 数据库。
确保连接到数据库的当前用户也关闭数据库。
如果数据库采用 .mdb文件格式，则删除用户级安全性。
备份数据库。有关详细信息，请参阅使用备份和还原过程保护数据。

提示请考虑在桌面上安装 Microsoft SQL Server Express 版本，该版本最多支持 10 GB，并且是运行和检查迁移的免费且更简单的方法。连接时，请使用 LocalDB 作为数据库实例。

提示如果可能，请使用独立版本的 Access。

运行 SSMA

Microsoft 提供 Microsoft SQL Server 迁移助手 (SSMA) ，以便更轻松地进行迁移。 SSMA 主要迁移表和选择没有参数的查询。窗体、报表、宏和 VBA 模块不会转换。 SQL Server 元数据资源管理器显示 Access 数据库对象和 SQL Server 对象，使你能够查看这两个数据库的当前内容。如果决定将来传输其他对象，这两个连接将保存在迁移文件中。

注意迁移过程可能需要一些时间，具体取决于数据库对象的大小和必须传输的数据量。

若要使用 SSMA 迁移数据库，请先通过双击下载的 MSI 文件来下载并安装软件。请确保为计算机安装相应的 32 位或 64 位版本。
安装 SSMA 后，在桌面上打开它，最好是从具有 Access 数据库文件的计算机上打开它。

还可以在有权从共享文件夹中的网络访问 Access 数据库的计算机上打开它。
按照 SSMA 中的开始说明提供基本信息，例如 SQL Server 位置、要迁移的 Access 数据库和对象、连接信息以及是否要创建链接表。
如果要迁移到 SQL Server 2016 或更高版本并想要更新链接表，请通过选择“ 查看工具 ”> “项目设置 ”> “常规”来添加 rowversion 列。

rowversion 字段有助于避免记录冲突。 Access 在 SQL Server 链接表中使用此 rowversion 字段来确定上次更新记录的时间。此外，如果将 rowversion 字段添加到查询，Access 将使用该字段在更新操作后重新选择该行。这有助于避免写入冲突错误以及当 Access 检测到原始提交的不同结果（例如，浮点数数据类型和修改列的触发器）时可能发生的写入冲突错误和记录删除方案，从而提高了效率。但是，请避免在窗体、报表或 VBA 代码中使用 rowversion 字段。有关详细信息，请参阅 rowversion。

注意避免将 rowversion 与时间戳混淆。尽管关键字 timestamp 是 SQL Server 中 rowversion 的同义词，但不能使用 rowversion 来为数据条目添加时间戳。
若要设置精确的数据类型，请选择“ 查看工具 > 项目设置 >类型映射”。例如，如果只存储英语文本，则可以使用 varchar 而不是 nvarchar 数据类型。

转换对象

SSMA 将 Access 对象转换为 SQL Server 对象，但不立即复制对象。 SSMA 提供了以下要迁移的对象的列表，以便确定是否要将对象移动到 SQL Server 数据库：

表和列
选择“不带参数的查询”。
主键和外键
索引和默认值
检查约束 (允许零长度列属性、列验证规则、表验证)

最佳做法是使用 SSMA 评估报告，其中显示了转换结果，包括错误、警告、信息性消息、执行迁移的时间估计，以及在实际移动对象之前要执行的各个错误更正步骤。

转换数据库对象从 Access 元数据获取对象定义，将它们转换为等效的 Transact-SQL (T-SQL) 语法，然后将此信息加载到项目中。然后，可以使用 SQL Server 或 SQL Azure 元数据资源管理器查看 SQL Server 或 SQL Azure 对象及其属性。

若要转换、加载对象并将其迁移到 SQL Server，请遵循本指南。

提示成功迁移 Access 数据库后，请保存项目文件以供以后使用，以便再次迁移数据以进行测试或最终迁移。

链接表

请考虑安装最新版本的 SQL Server OLE DB 和 ODBC 驱动程序，而不是使用 Windows 附带的本机 SQL Server 驱动程序。新驱动程序不仅速度更快，而且支持 Azure SQL 中的新功能，而以前的驱动程序则不支持。可以在使用转换后的数据库的每台计算机上安装驱动程序。有关详细信息，请参阅 Microsoft OLE DB Driver 18 for SQL Server 和 Microsoft ODBC Driver 17 for SQL Server。

迁移 Access 表后，可以链接到 SQL Server 中现在托管数据的表。直接从 Access 链接还提供了一种更简单的方法来查看数据，而不是使用更复杂的 SQL Server 管理工具。可以根据 SQL Server 数据库管理员设置的权限来查询和编辑链接数据。

注意如果在链接过程中链接到 SQL Server 数据库时创建 ODBC DSN，请在使用新应用程序的所有计算机上创建同一 DSN，或者以编程方式使用 DSN 文件中存储的连接字符串。

有关详细信息，请参阅链接到或导入 Azure SQL Server 数据库中的数据和导入或链接到 SQL Server 数据库中的数据。

提示不要忘记使用 Access 中的链接表管理器来方便地刷新和重新链接表。有关详细信息，请参阅管理链接表。

测试和修订

以下部分介绍了在迁移过程中可能会遇到的常见问题以及如何处理这些问题。

查询

仅转换选择查询;其他查询则不是，包括选择采用参数的查询。某些查询可能无法完全转换，并且 SSMA 报告转换过程中的查询错误。可以使用 T-SQL 语法手动编辑不转换的对象。语法错误可能还需要手动将特定于 Access 的函数和数据类型转换为 SQL Server 函数和数据类型。有关详细信息，请参阅将 Access SQL 与 SQL Server TSQL 进行比较。

数据类型

Access 和 SQL Server 的数据类型相似，但请注意以下潜在问题。

大数大数数据类型存储非货币的数值，并与 SQL bigint 数据类型兼容。可以使用此数据类型有效地计算大数，但它需要使用 Access 16 (16.0.7812 或更高版本) .accdb 数据库文件格式，并且使用 64 位版本的 Access 性能更好。有关详细信息，请参阅使用大数数据类型和在 64 位或 32 位版本的 Office 之间进行选择。

是/否 默认情况下，Access 是/否列转换为 SQL Server 位字段。若要避免记录锁定，请确保将位字段设置为不允许 NULL 值。在 SSMA 中，可以选择位列，将 Allow Nulls 属性设置为 NO。在 TSQL 中，使用 CREATE TABLE 或 ALTER TABLE 语句。

日期和时间 有几个日期和时间注意事项：

如果数据库的兼容级别为 130 (SQL Server 2016) 或更高版本，并且链接表包含一个或多个 datetime 或 datetime2 列，则表可能会在结果中返回消息 #deleted。有关详细信息，请参阅 access linked table to SQL-Server database returns #deleted。
使用 Access 日期/时间数据类型映射到 datetime 数据类型。使用 Access Date/Time Extended 数据类型可映射到具有较大日期和时间范围的 datetime2 数据类型。有关详细信息，请参阅使用日期/时间扩展数据类型。
在 SQL Server 中查询日期时，请同时考虑时间和日期。例如：
- DateOrdered 在 19 年 1 月 1 日和 19 年 1 月 31 日之间可能不包括所有订单。
- 日期在 1/1/19 00：00：00 AM 和 1/31/19 11：59：59 PM 之间指定包含所有订单。

附件 Attachment 数据类型将文件存储在 Access 数据库中。在 SQL Server 中，有几个选项需要考虑。可以从 Access 数据库中提取文件，然后考虑在 SQL Server 数据库中存储指向文件的链接。或者，可以使用 FILESTREAM、FileTable 或远程 BLOB 存储 (RBS) 来保留存储在 SQL Server 数据库中的附件。

链接访问表具有 SQL Server 不支持的超链接列。默认情况下，这些列将转换为 nvarchar (SQL Server 中的最大) 列，但你可以自定义映射以选择较小的数据类型。在 Access 解决方案中，如果将控件的 Hyperlink 属性设置为 true，则仍可以在窗体和报表中使用 超链接 行为。

多值字段 Access 多值字段作为包含分隔值集的 ntext 字段转换为 SQL Server。由于 SQL Server 不支持模拟多对多关系的多值数据类型，因此可能需要进行额外的设计和转换工作。

有关映射 Access 和 SQL Server 数据类型的详细信息，请参阅比较数据类型。

注意多值字段不会转换。

有关详细信息，请参阅日期和时间类型、字符串和二进制类型以及数值类型。

Visual Basic

尽管 SQL Server 不支持 VBA，但请注意以下可能的问题：

查询中的 VBA 函数 访问查询支持对查询列中的数据使用 VBA 函数。但是无法使用 VBA 函数的 Access 查询在 SQL Server 上运行，因此所有请求的数据都会传递给 Microsoft Access 进行处理。在大多数情况下，这些查询应转换为传递查询。

查询中的用户定义的函数 Microsoft Access 查询支持使用 VBA 模块中定义的函数来处理传递给它们的数据。查询可以是独立查询、窗体/报表记录源中的 SQL 语句、窗体上的组合框和列表框的数据源、报表和表字段，以及默认或验证规则表达式。 SQL Server 无法运行这些用户定义的函数。可能需要手动重新设计这些函数，并将其转换为 SQL Server 上的存储过程。

优化性能

到目前为止，使用新的后端 SQL Server 优化性能的最重要方法是决定何时使用本地或远程查询。将数据迁移到 SQL Server 时，还会从文件服务器迁移到客户端-服务器数据库计算模型。遵循以下一般准则：

在客户端上运行小型只读查询，以最快的速度进行访问。
在服务器上运行长时间的读/写查询，以利用更大的处理能力。
使用筛选器和聚合最大程度地减少网络流量，以便仅传输所需的数据。

有关详细信息，请参阅创建传递查询。

下面是其他建议准则。

在服务器上放置逻辑 应用程序还可以使用视图、用户定义的函数、存储过程、计算字段和触发器来集中和共享服务器（而不是客户端）上的应用程序逻辑、业务规则和策略、复杂查询、数据验证和引用完整性代码。问问自己，此查询或任务能否在服务器上更好、更快地执行？最后，测试每个查询以确保最佳性能。

在窗体和报表中使用视图 在 Access 中执行以下操作：

对于窗体，对只读窗体使用 SQL 视图，对读/写窗体使用 SQL 索引视图作为记录源。
对于报表，请使用 SQL 视图作为记录源。但是，请为每个报表创建单独的视图，以便可以更轻松地更新特定报表，而不会影响其他报表。

尽量减少在窗体或报表中加载数据 在用户请求数据之前，不要显示数据。例如，将 recordsource 属性留空，让用户在窗体上选择筛选器，然后使用筛选器填充 recordsource 属性。或者，使用 DoCmd.OpenForm 和 DoCmd.OpenReport 的 where 子句显示用户所需的确切记录 () 。请考虑关闭记录导航。

使用异类查询时要小心 避免运行包含本地 Access 表和 SQL Server 链接表（有时称为混合查询）的查询。这种类型的查询仍需要 Access 将所有 SQL Server 数据下载到本地计算机，然后运行查询，它不会在 SQL Server 中运行查询。

何时使用本地表 考虑对很少更改的数据使用本地表，例如国家或地区的州或省列表。静态表通常用于筛选，可以在 Access 前端上更好地执行。

有关详细信息，请参阅数据库引擎优化顾问、使用性能分析器优化 Access 数据库和优化链接到 SQL Server 的 Microsoft Office Access 应用程序。