Azure SQL Database elastic query

In this post, let us see how we can achieve cross database querying between two azure sql databases that resides in same server.

In previous posts, I wrote about 

Going through above topics will be helpful before creating an elastic query.
An elastic query can be used to make data located in a Azure SQL database available to other Azure SQL databases. 


I have created two Azure sql databases AzSQLDB & AzSQLDB_OLTP in same server azsqldbserv as shown below:






Creating Azure sql database and server



 Configuring elastic database pool while creating database:




 Once all the validations are successful, create database:




 We have an option to query the above created Azure sql database in the cloud itself, through data explorer option:





 But let us use, newly introduced SQLOPS tool to create objects and elastic query:


 Provide the IP and save the firewall rule to connect Azure sql database from SQLOPS tool:




 Then I have created a table called Test in AzSQLDB which can be referenced in AzSQLDB_OLTP after establishing elastic query set up.




DROP TABLE IF EXISTS Test
CREATE TABLE Test (Id INT, Name VARCHAR(25))
INSERT Test SELECT 1,'Sathya'
INSERT Test SELECT 2,'Shalu'






Execute below queries in AzSQLDB_OLTP




CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Azkey1234';  
--Create database scoped credential with sql server authentication
CREATE DATABASE SCOPED CREDENTIAL SQL_Credential    
WITH IDENTITY = 'Sathya',  
SECRET = '';  

--Create external data source
CREATE EXTERNAL DATA SOURCE ElasticDBQueryDataSrc WITH   
    (TYPE = RDBMS,   
    LOCATION = 'azsqldbserv.database.windows.net',   
    DATABASE_NAME = 'AzSQLDB',   
    CREDENTIAL = SQL_Credential   
) ;   

--Create external table
CREATE EXTERNAL TABLE Test (Id INT, Name VARCHAR(25))
WITH 
( DATA_SOURCE = ElasticDBQueryDataSrc) 




Now if we connect to Azure SQL database server from SSMS, in the Object Explorer we can see the External table and data source.




Now lets refer the table that resides in AzSQLDB from AzSQLDB_OLTP




As you can see in above screenshot, execution plan shows physical operation as Remote query.

I tried one more thing:
I was not able to create a table called Test in AzSQLDB_OLTP, but I was able to create under different schema




Elastic pools help you manage and scale multiple Azure SQL databases


Reference:


See Also: 



No comments: