数据迁移 之Sql Server迁Qgsql踩过的坑（理论+实操）
本周跟着师傅做了一些事情学到了很多东西，分享一下本周踩过的坑和经验。
为了增强系统稳定性，益维护性 ，降低成本，增强数据库的单一职责 ，迁移前建立更好的分区，更好的优化性能， 对公司的sql server 数据库进行迁移 统一数据源 。
数据迁移流程图 (迁移完成后，将会切除sql server)，降低维护成本
1. 规范数据源
要清晰的知道那个连接数据源的数据转移到那个数据库，有多少数据源，要完成的任务有多少。不清晰的迁移的数据库 连接的数据源 编写迁移脚本时，可能会产生混乱。浪费不必要的时间。
2. 创建数据库
建立新的数据库 知道原sqlserver中有多少条数据，选择合适的方式创建分区。增强系统的性能。
3 . 编写迁移业务
明确知道了要迁移的表和要迁移到的表 ，开始编写迁移脚本
3.1 定义抽象公共规范
抽象工具类中定义要查的表，要和插入的表的抽象方法 ，编写抽象方法，普通方法把抽象方法组装成具体业务
public abstract class DataSyncHelper {
protected abstract T fetchById(int Id);
protected abstract int update(T elem);
protected abstract int removeById(T ele);
protected abstract int getCount(SyncDataDto syncData);
protected abstract List getList(SyncDataDto syncData);
protected abstract List convertTo(List dataList);
protected abstract int[] insertBatch(List elems);
public void syncStart(SyncDataDto syncData, int pageSize) {
// 获取需要同步的数据总数
int count = getCount(syncData);
if (count convertTo(List dataList) {
return dataList;
}
//最后直接插入
@Override
protected int[] insertBatch(List elems) {
return payResoureSyncDao.batchAdd((List) elems);
}
}
4. 将老的业务编写成为支持pgsql数据库
数据迁移时新数据同步方案
sql server 迁移的过程中，为了不影响正常的业务 ，进行热迁移。数据写入新的数据库的同时，由于业务会产生新的数据，手写的迁移代码并不能感知到有新的数据加入进来，这个时候就要想办法，把新产生的数据继续写入到新库
第一种解决方案 CDC （直接上实操）
sql server自带的日志，他可以记录所有新增修改删除的日志，通过日志还原成sql语句，还原后再次插入新的数据库中，这个地方踩了很多坑，具体如下实操 ，优点 可以准确的还原sql ,缺点 难以配置。
数据库开启CDC之后
## 查看数据库的 CDC 状态
-
is_cdc_enabled = 1 开启CDC
SELECT [name], is_cdc_enabled FROM sys.databases;
查看表的 CDC 状态
-
is_tracked_by_cdc = 1 开启CDC
SELECT [name], is_tracked_by_cdc FROM sys.tables;
查看 CDC 作业的状态
EXEC msdb.dbo.sp_help_job;
数据库开启 CDC
USE YourDatabase; -- 数据库名称
GO
EXEC sys.sp_cdc_enable_db;
GO
-- MyTable 如下中代表的是表名称
表关闭 CDC
EXECUTE sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@capture_instance = N'dbo_MyTable'
表开启CDC
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = NULL,
@supports_net_changes = 0
查看某个表某个时间段的CDC
DECLARE @from_lsn binary(10), @to_lsn binary(10)
-- 获取开始和结束时间对应的 LSN
SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', '2023-09-21T00:00:00')
SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', '2023-09-22T00:00:00')
-- 查询变化
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_MyTable(@from_lsn, @to_lsn, 'all')
某个表最新的CDC
SELECT MAX(__$start_lsn) AS latest_lsn FROM cdc.dbo_MyTable_CT
最新的CDC返回的时间
DECLARE @latest_lsn binary(10);
SET @latest_lsn = (SELECT MAX(__$start_lsn) FROM cdc.dbo_MyTable_CT);
SELECT sys.fn_cdc_map_lsn_to_time(@latest_lsn) AS latest_change_time;
查看CDC当前设置的时长
- retention 单位是分钟
SELECT job_id, retention FROM msdb.dbo.cdc_jobs WHERE job_type = 'cleanup'
设置CDC保留的时长/ 如下是3天
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 4320; -- 3 days in minutes
-- 以下待验证
创建触发器监听表，向一张表写记录
字段如下：
主键，创建日期，变动的表名称，变动的类型，变动的表的id或条件等
创建触发器监听表并向Service Broker发送消息
CREATE TRIGGER YourTrigger ON YourTable
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @message NVARCHAR(MAX);
SET @message = N'New changes in YourTable';
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE YourService
TO SERVICE 'YourService', 'CURRENT DATABASE'
ON CONTRACT [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @handle MESSAGE TYPE [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] (@message);
END;
应用层监听Service Broker
public List receiveMessages() {
return jdbcTemplate.query(
"RECEIVE message_body FROM YourQueue;",
new RowMapper() {
@Override
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
return new String(rs.getBytes(1), StandardCharsets.UTF_8);
}
}
);
}
第二种解决方案
ApexSQL Log 工具，类似的工具还有很多。 连接数据库后可以记录日志 ，根据日志反向生成sql语句，把业务刚产生的数据插入新的数据库
优点操作简单，缺点数据不一致
第三种解决方案
手写业务 再.net业务里新增业务，当.net服务执行增删改业务中，增加同步到pgsql的业务
缺点 成本过高，且数据容易混乱
