好风凭借力 – 使用Babelfish 加速迁移 SQL Server的代码转换实践
1. 前言
尽管毫无疑问,传统商业许可数据库拥有丰富的功能和完善的支持,但其严格的定价模式、繁琐的许可条款以及较高的总体拥有成本(TCO)使得企业希望采用成本更低的开源解决方案。某些方面,开源数据库以更低的成本提供了相同甚至更好的功能。从商业数据库迁移到开源数据库可为企业在许可和支持方面节省大量成本。
PostgreSQL是企业级的,功能丰富的开源数据库系统,它高度可靠且性能卓越,非常适合实时和任务关键型应用程序。Amazon Aurora 是一种关系数据库服务,既有高端商用数据库的高速度和可用性,也有开源数据库的简单性和成本效益。Aurora 与 MySQL 和 PostgreSQL 完全兼容,使现有应用程序和工具无需修改即可运行。与典型的PostgreSQL 数据库相比,它将性能提高了三倍,并且增加了可扩展性、持久性和安全性。
从传统的 SQL Server 数据库迁移可能非常耗时且需耗费大量资源,任何迁移都涉及三个主要步骤:移动架构、迁移数据和修改客户端应用程序。正如下图中我们所见:迁移数据库时,您可以使用 AWS Schema Conversion Tool(SCT)配合AWS Database Migration Service (DMS) 自动迁移数据库架构和数据,但迁移应用程序本身时,通常需要完成更多的工作,包括重写与数据库交互的应用程序代码,将 T-SQL 代码迁移到 PL/pgSQL 中,这是复杂、耗时且有风险的。
Babelfish for Aurora PostgreSQL 是 Amazon Aurora PostgreSQL 兼容版本的一项新功能,可以理解 Microsoft SQL Server 专有的 SQL 语言 T-SQL,并支持相同的通信协议,因此,修改 SQL Server 上运行的应用程序并将其移动到 Aurora 所需的工作量将减少,从而可实现更快、风险更低且更具成本效益的迁移。
Babelfish 通过支持 Aurora PostgreSQL 的 Microsoft SQL Server 数据类型、语法和函数来支持 T-SQL 和 SQL Server 行为。但请注意,Babelfish 并不提供对 T-SQL 的100%完整支持,仍然有一些差异和限制,某些情况下需要做手工的代码转换。
本文将列举并演示一些高频及常见的典型代码转换案例,帮助您更高效快速地完成迁移工作。
2. 环境准备
在开始我们的演示之前,假设在您的工作环境,已有一个准备迁移的SQL Server源库,那么除此之外,您还需要设置好以下相关的组件:
- Babelfish Compass
这是一个开源的SQL Server 迁移到 Babelfish 的语法评估工具,可以在 GitHub 上下载。它能在Windows和Linux平台下运行,需要Java环境支持,当前的版本是 v2022-04
- Babelfish for Aurora PostgreSQL
Babelfish 从2021年秋发布第一个版本1.0.0开始,目前版本已经更新到1.2.1,对应的Aurora PostgreSQL 版本是13.6。您可以根据官方文档说明来操作,只需简单几步即可创建一个Babelfish for Aurora PostgreSQL 集群环境。配置过程中需要注意的就是数据库迁移模式的选择,还有如果有中文数据的话那么在排序规则中请选择“chinese_prc_ci_as”
到目前为止,一个包含SQL Server源和Aurora PostgreSQL目标以及迁移评估工具的环境已经准备好。接下来,请参考这个博客的内容,您只需要花短短的几分钟就能生成一个Babelfish迁移评估报告。
3. 代码转换
3.1 转换评估
Babelfish Compass 工具生成的评估报告是评估迁移工作内容和工作量的指引,您可以根据其中列出的需要修改的项目,逐一编写SQL代码转换内容。
评估报告的Summary 章节列出了迁移SQL Server源到Babelfish目标的 T-SQL 的语法特性兼容统计,包括支持、不支持、语义审查、手动审查及可忽略项。其中最关键的是不支持特性的内容,这些含有不支持特性的SQL语句,如果不作修改,在Babelfish for Aurora PostgreSQL环境中大部分执行会报错:“‘???’ is not currently supported in Babelfish”,而其他的一些SQL语句虽然没有报错,但不会真正生效。
在评估报告中我们可以查看这些不支持特性的SQL分类统计,下图中的评估报告列出了每一类不支持特性的SQL语句,它显示了我们的案例所用的DDL脚本在Babelfish中不支持的特性主要有对表增加约束语句,Merge语句、修改数据库、修改角色、执行某些系统存储过程等。
3.2 转换原则
Babelfish 为 Aurora PostgreSQL 数据库集群提供了一个额外的端点,使其能够了解 SQL Server 线路级协议和常用的 SQL Server 语句。迁移之后,您仍然可以使用相同的T-SQL开发工具和驱动,连接到TDS端口完成相关的开发。您也可以使用原生 PostgreSQL 连接在PostgreSQL这一端做开发,再从T-SQL这端进行调用。这一种兼容模式,能帮助我们解决大部分的Babelfish对T-SQL的兼容性问题。
- 选择转换模式:如上所述,对于部分不支持的SQL语句,我们可以选择在T-SQL中进行改写,也可以在PostgreSQL中修改再从T-SQL中调用。转换的原则是根据应用的连接开发模式而定,例如 .net应用连接到TDS端开发,那么首选转换模式就是在T-SQL中进行转换。如果在T-SQL这端无法改写或存在修改后的性能问题,那么可以尝试在PostgreSQL中进行修改。
- 代码可读性:对于要修改的SQL语句,可能有好几种的改写方法。简单、高效、可读性好永远都是首选。例如,大部分情况下,使用Case语句比使用..Then更容易理解。
3.3 简单代码转换
归于这一类的代码转换,其特点就是修改简单,但其数量常常在评估报告中列出的所有不支持特性的SQL语句中占绝大部分。此类代码转换工作一般而言只需屏蔽相关选项、注释整条语句或简单修改即可。如此修改的原因,是缘于PostgreSQL和SQL Server的两者间的特性差异或Babelfish的限制。SQL Server中的某些选项或操作,在Babelfish不支持且不会对功能执行有影响,可以直接忽略。虽然这类SQL语句改写简单,但能达到了相同的效果。
演示之前,让我们看看接下来都会使用到两张表的结构:
- ALTER TABLE..CHECK CONSTRAINT
原语句
修改后语句
说明:
- 在Babelfish 中不支持CHECK CONSTRAINT语句启用表的约束,ALTER表添加约束后会自动启用约束
- 在Babelfish 中添加约束时不支持WITH CHECK/NOHECK 选项对已有数据进行约束检查
- 这种不支持的ALTER TABLE特性的语句是迁移过程中最常见的,一般是修改后在Babelfish 上新建表和约束,再导入表的数据,表的约束会自动检查导入的数据,保证数据约束有效
- ALTER ROLE..
原语句
修改后语句
说明:
- 目前 Babelfish 只支持用户数据库中的 dbo 用户,您不能创建具有较低权限的用户,例如对某些表的只读权限
- 大部分此类语句都是用户以操作系统权限登陆SQL Server源后倒出的DDL语句,可以直接注释屏蔽语句
- ALTER DATABASE..
原语句
修改后语句
说明:
- Babelfish 不支持ALTER DATABASE 语法,Aurora PostgreSQL是一个全托管型数据库,会限制一些数据库修改语句,这些语句可以直接注释屏蔽
- ALTER AUTHORIZATION ON object
原语句
修改后语句
说明:
- Babelfish 不支持AUTHORIZATION的创建、修改和删除,可以直接注释屏蔽
- EXEC sys.sp_addextendedproperty
原语句
修改后语句(在PostgreSQL端修改)
说明:
- Babelfish 不支持使用系统存储过程sp_addextendedproperty为字段增加说明,可以直接注释屏蔽此SQL语句,并连接到PostgreSQL端使用comment增加字段说明
- OBJECTPROPERTY
原语句
修改后语句
说明:
- Babelfish 不支持内置的元数据函数 OBJECTPROPERTYEX,可根据SQL语义进行适当改写
- SET ROWCOUNT
原语句
修改后语句
第二种修改
说明:
- Babelfish 不支持SET ROWCOUNT语句来返回指定的行数,可根据SQL语义进行适当改写。从示例中我们看到可以有多种的改写方法,在业务复杂的场景下应从代码的可读性和性能影响方面做选择
- CURRENT OF
原语句
修改后语句
备注:
- Where Current Of语句允许您更新或者是删除最后由cursor取的记录,Babelfish 不支持Current Of语句,可根据SQL语句上下文语义选取变量
- IDENTITY
原语句
修改后语句
说明:
- Babelfish 不支持IDENTITY函数,用于在带有 INTO 子句的 SELECT 语句中将标识列插入到新表中,可使用row_number() over ()方式改写
3.4 复杂代码转换
相对于前面介绍简单代码转换,接下来的这些SQL语句会复杂一些,修改内容也比较多。同时,您还需要仔细地审查SQL语句中上下文之间的关系,以确保修改后的语句和原语句执行得到相同的效果。
- MERGE
在这个案例演示之前,创建两张MERGE使用的源表和目标表
原语句
修改后语句
第二种修改(在PostgreSQL端修改)
第三种修改(在PostgreSQL端修改)
说明:
- MERGE是常用的一种数据合并更新语句,Babelfish 不支持MERGE语句,一般来说可根据SQL语义在T-SQL中拆分成多个DML语句,也可以在PostgreSQL端进行等价的改写。
- PostgreSQL目前还不支持MERGE语句,可以使用UPSET或CONFLICT语句实现,INSERT ON CONFLICT的执行开销要小于UPDATE语句
- FULLTEXT 全文搜索
Babelfish 不支持SQL Server的全文搜索,不支持以下的语句及系统存储过程
Amazon Aurora PostgreSQL 兼容版本增加了对 pg_bigm 扩展程序的支持。pg_bigm 扩展程序在 PostgreSQL 中提供有全文搜索功能。此扩展程序允许用户创建 2-gram(双组),以提高全文搜索速度。以下案例演示如何在PostgreSQL端通过扩展启用全文搜索功能
全文搜索设置成功后可以在TDS端口通过T-SQL调用
- SWITCHOFFSET
原语句
修改后语句(在PostgreSQL端创建自定义函数)
说明:
- Babelfish 不支持SWITCHOFFSET和TODATETIMEOFFSET之类的时区偏移量内置函数,可以在PostgreSQL端创建自定义函数并在TDS端口通过T-SQL调用来实现相同功能
- XML 方法
在本案例演示之前,创建一张和xml解析相关的表
原语句
修改后语句(首先在PostgreSQL端创建自定义函数解析XML)
连接TDS端口在T-SQL中修改SQL语句并调用PostgreSQL端创建的自定义函数
在T-SQL中调用存储过程测试,查询结果显示xml解析正常,数据显示正确
说明:
- Babelfish 不支持解析XML数据的方法,包括 VALUES、XML.NODES 和其他方法,可以在PostgreSQL端创建自定义函数并在TDS端口通过T-SQL调用来完成XML数据的解析工作
4. 总结
通过前面的案例介绍,我们为您展示了使用 Babelfish 迁移 SQL Server时一些最常见的不支持特性SQL的转换方法。当前,Babelfish for PostgreSQL 项目持续向前发展,版本在不断更新。每个新的版本都会添加一些重要的功能,包括增加语法的兼容和SQL Server原生功能的支持。建议您在规划和实施 SQL Server 迁移时经常检查 Babelfish 的特性支持说明,使用最新的特性支持来完成代码的转换。同时,在2021年10月28日,亚马逊云科技正式宣布推出 Babelfish for PostgreSQL 开源项目。此举使用户能够在自己的 PostgreSQL 服务器上利用 Babelfish。
更多更详细的 SQL Server 迁移到 Amazon Aurora PostgreSQL 代码转换请参考官方迁移手册,但请注意,这些转换都是在PostgreSQL端改写,需要考虑如何在T-SQL侧调用。
本篇作者
相关文章
- Jgit的使用笔记
- 利用Github Action实现Tornadofx/JavaFx打包
- 叹息!GitHub Trending 即将成为历史!
- 微软软了?开源社区讨论炸锅,GitHub CEO 亲自来答
- GitHub Trending 列表频现重复项,前后端都没去重?
- Photoshop Elements 2021版本软件安装教程(mac+windows全版本都有)
- (ps全版本)Photoshop 2020的安装与破解教程(mac+windows全版本都有)
- (ps全版本)Photoshop cc2018的安装与破解教程(mac+windows全版本,包括2023
- 环境搭建:Oracle GoldenGate 大数据迁移到 Redshift/Flat file/Flume/Kafka测试流程
- 每个开发人员都要掌握的:最小 Linux 基础课
- 来撸羊毛了!Windows 环境下 Hexo 博客搭建,并部署到 GitHub Pages
- 超实用!手把手入门 MongoDB:这些坑点请一定远离
- 【GitHub日报】22-10-09 zustand、neovim、webtorrent、express 等4款App今日上新
- 【GitHub日报】22-10-10 brew、minio、vite、seaweedfs、dbeaver 等8款App今日上新
- 【GitHub日报】22-10-11 cobra、grafana、vue、ToolJet、redwood 等13款App今日上新
- Photoshop 2018 下载及安装教程(mac+windows全版本都有,包括最新的2023)
- Photoshop 2017 下载及安装教程(mac+windows全版本都有,包括最新的2023)
- Photoshop 2020 下载及安装教程(mac+windows全版本都有,包括最新的2023)
- Photoshop 2023 资源免费下载(mac+windows全版本都有,包括最新的2023)
- 最新版本Photoshop CC2018软件安装教程(mac+windows全版本都有,包括2023