SQLserver迁移TiDB场景的实践

转载

TiDB社区干货传送门 2022-08-12 16:34:59

作者：gloria_cy

前言

作为一个房地产公司，现在整体业务都没有前几年好，公司整体都需要控制成本，技术上也开始开源节流。此项目之前整包买的服务，供应商使用了SQLserver数据库，现在项目进行自研，数据库从商业转换为开源免费产品。

为什么选TiDB

选型总是个复杂繁琐过程，考虑研发成本和运维成本，重要的是后期性能和稳定性。开始我们测试了两个方案，有TiDB+tiflash、MySQL+Elasticsearch，SQLserver承载着oltp和olap的业务，测试的两个方案中后者效率上优于TiDB，但TiDB+tiflash也完全满足现在业务，部分oltp场景对延时要求高，不能超过5分钟，公司使用TiDB已一年时间使用和维护上积攒了很多经验，选择使用TiDB方案。迁移TiDB对部分场景进行了SQL调整。
如下三个复杂场景测试对比表格:
硬件配置：

节点名称	数量	配置	用途
Tidb+pd	3	16c64g	计算管理节点
tikv	3	32c128g	kv存储节点
tiflash	1	64c128g	列存储节点
SQLserver	3	64c198g	存储服务器

压测对比：

SQLserver迁移TiDB场景的实践_sql

上图看出优化后TiDB响应时间比在SQLserver里好很多，测试的三个场景复杂且重要，但并发不高，测试时也没做高并发测试。

迁移过程

4.1、架构

SQLserver迁移TiDB场景的实践_服务器_02

上图架构链路比较长，因为SQLserver发布到订阅比较麻烦，主要描述这个过程，下面主要介绍下SQL数据分发订阅过程。

架构配置说明：
a. SQLserver共三台主机，使用的AlwaysOn高可用架构。
b. 发布服务器是数据的来源服务器，维护源数据，决定哪些数据将被分发，检测哪些数据发生了修改，并将这些信息提交给分发服务器。发布组件由两台主机使用AlwaysOn架构搭建而成。
c. 分发服务器负责把从发布服务器拿来的数据传送至订阅服务器。分发组件由两台组件使用AlwaysOn架构搭建而成。
d. 订阅服务器就是发布服务器数据的副本，接收维护数据。
e. 使用SQLserver自带CDC订阅数据写入kafka。
f. 程序消费kafka信息写入TiDB集群。

因让TiDB承接部分线上读业务，让读业务在TiDB上测试没问题在迁移写到TiDB。虽然链路复杂些，比双写减少了多一次事物的保证。固使用了这套比较长的链路。
下面主要介绍SQLserver的发布和分发，如何配置这两块的步骤。之前只知道有这个功能，从未使用过，刚好接这个项目熟悉了整个过程，下面是配置过程和命令，这两个比较复杂列举出来，发布和订阅使用AlwaysOn做的高可用，其他步骤比较简单这不做列举。

服务器配置信息：

角色	主机	IP
发布	BJCRDB01	10.12.10.1
BJCRDB02	10.12.10.2
listener(监听)	10.12.10.3
分发	BJCRDB03	10.12.10.60
BJCRDB04	10.12.10.61
listener(监听)	10.12.10.63
订阅	BJCDCDB01	10.12.12.101
BJCDCDB02	10.12.12.102
BJCDCDB03	10.12.12.103

a . 分发主节点创建远程分发库 ( BJCRDB03)

use master

exec sp_adddistributor @distributor = N'BJCRDB03', @password = N'654321'

GO

exec sp_adddistributiondb @database = N'distribution', @data_folder = N'E:\"SQLDATA',  @log_folder = N'E:\"SQLDATA', @log_file_size = 2, @min_distretention = 0,  @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000,  @deletebatchsize_cmd = 2000, @security_mode = 1

GO

use [distribution]

if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))

       create table UIProperties(id int)

if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo',  'table', 'UIProperties', null, null)))

       EXEC sp_updateextendedproperty N'SnapshotFolder',  N'\"\"\"cr_new', 'user', dbo, 'table', 'UIProperties'

else

       EXEC sp_addextendedproperty N'SnapshotFolder',  N'\"\"\"crm_new', 'user', dbo, 'table', 'UIProperties'

GO

exec sp_adddistpublisher @publisher = N'BJCRDB03', @distribution_db =  N'distribution', @security_mode = 1, @working_directory =  N'\"\"\"cr_new', @trusted = N'false', @thirdparty_flag = 0,  @publisher_type = N'MSSQLSERVER'

GO

b . 分发副节点创建分发库 ( BJCRDB04)

use master

exec sp_adddistributor @distributor = N' BJCRDB04', @password = N'654321'

GO

ALTER AVAILABILITY GROUP [crdistag]

GRANT CREATE ANY DATABASE;

GO

c . 创建分发库高可用AG(BJCRDB03)

USE MASTER

CREATE AVAILABILITY GROUP [crdistag]

FOR DATABASE [distribution]

REPLICA ON

'BJCRDB03'

WITH (ENDPOINT_URL = N'TCP://BJCRDB03.work.net:5022',

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),

SEEDING_MODE = AUTOMATIC),

'BJCRDB04'

WITH (ENDPOINT_URL = N'TCP://BJCRDB04.work.net:5022',

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),

SEEDING_MODE = AUTOMATIC);

GO

USE [master]

GO

ALTER AVAILABILITY GROUP [crdistag]

ADD LISTENER N'crdistlsn' (

WITH IP

((N'10.12.10.63', N'255.255.255.0')

)

, PORT=1433);

GO

d .分发副节点添加分发库 ( BJCRDB04)

USE MASTER

EXEC SP_ADDDISTRIBUTIONDB @DATABASE = N'distribution',@SECURITY_MONE = 1

e . 添加远程发布服务器 ( BJCRDB03& BJCRDB04)


EXEC sp_adddistpublisher @publisher ='BJCRDB01',@distribution_db = 'distribution',

@working_directory = '\"\"\"cr_new'

go

EXEC sp_adddistpublisher @publisher ='BJCRDB02',@distribution_db = 'distribution',

@working_directory = '\"\"\"cr_new'

Go

f. 发布主节点，添加分发AG监听作为分发服务器 ( BJCRDB01)


  EXEC sp_addDistributor @distributor = 'crdistlsn',

       @password ='654321'

GO

USE master

GO

EXEC sys.sp_replicationdboption @dbname = 'erpdb', @optname = 'publish', @value = 'true';

EXEC sys.sp_replicationdboption @dbname = 'erpdb', @optname = 'merge publish', @value = 'true';

g.发布副节点，添加分发AG监听作为分发服务器 ( BJCRDB02)

EXEC sp_addDistributor @distributor = 'crdistlsn',

       @password ='654321'

GO

h. 将发布服务器由发布主节点重定向到发布AG监听(BJCRDB03)

USE distribution;

GO

EXEC sys.sp_redirect_publisher

@original_publisher = 'BJCRDB01',

@publisher_db = 'erpdb',

@redirected_publisher = 'crdistlsn';

USE distribution;

GO

EXEC sys.sp_redirect_publisher

@original_publisher = 'BJCRDB01',

@publisher_db = 'MysoftZZSGL',

@redirected_publisher = 'crdistlsn';

发布和分发到这配置完成，订阅数据到kafka。SQLserver这块配置完成，开发写程序消费kafka数据写入TiDB，整个数据同步完成。

总结

同步原理更大部分cdc工具大同小异，只是SQLserver的配置变得麻烦。公司还有很多业务使用的SQLserver，有了这次方法的尝试，后续olap+oltp混合场景很多都可以考虑用TiDB替换，减少商业数据库使用为公司节省部分成本。在TiDB使用过程中我们在持续积累经验，不断增加使用量。愿TiDB越做越强让我们更多的商业数据库场景可以迁移TiDB。