Thursday, October 4, 2018

Azure – Data warehouse – Elastic feature in Azure SQL Server


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


No comments:

Post a Comment