Cross Database Queries In Azure SQL

Step 1

Create two different databases using one of the ways given below in Windows Azure portal (https://portal.azure.com)

Way 1

New - SQL databases - Add.

Way 2

New - Databases - SQL database.

Please fill in the details on the screen given below.

If you do not have resource group, please select Create new.

If you do not have Server, create new Server, using the screen given below.

Please note the Server admin login & Password. This will be required for the further process.

Once you have filled all the required information, please click Create.

Repeat the process given above to create another database.

In this case, we have created two different databases, as shown below.

1. myDemoDB1
2. myDemoDB2
   
   
   

Once the databases are created, you need to set the Firewall rules (either Server level or database level) depending on your requirement.

For setting rules, you can refer to

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure

Step 2

Writing cross database query

Open SQL Server Management Studio and connect to the Server, which we have created, using the Server admin login & Password, which we have used while creating the Server.

You can get the Server name by going to Yourdatabasename - Overview - Server name.

Now, create two different tables in two databases.

1. Employee in myDemoDB1
   

   USE [myDemoDB1]
   GO
   
   /****** Object:  Table [dbo].[Employee]    Script Date: 04/05/2017 15:14:33 ******/
   SET ANSI_NULLS ON
   GO
   
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE TABLE [dbo].[Employee](
       [Id] [int] NOT NULL,
       [FirstName] [varchar](50) NULL,
       [LastName] [varchar](50) NULL,
       [DeptId] [int] NULL
   )
   
   GO

2. Department in myDemoDB2
   

   USE [myDemoDB2]
   GO
   
   /****** Object:  Table [dbo].[Department]    Script Date: 04/05/2017 15:17:02 ******/
   SET ANSI_NULLS ON
   GO
   
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE TABLE [dbo].[Department](
       [DeptId] [int] NOT NULL,
       [Name] [varchar](50) NULL
   )
   
   GO

To access the table from the remote database, we need to create elastic database query.

For this, we need to define an external data source, which will point to the target database. 

CREATE EXTERNAL DATA SOURCE RefmyDemoDB2
WITH
(
    TYPE=RDBMS,
    LOCATION='your server name',
    DATABASE_NAME='myDemoDB2',
    CREDENTIAL= your “Server admin login”
);

The query given above has created a reference to the target database (In this case, it is myDemoDB2).

Now, using the reference given above, we can create an external table reference. 

CREATE EXTERNAL TABLE [dbo].[Department](
    [DeptId] [int] NOT NULL,
    [Name] [varchar] (50) NULL
)
WITH
(
    DATA_SOURCE = RefmyDemoDB2
);

If you have noticed that we have used CREDENTIAL but never specified the password for it, so it’ll give you an error.

Thus, let’s modify our query to include the user credentials

The query given below will create user credentials for you. 

CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin
WITH IDENTITY = 'yourServeradminlogin',
SECRET = 'yourPassword';

Let’s encrypt these credentials, using Master Key Encryption.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';

Finally, our full query will look, as shown below. 

DROP EXTERNAL TABLE [Department]
DROP EXTERNAL DATA SOURCE RefmyDemoDB2
DROP DATABASE SCOPED CREDENTIAL yourServeradminlogin
DROP MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';

CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin
WITH IDENTITY = 'yourServeradminlogin',
SECRET = 'yourPassword';

CREATE EXTERNAL DATA SOURCE RefmyDemoDB2
WITH
(
    TYPE=RDBMS,
    LOCATION='testdbdemoserver.database.windows.net',
    DATABASE_NAME='myDemoDB2',
    CREDENTIAL= yourServeradminlogin
);

CREATE EXTERNAL TABLE [dbo].[Department](
    [DeptId] [int] NOT NULL,
    [Name] [varchar](50) NULL
)
WITH
(
    DATA_SOURCE = RefmyDemoDB2
);


/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *
  FROM [dbo].[Employee] E
  INNER JOIN [dbo].[Department] D
  ON E.DeptId = D.DeptId

The output of the query will look, as shown below.

Please note the Department table under External Tables. This acts as a link between the two different databases.

Now, you can write cross database queries in Azure SQL, using the steps mentioned above.