Background
The purpose of this article is to provide a step-by-step method of creating a linked server that point to a server different from its name. Configuring a linked server in this manner is very handy when we need to execute SQL scripts containing linked server names in our different environments.
Suppose there are servers ‘A’ and ‘B’. You want to make server ‘A’ as linked server on your machine. Once completed the configuration of linked server for server ‘A’. It should be points to itself (Server ‘A’) only. But instead of pointing the same server (server ‘A’), you can also point it to some other server (server ‘B’). If you point the Linked server ‘A’ to server B‘, then Linked server ‘A’ will extract all the data from server ‘B’.
This technique is very useful while working in different environments (dev, test, pre-production) with scripts having linked server names added at different places.
Let’s assume that our scripts have linked server name referring to a production server mentioned at many different places. We may not be able to test the script with respect to the production server. Also every time changing the code (e.g.renaming the linked server for testing) is not good. In this scenario, we can use our technique to create a linked server (may be our production server) that points to our development server but with a name same as the production server. Thus, we will be able to use the same script in different environments without changing the linked server names.
What is a Linked Server?
Linked Server is a mechanism in SQL Server by which we can add other SQL Server to a Group on a different SQL Server instance and query both the SQL Server DBs using T-SQL Statements. A linked server definition specifies an OLE DB provider and an OLE DB data source. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.
Linked Server Configuration
Approach 1 – Using Script to create Linked Server
/****** Object: LinkedServer ******/
--New Linked Server with Windows Authentication
EXEC master.dbo.sp_addlinkedserver
--Provide a Name for Linked server by which we want to create it@server = N'BIMSQL01',
@srvproduct=N'SQL Native Client',
@provider=N'SQLNCLI',
/*Name of the server that we want to point. Please note that the name of the linked server and the actual server it points to are different.*/
@datasrc=N'BIMSQL02',
@provstr=N'Provider=SQLOLEDB.1; /*complete connection string for a server that we want to point*/
Integrated Security=SSPI;
Persist Security Info=False;Initial Catalog=ITSDB;
Data Source=BIMSQL02'
--If you have SQL login, provide credentials else make it as NULL.
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'BIMSQL01', --Name for Linked server
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'sa',
@rmtpassword='Q!W@E#R$'
Approach 2 – Using Linked Server Wizard
Follow the steps below for configuring the linked server that points to a server different from its name:
1) For creating the linked server, go to Start, All Programs, Microsoft SQL Server 2008, SQL Server Management studio. It will open connection window. Check the server name & connect it.
2) In Management studio, go to Server Objects, Linked Servers. Right click on the Linked server folder and click onNew Linked Server…
3) It will open the Linked server configuration window.
4) For configuring the linked server, fill the following required information about the linked server:
Ø Linked server: Provide the name with which you are going to refer the linked server. For Ex: BIMSQL01
Ø Server Type: Other data source
Ø Provider: SQL Native Client
Ø Product: SQL Native Client
Ø Data Source: The data source is usually the name of the database server. So, here you need to give the name of the actual server (BIMSQL02) on which you want to query data remotely. Please note that the name of the linked server and the actual server it points to are different.
Ø Provider string: Here you need to provide the complete connection string for the server (BIMSQL02). The format of connection string is shown below:
Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=ITSDB; Data Source=BIMSQL02
Ø Catalog: It is optional field. You can mention the database name here.
Ø Go to Security tab
Here you need to choose the security context in order to make connection with the linked server. For this document, we are using windows authentication. For windows authentication, you can choose connection will “Be made using the logins current security context”.
For SQL Server authentication, we need to choose “Be made using this security context” and provide the required SQL login and password.
Ø After giving the complete information, click Ok. It will create the Linked server named ‘BIMSQL01’ that will actually extract the data from BIMSQL02 server.
Ø For checking the Linked server, go to Server objects, Linked server, Expand the linked server folder. It enlists the entire linked servers. Verify that ‘BIMSQL01’ should be in the list.
Ø For testing the linked server connection, right click on the linked server (BIMSQL01) and click Test Connection.
Ø Test the data retrieval using T-SQL:
SELECT * FROM BIMSQL01.ITSDB.dbo.Region
----------------------------------End of Document----------------------------------------
Understanding about LINKED SERVER In SQL
Linked Server in nothing but combining two different Server database together and perform a single query or joining table objects from different database of different server.
For Example:
You have Server-A with Database-A and Server-B with Database-B in different geographical locations. Now you want to make a SQL statement by JOINING Table-A from Server-A / Database-A and Table-B from Server-B/Database-B. to do this you need to configure linked server configurations.
Configuring Linked Server.
Syntax
sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]
Suppose You are currently logged in at SERVER-A
USE master
GO
EXEC sp_addlinkedserver
@server=N'S1_instance1', -- Linked Server Name
@srvproduct=N'',
@provider=N'SQLNCLI', -- Provider Name
@datasrc=N'Server-B'; -- SQL Server Instance Name
Provider Details:
SQL Server
|
Microsoft SQL Server Native Client OLE DB Provider
|
SQLNCLI
|
Oracle
|
Microsoft OLE DB Provider for Oracle
|
MSDAORA
|
It can connect any server supporting Microsoft OLEDB Provider.
Linked Server Login
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'Server-B',
@useself = 'TRUE',
@locallogin = 'Domain\Mary',
@rmtuser = 'mysqllog',
@rmtpassword = 'rmtpassword'
To Drop linked Server Login
sp_droplinkedsrvlogin [ @rmtsrvname= ] 'rmtsrvname' ,
[ @locallogin= ] 'locallogin'
Execute SQL
SELECT a.Roll, a.StudentName
FROM Server-B.Dtabase-B.dbo.Table-B
