Azure –
Data warehouse – Elastic
feature in Azure SQL Server
Given Scenario:
I have more than 1 Azure SQL databases
and I need to write ETL program to populate records from more than one database,
also need to do some manipulation or massage the data before loading. Due to
country specific regulatory requirement and licensing cost, I was recommended
not to use Azure Data Factory or Azure Data Bricks.
Solution:
Azure SQL
Server Database has a feature called “Elastic Query”, by using this feature you
can connect to more than one Azure SQL databases and write queries on them. Therefore,
you can connect and fetch records from more than one database.
There are 5
steps that you need to follow to use Elastic feature
Step 1: Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Password>';
Step 2: Create database scoped credential
CREATE
DATABASE SCOPED CREDENTIAL <ElasticDBQuery_Name>
WITH IDENTITY = '<UserName>',
SECRET = '<Password>';
Step 3: Create External Data Source
CREATE
EXTERNAL DATA SOURCE <DS_Name4Elastic> WITH
(TYPE
= RDBMS,
LOCATION = '<Azure SQL Server>',
DATABASE_NAME = '<Azure Database>',
CREDENTIAL = < ElasticDBQuery_Name >,
)
;
Step 4: Create Schema (This is optional – I
created it for clarity purpose)
Create
Schema <Elastic Schema>
ð This is to separate elastic
tables with other database objects. This step is not needed.
Step 5: Create External Tables
CREATE
EXTERNAL TABLE [<Elastic Schema>].[DimCustomer](
[CustomerNumber]
[int] IDENTITY(1,1) NOT NULL Primary Key,
[CustomerName]
[nVarchar](100) NULL,
[LocationKey]
[int] NULL,
……
)
WITH
(
DATA_SOURCE = <DS_Name4Elastic>,
SCHEMA_NAME = '<SourceDBSchema>',
OBJECT_NAME = '<SourceDBTableName>'
)
GO
Challenges:
- Except nVarchar(Max), other LOB data types are not support (eg XML
Datatype).
- To call Stored Procedure from one Azure database to another database.
You need to use “EXEC SP_EXECUTE_REMOTE”.
- When you use “EXEC SP_EXECUTE_REMOTE”, you cannot return set of
records as output parameter.
- Create
table in Source, Dump the data in Source, then pull it in Target (using elastic
feature)
Limitation of Elastic Queries:
- Running your first
elastic query will take few minutes because it load the elastic query
functionality.
- Scripting of
external data sources or external tables from SSMS or SSDT is not yet
supported.
- Import/Export for
SQL DB does not yet support external data sources and external tables. If you
need to use Import/Export, drop these objects before exporting and then
re-create them after importing.
- Elastic query
currently only supports read-only access to external tables. You can, however,
use full T-SQL functionality on the database where the external table is
defined. This can be useful to, e.g., persist temporary results using, e.g.,
SELECT INTO, or to define stored procedures on the elastic query database which
refer to external tables.
- Except for
nvarchar(max), LOB types are not supported in external table definitions.
- Column statistics
over external tables are currently not supported. Tables statistics are
supported, but need to be created manually.
Reference materials:
Other Info:
a. The source table structure and external table structure should be
same.
b. If the password or
credentials are changed in source database, then use below command to change
the credentials.
Error Message
Msg
46824, Level 16, State 1, Line 192
Login
failed on <Azure SQL Server>.
Please verify that the shards are accessible and that the credential
information affiliated with external data source <DS_Name4Elastic> is
correct.
Fix
ALTER DATABASE SCOPED CREDENTIAL <ElasticDBQuery_Name>
WITH IDENTITY = '<UserName>',
SECRET = <New Password>';
See
the link for further details
