Enabling Data Sharing Across Platforms in Azure SQL Database

Introduction

In this guide, you'll learn how to enable data sharing across platforms in Azure SQL Server using External tables & data tracker script. Azure SQL does not support cross-database queries by default, unlike SQLite and MSSQL Server. To handle this, you can use external tables to pull data from other databases, such as the Asset and Report databases. To set this up, you'll need to run a custom script that enables the data tracker on your Azure SQL database. Cross-database queries are not natively supported in Azure SQL, making this script essential for enabling data sharing across platforms.

Note: This process is specifically for Azure SQL servers and does not apply to Azure SQL Managed Instances. By following the steps below, you can set up the necessary configurations to seamlessly share data across different databases.

Cross-Database Query Approaches for Azure SQL

Azure SQL doesn’t support cross-database queries by default, but it offers alternative methods to access data from different databases. Here are the two primary approaches:

1. Creating a Linked Server
   This method links a remote dataset with your Azure SQL database. It requires the Server Objects feature, which is usually not available on Azure SQL but works well between a hosted SQL Server and an Azure SQL database.

2. Using Azure SQL Elastic Query (External Tables)
   Here’s a simple diagram to help you understand the data sharing process described:

Explanation

The Controller service retrieves information from the Report database (Azure SQL DB). Some of the data is kept in the Asset database (Azure SQL DB), so tables from the Asset database are linked as external tables. These external tables are then used within the Report database to access data from the Asset database.

This approach lets you link a remote table as an external table in Azure SQL, which is used for the data tracker service to enable cross-database queries and data sharing between the two databases.

To Set Up External Tables in Azure SQL Database, please follow the steps below:

1. Create a Master Key

A master key is needed to secure the credentials for external data sources (remote database).
Switch to the Target Database:

USE [ReportDB];

Create the Master Key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD='new_password';

Replace 'new_password' with your chosen secure password.

2. Create Scoped Credentials

Scoped credentials authenticate the external data source.
To define the credentials:

CREATE DATABASE SCOPED CREDENTIAL AppCredential
WITH IDENTITY = 'login', SECRET = 'login_password';

Replace 'login' with your username and 'login_password' with your password.

3. Create an External Data Source in the Report Database

Define the connection to the remote database using the scoped credentials.
To set up the external data source:

CREATE EXTERNAL DATA SOURCE RemoteDatabase
WITH
(
    TYPE=RDBMS,
    LOCATION='servername.database.windows.net',
    DATABASE_NAME='AssetDB',
    CREDENTIAL=AppCredential
);

Replace the “servername” with your server's name.

4. Create External Tables with the Same Schema in the Report Database

Set up external tables in the Report database with the same schema as the remote (Asset database) tables to enable smooth querying.
To create external tables:

CREATE EXTERNAL TABLE HierarchyItemMetadata
(
    Id uniqueidentifier NOT NULL,
    Title nvarchar(256),
    "Description" nvarchar(512),
    "Type" integer,
    LinkedWith integer NOT NULL
)
WITH
(
    DATA_SOURCE = RemoteDatabase
);

CREATE EXTERNAL TABLE FlowInfo
(
    Id uniqueidentifier NOT NULL,
    FlowchartId uniqueidentifier NOT NULL,
    IsDeleted bit,
    TimeoutSeconds integer,
    DefaultTimeoutSec integer,
    VideoRecording integer,
    EnableVideoSubtitles bit NOT NULL,
    LoggingLevel integer,
    DateCreation datetimeoffset,
    DateModify datetimeoffset,
    EnvironmentId uniqueidentifier,
    IsFailedUnlessPassed bit,
    WorkflowStatusId uniqueidentifier NOT NULL,
    AssigneeUserId uniqueidentifier,
    AgentConfigurationId uniqueidentifier
)
WITH
(
    DATA_SOURCE = RemoteDatabase
);

-- Add additional table creation statements as necessary.

5. Grant Select Permissions

Ensure that the Leapwork db user has the necessary permissions to query the external tables.

GRANT SELECT ON [HierarchyItemMetadata] TO Username;
GRANT SELECT ON [FlowInfo] TO Username;
GRANT SELECT ON [EnvironmentHierarchyItemMetadata] TO Username;
-- Continue granting permissions for other external tables.

By following these steps and running the provided script, you can enable cross-database queries in Azure SQL Server and ensure that the data tracker service operates smoothly with both the databases.

Key Troubleshooting Areas and Steps

Script Errors:

• What Might Go Wrong: Syntax or execution errors in the script.
• What to do: Check for typos and verify SQL syntax.

Permission Issues:

• What Might Go Wrong: Lack of permissions for querying external tables.
• What to do: Ensure the Leapwork db user has SELECT permissions for all external tables.

Incorrect External Data Source Configuration:

• What Might Go Wrong: Errors in defining the external data source.
• What to do: Verify the server's name, database name, and credentials in the CREATE EXTERNAL DATA SOURCE statement.

Master Key Problems:

• What Might Go Wrong: Issues with creating or using the master key.
• What to do: Ensure the master key password is correct and the key creation script ran successfully.

Schema Mismatch:

• What Might Go Wrong: Mismatch between remote and external table schemas.
• What to do: Confirm that external tables have the same schema as the remote tables in the CREATE EXTERNAL TABLE statements.

For further assistance or clarification, please contact our Priority Support team.