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.