将 Access 数据库迁移到 SQL Server

将 Access 数据库迁移到 SQL Server

我们都有限制, Access 数据库也不例外。 例如, Access 数据库的大小限制为 2 GB, 并且不能支持超过255的并发用户。 因此, 当你的 Access 数据库转到下一级别时, 你可以迁移到 SQL Server。 SQL Server (无论是在本地还是在 Azure 云中) 支持大量的数据、更多并发用户以及比 JET/ACE 数据库引擎更大的容量。 本指南让你能够顺利开始使用 SQL Server 旅程, 有助于保留你创建的 Access 前端解决方案, 并希望 motivates 将来的数据库解决方案使用 Access。 "升迁向导" 已从 Access 2013 中的 Access 中删除, 因此现在可以使用 Microsoft SQL Server 迁移助手 (SSMA)。 若要成功迁移, 请按照下列步骤操作。

数据库迁移到 SQL Server 的阶段

准备工作

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

关于拆分数据库

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

因此, 您仍然可以将具有链接表的前端 Access 数据库保留到 SQL Server 表中。 实际上, 你可以利用 SQL Server 的可伸缩性, 获得 Access 数据库提供的快速应用程序开发的好处。

SQL Server 的好处

是否仍需要一些可信才能迁移到 SQL Server? 下面是要考虑的一些其他好处:

  • 更多并发用户    在添加更多用户时, SQL Server 可以处理比访问和最小化内存需求更多的并发用户。

  • 提高了可用性    使用 SQL Server, 你可以在数据库正在使用时动态备份 (增量备份或完成数据库备份)。 因此,不必强制使用户退出数据库即可备份数据。

  • 高性能和可伸缩性    SQL Server 数据库通常比 Access 数据库性能更好, 尤其是在大小较大的数据库中。 此外, SQL Server 通过并行处理查询, 使用单个进程中的多个本机线程处理用户请求, 更快、更高效地处理查询。

  • 提高了安全性    通过使用受信任的连接, SQL Server 集成了 Windows 系统安全性以提供对网络和数据库的单个集成访问, 从而充分利用这两个安全系统。 这使得管理复杂的安全方案变得更加容易。 SQL Server 是适用于敏感信息 (如社会保险号、信用卡数据和机密地址) 的理想存储。

  • 即时恢复    如果操作系统崩溃或电源中断, SQL Server 可以在几分钟内自动将数据库恢复到一致状态, 并且无需数据库管理员干预。

  • 使用 VPN    Access 和虚拟专用网络 (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 Server 选项

第一步

在运行 SSMA 之前, 你可以提前解决一些问题, 从而有助于简化迁移过程:

  • 添加表索引和主键    请确保每个 Access 表都有索引和主键。 SQL Server 要求所有表至少有一个索引, 并且如果可以更新表, 则需要将一个链接表用作主键。

  • 检查主键/外键关系    请确保这些关系基于具有一致数据类型和大小的字段。 SQL Server 不支持具有不同数据类型的联接列和外键约束中的大小。

  • 删除附件列    SSMA 不迁移包含附件列的表。

在运行 SSMA 之前, 请先执行以下第一个步骤。

  1. 关闭 Access 数据库。

  2. 请确保连接到数据库的当前用户也关闭数据库。

  3. 如果数据库采用.mdb 文件格式, 则删除用户级安全

  4. 备份数据库。 有关详细信息, 请参阅通过备份和还原过程保护数据

提示    请考虑在桌面上安装MICROSOFT SQL Server Express edition , 它支持最高 10 GB 的功能, 并且是一种免费且更简单的方法来运行和检查迁移。 连接时, 请使用LocalDB 作为数据库实例

提示    如果可能, 请使用 Access 的单独版本。 如果你只能使用 Office 365, 则使用 Access 2010 数据库引擎在使用 SSMA 时迁移 Access 数据库。 有关详细信息, 请参阅Microsoft Access 数据库引擎2010可再发行组件

运行 SSMA

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

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

  1. 若要使用 SSMA 迁移数据库, 请先通过双击下载的 MSI 文件下载并安装软件。 请确保为你的计算机安装合适的32或64位版本。

  2. 安装 SSMA 后, 请在桌面上打开它, 最好从具有 Access 数据库文件的计算机上打开它。

    您也可以在具有从共享文件夹中的网络访问 Access 数据库的计算机上打开它。

  3. 按照 SSMA 中的 "开始" 说明提供基本信息, 如 SQL Server 位置、Access 数据库和要迁移的对象、连接信息以及是否要创建链接表。

  4. 如果你要迁移到 SQL Server 2016 或更高版本, 并且想要更新链接表, 请通过选择 >常规的 >项目设置中的 "审阅工具" 来添加 rowversion 列。

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

    注意    避免混淆 rowversion 和时间戳。 尽管关键字时间戳是 SQL Server 中 rowversion 的同义词, 但不能使用 rowversion 作为数据输入的时间戳方式。

  5. 若要设置精确的数据类型, 请在 ">项目设置" >类型映射中选择 "审阅工具"。 例如, 如果仅存储英语文本, 则可以使用varchar而不是nvarchar数据类型。

转换对象

SSMA 将 Access 对象转换为 SQL Server 对象, 但不立即复制对象。 SSMA 提供要迁移的以下对象的列表, 以便你可以决定是否要将它们移动到 SQL Server 数据库:

  • 表和列

  • 选择不带参数的查询。

  • 主键和外键

  • 索引和默认值

  • 检查约束 (允许零长度的 column 属性、列有效性规则、表验证)

最佳做法是使用 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 驱动程序 18 FOR Sql serverMicrosoft ODBC 驱动程序 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 类。 有关详细信息, 请参阅使用 Sql SERVER TSQL 比较 ACCESS SQL

数据类型

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

大型号码    "大数字" 数据类型存储非货币的数值, 并且与 SQL bigint 数据类型兼容。 你可以使用此数据类型来有效地计算大量数字, 但需要使用 Access 16 (16.0.7812 或更高版本) .accdb 数据库文件格式, 并使用64位版本的 Access 执行更好的操作。 有关详细信息, 请参阅使用大型数字数据类型, 然后在64位或32位版本的 Office 之间进行选择

是/否​​    默认情况下, Access "是/否" 列将转换为 SQL Server 位域。 若要避免记录锁定, 确保 "位域" 设置为 "不允许空值"。 在 SSMA 中, 你可以选择 "位" 列, 将 "允许 null " 属性设置为 "否"。 在 TSQL 中, 使用CREATE tableALTER table语句。

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

  • 如果数据库的兼容性级别是 130 (SQL Server 2016) 或更高版本, 并且链接表包含一个或多个 datetime 或 datetime2 列, 则该表可能会返回 #deleted 在结果中的消息。 有关详细信息, 请参阅访问链接表到 SQL-服务器数据库返回 #deleted

  • 使用日期范围比 datetime 更大的datetime2数据类型。

  • 在 SQL Server 中查询日期时, 请考虑时间和日期。 例如:

    • 1/1/19 和1/31/19 之间的 DateOrdered 可能不包含所有订单。

    • 1/1/19 00:00:00 AM 和 1/31/19 11:59:59 PM 之间的 DateOrdered 不包括所有订单。

附件   "附件" 数据类型将文件存储在 Access 数据库中。 在 SQL Server 中, 你有多个选项可供考虑。 你可以从 Access 数据库中提取文件, 然后考虑将指向这些文件的链接存储在 SQL Server 数据库中。 或者, 你可以使用 FILESTREAM、FileTables 或远程 BLOB 存储 (RBS) 保存在 SQL Server 数据库中存储的附件。

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

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

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

注意    在 Access 2010 中, 多值字段不会转换且已停用。

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

Visual Basic

尽管 SQL Server 不支持 VBA, 但请注意以下可能存在的问题:

查询中的 VBA 函数    Access 查询支持查询列中数据的 VBA 函数。 但是, 使用 VBA 函数的 Access 查询不能在 SQL Server 上运行, 因此所有请求的数据都将传递到 Microsoft Access 以供处理。 在大多数情况下, 应将这些查询转换为传递查询

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

优化性能

到目前为止, 使用新的后端 SQL Server 优化性能的最重要方法是确定何时使用本地或远程查询。 将数据迁移到 SQL Server 后, 您也可以从文件服务器移动到计算的客户端服务器数据库模型。 请遵循以下一般指南:

  • 在客户端上运行小的只读查询以实现最快的访问。

  • 在服务器上运行长、读/写查询以充分利用较高的处理能力。

  • 将筛选器和聚合的网络流量降至最低以仅传输所需的数据。

优化客户端服务器数据库模型中的性能

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

以下是其他建议的指南。

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

在窗体和报表中使用视图    在 Access 中, 请执行下列操作:

  • 对于窗体, 将只读窗体和 SQL 索引视图的 SQL 视图用作记录源的读/写窗体的 sql 视图。

  • 对于报表, 请使用 SQL 视图作为记录源。 但是, 为每个报表创建单独的视图, 以便你可以更轻松地更新特定报表, 而不会影响其他报表。

最小化窗体或报表中的加载数据    在用户要求输入数据之前不要显示数据。 例如, 将 "记录源" 属性保留为空, 让用户在窗体上选择一个筛选器, 然后使用筛选器填充 "记录源" 属性。 或者, 使用 DoCmd 和 DoCmd 的 where 子句来显示用户所需的确切记录。 请考虑关闭记录导航。

小心处理异类查询   避免运行合并本地 Access 表和 SQL Server 链接表的查询, 有时称为混合查询。 此类型的查询仍要求 Access 将所有 SQL Server 数据下载到本地计算机, 然后运行该查询, 它不会在 SQL Server 中运行查询。

何时使用本地表    对于很少更改的数据 (如国家或地区中的州或省的列表), 请考虑使用本地表。 静态表通常用于筛选, 并且可以在 Access 前端更好地运行。

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

另请参阅

Azure 数据库迁移指南

Microsoft 数据迁移博客

Microsoft 对 SQL Server 迁移、转换和升迁的访问

共享 Access 桌面数据库的方法

注意:  本页面是自动翻译的,可能包含语法错误或不准确之处。 我们的目的是使此内容能对你有所帮助。 能否告知我们此信息是否有所帮助? 下面是该参考内容的英文版

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×