How to execute SQL scripts using DatabaseBuilder webjob¶

In order to easily deploy a Sidra Data Platform installation in a new environment and be able to replicate the deployment every time, all the information about Data Factory pipelines that is stored in the metadata database must be also included in the code base as SQL scripts..

To easy that task, Sidra provides the DatabaseBuilder webjob to automatize the management and execution of those scripts. It can also be used to execute the scripts to populate the metadata of Providers, Entities, Attributes, etc.

This webjob can be added to both Sidra Service and Data Product solutions.

Project structure¶

The functionality of the WebJob is included in the Sidra package SIDRA.DotNetCore.Webjob.DatabaseBuilder. So, in the Visual Studio solution, the DatabaseBuilder project will be just a host that references the Sidra package. It can also contains the scripts to be executed.

The location of the scripts can be configured in the WebJob, they can be stored:

• In an Azure Storage account by setting up the Application Setting DatabaseBuilder.StoragePath to the path to the Azure Storage account.
• Locally within the WebJob by setting up the Application Setting DatabaseBuilder.LocalFolderName to the local folder name.

{
    "DatabaseBuilder.LocalFolderName": "",
    "DatabaseBuilder.StoragePath" : ""
}

In case any of the previous settings is configured, the scripts will be retrieved locally from the folder "Scripts". That is the default configuration used for both Sidra Service and Data Products.

Databases and the scripts folder structure¶

Wherever the scripts were located, they have to comply with the following folder structure so they can be handled by the DatabaseBuilder. It must be a folder for each database that will be built up. Each of these folders will contain the scripts that will be executed in the respective database. Some folders have specific names because they refer to well known databases (Sidra Service, DW, Log, Client) but the DatabaseBuilder allows to add any number of additional folders to manage the scripts of any number of additional databases.

• SidraContext folder for the Sidra Service database scripts.
• DWContext folder for the OperationalWarehouse database scripts.
• LogContext folder for the Log database scripts.
• ClientContext folder for the Data Product database scripts.
• AdditionalDatabaseContext folder for an additional database (a folder for each additional database).

Additionally to the folder structure, the WebJob must be configured to select which folders must manage. This configuration is performed in the Main method of the Program class in the DatabaseBuilder host project by using the Options class.

static void Main(string[] args)
{
    ...

    // Add log configuration as well if required
    JobExecutor.Execute(
        configuration,
        new Options()
        {
            CreateCoreDatabase = true,
            CreateDWDatabase = true,
            CreateLogDatabase = true,
            CreateClientDatabase = false,
            AdditionalDatabases = null
        },
        loggingBuilderAction);

    ...
}

Scripts naming convention¶

Inside of the previous folders, the scripts will be files containing the SQL sentences to execute in the corresponding database. The files must comply with this naming convention:

executionOrder_[environment_]name.sql

where:

• The executionOrder must be a number that will be used to sort the files before executing them. So the file with the minor execution order will be the first in being executed.

• The environment is an optional part of the naming that is used to filter the scripts to be executed. There is an Application Setting named Environment that can be configured for that purpose. Only the files with an environment matching the value of that Application Setting will be executed. If the filename does not include an environment, it will always be executed.

{
    "Environment": "Dev"
}

• The name is a word used to describe the script. It is usual to use PBI + the number of the user story in the backlog, but it is just a good practice and it is not mandatory. It is important to highlight that the final .sql of the naming convention must be lower case. The name must be comprised of only lowercase (a-z) or uppercase (A-Z) letters and figures (0-9). Other chars will cause the script to be ignored, such as space ( ), dash (-), underscore (_), parenthesis, punctuation, etc.

Using the previous configuration, only the first and third scripts will be executed:

01_Dev_PBI0001.sql
02_Prod_PBI0002.sql
03_PBI0004.sql

Sidra databases seed execution¶

Sidra requires to populate the databases with some base information to allow the system to function. That seed for the databases is included in the Sidra persistence packages (e.g. PlainConcepts.SIDRA.DotNetCore.Persistence for the Core database) and it is executed by the DatabaseBuilder.

App settings¶

The DatabaseBuilder can be configured using the appsettings.json file. This is a summary of all the configuration parameters:

{
    "ConnectionStrings": {
        "SidraContext": "",
        "DWContext": "",
        "ClientContext": "",
        "LogContext": "",
        "AdditionalDatabaseContext" : ""
    },
    "DatabaseBuilder.LocalFolderName" : "",
    "DatabaseBuilder.StoragePath" : "",
    "AzureStorage.AccountName" : "",
    "AzureStorage.Key" : "",
    "Environment": ""
}    

How to test locally¶

The DatabaseBuilder project is deployed as a WebJob but it is a Console Application, that means that it can be executed locally so it is possible to test the execution of the scripts in a local database before executing those changes in the actual database infrastructure.

In order to test those scripts locally, it is necessary to configure the appsettings.json file of the DatabaseBuilder project with the following parameters: