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

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

2. Department in myDemoDB2
   
   1. USE [myDemoDB2]
   2. GO
   4. /****** Object: Table [dbo].[Department] Script Date: 04/05/2017 15:17:02 ******/
   5. SET ANSI_NULLS ON
   6. GO
   8. SET QUOTED_IDENTIFIER ON
   9. GO
   11. CREATE TABLE [dbo].[Department](
   12. [DeptId] [int] NOT NULL,
   13. [Name] [varchar](50) NULL
   14. )
   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.

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.

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.

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

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

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.