1

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.

  • For Elastic Database Queries you set up a credential and connect using a SQL Login. I don't think there's any requirement for the Logical Servers to be in the same subscription. – David Browne - Microsoft Jan 4 at 22:19
3

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
|improve this answer|||||
  • +1 solid answer here. There may be better ways to keep your environments in sync, but very possible with what Taiob shared. – Mike Walsh Jan 5 at 13:59
  • Thanks Taiob - very useful – RGI Jan 5 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.