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.

Azure

Way 2

New - Databases - SQL database.

Azure

Please fill in the details on the screen given below.

Azure

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.

Azure

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

    Azure

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.

Azure

Now, create two different tables in two databases.

  1. Employee in myDemoDB1
    1. USE [myDemoDB1]  
    2. GO  
    3.   
    4. /****** Object:  Table [dbo].[Employee]    Script Date: 04/05/2017 15:14:33 ******/  
    5. SET ANSI_NULLS ON  
    6. GO  
    7.   
    8. SET QUOTED_IDENTIFIER ON  
    9. GO  
    10.   
    11. CREATE TABLE [dbo].[Employee](  
    12.     [Id] [intNOT NULL,  
    13.     [FirstName] [varchar](50) NULL,  
    14.     [LastName] [varchar](50) NULL,  
    15.     [DeptId] [intNULL  
    16. )  
    17.   
    18. GO  
  1. Department in myDemoDB2
    1. USE [myDemoDB2]  
    2. GO  
    3.   
    4. /****** Object:  Table [dbo].[Department]    Script Date: 04/05/2017 15:17:02 ******/  
    5. SET ANSI_NULLS ON  
    6. GO  
    7.   
    8. SET QUOTED_IDENTIFIER ON  
    9. GO  
    10.   
    11. CREATE TABLE [dbo].[Department](  
    12.     [DeptId] [intNOT NULL,  
    13.     [Name] [varchar](50) NULL  
    14. )  
    15.   
    16. 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. 

  1. CREATE EXTERNAL DATA SOURCE RefmyDemoDB2  
  2. WITH  
  3. (  
  4.     TYPE=RDBMS,  
  5.     LOCATION='your server name',  
  6.     DATABASE_NAME='myDemoDB2',  
  7.     CREDENTIAL= your “Server admin login”  
  8. );   

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. 

  1. CREATE EXTERNAL TABLE [dbo].[Department](  
  2.     [DeptId] [intNOT NULL,  
  3.     [Name] [varchar] (50) NULL  
  4. )  
  5. WITH  
  6. (  
  7.     DATA_SOURCE = RefmyDemoDB2  
  8. );    

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

Azure

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

The query given below will create user credentials for you. 

  1. CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin  
  2. WITH IDENTITY = 'yourServeradminlogin',  
  3. SECRET = 'yourPassword';   

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

  1. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';   

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

  1. DROP EXTERNAL TABLE [Department]  
  2. DROP EXTERNAL DATA SOURCE RefmyDemoDB2  
  3. DROP DATABASE SCOPED CREDENTIAL yourServeradminlogin  
  4. DROP MASTER KEY  
  5.   
  6. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';  
  7.   
  8. CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin  
  9. WITH IDENTITY = 'yourServeradminlogin',  
  10. SECRET = 'yourPassword';  
  11.   
  12. CREATE EXTERNAL DATA SOURCE RefmyDemoDB2  
  13. WITH  
  14. (  
  15.     TYPE=RDBMS,  
  16.     LOCATION='testdbdemoserver.database.windows.net',  
  17.     DATABASE_NAME='myDemoDB2',  
  18.     CREDENTIAL= yourServeradminlogin  
  19. );  
  20.   
  21. CREATE EXTERNAL TABLE [dbo].[Department](  
  22.     [DeptId] [intNOT NULL,  
  23.     [Name] [varchar](50) NULL  
  24. )  
  25. WITH  
  26. (  
  27.     DATA_SOURCE = RefmyDemoDB2  
  28. );  
  29.   
  30.   
  31. /****** Script for SelectTopNRows command from SSMS  ******/  
  32. SELECT *  
  33.   FROM [dbo].[Employee] E  
  34.   INNER JOIN [dbo].[Department] D  
  35.   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.

Azure

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