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?
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
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.
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