Azure SQL Database – Como fazer um join entre tabelas de bases diferentes? – Fabricio Lima

Azure SQL Database – Como fazer um join entre tabelas de bases diferentes?

12 de julho de 201817 de julho de 2018Fabrício LimaAzure SQL Database, Managed Instance, SQL Azure, Virtual PASS BR

Fala Pessoal,

Continuando a série de posts sobre o Azure SQL Database, no meu dia a dia de Consultorias é bem comum ver sistemas que utilizam mais de uma base de dados para realizar operações.

E no Azure SQL Database? Isso funciona?

Vamos testar juntos?

Criei duas bases (database01 e database02) no meu portal do azure conforme pode ser visto abaixo:

Em seguida criei uma tabela em cada uma dessas bases de dados e populei com algumas informações:

Database01:

CREATE TABLE sqlfamily(
 ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
 Nome VARCHAR(50),
 Mais_Conhecido_Como VARCHAR(100)
)
 
INSERT INTO sqlfamily(Nome, Mais_Conhecido_Como) 
VALUES 
 ( 'Fabricio Lima' ,'Fabricio Lima'),
 ( 'Fabiano Amorim' ,'Expert em vídeos fakes de futebol'),
 ( 'Luiz Gareth','Morango do Nordeste' ),
 ( 'Arthur Luz', 'Vagalume' ),
 ( 'Edvaldo Castro', 'Oldvaldo Castro' )

CREATE TABLE sqlfamily(

ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY,

Nome VARCHAR(50),

Mais_Conhecido_Como VARCHAR(100)

)

INSERT INTO sqlfamily(Nome, Mais_Conhecido_Como)

VALUES

( 'Fabricio Lima' ,'Fabricio Lima'),

( 'Fabiano Amorim' ,'Expert em vídeos fakes de futebol'),

( 'Luiz Gareth','Morango do Nordeste' ),

( 'Arthur Luz', 'Vagalume' ),

( 'Edvaldo Castro', 'Oldvaldo Castro' )

Database02:

CREATE TABLE sqlfamily(
 ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
 Nome VARCHAR(50),
 Hobby VARCHAR(100))
 
INSERT INTO sqlfamily(Nome, Hobby) 
VALUES 
( 'Fabricio Lima' ,'Futebol, Corrida, Seriados, Viajar, Rock com a galera, ...'),
( 'Fabiano Amorim' ,'Desafinar uma guitarra...'),
( 'Luiz Gareth','Tomar aquela batida de morango...' ),
( 'Edvaldo Castro', 'Jogar dominó na praça...' ),
( 'Arthur Luz', 'Ilimunar Ambientes...' )

CREATE TABLE sqlfamily(

ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY,

Nome VARCHAR(50),

Hobby VARCHAR(100))

INSERT INTO sqlfamily(Nome, Hobby)

VALUES

( 'Fabricio Lima' ,'Futebol, Corrida, Seriados, Viajar, Rock com a galera, ...'),

( 'Fabiano Amorim' ,'Desafinar uma guitarra...'),

( 'Luiz Gareth','Tomar aquela batida de morango...' ),

( 'Edvaldo Castro', 'Jogar dominó na praça...' ),

( 'Arthur Luz', 'Ilimunar Ambientes...' )

Após criar e popular as duas tabelas com mesmo nome, mas em bases diferentes, vamos tentar fazer um join nelas para ver o que acontece:

Vixe… Deu ruim… E agora Fabrício???

Pois é… No Azure SQL Database não temos suporte para realizar um CROSS database igual realizamos em nossas instâncias On-Premise. Só no Azure SQL Managed Instance que isso será possível.

Mas… Contudo…Entretanto…Todavia…

Conseguimos chegar perto disso com a utilização de EXTERNAL TABLES.

Para fazer isso, vamos executar TODOS os comandos abaixo na database01.

Primeiro temos que criar uma master key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'senhadificil->123456'

1	CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'senhadificil->123456'

Quando criei a database02, eu também criei um login chamado dba_admin para acessar essa base.

Dito isso, vamos criar uma credencial para acessar a database02 com esse login:

CREATE DATABASE SCOPED CREDENTIAL Db02Credential WITH IDENTITY = 'dba_admin', SECRET = 'senha_user_dba_admin'

1	CREATE DATABASE SCOPED CREDENTIAL Db02Credential WITH IDENTITY = 'dba_admin', SECRET = 'senha_user_dba_admin'

O próximo passo é criar um DATA SOURCE que vamos utilizar para acessar o servidor lógico e a database02. Utilizamos a credencial do passo anterior:

CREATE EXTERNAL DATA SOURCE Db02DataSource WITH
(TYPE = RDBMS, --Query
LOCATION = 'srvfabriciolimasolucoes.database.windows.net', --Servidor logico no azure onde está a base database02
DATABASE_NAME = 'database02',	--base que queremos acessar com a credencial Db02Credential
CREDENTIAL = Db02Credential		--Db02Credential - credencial que criamos para acessar a base database02
) ;

CREATE EXTERNAL DATA SOURCE Db02DataSource WITH

(TYPE = RDBMS, --Query

LOCATION = 'srvfabriciolimasolucoes.database.windows.net', --Servidor logico no azure onde está a base database02

DATABASE_NAME = 'database02', --base que queremos acessar com a credencial Db02Credential

CREDENTIAL = Db02Credential --Db02Credential - credencial que criamos para acessar a base database02

) ;

Finalmente vamos criar nossa EXTERNAL TABLE:

CREATE EXTERNAL TABLE dbo.sqlfamily_ext(
	--ID int,  --Conseguimos retornar menos colunas em uma external table. Ela fica parecida com uma view. Não vou usar a coluna ID.
	Nome VARCHAR(50), --se criar a coluna na external table com um tipo de dados diferente, da erro.
	Hobby VARCHAR(100)
)
WITH
(
DATA_SOURCE = Db02DataSource,
  SCHEMA_NAME = 'dbo', --Schema da tabela na database02
  OBJECT_NAME = 'sqlfamily' --Nome da tabela que eu quero referenciar na database02
);

CREATE EXTERNAL TABLE dbo.sqlfamily_ext(

--ID int, --Conseguimos retornar menos colunas em uma external table. Ela fica parecida com uma view. Não vou usar a coluna ID.

Nome VARCHAR(50), --se criar a coluna na external table com um tipo de dados diferente, da erro.

Hobby VARCHAR(100)

)

WITH

(

DATA_SOURCE = Db02DataSource,

SCHEMA_NAME = 'dbo', --Schema da tabela na database02

OBJECT_NAME = 'sqlfamily' --Nome da tabela que eu quero referenciar na database02

);

Essa EXTERNAL TABLE é como se fosse um objeto da base database01, tanto que não conseguimos criar ela com o mesmo nome sqlfamily. Tive que colocar um sufixo “_ext” no nome para diferenciar o nome dos objetos.

Podemos conferir os objetos que criamos nas DMV’s abaixo:

SELECT * FROM sys.symmetric_keys
select * from sys.external_data_sources; 
select * from sys.database_scoped_credentials
select * from sys.external_tables;

SELECT * FROM sys.symmetric_keys

select * from sys.external_data_sources;

select * from sys.database_scoped_credentials

select * from sys.external_tables;

Criada nossa External Table, agora conseguimos usar ela para acessar a tabela sqlfamily da database02.

Nosso JOIN fica da seguinte forma:

SELECT A.nome,
	   A.Mais_Conhecido_Como,
	   B.Hobby 
FROM database01.dbo.sqlfamily A
	JOIN database01.dbo.sqlfamily_ext B ON A.Nome = B.nome

SELECT A.nome,

A.Mais_Conhecido_Como,

B.Hobby

FROM database01.dbo.sqlfamily A

JOIN database01.dbo.sqlfamily_ext B ON A.Nome = B.nome

Agora conseguimos acessar a database02 e pegar a informação da coluna Hobby:

Essa EXTERNAL TABLE é estática Fabrício? Se eu fizer novos inserts na database02 eles vão ser retornados automaticamente?

A external table é como se fosse um Linked Server para a database02. Ou seja, novos inserts serão retornados normalmente por esse join.

Vamos inserir uma linha em cada base para conferir.

--Rodar na database01
INSERT INTO sqlfamily(Nome, Mais_Conhecido_Como) 
VALUES  ( 'Diego Nogare' ,'NogareDBA')

--Rodar na database02
INSERT INTO sqlfamily(Nome, Hobby) 
VALUES  ( 'Diego Nogare' ,'Fazer shrink em arquivos MDF...')

--Rodar na database01

INSERT INTO sqlfamily(Nome, Mais_Conhecido_Como)

VALUES ( 'Diego Nogare' ,'NogareDBA')

--Rodar na database02

INSERT INTO sqlfamily(Nome, Hobby)

VALUES ( 'Diego Nogare' ,'Fazer shrink em arquivos MDF...')

Executando o JOIN novamente, nossas linhas que foram inseridas nas duas databases já são retornadas:

Legal Fabrício. Eu consigo fazer um insert nessa EXTERNAL TABLE?

Resposta:

Não conseguimos fazer um insert. Ela é utilizada apenas para consultas.

E a performance disso Fabrício? É boa?

Excelente pergunta.

Você já deve imaginar a resposta, mas vamos deixar os detalhes para um próximo post. Aguardem!

Posts Relacionados:

Gostou da dica?

Curta, comente, compartilhe com os coleguinhas…

Assine meu canal no Youtube e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

Abraços,

Fabrício Lima

Microsoft Data Platform MVP

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006

Azure SQL Database – Como fazer um join entre tabelas de bases diferentes?

Curtir isso:

Relacionado

Deixe uma respostaCancelar resposta

Compartilhe isso:

Curtir isso:

Relacionado

Deixe uma respostaCancelar resposta