How to Create Microsoft SQL Server Linked Server

Using linked server, SQL Server developers and administrators can connect two different database instances.
In any of SQL Server versions like SQL Server 2000, Microsoft SQL Server 2005, MS SQL Server 2008 or the latest SQL Server 2011 versions Microsoft SQL Server linked server wizard enables easily define the relations between two databases in different database servers.
After defining the link server database either using sql linked server graphical user interface (GUI) or sp_addlinkedserver procedure, database developers can manage DML (Data Manipulation Language) or DDL (Data Definition Language) transactions on the linked server.

Why SQL Server professionals require the need to create SQL Server linked server?

One of the common reasons of creating linked server SQL Server DBA's need to transfer data in a sql table into a database table which is located on a different database server.
Using a SQL linked server is one of the most given answers to data transfer question in SQL Server community forums.
Or instead of using a data compare tool, you might want to compare two tables in different database servers using linked server.
The reasons behind using SQL Sserver linked server might be very different from application to application.
For example, once I was building a data merge application. I had to store some form of customers sql table records in a different SQL Server instance. And the easiest way of transferring data from one SQL Server to another SQL Server instance is to create linked server and execute sql statements using sql linked server.

When a database server is defined as a Microsoft SQL Server linked server on another server, in t-sql codes sql developers can use the below syntax to reach a database table :
[LinkedServerName].DatabaseNAme.SchemaName.TableName
This syntax represents a sql table in the SQL Server where the linked server is created.

Let's assume that you have a SQL Server 2000 installation on you local computer where you are the administator of your server.
And assume that you want to connect to a shared database server which is located on a hosting firm using the sql linked server.
We will now demonstrate how to create linked server on Microsoft SQL Server 2000.
Please note that creating link server process is similar on every Microsoft SQL Server version.
Most important requirement for creating a SQL Server linked server is the user who is managing this task should have sysadmin server role

First open SQL Server Enterprise Manager (or SQL Server Management Studio, SSMS), then connect to the local SQL Server instance.
Open the "Security" folder of the connected SQL Server instance.
You will see the "Linked Servers" node.
Right click on the "Linked Servers" node and choose "New Linked Server...".
Or using the SQL Server Enterprise Manager menu, choose "Action" then follow "New Linked Server..." to create a new sql linked server.

SQL Server linked server

The following SQL Server Linked Server Properties - New Linked Server wizard screen will be displayed.

SQL Linked Server Properties

In General tab, first SQL administrator chooses the data Server type.
If the administrator wants to connect to a MS SQL Server instance database, he should choose the "SQL Server" as the server type.
On the top section of the screen, the user should supply the SQL Server instance name or the SQL Server IP address into the "Linked Server" textbox.
As you see in the above screenshot, the user create a sql linked server to the SQL Server instance named SQL2K.KODYAZ.COM

After defining the target SQL Server which is being configured as link server, the sql security context, the authentication properties as set in the "Security" tab of the "New Linked Server Properties" wizard.
For example if you have a sql login which has authentication on the target sql linked server, you can set the sql login name and the login password after choosing the "Be made using this security context" option and using "Remote login" and "With password" input areas.

new linked server sql login security properties

SQL developers and administrators can complete the necessary task to create sql "Linked Server" in short.

Now the new linked server can be viewed in the left pane of the SQL Server Enterprise Manager Linked Servers node.
When the new link server is drilled through sql tables and sql views can be seen listed.

After the you create linked server, using a Query Analyzer, remote data rows can be queried using sql linked server

Now let's code sample sql scripts to deal with data.
Create a database table named tblSample using the CREATE TABLE command as follows :


CREATE TABLE tblSample (

  column1 int

)

Code

And populate sample table with data using the following t-sql INSERT INTO commands.


INSERT INTO tblSample SELECT 4

INSERT INTO tblSample SELECT 6

INSERT INTO tblSample SELECT 2

Code

Now you will see that it is possible to transfer sample table data to remote server using sql linked server as follows:


INSERT INTO [sql2k.kodyaz.com].databasename.dbo.tblTargetTable

SELECT column1 FROM tblSample

Code

It is important to surround the linked server name with "[" and "]".
Write the SQL Server linked server name inside "[]".
Otherwise, your sql script will cause an sql engine error.

After you create sql linked server, you can compare data between two tables which are in different SQL Server instances as follows.
The following t-sql code using sql linked server, can join two tables and compare row data.


SELECT *

FROM 

  tblSample A,

  [sql2k.kodyaz.com].databasename.dbo.tblTargetTable B

WHERE A.column1 = B.column1

Code