Cross-Database Queries in Azure SQL Database

Estive migrando alguns bancos on premise para o Azure sql elastic pool, em algumas consultas verifiquei que elas faziam inner join em outros bancos, sabemos que de forma nativa os bancos do Azure SQL são isolados e não suportam o cross-database, desta forma apenas usando o Azure sql elastic pool podemos ter uma opção para fazer nossos selects entre bancos, abaixo seguem informações:

• Tela de erro caso tentem ler alguma tabela de outro banco dentro do elastic pool, neste exemplo estou fazendo um select no banco destino.dbo.tb_aluno.

Desta forma segue uma solução que pode resolver este problema, usando external tables.

1 – Criar login no master.

CREATE LOGIN usr_crossdb WITH PASSWORD='P@ssw0rd';

2 – Devemos criar o usuário que terá acesso a leitura nos dois bancos, origem e destino.

CREATE USER usr_crossdb FOR LOGIN usr_crossdb;

3 – Permissão de leitura nos dois bancos, origem e destino.

EXEC sp_addrolemember 'db_datareader', 'usr_crossdb'

4 – Criar MASTER KEY no servidor de destino.

	
CREATE MASTER KEY ENCRYPTION BY PASSWORD='P@ssw0rd';

5 – Criar a credencial no servidor de destino.

CREATE DATABASE SCOPED CREDENTIAL usr_crossdb -- credential name
WITH IDENTITY = 'usr_crossdb', -- login or contained user name
SECRET = 'P@ssw0rd';-- login or contained user password

6 – Criar o data source de acesso ao banco onde queremos fazer a leitura dos dados.

CREATE EXTERNAL DATA SOURCE ACESSO_REMOTODB
WITH
(
TYPE=RDBMS, -- data source type
LOCATION='srvhmldb.database.windows.net', -- Azure SQL Database server name
DATABASE_NAME='origem', -- database name
CREDENTIAL=usr_crossdb -- credential used to connect to server / database
);

7 – Verificar a ddl da tabela que iremos fazer a leitura, copiar a ddl e adaptar para que seja criado uma external table com os campos e tipo de dados da tabela de origem.

	
create table tb_aluno (matricula bigint identity(1,1),nome varchar(300) not null);

8 – Criar um esquema para diferenciar com o nome do banco de origem para fácil entendimento.

CREATE SCHEMA DB_ORIGEM;

9 – Criar external table apontando para o data source criado usando a ddl da tabela de origem sem identity.

CREATE EXTERNAL TABLE [DB_ORIGEM].[tb_aluno]
(
matricula bigint,
nome varchar(300) not null
)
WITH (DATA_SOURCE = [ACESSO_REMOTODB], -- data source
SCHEMA_NAME = 'dbo', -- external table schema
OBJECT_NAME = 'tb_aluno' -- name of table in external database
);
GO

10 – Select entre os bancos.

select al.matricula,
al.nome,
h.nt1,
h.nt2,
h.nt3
from [DB_ORIGEM].[tb_aluno] al
inner join tb_historico_2008 h
on al.matricula = h.matricula

Select entre os bancos origem e destino.

Bancos usados.