Not all people know that it is possible to run queries on other Azure SQL Databases. Normally with on-premise databases, we are used to use the following cross database query :
SELECT * FROM SourceDB.[SalesLT].[Customer]
But it will run into an error in Azure :
Msg 40515, Level 15, State 1, Line 16
Reference to database and/or server name in 'SourceDB.SalesLT.Customer' is not supported in this version of SQL Server.
From Microsoft : "The solution is using elastic queries. The elastic query feature enables you to run a Transact-SQL query that spans multiple databases in Azure SQL Database. It allows you to perform cross-database queries to access remote tables, and to connect Microsoft and third-party tools (Excel, Power BI, 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"
For this blogpost I've used the following link by David Postlethwaite and there other great resources on this topic too.
This is the setup of the databases:
I've gathered the steps to create elastic queries in this blogpost.
1. Create the loginFirst start with creating a login in the master database for the user we are going to use.
-- Go to Master database USE Master GO CREATE LOGIN ElasticUser WITH PASSWORD = '6rJbb3Gh@Bq):ngE' GO
2. Create the user in the source databaseCreate the user in the SourceDB database and assign it to the db_owner role.
USE SourceDB GO CREATE USER ElasticUser FOR LOGIN ElasticUser GO ALTER ROLE [db_owner] ADD MEMBER ElasticUser GO
2. Create the user in the destination databaseThen create the user in the DestinationDB database and again assign it to db_owner role
USE DestinationDB GO CREATE USER ElasticUser FOR LOGIN ElasticUser GO ALTER ROLE [db_owner] ADD MEMBER ElasticUser GO
Create the master keyCreate the Master Key in the DestinationDB database with a strong passowrd. This will create a symmetric key in order to protect the private keys in the database.
/* DROP MASTER KEY GO */ CREATE MASTER KEY ENCRYPTION BY PASSWORD = '6rJbb3Gh@Bq):ngE';
Create the database scoped credentialThen create the database scroped credential with the CREATE DATABASE SCOPED CREDENTIAL statement. The credential is used by the database to access to the external location anytime the database is performing an operation that requires access. The credential is used by the database to access to the external location anytime the database is performing an operation that requires access.
/* DROP DATABASE SCOPED CREDENTIAL henniecredential GO */ CREATE DATABASE SCOPED CREDENTIAL ElasticCredential WITH IDENTITY = 'ElasticUser', SECRET = '6rJbb3Gh@Bq):ngE';
Create the external data sourceThe CREATE EXTERNAL DATA SOURCE is used for the connectivity and is used by the elastic queries. The script for creating the external data source is as follows:
/* DROP EXTERNAL DATA SOURCE [sourceDB] GO */ CREATE EXTERNAL DATA SOURCE sourceDB WITH (TYPE = RDBMS, LOCATION = 'server-280742145.database.windows.net', DATABASE_NAME = 'SourceDB', CREDENTIAL = ElasticCredential ) ; GO
Create the schemaI'm using the Customer table from the AdventureWorksLT database and the table is created in the SalesLT schema and therefore we need to create a schema with the same name in the destinationDB.
/* DROP SCHEMA IF EXISTS SalesLT GO */ CREATE SCHEMA SalesLT GO
Create the external tableThe following statement creates the external table for the SalesLT.Customer table.
/* DROP EXTERNAL TABLE [SalesLT].[Customer] GO */ CREATE EXTERNAL TABLE SalesLT.[Customer]( [CustomerID] [int] NOT NULL, [NameStyle] bit NOT NULL, [Title] [nvarchar](8) NULL, [FirstName] nvarchar(50) NOT NULL, [MiddleName] nvarchar(50) NULL, [LastName] nvarchar(50) NOT NULL, [Suffix] [nvarchar](10) NULL, [CompanyName] [nvarchar](128) NULL, [SalesPerson] [nvarchar](256) NULL, [EmailAddress] [nvarchar](50) NULL, [Phone] nvarchar(25) NULL, [PasswordHash] [varchar](128) NOT NULL, [PasswordSalt] [varchar](10) NOT NULL, [rowguid] [uniqueidentifier] NOT NULL, [ModifiedDate] [datetime] NOT NULL, ) WITH ( DATA_SOURCE = sourceDB );
After running the DDL statements, you can access the remote table “Customer” 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.
Test the External TableTest the external query with a select query and see if we can get some results back.
SELECT * FROM SalesLT.[Customer]
And this results in the following result!!
Now if there is a difference in the datatype for one tiny length or datatype you will receive the following error
The data type of the column 'Phone' in the external table is different than the column's data type in the underlying standalone or sharded table present on the external source.
This blogpost is about creating external tables using in elastic queries.