在SQL Server 2008 R2上将多个链接的服务器从一台服务器迁移到另一台服务器 [英] migrating multiple linked server from one server to another on sql server 2008 R2

问题描述

我有25台链接服务器(它将连接远程sql server 2000)在一台服务器上完美运行，我什至不知道那些链接服务器的登录信息.我当前的环境是sql server 2008 R2，我的新环境也是sql server 2008 R2，我只需要将所有链接的服务器从旧迁移到新，并保留所有登录信息

I have 25 linked server(which will connect remote sql server 2000) working perfectly on one server, i even don't know login information of those linked server. My current environment is sql server 2008 R2, my new environment is also sql server 2008 R2 , i just need to migrate all linked server from old to new, preserving all login information

可能吗?

推荐答案

如果要处理的链接服务器具有使用密码指定的特定用户，那么我认为您将无法保留该密码信息.由于链接服务器处于服务器级别，因此该信息将不会保存在任何数据库备份中.但是，您可以运行以下查询，以获取链接服务器的列表，并检查哪些服务器使用传递凭据.对于这种情况，只需在新数据库中创建新的链接服务器定义(您可以通过右键单击SSMS中的链接服务器并生成脚本来轻松获得此SQL).对于那些没有的服务器，可以通过TSQL创建链接服务器，但是必须给它们提供新的凭据.

If you're dealing with linked servers that have specific users specified with password then I don't think you'll be able to preserve that password information. Since linked servers are at the server level that information wouldn't be in any db backup. But, you could run queries like the one below to get the list of linked servers and check which ones use pass-through credentials. For the ones that do, just create new linked server definitions in the new database (you can easily get this SQL by right-clicking on the linked server in SSMS and generating the script). For the ones that don't, you can create the linked server via TSQL but you'll have to give them new credentials.

SELECT
    serv.NAME,
    serv.product,
    serv.provider,
    serv.data_source,
    serv.catalog,
    prin.name,
    ls_logins.uses_self_credential,
    ls_logins.remote_name
FROM
    sys.servers AS serv
    LEFT JOIN sys.linked_logins AS ls_logins
    ON serv.server_id = ls_logins.server_id
    LEFT JOIN sys.server_principals AS prin
    ON ls_logins.local_principal_id = prin.principal_id

这篇关于在SQL Server 2008 R2上将多个链接的服务器从一台服务器迁移到另一台服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！