Thursday, April 14, 2016

How to Create a Linked Server

Why we will create linked server?
·         If you want to connect another sql server instance from you instance.
·         When Sql server databases distributed across the network.

What is linked server?
·         OLE DB data source referred as linked server in SQL Server
·         As part of retrieving data from other instances of sql server we will create linked server connection

Steps to follow for creating linked server in SQL Server 2012.
·         There are two ways to create linked server
1.    By using sql server stored procedure sp_addlinkedserver
2.    By using sql server management studio

Using sql server stored procedure sp_addlinkedserver
EXEC sp_addlinkedserver  
   @server=N'ServerName',
   @srvproduct=N'',
   @provider=N'SQLNCLI',
   @datasrc=N'ServerName\DataBaseInstance';

The provider given in above syntax is sql server native client oledb provider when we use sp_addlinkedserver procedure all local logins will be mapped to linked server for security reason we will never do this or we will alter the permission’s to the linked server after creating

Using sql server management studio:-
Login into to sql server management studio there you will find Server objects

Right click on Server objects and select new linked server 
In general page give Linked server name:-
Server type is other data source

Select the provider if another instance you are connection is sql server select Microsoft OLE DB Provider for sql server

Product should not be empty Give nay name SQL Server 2012

Data source is IP/Sql Server instance which is shared to you
xxxx.xxxx.xxxx.xxx/SQL2012

Provider string leave empty

Catalog is database name and optional

Go to security tab and set the [be made using this security context] parameter as below



Remote Login: - user name share to you [remote machine]
With password: - Enter password

And click ok done now you are ready to use.