Cross Database Queries in Azure

Question

Is there a way to query data from another database in Azure

Answer 1

Yes, in fact, there is a way...

First you need to create a master key with credentials. Run this script on the database you will be running query on (that needs to access the other database):

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master_key_password>'; 
CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred 
WITH IDENTITY = '<username>', 
SECRET = '<password>';

The "master_key_password" is a strong password of your choosing used to encrypt the connection credentials. The "username" and "password" should be the username and password used to log in into the target database that you want to query from.

Next you need to create an external data source. Run this script on the same database you will be running query on:

CREATE EXTERNAL DATA SOURCE MyElasticDataSrc WITH
    (TYPE = RDBMS,
    LOCATION = '<server_name>.database.windows.net',
    DATABASE_NAME = '<target_database_name>',
    CREDENTIAL = ElasticDBQueryCred,
) ;

Replace the location with the SQL server that you are trying to query from (It doesn't have to be same server as you are running this query on). Replace the database name with the name of the database you are querying from.

Next you have to create an "external table" in your database that you are running query from for each table that you want to query from in other database. The column definitions need to match the other table. Here's a sample query:

CREATE EXTERNAL TABLE [dbo].[_BackgroundChecks] (
    [PersonId] [int] NOT NULL,
    [Date] [nvarchar](50) NULL,
    [Document] [varbinary](max) NULL)
WITH
( DATA_SOURCE = MyElasticDataSrc)

You can then run a query from your current database to get data from the other database. Example:

SELECT top 10 *
FROM _backgroundChecks

Disclaimer: I pretty much just plagiarized this article:
https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical?view=azuresql