Viscosity
logo-black
  • Data
    • Database Services
      • Performance Assessment
      • Proactive Healthcheck
      • Database Migration & Consolidation
      • Performance Tuning
    • Microsoft Services
      • Microsoft Azure Services
      • Microsoft SQL Server Services
      • Microsoft Gold Azure Partner
    • Oracle Services
      • Oracle Database 19c
      • RAC
      • GoldenGate
      • Data Guard
      • Oracle & SQL Database
    • Viscosity Remote Services
  • Apps
    • App Dev Services
    • Oracle APEX
    • Viscosity AMP
    • Viscosity-ai
    • Shane-ai
  • Cloud
    • Cloud Services
    • Hybrid Cloud
    • Viscosity Edge Cloud
    • Virtualization & Cloud Expertise
    • Microsoft Azure Services
  • Infrastructure
    • Infrastructure Services
    • Exadata
      • Exadata Resale & Services
    • Oracle Database Appliance
      • Oracle Database Appliance Resale & Services
      • ODA Health Checks
  • Events
    • Upcoming Events
    • Virtual Training
    • 2024 Past Events
    • 2023 Past Events
    • 2022 Past Events
  • About
    • About Us
    • News
    • Blogs
    • Publications
    • Contact
Hit enter to search or ESC to close
ENGAGE WITH US

Azure , Microsoft , SQL , VMware , SQL Server

Configuring Linked Servers Between Two Azure SQL VMs in Different Virtual Networks

By Julio Ayapan
September 19, 2022

Linked Server is a very useful feature that allows you to connect to another database instance from the SQL Server Database Engine. It enables the database instance to read data and execute commands against the remote database using an OLE DB provider. 

 

The remote server can be supported by an OLE DB provider: SQL Server, Oracle, MySQL, etc.  

 

Linked Server is also available in Azure, but there are certain restrictions depending on the source and target database: 

  • SQL Managed Instances can only connect to MS SQL Server databases (On Premise or any other cloud provider). 
  • Linked Servers in Azure SQL VMs are fully supported. It only requires specific network rules to connect two databases successfully. 
  • Azure SQL DB does not support linked servers at all. 
  • All three Azure SQL options can be the target of a Linked Server, assuming the target is visible from the source. 

In this article, I will show you how to successfully set up a linked server between two Azure SQL VMs in different virtual networks.  

 

I’m using the following environment in Azure: 

SQL Virtual Machine 

SQL VM IP Address 

Resource Group 

Virtual Network 

VM Subnet 

Address Space 

VNA-SQL1 

10.0.0.4 

VNARG 

VNARG-vnet 

10.0.0.0/24 

10.0.0.0/16 

VNA-SQL2 

10.1.0.4 

VNARG 

VNARG-vnet1 

10.1.0.0/24 

10.1.0.0/16 

Graphical user interface, application Description automatically generated

 

Configuring VNET Peering 

 

Virtual Network Peering allows connections between virtual networks within the same Azure region via Azure backbone infrastructure. In this architecture, I will use it to allow communication between VNA-SQL1 and VNA-SQL2.  

 

To create the Virtual Network Peering, I will use Azure Cloud Shell, the interactive environment activated from the Azure portal through a web browser.  

 

To start a Cloud Shell console, select the Cloud Shell button on the menu bar at the upper right in the Azure portal: 

 

Graphical user interface, application Description automatically generated

 

Select PowerShell in the left menu, and you will be granted console access: 

 

Graphical user interface, text Description automatically generated

 

The process is straightforward. I will create a Virtual Network Peering for each route in the connection. In this case, one VNet Peering for communication from VNARG-vnet to VNARG-vnet1 and one VNet Peering for communication from VNARG-vnet1 to VNARG-vnet. 

 

Let’s start capturing the current VNet information: 

$vnet1 = Get-AzVirtualNetwork -Name VNARG-vnet -ResourceGroupName VNARG 

$vnet2 = Get-AzVirtualNetwork -Name VNARG-vnet1 -ResourceGroupName VNARG 

 

A picture containing text Description automatically generated

 

Then, I will create the two Virtual Network Peering’s defining the Source (VirtualNetwork) and the Destination (RemoteVirtualNetwork): 

Add-AzVirtualNetworkPeering -Name SQL1toSQL2 -VirtualNetwork $vnet1 -RemoteVirtualNetworkId $vnet2.Id 

Add-AzVirtualNetworkPeering -Name SQL2toSQL1 -VirtualNetwork $vnet2 -RemoteVirtualNetworkId $vnet1.Id 

A picture containing text Description automatically generated

 

 

After configuring both Virtual Network Peering’s, VNA-SQL1 and VNA-SQL2 can communicate with each other. Below is a ping test before and after configuration. 

 

From VNA-SQL1 to VNA-SQL2: 

Text Description automatically generated

 

From VNA-SQL2 to VNA-SQL1: 

Text Description automatically generated

 

Note: By default, Ping (ICMP) is not allowed by Windows Firewall in an Azure SQL VM; you must enable the firewall rule before testing a ping command.  

 

Configuring Linked Server 

Once the communication is set up between both SQL VMs, we can configure the Linked Server.   

 

I will configure VNA-SQL2 as a linked server within VNA-SQL1. 

 

In the first step, I must define a network inbound security rule to allow traffic between VNA-SQL1 and VNA-SQL2. 

 

On the Azure portal, I go to VNA-SQL1 Virtual Machine, Networking, and Add the inbound security rule: 

 

 

 

Use the following data: 

  • Source: Any 
  • Source port ranges: * 
  • Destination: IP Addresses 
  • Destination IP addresses/CIDR ranges: 10.1.0.0/24   ß VNARG-vnet1, where VNA-SQL2 is located. 
  • Service: MS SQL 
  • Destination port ranges: 1433 
  • Protocol: TCP 
  • Action: Allow 
  • Name and Priority: may be any.  

Next, I can configure a linked server by using SQL Server Management Studio or the sp_addlinkedserver (Transact-SQL) statement to create the linked server. If you want to use SSMS in your DB connection, go to Server Objects, Linked Servers. Right click on Linked Servers and “New Linked Server.” Fill the wizard with the desired options to create the connection to the target DB. 

 

I’m using a script to create the linked server; I will briefly explain the used server options. The same options are available from the New Linked Server wizard. From VNA-SQL1, I execute:

USE [master] 
GO 
EXEC master.dbo.sp_addlinkedserver @server = N'10.1.0.4', @srvproduct=N'SQL Server' 
 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'collation compatible', @optvalue=N'false' 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'data access', @optvalue=N'true' 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'dist', @optvalue=N'false' 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'pub', @optvalue=N'false' 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'rpc', @optvalue=N'false' 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'rpc out', @optvalue=N'false' 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'sub', @optvalue=N'false' 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'connect timeout', @optvalue=N'0' 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'collation name', @optvalue=null 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'lazy schema validation', @optvalue=N'false' 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'query timeout', @optvalue=N'0' 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'use remote collation', @optvalue=N'true' 
GO 
EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'remote proc transaction promotion', @optvalue=N'true' 
GO 
USE [master] 
GO 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'10.1.0.4', @locallogin = N'vna', @useself = N'True' 
GO 
USE [master] 
GO 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'10.1.0.4', @locallogin = N'VNA-SQL1\vnaadmin', @useself = N'False', @rmtuser = N'vna', @rmtpassword = N'k90#iU834X1y!' 
GO 
USE [master] 
GO 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'10.1.0.4', @locallogin = NULL , @useself = N'False' 
GO 
 
In the first line, I’m adding the SQL VM VNA-SQL2 (10.1.0.4) as SQL Server Remote Server. 

Then, I define the options: 

  • Collation Compatible: instruct SQL Server to evaluate comparisons on character columns locally.
  • Data Access: allows access to the remote server.  
  • Dist, Pub, and Sub: Distributor, Publisher, and Subscriber are not defined. They help us to define a Distributor and Publisher in a Replication configuration. 
  • RPC and RPC Out: these options allow Remote Store Procedures calls. False means that we won’t be able to execute SP on the linked server. 
  • Connect Timeout: Defines the maximum number of seconds for a remote server timeout. If 0, the Remote Login Timeout config option of the SQL Server Instance will be used. 
  • Collation Name: Name of the collation of the remote server.  
  • Lazy Schema Validation: if false, SQL Server checks for schema changes that have occurred since compilation in remote tables. This check occurs before query execution. If there is a change in the schema, SQL Server recompiles the query with the new schema. If this option is set to true, schema checking of remote tables is delayed until execution. 
  • Query Timeout: time in seconds before a query expires. If 0, the query will run until it is complete. 
  • Remote Proc Transaction Promotion: When this option is false, local transactions will not be promoted to distributed transactions. 

Finally, I’m mapping the local logins as follows: 

 

Local Login 

Impersonate 

Remote Login 

VNA 

True 

VNA 

SQL1\vnaadmin 

False 

VNA 

 

Notes:  

  • When setting Impersonate as true, the same login must exist in the remote instance with the same password. 
  • Linked Server will be available only for the local logins I’m using in the mapping area. VNA and SQL1\vnaadmin. 

Testing the Linked Server 

The linked server will appear in the folder: Server Objects, Linked Servers, Server Name. The available databases in the linked server will appear inside “Catalogs.” 

 

Graphical user interface, application Description automatically generated

 

 
Note: The linked Server name appears as VNA-SQL2.PRIVATE.VNA.COM (instead of the IP address 10.1.0.4) because I’m using a Private DNS Resolver Service (more info on: How to Configure a Private DNS Resolver)  
 
 

The VNA-SQL2 server has a database called VNADEMO, a locally executed query is shown below: 

Graphical user interface Description automatically generated

I will execute the same query from the VNA-SQL1 server to confirm that the linked server is working.   

 

The syntax to execute statements in a linked server is: 

select [columns] from [linkedserver].[database_name].[schema_Name].[object_name]  

 

Graphical user interface, application, Word Description automatically generated

 

 

Linked server is working fine between the two VMs. On Azure, Linked servers are available in SQL Server Database Engine and Azure SQL Managed Instance. They are not enabled in Azure SQL Database singleton and elastic pools.  

 

Additional notes: 

  • Linked server offers the ability to access data from outside of SQL Server. From Azure, there are several methods to reach the target database: Azure Express Routes, Azure VPN solutions, Private Endpoints, etc. 
  • Azure Manage Instances have some restrictions when we work with Azure AD Authentication and Linked Servers (for more information, check Linked Servers). 

 

 

 
All posts
About Author
Julio Ayapan

Julio is a Senior DBA for Viscosity North America, with over 9 years of experience in multiple virtualizations, software development, and database technologies across different industries. He is an expert in database administration, analytics, operating systems, and cloud technologies. Julio is an Oracle Certified Professional and Expert, actively participates in Oracle-related forums, and maintains a Personal Technology blog.

You might also like
Configuring an Azure Private DNS Resolver Service Using PowerShell
Configuring an Azure Private DNS Resolver Service Using PowerShell
September 19, 2022
Viscosity is Hosting its First Event in Latin America
Viscosity is Hosting its First Event in Latin America
September 19, 2022
SUBMIT YOUR COMMENT
logo for footer

Viscosity's core expertise includes:

Data Transformation, Emerging Technology, High Availability & Scalability Solutions, Cloud Migrations, Performance Tuning, Data Integrations, Machine Learning, APEX Development, and Custom Application Development.


Solutions

Resources

Partnerships

Careers

Clients

 

Contact
Email: sales@viscosityna.com

Telephone:
(469) 444-1380

Address:
3016 Communications Pkwy Suite 200, Plano, TX 75093
Copyright 2024. All Rights Reserved by Viscosity North America.