sybase 与 sybase 表同步目前主要什么方法实现

alvin2012_zhang · 发表于 2013-6-19 21:58

iqlife 发表于 2013-6-19 20:10
整个方案和测试文档共享下吧，最近也准备搞数据同步这块

环境：
server name : test1
database name : test1
table name  : table11

server name : test2
database name : test2
table name : table21

esedit 加入同步服务器设置
1> exec sp_addserver test2, ASEnterprise, test2                ######添加远程服务器信息
2> go
Adding server 'test2', physical name 'test2'
Server added.
(return status = 0)
1> exec sp_addexternlogin test2, sa, sa , sybase             ######添加远程登陆信息
2> go
User 'sa' will be known as 'sa' in remote server 'test2
(return status = 0)
1> use test11
2> go
1> create proxy_table table21 at "test2.test21.dbo.table21"    ######创建代理表
2> go
1> select * from table21                                     ######可访问远程服务器表
2> go
id       name
----------- ----------
         2 alvin2

(1 row affected)

1> CREATE TRIGGER tr_table11                                  ######创建触发器同步数据
2> ON table11
3> FOR INSERT,UPDATE,DELETE
4> AS
5> IF NOT EXISTS(SELECT * FROM deleted) AND EXISTS(SELECT * FROM inserted)
6> BEGIN
7>    INSERT INTO table21 SELECT * FROM inserted
8> END
9> ELSE IF EXISTS(SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
10> BEGIN
11>    DELETE FROM table21 WHERE id IN (SELECT id FROM deleted)
12> END
13> ELSE IF EXISTS(SELECT * FROM deleted) AND EXISTS(SELECT * FROM inserted)
14> BEGIN
15>    DELETE FROM table21 WHERE id IN (SELECT id FROM deleted)
16>    INSERT INTO table21 SELECT * FROM inserted
17> END
18> GO
1> insert into table11 values(1,"alvin")
2> go
(1 row affected)
1> select * from table21
2> go
id       name
----------- ----------
         1 alvin

(1 row affected)
1> insert into table11 values(2,"alvi")
2> go
(1 row affected)
1> insert into table11 values(3,"alv")
2> go
(1 row affected)
1> commit
2> go
1> select * from table21                                     ######插入数据同步成功
2> go
id       name
----------- ----------
         1 alvin
         2 alvi
         3 alv

(3 rows affected)
1> delete from table11 where id=1
2> go
(1 row affected)
1> select * from table21                                     ######删除数据同步成功
2> go
id       name
----------- ----------
         2 alvi
         3 alv

(2 rows affected)
1> commit
2> go
1> update table11 set name="ultra" where id=2
2> go
(1 row affected)
1> select * from table21
2> go
id       name
----------- ----------
         3 alv
         2 ultra

(2 rows affected)
1> update table11 set name="ultra" where id=3
2> go
(1 row affected)
1> commit
2> go
1> select * from table21                                     ######更新数据同步成功
2> go
id       name
----------- ----------
         2 ultra
         3 ultra

(2 rows affected)
1> delete from table11 where id=2
2> go
(1 row affected)
1> select * from table21
2> go
id       name
----------- ----------
         3 ultra

(1 row affected)
1> delete from table11 where id=3
2> go
(1 row affected)
1> commit
2> go
1> select * from table21
2> go
id       name
----------- ----------

(0 rows affected)