Linked Server

The Linked Servers option allows you to connect to another instance of SQL Server running on a different machine, perhaps remotely in a different city/country. This can be useful if you need to perform distributed queries (query a remote database). Setting up a linked server is quite straight forward in SSMS(Sql Server Management Studio), all you need is details of the remote server, and the database that you need to query.


Creating Linked Server :-

1. Click Start, click All Programs,  Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
   
2. In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect.
   
3. In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.
   
   
   
   
   
   
   
4. In the New Linked Server dialog box, on the General page, in Linked server, enter the full network name of the SQL Serveryou want to link to.
   
   
   
   
   
5. Under Server Type ,Click Other Data Source(Sql Server This is server to server  access Purpose).
   
   
   • Linked Server:   Insert the reference name for this linked server. It’s preferable to use a name without spaces in order to avoid problems when using it in an SQL statement.                         
   • Provider Name: Select Micro Soft OLEDB  Provider for Analysis Service if you are trying to connect to the cube.               
   • Product Name: Insert the name of OLE DB data source to add as linked server.                   
   • Data Source: Insert the IP address of your Analysis Service or the name of a data server    
   • Provider String: Insert the connection string with the necessary parameters.
6. Select Security Tab.Choose Security Type
7. You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button. 

     8.Select Server Option Tab .
                    Set the Rpc and Rpc Out parameters to True, and then click OK.

 MSDN Online :  http://msdn.microsoft.com/en-us/library/aa560998(v=BTS.10).aspx




Thanks for Reading......... More to come:)