Linked servers – Upgrading from 2005 to 2012

Best way to find other server are linked to 2005 SQL Instance as I am migrating all to 2012 SQL Server.

I will migrate all linked server that is linked to 2005 SQL Instance but what is the best way to find other server in my environment are linked to 2005 SQL instance so I can change after migration to 2012 ?

More than focusing on other servers that link to your current server, I would worry about applications. In complex environments, some applications may be out of control and keep pointing to your old server.

In these cases, this is the plan I am using:

1. install the new server with the same instance name as the old one
2. migrate all server objects, including logins and everything else
3. switch off or rename the old server
4. add a CNAME in your DNS to point the old server name to the new
   server name

This way, you don’t have to worry about linked servers, ETL jobs or applications still pointing to the old server.

Obviously, this assumes that you already have tested everything to be compatible with the new version.

UPDATE

Based on your comments, if you still want to discover incoming linked servers, you can register all your servers and run a multi-server query like this:

select name
from sys.servers
where product = 'sql server'
    and data_source = 'your sql server 2005'

I don’t remember any DMVs or CVs in SQL Server that can tell you if there are inbound linked servers. They just come in as a server-to-server connection.

Do all the linked servers use the same credential or a known set of credentials to access your 2005 instance and the credential(s) is only used for the linked servers? If yes to both then make sure you have the Login Auditing property for the server set to log both failed and successful logins. From there, the log will show connections from those credentials and the source IP address. That will give you your host machine connecting to your database. With that you can use nslookup w.x.y.z or if that fails, nbtstat -a w.x.y.z will get you the hostname. Last resortping -a w.x.y.z will work too.