sql server 跨实例访问数据库

我们日常使用SQL Server数据库时，经常遇到需要在实例Instance01中跨实例访问Instance02中的数据。例如在做数据迁移时，如下语句:


insert into Instance01.DB01.dbo.Table01


          select * from Instance02.DB01.dbo.Table01


普通情况下，这样做是不允许的，因为SQL Server默认不可以跨实例访问数据。解决方案是使用存储过程sp_addlinkedserver进行实例注册。


sp_addlinkedserver在MSDN中的定义为:


sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]      


      [ , [ @provider= ] 'provider_name' ]     


      [ , [ @datasrc= ] 'data_source' ]


      [ , [ @location= ] 'location' ]


      [ , [ @provstr= ] 'provider_string' ]


      [ , [ @catalog= ] 'catalog' ] 


例如：在Instance01实例中，执行如下SQL语句EXEC sp_addlinkedserver ‘Instance02’ //只写第一个参数即可，默认情况下，注册的是SQL Server数据库，其他参数用法详见MSDN。


如果你的两个实例在同一个域中，且Instance01与Instance02有共同的域登陆帐号，那么经过上面的注册后，前面的insert语句就可以执行了。否则，还需要对注册的远程实例进行登陆帐号注册，在Instance01实例中，执行如下SQL语句


EXEC sp_addlinkedsrvlogin 'InstanceName','true'  //使用集成认证访问远程实例


或者 EXEC sp_addlinkedsrvlogin 'InstanceName','false','TJVictor,'sa','Password1' //使用Windows认证访问远程实例，当用户以TJVictor用户登陆Instance01实例访问Instance02时，默认把TJVictor映射成sa，且密码为Password1


经过 sp_addlinkedserver实例注册和sp_addlinkedsrvlogin登陆帐户注册后，就可以在Instance01中直接访问Instance02中的数据库数据了。


如果还无法访问，请检查本机DNS是否可以解析远程数据库的实例名。如果无法解析，可以在EXEC sp_addlinkedserver ‘Instance02’中把Instance02换为IP，或者在hosts文件中，自己建立相应DNS映射。


下面列举几个跨实例数据库访问的存储过程和视图。


存储过程名/视图名 作用 举例 
sp_addlinkedserver 注册远程数据库实例 exec sp_addlinkedserver ‘InstanceName’ 
sp_dropserver 删除远程数据库实例 exec sp_dropserver ‘InstanceName’ 
sp_addlinkedsrvlogin 注册远程实例登陆访问帐户 exec sp_addlinkedsrvlogin ‘InstanceName’, null 
sp_droplinkedsrvlogin 删除远程实例登陆访问帐户 EXEC sp_droplinkedsrvlogin 'InstanceName','UserName' 
sp_helpserver 当前实例已注册的可访问的实例(即查看使用sp_addlinkedserver已注册过的实例) sp_helpserver 
sys.sysservers 功能同sp_helpserver select * from sys.sysservers 
sys.linked_logins 查看已注册的登陆访问帐户(即查看使用sp_addlinkedsrvlogin已注册过的帐户) select * from sys.linked_logins 
sys.remote_logins 查看已注册的远端访问帐户 select * from sys.remote_logins