欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server 远程连接服务器详细配置(sp_addlinkedserver)

程序员文章站 2022-03-02 23:24:56
远程链接服务器详细配置 --建立连接服务器 exec sp_addlinkedserver '远程服务器ip','sql server' --标注存储 ex...

远程链接服务器详细配置

--建立连接服务器
exec sp_addlinkedserver '远程服务器ip','sql server'
--标注存储
exec sp_addlinkedserver
@server = 'server', --链接服务器的本地名称。也允许使用实例名称,例如myserver\sql1
@srvproduct = 'product_name' --ole db数据源的产品名。对于sql server实例来说,product_name是'sql server'
, @provider = 'provider_name' --这是ole db访问接口的唯一可编程标识。当没有指定它时,访问接口名称是 sql server数据源。sql server显式的provider_name是 sqlncli(microsoft sql native client ole db provider)。oracler的是 msdaora,oracle 8或更高版本的是oraoledb.oracle。ms access和ms excel的是 microsoft.jet.oledb.4.0。ibm db2的是db2oledb,以及odbc数据源的是msdasql
, @datasrc = 'data_source' --这是特定ole db访问接口解释的数据源。对于sql server,这是 sql server(servername或servername\instancename)的网络名称。对于oracle,这是sql*net别名。对于 ms access和msexcel,这是文件的完整路径和名称。对于odbc数据源,这是系统dsn名称
, @location = 'location' --由特定ole db访问接口解释的位置
, @provstr = 'provider_string' --ole db 访问接口特定的连接字符串。对于odbc连接,这是odbc连接字符串。对于ms excel,这是excel 5.0
, @catalog = 'catalog' --catalog的定义变化基于ole db访问接口的实现。对于sql server,这是可选的数据库名称,对于db2,这个目录是数据库的名称


--创建链接服务器上远程登录之间的映射
exec sp_addlinkedsrvlogin '远程服务器ip','false','sa','架构名','访问密码'
--标注存储
exec sp_addlinkedsrvlogin 
@rmtsrvname = '远程服务器ip', --要添加登录名映射的本地链接服务器
@useself = false, --当使用true值时,使用本地sql或windows登录名连接到远程服务器名。如果设为false,存储过程 sp_addlinkedsrvlogin的locallogin、rmtuser和rmtpassword参数将应用到新的映射中
@locallogin = null, --这是映射到远程登录名的sql server登录或windows用户的名称。如果这个参数置为null,映射将应用sql server实例中的所有本地登录名
@rmtuser = '架构名', --用来连接到链接服务器的用户/登录名的名称
@rmtpassword = '访问密码' --用来连接到链接服务器的用户/登录名的密码

--查询数据
select top 10 * from [222].pwms.pwms.bas_tb_sku

--查看链接服务器信息
select name,product,provider,data_source,query_timeout,lazy_schema_validation,is_remote_login_enabled,is_rpc_out_enabled
from sys.servers
where is_linked=1

--配置链接服务器属性
exec sp_serveroption '别名','name','远程服务器ip'
--标注存储
exec sp_serveroption 
@server = 'server' --配置属性的链接服务器的名称
,@optname = 'option_name' --要配置的选项
,@optvalue = 'option_value' --选项的新值
--附参数
参 数 描 述
collation compatible 如果你确认sql server实例与远程sql server拥有相同的排序规则就启用这个设置。由于sql server不再需要对数据源之间的字符列执行比较操作,把它们假定为相同的排序规则,这样做可以提升性能
collation name 如果启用了use remote collation并且是非sql server的数据源,则collation name指定远程服务器排序规则的名称。这个排序规则名称必须是sql server所支持的
connect timeout 指定在超时发生之前到链接服务器的连接会尝试多少秒。如果数值为“0”,sp_ configure的remote query timeout的服务器值用来当作默认值
data access 如果启用,就允许分布式查询访问
lazy schema validation 如果设为true,则架构不会在查询开始时去检测远程表。尽管这样会减少远程查询的负载,但是如果架构发生了变化并且你没有进行架构检测,比如说查询中引用的对象不能与查询命令进行通信,就会生成错误
query timeout 指定查询等待的超时值(秒数)。如果这个值为0,则query wait选项使用sp_configure值
rpc 启用从服务器进行远程过程调用
rpc out 启用远程过程调用到服务器
use remote collation 指定是使用远程服务器排序规则(true)还是本地服务器排序规则(false)

--删除链接服务器属性
exec sp_dropserver '4'
--标注存储
exec sp_dropserver
@server = 'server' --从sql server实例中删除的链接服务器的名称
,@droplogins = 'droplogins' --如果指定droplogins,则在删除链接服务器之前要删除登录名映射


--查看链接登录名
select s.name linkedservername,s.data_source linkedserver_source,s.is_linked,s.modify_date,
ll.remote_name,ll.local_principal_id,ll.uses_self_credential,
p.name localloginname
from sys.linked_logins ll
inner join sys.servers s on s.server_id=ll.server_id
left join sys.server_principals p on p.principal_id=ll.local_principal_id
where s.is_linked = 1

--删除链接服务器登录名映射
exec sp_droplinkedsrvlogin '远程服务器ip',null
--标注存储
exec sp_droplinkedsrvlogin 
@rmtsrvname = '远程服务器ip' --登录名映射的链接服务器名称
@locallogin = null --这是从链接服务器删除的sql server登录或windows用户映射的名称

sql

--简单点为远程机器设置远程链接服务器。
--假设远程机器ip为127.0.0.1
--建立连接服务器
exec sp_addlinkedserver '127.0.0.1','sql server'

--创建链接服务器上远程登录之间的映射
exec sp_addlinkedsrvlogin '10.0.0.222','false','sa','架构名','密码'

--查询数据
select top 10 * from [127.0.0.1].数据库名.架构名.表名

--查看链接服务器信息
select name,product,provider,data_source,query_timeout,lazy_schema_validation,is_remote_login_enabled,is_rpc_out_enabled
from sys.servers
where is_linked=1

--配置链接服务器属性设置别名
exec sp_serveroption 'remotecmp','name','127.0.0.1'

--查询数据
select top 10 * from remotecmp.数据库名.架构名.表名

上面的文章是通过sql自带的存储过程实现的,其他的可以参考其他的文章。