【DB宝84】使用OGG for MSSQL微服务快速双向同步RDS数据库（双主）

小麦苗DBA宝典

发布于 2022-02-23 19:32:59

2.4K0

发布于 2022-02-23 19:32:59

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

类似文章

OGG有传统的经典架构，也有最新的微服务，2个都可以远程捕获和应用数据，对数据库服务器是0侵入，而传统的经典架构是纯命令行模式，最新的微服务架构是图形化界面操作，几乎所有操作都可以在界面进行。

相关文章可以参考：

使用OGG for MySQL微服务快速双向同步RDS数据库：https://www.xmmup.com/shiyongoggweifuwukuaisushuangxiangtongburdsshujuku.html
OGG用于跨云RDS for MySQL之间配置双主实时同步–OGG远程捕获和投递：https://www.xmmup.com/oggyongyukuayunrdszhijianpeizhishuangzhushishitongbuyuanchengbuhuohetoudi.html
otter用于跨云RDS for mysql之间配置双主实时同步：https://www.xmmup.com/otteryongyukuayunrdszhijianpeizhishuangzhushishitongbu.html
OGG用于PG数据库之间双主实时同步（RDS for PG亦可）–OGG远程捕获和投递：https://www.xmmup.com/oggyongyupgshujukuzhijianshuangzhushishitongburds-for-pgyikeoggyuanchengbuhuohetoudi.html
使用OGG for PG微服务快速双向同步RDS数据库（双主）：https://www.xmmup.com/shiyongogg-for-pgweifuwukuaisushuangxiangtongburdsshujukushuangzhu.html
使用阿里的开源工具otter，这个方案经过验证是可行的。阿里数据同步工具Otter和Canal简介请参考：https://www.xmmup.com/alishujutongbugongjuotterhecanaljianjie.html
使用OGG for Oracle微服务双向同步Oracle数据库搭建双主架构（含DDL）：https://www.xmmup.com/shiyongogg-for-oracleweifuwushuangxiangtongbuoracleshujukuhanddl.html
使用OGG for mysql微服务搭建双主架构（含DDL）：https://www.xmmup.com/shiyongogg-for-mysqlweifuwudajianshuangzhujiagouhanddl.html

使用OGG for SQL Server微服务

-- 创建专用网络
docker network create --subnet=172.72.9.0/24 mssql-network



-- MSSQL A
docker rm -f mssql2019a
docker run -d --name mssql2019a -h mssql2019a \
-p 33200:1433  --net=mssql-network --ip 172.72.9.200  \
-e "ACCEPT_EULA=Y" -e "SA_PASSWORD=lhr@xxt123" -e TZ=Asia/Shanghai \
--privileged=true -u root  \
mcr.microsoft.com/mssql/server:2019-latest


-- MSSQL B
docker rm -f mssql2019b
docker run -d --name mssql2019b -h mssql2019b \
-p 33201:1433  --net=mssql-network --ip 172.72.9.201  \
-e "ACCEPT_EULA=Y" -e "SA_PASSWORD=lhr@xxt123" -e TZ=Asia/Shanghai \
--privileged=true -u root  \
mcr.microsoft.com/mssql/server:2019-latest


-- 使用ogg微服务，这里的端口需要一致
docker rm -f lhrogg214mamssql
docker run -d --name lhrogg214mamssql -h lhrogg214mamssql \
  --net=mssql-network --ip 172.72.9.205  \
  -p 9392:3389 -p 9000-9005:9000-9005 \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/ogg214mamssql:v1.0 \
  /usr/sbin/init




-- 启用代理
docker exec -it mssql2019a /opt/mssql/bin/mssql-conf set sqlagent.enabled true
docker exec -it mssql2019b /opt/mssql/bin/mssql-conf set sqlagent.enabled true

docker restart mssql2019a mssql2019b


-- 2个SQL Server库需要配置
sqlcmd -S 192.168.66.35,33200 -U sa -P lhr@xxt123
sqlcmd -S 192.168.66.35,33201 -U sa -P lhr@xxt123

-- 修改密码
USE [master]
GO
ALTER LOGIN [sa] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'lhr'
GO


-- 创建同步数据库
create database lhrdb;
go


-- 创建用户
use lhrdb;
create login ogg with password = 'lhr' ,DEFAULT_DATABASE=lhrdb, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
create user ogg for login ogg with default_schema=ogg;
exec sp_addsrvrolemember @loginame = N'ogg', @rolename = N'sysadmin';
ALTER ROLE db_owner ADD MEMBER ogg;
create schema ogg;
go



-- 启用cdc
use lhrdb;
EXECUTE sys.sp_cdc_enable_db;

create table testogg(id int primary key , name varchar(100));

SELECT name, recovery_model, recovery_model_desc,is_cdc_enabled
FROM sys.databases
WHERE name in ('lhrdb') ;

SELECT name,is_tracked_by_cdc FROM lhrdb.sys.tables WHERE is_tracked_by_cdc = 1;

go





-- OGGMA
cat > /ogg214c/ogg_ma/odbc.ini <<"EOF"
[mssql2019a]            
Driver = ODBC Driver 17 for SQL Server            
Server = 172.72.9.200,1433            
Database = lhrdb            
User = ogg            
Password = lhr

[mssql2019b]            
Driver = ODBC Driver 17 for SQL Server            
Server = 172.72.9.201,1433            
Database = lhrdb            
User = ogg            
Password = lhr
EOF

odbcinst -i -s -f /ogg214c/ogg_ma/odbc.ini




-- 添加TRANDATA
su - oracle
adminclient
CONNECT http://127.0.0.1:9000 deployment deploy214 as oggadmin password lhr


dblogin useridalias MSSQLA DOMAIN OGGMA


list tables dbo.*
ADD TRANDATA ogg.*
ADD TRANDATA dbo.*
INFO TRANDATA dbo.*



-- 源端配置JOB
EXECUTE sys.sp_cdc_drop_job 'cleanup';

cd  /ogg214c/ogg_deploy/etc/conf/ogg/
./ogg_cdc_cleanup_setup.sh  createJob ogg lhr lhrdb "172.72.9.200,1433" ogg

访问：http://192.168.66.35:9000 ，用户名：oggadmin，密码：lhr

创建身份证明

或直接访问：http://192.168.66.35:9001/

创建检查点表

image-20211227175721765

由于我们要配置双主，所以，这里的2个SQL Server都需要配置检查点表，否则只在目标端配置检查点表即可。

配置SQLA到SQLB的实时同步

创建extract进程

image-20211227175950733

参数：

EXTRACT exta
SOURCEDB mssql2019a USERIDALIAS MSSQLA, DOMAIN OGGMA
IGNOREREPLICATES
TRANLOGOPTIONS FILTERTABLE ogg.ckpt
EXTTRAIL ./dirdat/e1
TABLE dbo.*;

创建replicate进程

image-20211227180119727

参数：

REPLICAT repa
TARGETDB mssql2019b USERIDALIAS MSSQLB, DOMAIN OGGMA
MAP dbo.*, TARGET dbo.*;

测试同步

-- 源端
insert into testogg values(1,'a');

select * from testogg;

[root@docker35 ~]# sqlcmd -S 172.72.9.200 -U ogg -P lhr -d lhrdb                      

1> select * from testogg;
2> GO
id          name                                                                                                
----------- ----------------------------------------------------------------------------------------------------
          1 a                                                                                                   

(1 rows affected)
1> 

[root@docker35 ~]# sqlcmd -S 172.72.9.201 -U ogg -P lhr -d lhrdb     
1>  select * from testogg;
2> GO
id          name                                                                                                
----------- ----------------------------------------------------------------------------------------------------
          1 a                                                                                                   

(1 rows affected)

可以发现同步是正常的！

从统计信息也可以查到：

image-20211227180516938

image-20211227180534289

配置SQLB到SQLA的实时同步

创建extract进程

参数：

EXTRACT extb
SOURCEDB mssql2019b USERIDALIAS MSSQLB, DOMAIN OGGMA
IGNOREREPLICATES
TRANLOGOPTIONS FILTERTABLE ogg.ckpt
EXTTRAIL ./dirdat/e2
TABLE dbo.*;

创建replicate进程

参数：

REPLICAT repb
TARGETDB mssql2019a USERIDALIAS MSSQLA, DOMAIN OGGMA
MAP dbo.*, TARGET dbo.*;

测试同步

-- 源端
insert into testogg values(2,'b');

select * from testogg;

[root@docker35 ~]# sqlcmd -S 172.72.9.200 -U ogg -P lhr -d lhrdb -Q "select * from testogg;"  
id          name                                                                                                
----------- ----------------------------------------------------------------------------------------------------
          1 a                                                                                                   
          2 b                                                                                                   

(2 rows affected)
[root@docker35 ~]# sqlcmd -S 172.72.9.201 -U ogg -P lhr -d lhrdb -Q "select * from testogg;"   
id          name                                                                                                
----------- ----------------------------------------------------------------------------------------------------
          1 a                                                                                                   
          2 b                                                                                                   

(2 rows affected)

可以发现同步是正常的！

从统计信息也可以查到：

配置完成后的效果

批量数据操作

在SQLA侧操作

DECLARE @i INT
SET @i = 3
WHILE (@i <=1000)
BEGIN
INSERT INTO testogg values(@i,newid())
SET @i = @i + 2
END
GO


DECLARE @i INT
SET @i = 1
WHILE (@i <=50)
BEGIN
  update testogg set name=newid() where id=@i;
  SET @i = @i + 2
END
GO



[root@docker35 ~]# sqlcmd -S 172.72.9.200 -U ogg -P lhr -d lhrdb -Q "select count(*) from testogg;"   

-----------
        501

(1 rows affected)
[root@docker35 ~]# sqlcmd -S 172.72.9.201 -U ogg -P lhr -d lhrdb -Q "select count(*) from testogg;"   

-----------
        501

(1 rows affected)

可以看到数据是完全同步的！

在SQLB侧加压

DECLARE @i INT
SET @i = 4
WHILE (@i <=1000)
BEGIN
INSERT INTO testogg values(@i,newid())
SET @i = @i + 2
END
GO


DECLARE @i INT
SET @i = 50
WHILE (@i <=150)
BEGIN
  update testogg set name=newid() where id=@i;
  SET @i = @i + 2
END
GO

[root@docker35 ~]# sqlcmd -S 172.72.9.200 -U ogg -P lhr -d lhrdb -Q "select count(*) from testogg;"  

-----------
       1000

(1 rows affected)
[root@docker35 ~]# sqlcmd -S 172.72.9.201 -U ogg -P lhr -d lhrdb -Q "select count(*) from testogg;"  

-----------
       1000

(1 rows affected)