SQL Server: How to create a linked server pointing to a server different from its name

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’.

Where to use?

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 on New 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

It will extract the data from BIMSQL02
server instead of BIMSQL01 server.

Conclusion

By using the above steps, we can create a linked server that point to a server different from its name.

———————————-End
of Document—————————————-

Approach 1 – Using Script to create Linked Server

Approach 2 – Using Linked Server Wizard

Related Posts

Leave a Reply Cancel reply