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
