Linked Servers in SQL Server 2005

So I was faced with a problem the other day where I had to map data from one datasource to another (well, common problem) but both of the sources were SQL Servers, and I had to map the data based on some system ids and names, so I couldn’t just move all the data between the sources.
I then looked up the linked servers and was surprised at how easy that was.

Basically – all I needed to do what set up a linked server by using the following system stored procedures:
sp_addlinkedserver '<Server Alias>', '', 'SQLNCLI', NULL, NULL, 'SERVER=<IP>', NULL

sp_addlinkedsrvlogin '<Server Alias>', 'false', NULL, '<username>', '<password>'


The first call sets up the server, whereas the second one sets up a log in to the server.

Be aware that the traffic between the servers as far as I know are sent pretty plain text/not encrypted, so I would not advice this to run for a prolonged time in a product environment lest it is located behind some VPN or similar.

To remove the linked server once done then just call

sp_dropserver '<Server Alias>', 'droplogins';

Linking servers means you can access a different database server with a qualified name, as if it was on the same database server.

If I for instance set up a linked server called myRemoteServer, then I would be able to access a table in the remote database using:
myRemoteServer.<DatabaseName>.<SchemaName>.<TableName>

It makes it relative much easier to map data between sources compared to having to backup/restore the remote source database to the target server.