Sep
20
2011
One of the most common tasks when managing databases is data transfer between them. When this process is complex or involves different kinds of data sources, I usually recommend a tool such as SQL Server Integration Services (SSIS) or Pentaho. However, sometimes a quick transfer option is needed between two SQL Server instances without the overhead of building out a full ETL solution. This is where linked servers can jump in.
A linked server is a virtual connection that allows one instance of SQL Server to know about (and query) another. This then allows fully qualified queries that can references tables across servers. Before diving into creating one these, it should be noted that the you must be a SysAdmin on the SQL Server instance in order to create a linked server.
To start, expand the "Server Objects" node under the selected instance within SQL Server Management Studio (SSMS).
Note that we have a folder for linked servers displayed. Right click this folder and select "New Linked Server" which will bring up the following dialog.
Within this dialog, the first option to select is either SQL Server or "Other data source". The other data sources include several OLE DB options. Typically, I only recommend using linked servers when you can select the SQL Server radio button. I'm unsure of the exact behavior of the "Other data source" and generally avoid it.
With SQL Server selected, type the name of the server that you would like to link in the text box at the top. The next step is to set up the security for connecting to the remote server. A Local Login will need to be added. Here's a quote from MSDN regarding adding this login:
Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.
I typically select the account I want to use locally under the Local Login column, then select "Impersonate" as I'm often using domain accounts. The Remote User and Remote Password are used if you wanted to use SQL Server Authentication. The second half of the security page allows you to define a default connection to use for logins not listed in the table. If you'd like the logged in user to attempt to connect to the remote server with their log in, select "Be made using the login's current security context". The last option basically allows a connection to be made with a hardcoded username and password regardless of who logged in to the local (non-linked) server. This option is the least secure, and I don't recommend using it.
With these options finished, click "OK" and a new item will appear within the Linked Servers folder corresponding to the server name you entered. To query this linked server, we simply reference it from our query as seen below:
SELECT *
FROM [DAXSERVER1].[DAXDB1].[dbo].[CUSTTRANS];
From this fully qualified query, one can see that I'm querying the DAXSERVER1 SQL Server, with the DAXDB1 database and finally the CUSTTRANS table within the dbo schema. Now, if you've got access to create a linked server to DAXSERVER1 why not just connect to it directly with SSMS and query it there? If all you're doing is querying, then a direct connection is definitely more appropriate. However, as I mentioned earlier, this is very helpful for data transfer. See this query:
SELECT *
INTO [dbo].[CUSTTRANS_9172011]
FROM [DAXSERVER1].[DAXDB1].[DBO].[CUSTTRANS]
WHERE [CLOSED] = '';
This query actually transfers the records from the CUSTTRANS table (where they don't have a closed date) to a table named CUSTTRANS_9172011 in the local context. This can be used to copy data off of the production server, onto a separate location to essentially "freeze" the data for a point in time. This is extremely helpful when working with very rapidly updating data.
I hope this sheds some light on how to create a Linked Server as well as how one can leverage them to expand their querying reach without having to use another tool.
As always, I welcome any questions/comments below!