Retrieving data from two different servers in SQL Server

Question -

How can I select data in the same query/Sp from two different databases that are on two different servers in SQL Server?

Solution 1  - Using Linked servers

Creating a Linked Server using the Object Explorer

• Connect to the SQL Server instance where you want to write the query via SSMS(SQL Server Management Studio) using a SQL login that has sysadmin  permission.  

• In the Object Explorer Window go to the Server Objects -> Linked Servers
  
  
  
• Then the create new linked server wizard will come as seen in the below image. You will be able to see two severer types
  
  •  SQL severer
  •  Other Data source  
  
  

• If your target database is a SQL Server database you have to select "SQL Server" option. Once you select this all the other details will become disabled since they are not applicable for this server type.
•  Put your target database server instance name as the Linked Server name. Remember to put the whole name with the instance name if the target instance is not the  default instance of the server.
  
  Ex - Server Name - "DEV-DARSHANI\MSSQLSERVER16"
  
  If your target database is not a SQL Server database you can choose 'Other Data Source" and configure it accordingly by filling the details requesting. I am not going for more details of this option since here we only consider about two different SQL Servers.

• Switch to security tab to configure the security context for this Linked Sever.
  Here you can map local server logins to remote server logins. And also you will be able to define the default security context for connections over linked server by choosing one of the four options provided. (This will be considered for a login not defined in the list above)
  
• Not be made - If you do not want to consider a login which has not been defined in the above list.
•  Made without using a security context
•  Be made using the login's current security context - This will take the current logged in users security context
•  Be made using this security context - Here you should provide a remote login name and a password
• Click Ok, You will be able to see your newly created linked server in Server Objects -> Linked Servers. You will be able to see remote databases under Catalogs.

Creating a Linked Server using a Stored Procedure

 Adding a Linked Server

EXEC master.dbo.sp_addlinkedserver @server=N'[LinkedServerName]', @srvproduct=N'[DataStore]', @provider=N'[Provider]', @datasrc=N'[DSNName]'

Example  for SQL Server remote database

    - EXEC master.dbo.sp_addlinkedserver @server=N'DEV-DARSHANI\MSSQLSERVER16'

You can avoid  [DataStore],[Provider] and [DSNName]  for SQL Server remote database.

Configuring Security context

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DEV-DARSHANI\MSSQLSERVER16', @useself=N'False', @locallogin=NULL, @rmtuser=N'user1', @rmtpassword=N'p@ssword'

Retrieving data from linked severer

SELECT * FROM [Linked Server Name].[Database Name].[Schema].[Table Name]

SELECT * FROM  [IEPL-DARSHANI\MSSQLSERVER16].Delma.mas.Rates