UDB-TDS 迁移 SQL Server 实践

前言

​ 从传统的 SQL Server 数据库迁移可能非常耗时且需耗费大量资源，任何迁移都涉及三个主要步骤：移动架构、迁移数据和修改客户端应用程序。正如下图中我们所见：迁移数据库时，通常需要完成更多的工作，包括重写与数据库交互的应用程序代码，将 T-SQL 代码迁移到 PL/pgSQL 中，这是复杂、耗时且有风险的。

​ UDB-TDS 是 UDBTX 兼容版本的一项新功能，可以理解 Microsoft SQL Server 专有的 SQL 语言 T-SQL，并支持相同的通信协议，因此，修改 SQL Server 上运行的应用程序并将其移动到 UDBTX 所需的工作量将减少，从而可实现更快、风险更低且更具成本效益的迁移。

​ UDB-TDS 通过支持 UDBTX 的 Microsoft SQL Server 数据类型、语法和函数来支持 T-SQL 和 SQL Server 行为。但请注意，UDB-TDS 并不提供对 T-SQL 的100%完整支持，仍然有一些差异和限制，某些情况下需要做手工的代码转换。

​ 本文将列举并演示一些高频及常见的典型代码转换案例，帮助您更高效快速地完成迁移工作。

环境准备

​ 在开始我们的演示之前，假设在您的工作环境，已有一个准备迁移的 SQL Server 源库，那么除此之外，您还需要设置好以下相关的组件：

​ udbtxtds_compass

​ 这是一个SQL Server 迁移到 UDB-TDS 的语法评估工具，它能在Windows和Linux平台下运行，需要 Java 环境支持，当前的版本是 v2024-04

​ UDB-TDS

​ 您可以根据官方文档说明来操作，只需简单几步即可创建一个 UDB-TDS 集群环境。配置过程中需要注意的就是数据库迁移模式的选择，还有如果有中文数据的话那么在排序规则中请选择“chinese_prc_ci_as”

​ 到目前为止，一个包含SQL Server源和UDB-TDS目标以及迁移评估工具的环境已经准备好。接下来，请参考本文的内容，您只需要花短短的几分钟就能生成一个UDB-TDS迁移评估报告。

代码转换

转换估计

​ udbtxtds_compass 工具生成的评估报告是评估迁移工作内容和工作量的指引，您可以根据其中列出的需要修改的项目，逐一编写 SQL 代码转换内容。

​ 评估报告的Summary 章节列出了迁移 SQL Server 源到 UDB-TDS 目标的 T-SQL 的语法特性兼容统计，包括支持、不支持、语义审查、手动审查及可忽略项。其中最关键的是不支持特性的内容，这些含有不支持特性的 SQL 语句，如果不作修改，在 UDB-TDS 环境中大部分执行会报错：“‘???’ is not currently supported in UDB-TDS”，而其他的一些 SQL 语句虽然没有报错，但不会真正生效。

​ 在评估报告中我们可以查看这些不支持特性的 SQL 分类统计，下图中的评估报告列出了每一类不支持特性的 SQL 语句，它显示了我们的案例所用的 DDL 脚本在 Babelfish 中不支持的特性主要有对表增加约束语句，Merge 语句、修改数据库、修改角色、执行某些系统存储过程等。

转换原则

​ UDB-TDS 为 UDBTX 数据库集群提供了一个额外的端点，使其能够了解 SQL Server 线路级协议和常用的 SQL Server 语句。迁移之后，您仍然可以使用相同的 T-SQL 开发工具和驱动，连接到TDS端口完成相关的开发。您也可以使用原生UDBTX 连接在 UDBTX 这一端做开发，再从 T-SQL 这端进行调用。这一种兼容模式，能帮助我们解决大部分的UDB-TDS 对 T-SQL 的兼容性问题。

​ 选择转换模式：如上所述，对于部分不支持的 SQL 语句，我们可以选择在 T-SQL 中进行改写，也可以在 UDBTX 中修改再从 T-SQL 中调用。转换的原则是根据应用的连接开发模式而定，例如 .net 应用连接到 TDS 端开发，那么首选转换模式就是在T-SQL中进行转换。如果在T-SQL这端无法改写或存在修改后的性能问题，那么可以尝试在 UDBTX 中进行修改。

​ 代码可读性：对于要修改的SQL语句，可能有好几种的改写方法。简单、高效、可读性好永远都是首选。例如，大部分情况下，使用Case语句比使用..Then更容易理解。

简单代码转换

​ 归于这一类的代码转换，其特点就是修改简单，但其数量常常在评估报告中列出的所有不支持特性的 SQL 语句中占绝大部分。此类代码转换工作一般而言只需屏蔽相关选项、注释整条语句或简单修改即可。如此修改的原因，是缘于 UDBTX 和 SQL Server 的两者间的特性差异或 UDB-TDS 的限制。SQL Server 中的某些选项或操作，在 UDB-TDS 不支持且不会对功能执行有影响，可以直接忽略。虽然这类 SQL 语句改写简单，但能达到了相同的效果。

​ 演示之前，让我们看看接下来都会使用到两张表的结构：

create table dept(
	deptno int NOT NULL PRIMARY KEY,
	dname varchar(14),
	loc varchar(13)
)

create table employees (
	empno int NOT NULL PRIMARY KEY,
	ename varchar(10),
	job varchar(9),
	mgr int,
	hiredate datetime,
	sal money,
	comm money,
	deptno int
)

ALTER TABLE..CHECK CONSTRAINT

原语句

ALTER TABLE [dbo].[employees] WITH CHECK ADD CONSTRAINT [FK_DEPT] FOREIGN KEY([deptno])
REFERENCES [dbo].[dept] ([deptno])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_DEPT]
GO

修改后语句

ALTER TABLE [dbo].[employees] ADD CONSTRAINT [FK_DEPT] FOREIGN KEY([deptno])
REFERENCES [dbo].[dept] ([deptno])
GO

说明：

在 UDB-TDS 中不支持 CHECK CONSTRAINT 语句启用表的约束，ALTER 表添加约束后会自动启用约束

在 UDB-TDS 中添加约束时不支持 WITH CHECK/NOHECK 选项对已有数据进行约束检查

这种不支持的 ALTER TABLE 特性的语句是迁移过程中最常见的，一般是修改后在 UDB-TDS 上新建表和约束，再导入表的数据，表的约束会自动检查导入的数据，保证数据约束有效

ALTER ROLE..

原语句

ALTER ROLE [???] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO

修改后语句（注释掉）

/* ALTER ROLE [???] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO */

说明：

目前 UDB-TDS 只支持用户数据库中的 dbo 用户，您不能创建具有较低权限的用户，例如对某些表的只读权限

大部分此类语句都是用户以操作系统权限登陆 SQL Server源后倒出的 DDL 语句，可以直接注释屏蔽语句

ALTER DATABASE..

原语句

ALTER DATABASE [???] SET RECOVERY FULL
GO

修改后语句

/* ALTER DATABASE [???] SET RECOVERY FULL
GO */

说明：

UDB-TDS 不支持ALTER DATABASE 语法，Aurora PostgreSQL是一个全托管型数据库，会限制一些数据库修改语句，这些语句可以直接注释屏蔽

ALTER AUTHORIZATION ON object

原语句

ALTER AUTHORIZATION ON [dbo].[employees] TO SCHEMA OWNER
GO

修改后语句

/* ALTER AUTHORIZATION ON [dbo].[employees] TO SCHEMA OWNER
GO */

说明：

UDB-TDS 不支持AUTHORIZATION的创建、修改和删除，可以直接注释屏蔽

EXEC sys.sp_addextendedproperty

原语句

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号' ,
	@level0type=N'SCHEMA',@level0name=N'dbo',
	@level1type=N'TABLE',@level1name=N'dept',
	@level2type=N'COLUMN',@level2name=N'deptno'
GO

修改后语句（在 UDBTX 端修改）

COMMENT ON COLUMN dept.deptno IS '编号';

说明：

UDB-TDS 不支持使用系统存储过程 sp_addextendedproperty 为字段增加说明，可以直接注释屏蔽此 SQL 语句，并连接到 UDBTX 端使用comment增加字段说明

OBJECTPROPERTY

原语句

select name from sysobjects where objectproperty(id, N'IsTable') = 1 and name not like N'#%%' order by name
select * from sysobjects where id = object_id(N'temp_tableSpaceInfo') AND objectproperty(id, N'IsUserTable') = 1

修改后语句

select name from sysobjects where xtype in ('U','IT','S') and name not like N'#%%' order by name
select * from sysobjects where id = object_id(N'temp_tableSpaceInfo') AND xtype='U'

说明：

UDB-TDS 不支持内置的元数据函数 OBJECTPROPERTYEX，可根据SQL语义进行适当改写

SET ROWCOUNT

原语句

CREATE PROCEDURE [dbo].[P_Rowcount]
@id int
as
set nocount on
set rowcount @id
begin
select * from employees order by empno
end
GO

修改后语句

CREATE PROCEDURE [dbo].[P_Rowcount]
@id int
as
set nocount on
begin
select top (@id) * from employees order by empno
end
GO

第二种修改

CREATE PROCEDURE [dbo].[P_Rowcount]
@id int
as
set nocount on
begin
select * from employees order by empno offset 0 rows fetch first @id rows only;
end
GO

说明：

UDB-TDS 不支持 SET ROWCOUNT 语句来返回指定的行数，可根据 SQL 语义进行适当改写。从示例中我们看到可以有多种的改写方法，在业务复杂的场景下应从代码的可读性和性能影响方面做选择

CURRENT OF

原语句

CREATE PROCEDURE [dbo].[P_CurrentOf] AS
BEGIN
	DECLARE @empno int
	DECLARE NoResponce CURSOR FOR
		SELECT empno FROM employees;
	OPEN NoResponce;
	FETCH NEXT FROM NoResponce INTO @empno;
	DELETE FROM employees WHERE CURRENT OF NoResponce;
END
GO

修改后语句

CREATE PROCEDURE [dbo].[P_CurrentOf] AS
BEGIN
	DECLARE @empno int
	DECLARE NoResponce CURSOR FOR
		SELECT empno FROM employees;
	OPEN NoResponce;
	FETCH NEXT FROM NoResponce INTO @empno;
	DELETE FROM employees WHERE empno = @empno;
END
GO

备注:

Where Current Of 语句允许您更新或者是删除最后由 cursor 取的记录，Babelfish 不支持 Current Of 语句，可根据 SQL 语句上下文语义选取变量

IDENTITY

原语句

SELECT IDENTITY(INT,1,1) AS rowid,* INTO #tmp
FROM employees
ORDER BY empno

修改后语句

SELECT row_number() over () as rowid, * INTO #tmp
FROM employees
ORDER BY empno

说明：

UDB-TDS 不支持 IDENTITY 函数，用于在带有 INTO 子句的 SELECT 语句中将标识列插入到新表中，可使用row_number() over ()方式改写

复杂代码转换

​ 相对于前面介绍简单代码转换，接下来的这些 SQL 语句会复杂一些，修改内容也比较多。同时，您还需要仔细地审查SQL 语句中上下文之间的关系，以确保修改后的语句和原语句执行得到相同的效果。

MERGE

在这个案例演示之前，创建两张 MERGE 使用的源表和目标表

create table source
(
	id int not null primary key ,
	country varchar(20) null,
	city varchar(20)
);

insert into source
		(id, country, city)
	VALUES
		(1, 'RUSSIA', 'MOSCOW'),
		(2, 'FRANCE', 'PARIS'),
		(3, 'ENGLAND', 'LONDON'),
		(4, 'USA', 'NEW YORK'),
		(5, 'GERMANY', 'BERLIN'),
		(6, 'BRAZIL', 'BRASILIA');

create table target
(
	id int not null primary key ,
	country varchar(20) null,
	city varchar(20)
);

insert into target
		(id, country, city)
	VALUES
		(1, 'JAPAN', 'TOKYO'),
		(4, 'USA', 'DENVER'),
		(7, 'CHINA', 'BEI JING');

原语句

MERGE INTO target AS C2
USING source AS C1
ON C2.id = C1.id
WHEN MATCHED
	THEN UPDATE
		SET
			C2.country = C1.country,
			C2.city = c1.city
WHEN NOT MATCHED
	THEN INSERT (id, country, city)
		VALUES (C1.id, C1.country, C1.city);

修改后语句

begin
update target set country = C1.country, city = C1.city from (select id, country, city from source) C1 where target.id = C1.id;
insert into target (id, country, city) select * from source as C1 where not exists (select id from target where id = C1.id);
end
go

第二种修改（在UDBTX端修改）

with upsert as
(update target c2 set country=c1.country, city=c1.city
from source c1 where c1.id=c2.id
RETURNING c2.*
)
insert into target select a.id, a.country, a.city
from source a where a.id not in (select b.id from upsert b);

说明：

MERGE 是常用的一种数据合并更新语句，Babelfish 不支持 MERGE 语句，一般来说可根据 SQL 语义在 T-SQL 中拆分成多个 DML 语句，也可以在 PostgreSQL 端进行等价的改写。

UDBTX 目前还不支持 MERGE 语句，可以使用 UPSET 或 CONFLICT 语句实现，INSERT ON CONFLICT 的执行开销要小于UPDATE 语句

FULLTEXT 全文搜索

UDB-TDS 不支持 SQL Server的全文搜索，不支持以下的语句及系统存储过程

CREATE、ALTER、DROP FULLTEXT CATALOG
CREATE、ALTER、DROP FULLTEXT INDEX
CREATE、ALTER、DROP FULLTEXT STOPLIST
exec sp_fulltext_database 'enable';

UDBTX 增加了对 pg_bigm 扩展程序的支持。pg_bigm 扩展程序在 UDB-TDS 中提供有全文搜索功能。此扩展程序允许用户创建 2-gram（双组），以提高全文搜索速度。以下案例演示如何在UDBTX端通过扩展启用全文搜索功能

set search_path=dbo;
create extension pg_bigm;

CREATE TABLE fulltext_doc (doc text);
INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 成本优化');
INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 性能优化');
INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 提升使用体验');
INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 中提供 2-gram 全文搜索功能的工具');
INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 中提供 3-gram 全文搜索功能的工具');

CREATE INDEX fulltext_doc_idx ON fulltext_doc USING gin (doc gin_bigm_ops);
alter table fulltext_doc owner to dbo;

全文搜索设置成功后可以在 TDS 端口通过 T-SQL 调用

SWITCHOFFSET

原语句

SELECT CAST(SWITCHOFFSET(TODATETIMEOFFSET(SYSUTCDATETIME(),’+00:00’),’+08:00’) AS DATETIME)

修改后语句

CREATE OR REPLACE FUNCTION dbo.f_get_cst()
RETURNS sys.datetime AS $$
	BEGIN
		RETURN cast(timezone('Asia/Shanghai',now()) as sys.datetime);
	END;
$$ LANGUAGE plpgsql;

说明：

UDB-TDS 不支持SWITCHOFFSET和TODATETIMEOFFSET之类的时区偏移量内置函数，可以在UDBTX端创建自定义函数并在TDS端口通过T-SQL调用来实现相同功能

XML方法

在本案例演示之前，创建一张和 xml 解析相关的表

create table t_xml_test (
	id int,
	country nvarchar(max),
	industry nvarchar(max)
);

insert t_xml_test values(1, 'China', 'Manufacturing and foreign trade business');
insert t_xml_test values(2, 'USA', 'Financial and Bioindustry');
insert t_xml_test values(3, 'Russia', 'Resource export');

原语句

create procedure p_xml_test
@xml xml
as
begin
	set nocount on
	select * from t_xml_test
	where id in (select imgXML.Item.value('id[1]','int') from @xml.nodes('/root/country') as imgXML(Item));
	set nocount off
end
go

修改后语句（首先在 UDBTX 端创建自定义函数解析 XML）

CREATE OR REPLACE FUNCTION xmlQueryID(in_xml xml)
RETURNS TABLE (id text)
AS $$
DECLARE
BEGIN
	RETURN QUERY
	select * from (
		WITH xmldata(data) AS (VALUES (in_xml::xml))
		SELECT xmltable.*
		FROM XMLTABLE('/root/country' PASSING (SELECT data FROM xmldata) COLUMNS id text)) as foo;
END;
$$ LANGUAGE plpgsql;

连接 TDS 端口在 T-SQL 中修改 SQL 语句并调用 UDBTX 端创建的自定义函数

create procedure p_xml_test
@xml xml
as
begin
	set nocount on
	select * from t_xml_test
	where id in (select * from xmlQueryID(@xml)) ;
	set nocount off
end
go

在 T-SQL 中调用存储过程测试，查询结果显示 xml 解析正常，数据显示正确

说明：

UDB-TDS 不支持解析 XML 数据的方法，包括 VALUES、XML.NODES 和其他方法，可以在 UDBTX 端创建自定义函数并在 TDS 端口通过 T-SQL 调用来完成 XML 数据的解析工作

总结

​ 通过前面的案例介绍，我们为您展示了使用 UDB-TDS 迁移 SQL Server时一些最常见的不支持特性 SQL 的转换方法。当前，UDB-TDS续向前发展，版本在不断更新。每个新的版本都会添加一些重要的功能，包括增加语法的兼容和 SQLServer 原生功能的支持。建议您在规划和实施 SQL Server 迁移时经常检查 UDB-TDS 的特性支持说明，使用最新的特性支持来完成代码的转换。