使用sp_addlinkedserver完成长途数据库链接

MSSQLSE大切诺基VECRUISER跨过服务器务器链接服务器创立方法如下复制代码 代码如下:–声明变量 Declare @svrname
varchar(255State of Qatar, @dbname varchar(255卡塔尔国, @sapassword varchar(255卡塔尔–请在实践前,依据实际境况改过下列变量 select @svrname = ‘指标服务器名’,
@dbname = ‘目的数据库’, @sapassword = 目的数据库密码 if exists ( select
srvname from master.dbo.sysservers where srvname =’链接服务器名’卡塔尔 exec
sp_dropserver @server = ‘链接服务器名’,@droplogins =’droplogins’
–扩充链接服务器 exec sp_addlinkedserver @server =
‘链接服务器名’,@srvproduct=”,@provider = ‘SQ英雄联盟EDB’, @datasrc
=@svrname, @catalog =@dbname –扩展链接服务器远程登陆 exec
sp_addlinkedsrvlogin @rmtsrvname = ‘链接服务器名’,@useself
=’False’,@locallogin = ‘sa’,@rmtuser =’sa’,@rmtpassword =@sapassword
–配置参数 exec sp_serveroption @server =’链接服务器名’,@optname = ‘use
remote collation’,@optvalue =’false’exec sp_serveroption @server
=’链接服务器名’,@optname = ‘collation compatible’,@optvalue =’true’go

–查看当前链接景况:

图片 1–依期协作服务器上的数据 
图片 2–例子:
图片 3–测验境况,SQL Server2003,远程服务器名:xz,顾客名称为:sa,无密码,测量试验数据库:test
图片 4–服务器上的表(查询深入分析器连接到服务器上创制卡塔尔
图片 5create table [user](id int primary key,number varchar(4),name varchar(10))
图片 6go
图片 7–以下在局域网(本机操作卡塔尔
图片 8–本机的表,state表明:null 表示新增添记录,1 表示修正过的笔录,0 代表无变化的笔录
图片 9if exists (select * from dbo.sysobjects where id = object_id(N'[user]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
图片 10 drop table [user]
图片 11GO
图片 12create table [user](id int identity(1,1),number varchar(4),name varchar(10),state bit)
图片 13go
图片 14–创制触发器,维护state字段的值
图片 15create trigger t_state on [user]
图片 16after update
图片 17as
图片 18update [user] set state=1
图片 19from [user] a join inserted b on a.id=b.id
图片 20where a.state is not null
图片 21go
图片 22
图片 23–为了便利同步管理,创立链接服务器到要联合的服务器
图片 24–这里的远程服务器名称叫:xz,客户名称为:sa,无密码
图片 25if exists(select 1 from master..sysservers where srvname=’srv_lnk’)
图片 26 exec sp_dropserver ‘srv_lnk’,’droplogins’
图片 27go
图片 28exec sp_addlinkedserver  ‘srv_lnk’,”,’SQLOLEDB’,’xz’
图片 29exec sp_addlinkedsrvlogin ‘srv_lnk’,’false’,null,’sa’
图片 30go
图片 31
图片 32–制造同步管理的存储进程
图片 33if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_synchro]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
图片 34 drop procedure [dbo].[p_synchro]
图片 35GO
图片 36create proc p_synchro
图片 37as
图片 38–set  XACT_ABORT on
图片 39–运行远程服务器的MSDTC服务
图片 40–exec master..xp_cmdshell ‘isql /S”xz” /U”sa” /P”” /q”exec master..xp_cmdshell ”net start msdtc”,no_output”‘,no_output
图片 41
图片 42–运行本机的MSDTC服务
图片 43–exec master..xp_cmdshell ‘net start msdtc’,no_output
图片 44
图片 45–进行分布事务管理,假若表用标记列做主键,用下边包车型客车法子
图片 46–BEGIN DISTRIBUTED TRANSACTION
图片 47 –同步删除的数目
图片 48 delete from srv_lnk.test.dbo.[user]
图片 49  where id not in(select id from [user])
图片 50
图片 51 –同步大幅度增加的数额
图片 52 insert into srv_lnk.test.dbo.[user]
图片 53 select id,number,name from [user] where state is null
图片 54 
图片 55 –同步订正的多寡
图片 56 update srv_lnk.test.dbo.[user] set
图片 57  number=b.number,name=b.name
图片 58 from srv_lnk.test.dbo.[user] a
图片 59  join [user] b on a.id=b.id
图片 60 where b.state=1
图片 61 
图片 62 –同步后更新本机的标识
图片 63 update [user] set state=0 where isnull(state,1)=1
图片 64–COMMIT TRAN
图片 65go
图片 66
图片 67–创制作业,准时推行多少同步的囤积进程
图片 68if exists(SELECT 1 from msdb..sysjobs where name=’数据管理’State of Qatar
图片 69 EXECUTE msdb.dbo.sp_delete_job @job_name=’数据管理’
图片 70exec msdb..sp_add_job @job_name=’数据管理’
图片 71
图片 72–创制作业步骤
图片 73declare @sql varchar(800),@dbname varchar(250)
图片 74select @sql=’exec p_synchro’   –数据管理的下令
图片 75 ,@dbname=db_name(卡塔尔(قطر‎   –实施多少管理的数额库名
图片 76
图片 77exec msdb..sp_add_jobstep @job_name=’数据管理’,
图片 78 @step_name = ‘数据同步’,
图片 79 @subsystem = ‘TSQL’,
图片 80 @database_name=@dbname,
图片 81    @command = @sql,
图片 82 @retry_attempts = 5,   –重试次数
图片 83 @retry_interval = 5    –重试间距
图片 84
图片 85–创设调治
图片 86EXEC msdb..sp_add_jobschedule @job_name = ‘数据管理’, 
图片 87 @name = ‘时间布署’,
图片 88 @freq_type = 4,     –每天
图片 89 @freq_interval = 1,    –每日执行贰次
图片 90 @active_start_time = 00000  –0点执行
图片 91go
图片 92
图片 93

select * from sys.servers;

–使用 sp_helpserver
来展现可用的服务器

Exec sp_helpserver

–删除已经存在的有些链接

Exec sp_droplinkedsrvlogin
服务器小名,Null Exec sp_dropserver 服务器小名

–使用sp_addlinkedserver来扩充链接

EXEC
sp_addlinkedserver @server=’192.168.2.66′,–被访问的服务器外号(习于旧贯上直接运用对象服务器IP,或取个小名如:JOY)
@srvproduct=”, @provider=’SQ英雄结盟EDB’,
@datasrc=’192.168.2.66′ –要采访的服务器

–使用sp_addlinkedsrvlogin
来充实客户登入链接