Sunday, September 30, 2012

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


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$'

 GO

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

7 comments:

  1. I would like to say that this blog really convinced me to do it! Thanks, very good post. linkedin ads tips

    ReplyDelete
  2. Web applications might involve client provided contribution to make custom SQL explanations for dynamic website page demands.
    https://onohosting.com/

    ReplyDelete
  3. They will be answerable for help of the database they planned on the off chance that one isn't. Other than this distinction the ranges of abilities are basically the same, without a doubt most database courses see no difference amongst the two jobs.https://hostinglelo.in/

    ReplyDelete
  4. The volume on the hard drive should likewise be respected while haggling with a rental server supplier as a SSD drive is respected much preferred for speedier execution over an ordinary hard drive. Minecraft Servers

    ReplyDelete
  5. Players must navigate an infinitely expanding world of cubes and collect blocks of various materials.Minecraft Server List

    ReplyDelete
  6. Uttar Pradesh Madhyamik Shiksha Parishad (UPMSP) which Conducts the Examination of High School in Month of March. Supplementary Examination Conducts in month of Jun. UPMSP Annually and Private and Regular Students Participate in High School Examination every year more than laks of students. UPMSP Inermediate Question Paper 2023 UP Board 12th Class Model Paper 2023 now available on the official website, UPMSP has been Announced 12th Examination (secondary school level examination) Public Examination Programme 2023 soon, Students can Download UP Board 12th Class Previous Question Paper 2023 which is help in your study, and support in getting more marks in public exam.

    ReplyDelete
  7. Finding the right DMCA Ignored hosting provider is tricky because it is offshore and operated by discreet organizations. This is because, as the name suggests, these hosting providers ignore any DMCA takedown requests of their clients’ websites.

    ReplyDelete