跨服务器间的sqlserver数据库- 表数据同步

一 、创建只读取账号1、数据库的服务器必须设置为 SQL Server 和 Windows 身份验证模式2、服务器└─ 安全性└─ 登录名 -----新建登录名 如图例如登录名sync_user身份验证SQL Server 身份验证密码********取消【强制密码过期】3、服务器角色 选 public4、用户映射角色成员身份的话是 读取- db_datareader 编辑--db_datawriter 自己根据需要勾选5、然后 进入表或者选中表点击---数据库↓安全性↓用户 ↓sync_XXXX在用户名上 -右键属性 选择安全对象添加dbo.表名 【给于 查询权限我这里只要同步数据不需要其他权限】第二步测试库建立 Linked Server在测试服务器执行EXEC master.dbo.sp_addlinkedserver serverPROD_DB,//连接目标服务器别名 srvproduct ,//产品名称一般为空字符串也必填不写竟然报错 providerMSOLEDBSQL,//使用什么驱动连接 SQL Server datasrc正式服务器IP,1433;//正式 SQL Server 地址端口设置账号EXEC master.dbo.sp_addlinkedsrvlogin rmtsrvnamePROD_DB,//指定哪个 Linked Server 使用这个账号与建的服务器别名保持一致 useselffalse,//不使用当前登录账号去访问远程服务器 rmtuser同步账号,//远程库的账号【也就是为什么第一步先建一个只读账号】 rmtpassword密码;//远程库的密码测试连接成功否SELECT TOP 10 *FROM OA_PROD_LINK.数据库名.dbo.表名;如果返回数据说明成功。确定表里有数据哈所以测试库三、建立索引【表字段建立索引提升效率】3.1 测试库建索引CREATE NONCLUSTERED INDEX IX_表前缀_表名_字段 ON dbo.表名(字段); GO3.2 正式库也建立登录正式库CREATE NONCLUSTERED INDEX IX_表前缀_表名_字段 ON dbo.表名(字段); GO四、创建同步存储过程位置测试库创建 存储过程方便调用USE OA_TEST; GO CREATE PROCEDURE dbo.Sync_自己起个名 ( month varchar(7) ) AS BEGIN SET NOCOUNT ON; DECLARE cnt INT; BEGIN TRY --1 删除测试库该月份数据 DELETE FROM dbo.oa_attendance WHERE monthmonth; --2 插入正式库数据 INSERT INTO dbo.测试库表名 ( staff_id, uid, xxxxx, createtime, updatetime, deletetime ) SELECT staff_id, uid, xxxx, createtime, updatetime, deletetime FROM OA_PROD_LINK.OA_PROD.dbo.正式库表名 WHERE monthmonth; SET cntROWCOUNT; --3 写日志 日志表根据需要加不加都可 INSERT INTO dbo.Sync_Log ( table_name, sync_month, sync_count, status ) VALUES ( oa_attendance, month, cnt, SUCCESS ); END TRY BEGIN CATCH INSERT INTO dbo.Sync_Log ( table_name, sync_month, sync_count, status, error_msg ) VALUES ( oa_attendance, month, 0, FAIL, ERROR_MESSAGE() ); END CATCH END GO存储过程日志表结构CREATE TABLE dbo.Sync_Log(id INT IDENTITY(1,1) PRIMARY KEY,table_name VARCHAR(100),sync_month VARCHAR(20),sync_count INT,sync_time DATETIME DEFAULT GETDATE(),status VARCHAR(20),error_msg VARCHAR(500));GO五、第一次同步测试比如同步2026-05执行EXEC dbo.Sync_自己起的存储过程名字 2026-05;我使用的两端均是 sqlserver不同类型的自己测试哈OK这样跨库同步数据就大功告成4万多条数据同步时间2-3s继续撸代码