kohera-logo-regular.svg

How To: Azure SQL DB Cross database queries using synonyms

When helping a fellow dba on dba.stackexchange.com, I came across an interesting subject. What about synonyms in Azure SQL DB? We know that synonyms that stay in the current database scope work, but how do we get cross db synonyms to work?

Cross database synonyms are not directly supported in Azure SQL DB (Workaround in next step)

I created two Azure SQL DB’s to prove this:

Error when trying to run this query at the stackoverflow database:

Use StackOverflow
CREATE SYNONYM Test  
FOR [OtherDB].dbo.test;  
GO  

Result:

Msg 40515, Level 15, State 1, Line 16 Reference to database and/or server name in ‘OtherDB.dbo.test’ is not supported in this version of SQL Server.

Show me the money: the workaround

What you can do however, is create an external table for that secondary database as long as it is in the same server, and create a synonym for the external table as to facillitate cross db queries!

Step 1: Create a master key in database1 (stackoverflow)

CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'STRONG123P@SSWORD'

Step 2: Create a database scoped credential (naming was a bit sloppy, apologies).

  Important here is that you use the exact! password for the account, vertora in this example.

CREATE DATABASE SCOPED CREDENTIAL nah WITH IDENTITY = 'vertora',
SECRET = 'MyAdminAccountStrongP@ssword';
;

Step 3: Create an external data source to the other database

CREATE EXTERNAL DATA SOURCE OtherDB
WITH
(
    TYPE=RDBMS,
    LOCATION='vertoraperformancetuning.database.windows.net',
    DATABASE_NAME='OtherDB',
    CREDENTIAL= nah
);

Step 3.1: Query executed on the other database (testing purposes)

 

Step 4: create an external table

CREATE EXTERNAL TABLE dbo.test(
id int ,
value nvarchar(255)
)
Step 5: Create a synonym for the table
Use StackOverflow CREATE SYNONYM Test123 FOR dbo.test;
GO

Step 6: Succes!

select * from dbo.Test123

Another route you could go is with managed instances, but that is not in the scope of this blogpost ;).

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...