Over a million developers have joined DZone.

Cross Database Query in Azure SQL Database

Query across databases is not a limitation of Azure, but it has to be done with a little finesse.

· Cloud Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database on AWS. Try it now! Brought to you in partnership with MongoDB.

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:

dblist

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 Polybase 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. And, even 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.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:
database ,dba ,azure

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}