How to join two Azure databases on the same Azure Server

Question

I dont understand why I am not finding better info on this? it seems pretty simple request. I just want to join two DB's that are on the same Azure sql server on a select query. Is there an easy way to do this?? Connecting two sql DB's on the same sql server instance is straight forward easy. Why so complicated here??

Answer 1

Good day @Kris carbone ,

Is it really the same as SQL Server on-premises?!?

> it seems pretty simple request

Let me start with this point as I think that it is the most important one. You must understand "why" before you discuss the "how"

So... the point is that this is far from being "simple". The mistake is that people think about SQL Server on-premises and the Azure logical SQL Server as it was the same product which is a HUGE MISTAKE!

When you are speaking about two databases in the same SQL Server on-premises, then you are speaking about two databases which are controlled by the same application under the same machine.

But when we are speaking about two databases under the same Azure logical SQL Server then we are speaking about databases which might be under different servers in totally different machines and they have nothing that they shared between them. In fact, you can even have totally different products under the same logical server - for example under the same Azure Logical Server, you can have Azure Synapse databases (which is based on product name parallel data warehouse) together with Azure Databases (which is based on SQL Server).

Azure Logical SQL Server is NOT a real SQL Server! This is only a "logical application" which wraps the databases under one logical (not physical) entity for the sake of the managing the databases using the Azure application.

> Check the Documentation: In Azure SQL Database and Azure Synapse Analytics, a server is a logical construct that acts as a central administrative point for a collection of databases.

Do you start to understand that JOIN between two Azure databases is not so simple?!?

It is like JOIN between two databases which are not in the same server. Moreover! We do not have any information about the physical servers or machines which host the databases. We have only control on the database level. THIS IS THE WHAT YOU CHOSE TO USE! If you are using Azure Database then you use a database level service and not a server level service. Each database is managed by Microsoft separately as it is a separate service. Like other services, you have some support it combining two or more services and manage them together using a wrapper tool (for example using Azure Elastic Pool) but these are only a logical applications which support these separate services and give you a way to manage them faster. Behind the scenes each entity still managed separately.

OK... I assume that the issue is more clear now about why this is NOT a simple as managing two on-premises databases on the same server. We can now move to the "how", meaning we can now speak about solutions

So, what next?

> I just want to join two DB's that are on the same Azure sql server on a select query. Is there an easy way to do this??

No. There is a way, but it is far from being easy.

As I mentioned above, in many cases Microsoft provides tools and features (wrappers or separate tools) in order to manage multiple services together. This is done in the case of Azure Databases as well.

In order to manage Azure Databases, Microsoft provides several tools and features which named together "Azure Elastic Database Tools". These tools and features include for example Elastic Database pools, Elastic Database jobs, Elastic Database client library, Elastic Database split-merge tool, Elastic Database query, Elastic transactions...

The tool which cover your request named "Azure Elastic Query":

> Check the Documentation: The elastic query feature (in preview) enables you to run a Transact-SQL query that spans multiple databases in Azure SQL Database. It allows you to perform cross-database queries to access remote tables...

The main steps in order to use cross databases queries are:

1. CREATE MASTER KEY
2. CREATE DATABASE SCOPED CREDENTIAL
3. CREATE EXTERNAL DATA SOURCE
4. CREATE EXTERNAL TABLE

Read more!

Get started with cross-database queries: https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical

Get started with Elastic Database Tools: https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-scale-get-started

Scaling out with Azure SQL Database: https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-scale-introduction

Answer 2

To join tables in 2 different databases in On-premise SQL Server is easy and straightforward but it's not simple in Azure SQL DB.

In DB1, you will have to create external table that points to table in DB2.

Please see step by step in below tutorial - azure-sql-cross-database-query

===============================================
If the response helped, do "Accept Answer" and upvote it -- Vaibhav