Azure SQL – Elastic Queries Across Subscriptions

azuresql server

Is it possible to run cross-database, actually cross-subscription, in Azure SQL?

I have three subscriptions (Dev, Test and Prod) and I need to need to keep Test up to date with Prod.

Unless I'm not reading the many sites correctly they seem to all assume that the databases are in the same subscription albeit possibly different servers.

Best Answer

Yes Elastic queries across subscription is possible.

There are several limitations to this feature. For example:

You must possess ALTER ANY EXTERNAL DATA SOURCE permission. This permission is included with the ALTER DATABASE permission. ALTER ANY EXTERNAL DATA SOURCE permissions are needed to refer to the underlying data source.

Authentication using Azure Active Directory with elastic queries is not currently supported.

Read this for details: Azure SQL Database elastic query overview (preview)

Create 2 resource groups in two different subscriptions. Create one Azure SQL Server and one empty Azure SQL database in each resource group. Then you can follow along with the following code.

I used the following names in my setup:

# The logical server name: Use a random value or replace with your own value (do not capitalize)
$SqlServerName1 = "taiobeqdemo1"
$SqlServerName2 = "taiobeqdemo2"

# The database name
$customerDatabase = "taiobCustomer"
$orderDatabase = "taiobOrders"

--Connect taiobeqdemo1.database.windows.net
--Change database context to taiobCustomer

SET NOCOUNT ON;
CREATE TABLE [dbo].[CustomerInformation](
    [CustomerID] [int] NOT NULL,
  [CustomerName] [varchar](50) NULL,
  [Company] [varchar](50) NULL
CONSTRAINT [CustID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
)
GO
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (1, 'Jack', 'ABC');
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (2, 'Steve', 'XYZ');
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (3, 'Lylla', 'MNO');
GO

--Connect taiobeqdemo2.database.windows.net
--Change database context to taiobOrders

CREATE TABLE [dbo].[OrderInformation](
  [OrderID] [int] NOT NULL,
  [CustomerID] [int] NOT NULL
  )
GO
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (123, 1);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (149, 2);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (857, 2);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (321, 1);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (564, 8);
GO

--Connect taiobeqdemo1.database.windows.net

--Change database context to taiobCustomer

CREATE USER taiobelasticquery WITH PASSWORD = 'taiobelasticque$0';
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'taiobelastic$0';
GO

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
    WITH IDENTITY = 'taiobelasticquery',
    SECRET = 'taiobelasticque$0';  
GO
--Create External data source
CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
    (TYPE = RDBMS,
  LOCATION = 'taiobeqdemo2.database.windows.net',
  DATABASE_NAME = 'taiobOrders',
  CREDENTIAL = ElasticDBQueryCred,
);
GO

CREATE EXTERNAL TABLE [dbo].[OrderInformation]
( [OrderID] [int] NOT NULL,
  [CustomerID] [int] NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc);
GO

--Connect taiobeqdemo2.database.windows.net
--Change database context to taiobOrders

CREATE USER taiobelasticquery WITH PASSWORD = 'taiobelasticque$0'; 
GO
EXEC sp_addrolemember 'db_datareader', 'taiobelasticquery';
GO

Now you are to do run elastic query from taiobeqdemo1.database.windows.net servers taiobOrders database.

--Change context to taiobOrders

SELECT 
    OrderInformation.CustomerID, 
    OrderInformation.OrderId, 
    CustomerInformation.CustomerName, 
    CustomerInformation.Company
FROM OrderInformation
INNER JOIN CustomerInformation
ON CustomerInformation.CustomerID = OrderInformation.CustomerID;
GO
Related Question