You can’t query across databases in Azure SQL Database… or can you?

Let’s check. I’ve created two new databases on an existing server:

I’ve created two tables on each respective database:

CREATE TABLE dbo.DB1Table ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Val VARCHAR(50) ); CREATE TABLE dbo.DB2Table ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Val VARCHAR(50) );

Now, let’s query the DB2 table from the DB1 database:

SELECT * FROM DB2.dbo.DB2Table AS dt;

And here’s the lovely error message:

Msg 40515, Level 15, State 1, Line 35

Reference to database and/or server name in ‘DB2.dbo.DB2Table’ is not supported in this version of SQL Server.

So, like I said, you can’t do three part name cross-database queries in Azure SQL Database… oh wait, that’s not quite what I said is it. Let’s do this. Let’s create a new security credential within DB1 for a login that can get us into DB2:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'securitymatters'; CREATE DATABASE SCOPED CREDENTIAL DB2Security WITH IDENTITY = 'Grant', SECRET = 'securitymatters';

Then, we’ll use that to define an external data source:

CREATE EXTERNAL DATA SOURCE DB2Access WITH ( TYPE=RDBMS, LOCATION='myservernotyours.database.secure.windows.net', DATABASE_NAME='DB2', CREDENTIAL= DB2Security);

With this, we can put Elastic Query (corrected from Polybase see note below) to work and create an external table:

CREATE EXTERNAL TABLE dbo.DB2Table ( ID int, Val varchar(50)) WITH ( DATA_SOURCE = DB2Access);

And that’s it. If I query dbo.DB2Table from DB1, I get to see data in DB2. In short, you can do a cross database query within Azure SQL Database. Yeah, it’s going to require some setup and possibly some code modifications since you can’t use the old three part naming for performing the query, but, you can do it. Further, note that these are Standard, not Premium databases. Further further, they’re not a part of an elastic pool. It’s just using the external data source and external table to connect the two databases. However, if the one thing keeping you from moving into Azure SQL Database is the ability to query across databases, that’s gone.

Share this: Twitter

Facebook

Reddit

LinkedIn

Tumblr

WhatsApp

Pocket

Email

