Add Linked Server in SQL Server 2008!!!

Linked Servers allows you to connect to other database instances on the same server or on another machine or remote servers.

As per Microsoft BOL "A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed."

Let see how can we add a linked server using  SQL Server Management Studio (SSMS):

Step: 1
In SQL Server Management Studio, open Object Explorer, expand Server Objects, right-click Linked Servers, and then click New Linked Server.

Step: 2  
The “New Linked Server”  window will appears as below.

For “Server Type” make sure “Other Data Source” is selected.  If you will select the SQL Server option then  the name specified in Linked server must be the network name of the server in other words you can not give a friendly name to it.

Now I have selected Other data source so I can give a friendly name to it. I have given LINKED_SERVER1.

Provider – Select “Microsoft OLE DB Provider for SQL Server”

Product Name – SQLSERVER  

Datasource – Enter  the actual server name, and instance name  as :
SERVERNAME\INSTANCENAME

ProviderString: We can leave it Blank

Catalog : It is Optional. If given then it will be the default database you will be using. 

On the Security page, specify the security context that will be used when the original SQL Server connects to the linked server. In a domain environment where users are connecting by using their domain logins, selecting Be made using the login’s current security context is often the best choice. When users connect to the original SQL Server by using a SQL Server login, the best choice is often to select By using this security context, and then providing the necessary credentials to authenticate at the linked server.

I have selected Be made using the login’s current security context:

Step: 3 Click OK, and the new linked server is created. You can see it under linked server as below:

So this is how we can add linked Server in SQL Server 2008.

For more information about linked server components please go through below microsoft BOL link:
http://msdn.microsoft.com/en-IN/library/ff772782.aspx#SSMSProcedure

References:
Microsoft Online Books Sql Server 2008

Thanks For Reading this Post!!