Migrating SQL Server Linked Servers from One Server to Another
In many environments it is common to notice Linked Servers in various SQL Server instances. These are server level objects comes under security objects. DBAs are responsible for creating linked servers between SQL Server instances or to other 3rd party products such as Oracl, MySQL, DB2, etc. In situations when SQL Server instance is migrated from one server to another server due to hardware upgrade or when upgrading or migrating lower SQL Server version to higher SQL Server versions, all dependent objects also are required to be migrated which include Logins, Jobs, Linked Server as well. If there are one or two linked servers, DBAs can check the configuration of the linked server and create them on the new server, but in cases where the number of linked servers or configuration is different, it is better to find an automated way of moving the linked servers.
Linked server can be used in scheduled jobs, DTS packages, stored procedures or views. If linked servers aren’t created on the destination machine, any component or other user objects depending on them will fail. Below script can be used to script out the linked servers on source or existing of old server and then create them on the destination or target or new server using the generated script. Important thing to keep in mind is that this will not automatically copy over any remote security connections, so you would have to apply the appropriate logins manually. This can be found under the security tab of the linked server properties.
set nocount on
select ‘exec master..sp_addlinkedserver
@server = ”’ + srvname + ”’,
@srvproduct =”’ + srvproduct + ”’,
@provider =”’ + providername + ”’, @datasrc = ”’ +
datasource + ”” from master..sysservers
Where srvname <> @@servername
What are Linked Servers in SQL Server?
Linked servers allows the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server or remote SQL Server instances. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL(TSQL) statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types of OLE DB data sources can be configured as linked servers such as Microsoft Access, Sybase, MySQL, Excel, etc. Linked servers offer advantages such as provides the ability to access data from outside of SQL Server, provides the ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise, provides the ability to address diverse data sources similarly.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.