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. This is a way to achieve the DevOps practice of Infrastructure as code.

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 Core and Client app solutions.

Project structure

The functionality of the WebJob is included in the Sidra package PlainConcepts.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.
1
2
3
4
{
    "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 Core and Client apps.

tutorial-databasebuilder-project

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 (Core, DW, Log, Client) but the DatabaseBuilder allows to add any number of additional folders to manage the scripts of any number of additional databases.

  • CoreContext folder for the Core database scripts.
  • DWContext folder for the OperationalWarehouse database scripts.
  • LogContext folder for the Log database scripts.
  • ClientContext folder for the Client app 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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
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:

1
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.

1
2
3
{
    "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.

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

1
2
3
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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
{
    "ConnectionStrings": {
        "CoreContext": "",
        "DWContext": "",
        "ClientContext": "",
        "LogContext": "",
        "AdditionalDatabaseContext" : ""
    },
    "DatabaseBuilder.LocalFolderName" : "",
    "DatabaseBuilder.StoragePath" : "",
    "AzureStorage.AccountName" : "",
    "AzureStorage.Key" : "",
    "Environment": ""
}    
Parameter Description
CoreContext Connection String for the Core database
DWContext Connection String for the DataWarehouse database
ClientContext Connection String for the Client app database
LogContext Connection String for the Log database
DatabaseBuilder.LocalFolderName Name of the local folder where the scripts are stored. If it is empty, it will use 'Scripts' by default.
DatabaseBuilder.StoragePath Path of the Azure Storage account where the scripts are stored. If it is not empty, it will ignore the DatabaseBuilder.LocalFolderName setting.
AzureStorage.AccountName The name of the Azure Storage account where the scripts are stored.
AzureStorage.Key The key to access the previous Azure Storage account parameter.
Environment String that can be setup to filter the scripts that will be executed.

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:

Connection String Sample value
CoreContext Server=localhost;Initial Catalog=Sidra.DatabaseBuilder.Core;Integrated Security=true;
DWContext Server=localhost;Initial Catalog=Sidra.DatabaseBuilder.DW;Integrated Security=true;
ClientContext Server=localhost;Initial Catalog=Sidra.DatabaseBuilder.Client;Integrated Security=true;
LogContext Server=localhost;Initial Catalog=Sidra.DatabaseBuilder.Log;Integrated Security=true;