server name : test2
database name : test2
table name : table21
database name : test2
table name : table21
dsedit 加入同步服务器设置
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
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
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
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
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
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> 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
----------- ----------
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)
缺点:当备库出现问题的时候 ,会影响主库。