SQL Azure - Cross-Market Queries

Question

We have two databases: "D1" and "D2". Each database has one table, say T1 in D1 and T2 in D2. Now I want to write a storage procedure in the D1 database that will access the table in the D2 database, something like the code below ...

USE D1
GO
Create or replace sp_test
(
    --other detials 

    SELECT * FROM  D2.dbo.T2
)
GO

Any idea how I can do this?

Note. We used to use both databases on the same server. Therefore, I had no problems. But now, after switching to Azure, I don’t know how we can do this. Also, according to my information, we do not have a related server function available in SQL-Azure.

Answer 1

Azure SQL ( SQL Azure) - , , - ...

Answer 2

SQL Azure V12, , ( , , - , ), sp_bindsession. , . , .

Answer 3

SQL Azure. D1 D1, D2.

-

USE D1
-- Not sure if the following line is necessary. Omit it and if SQL Squeals, put it in.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MasterKeyPassword';
CREATE DATABASE SCOPED CREDENTIAL D2User WITH IDENTITY='D2UserID', SECRET='D2UserPassword';
CREATE EXTERNAL DATA SOURCE D2DataSource
WITH
(
    TYPE=RDBMS,
    LOCATION='d2.database.windows.net',
    DATABASE_NAME='D2',
    CREDENTIAL=D2User
);
CREATE EXTERNAL TABLE [dbo].[T2](
    [ID] [INT] NOT NULL,
    [Info] [NVARCHAR] (25) NULL
)
WITH
(
    DATA_SOURCE = D2DataSource
);

Of course, you must replace all relevant data with your own selected passwords, database locations, etc.