How to run a query that will span multiple databases in Azure SQL Databases:
Execute on Azure SQL Database Source Server in User DB. (The Server & DB that the main query will execute in)
Step 1:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
Step 2:
CREATE DATABASE SCOPED CREDENTIAL <InsertCredentialNameHere> WITH IDENTITY = '', SECRET = ''
Step 3:
CREATE EXTERNAL DATA SOURCE <InsertElasticQueryNameHere>
WITH (
TYPE = RDBMS,
LOCATION = 'xyz.database.windows.net',
DATABASE_NAME = '<InsertTargetDBNameHere>',
CREDENTIAL = <InsertCredentialNameHere>
);
--Confirm
select * from sys.external_data_sources;
Step 4:
CREATE EXTERNAL TABLE [dbo].[INVOICES](
[InvoiceID] [int] NOT NULL,
...
)
WITH
(
DATA_SOURCE = <InsertElasticQueryNameHere>
);
--Confirm
select * from sys.external_tables;
Execute in Azure SQL Database Target Server:
--Execute in Target Master:
CREATE LOGIN xyz WITH PASSWORD = ''
GO
--Execute in Target User DB: (The physical location of the external / references table(s))
CREATE USER xyz FOR LOGIN xyz
GO
EXEC sp_addrolemember 'ud_xyz', 'xyz'
Execute on Azure SQL Database Source Server in User DB:
Select * from tablename --calls the external table
From Microsoft:
"The elastic database query feature (in preview) enables you to run a Transact-SQL query that spans multiple databases in Azure SQL Database (SQLDB). It allows you to perform cross-database queries to access remote tables, and to connect Microsoft and third party tools (Excel, PowerBI, Tableau, etc.) to query across data tiers with multiple databases. Using this feature, you can scale out queries to large data tiers in SQL Database and visualize the results in business intelligence (BI) reports."
"After running the DDL statements, you can access the remote table “mytable” as though it were a local table. Azure SQL Database automatically opens a connection to the remote database, processes your request on the remote database, and returns the results."
Resources:
Microsoft Azure
Azure SQL Database elastic database query overview (preview)
Cross-Database Queries in Azure SQL Database
Query across cloud databases with different schemas (preview)
CREATE EXTERNAL DATA SOURCE (Transact-SQL)
CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
Note:
You will receive this error if using "with (nolock)" - "Table hints are not supported on queries that reference external tables".
Removal of that table hint will allow the query to execute.
|
|
|
|
|
|