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
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.
Execute below queries in AzSQLDB_OLTP
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
In previous posts, I wrote about
- How to create Azure SQL database and server
- How to connect Azure SQL database through SSMS
- Create SQL objects in Azure SQL database
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
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
No comments:
Post a Comment