Starting from DB version 3.4.12.0005 it is possible to deliver updates for stored procedures and other objects to Azure Reporting database by using PUSH mechanisms. The goal is to: 

  • Maintain consistency and parity of all remote databases towards the Luware central reporting DB.
  • Create an overview of updates performed on a remote database and push missing updates.
  • Quickly identify issues related to updates that may have been caused by specific SQL scripts.

(lightbulb) For more details on this also refer to our latest "Reporting" Document. Ask your Luware BI-Representative contact for details.

Detail Description

The destination database for reporting can reside in MS Azure Cloud or other instance of MS SQL Server.

Procedure

In short the procedure will work as follows: 

  1. Update definitions are added into Reporting as TSQL code
  2. A "DBUpdateForPush" table in the main database is created.
  3. The Push mechanism will copy this definition into the Reporting database to the same table for every registered Teanants database (in [etl].[Tenants] table).
  4. A call of the "Reporting.usp_ReportingUpdate" stored procedure the reporting database will apply updates delivered so far.

(info) Details can be found in the step-by-step process below.

Types of Updates

There are two types of updates with the following scenarios:

  • Manual Updates can contain a hotfix or any other T-sql statement than must be run against remote database. This method is also performed when updating the main DB to a newer version. For example, we want to drop unused object in Tenants’ remote databases, and then we can run next statement in main database

    (info) Example: This statement will be delivered with next Push Agent Job (step for pushing [Reporting].[DBUpdateForPush] table) for every tenant.

    INSERT INTO Reporting.DBUpdateForPush (UpdateName, UpdateDefinition)
    VALUES ('SR1.01.01', N'
      DROP VIEW IF EXISTS [Reporting].[AgentSelectionType1];
    ');
    
    SQL
  • Automatic updates are created when the System detects changes that may affect reporting itself. e.g., a new stored procedure has been added to "Reporting.DataSetMapping" table. 
    The System generates an update definition for creating stored procedure and inserts it to the "Reporting.DBUpdateForPushTable"

DBUpdatesForPush Table

  • To decide which stored procedure has been changed, the "Reporting.DataSetMappingRoutineTS" table is used
  • Multiple pending Updates are applied in order as sorted by "UpdateId" (Oldest to newest)

DBUpdateForPush Table Row Description

Automatic Updates via Dataloader

(lightbulb) Automatic updates are generated as part the Integration job step (→ Reporting.usp_Dataloader stored procedure). By default, this step is disabled, and dataloader performs in its base operation. 

(warning) Enable this step ONLY if the push mechanism has been used at least once before and is intended to be used in future for this DB.

To enable execution of this step use following statement:

UPDATE Reporting._DataLoaderCustomSteps
SET IsEnabled = 1
WHERE Description ='PUSH_DB_UPDATES'
SQL

→ Once this step is enabled, the integration job compares the timestamp for reporting stored procedures and inserts update definition for these changes into the Reporting DBUpdateForPush table.

Update Plan - Push DB Update

Each time we update our Main reporting DB we automatically push the latest update DB SQL scripts to the customer tenant.

→ Multiple updates will be pushed to a queue of DB SQL scripts. They will be performed in order to ensure that the DB remains intact during productive use.
→ The scripts need to be applied regularly to stay compatible to the latest Historic Reports.
→ These SQL update scripts will not be automatically applied. The update process must be triggered actively by an DB Administrator as described in the steps below.

To run updates via this method for first time db_owner role is required in the remote database as necessary tables need to be created first.


Before performing any of the steps below, Contact the Customer-Side DB Admin to receive his approval or send this instruction to him.

(lightbulb) When working in Azure it is also possible to create a sandbox database as additional backup, and then try installing updates in this DB first → See "Sandbox Procedure" chapter below.


Preparation

  1. Disable the Tenant’s Push job from main DB or plan for an appropriate period to avoid pushing data when updating DB structure.
  2. Create (Azure) DB Backup of reporting database.
    (info) The procedure is shown in the Sandbox Example below

Applying DB Updates

@Install Parameter

Neither Manual nor Automatically generated Updates will be (installed) to a remote database structure after pushing.

  • If parameter @Install is set to 1 then system will attempt to apply updates to remote database right after Push delivery. Otherwise updates will be copied to remote db only without execution.
  • If @Install parameter is omitted or set to 0, calling this stored procedure returns a list of available (delivered) updates and their status 

    Update Status - NULL indicates that update wasn’t installed, otherwise a status is shown (e.g. SUCCESS, FAIL)

Applying the DB Updates can be executed in two ways:

via Luware main DB

In Stratus Main DB

EXEC [Reporting].[usp_Push_ReportingUpdate] 
@TenantId = '009ABCBA-A8B2-4802-966D-963D86E08954',
@TenantName = 'tenant_Navy',
@Install = 1;
SQL

(info) After the update completes, the list of applied updates and their "InstallStatus" is shown.

via Customer-side DB

In Remote / Azure DB

When updating Tenant’s DB at first time, make sure that db_owner role is assigned to user account for Tenant’s linked server or to the user who calls the SQL statement against the Azure DB.

(lightbulb)After performing the updates db_owner role can be removed from the user.


EXEC [Reporting].[usp_ReportingUpdate] 
@Install = 1;
SQL

(info) After the update completes, the list of applied updates and their "InstallStatus" is shown.

Check BI Reports

Check Power BI reports connected to the updated DB:

  1. Open Report in Power BI.
  2. In the Data Connection Change Version Parameter to a new value (if needed)
  3. Press "Refresh" button in main / data ribbon of Power BI to test the connection.

Post-Update Cleanup / Checks

Reenable Tenant’s Push job if needed (if disabled on step 1).

A new SQL Agent job step is automatically added for a new tenant. You can recreate an Agent job for any existent tenant or add new step manually at the beginning with a definition similar to:

EXEC [Reporting].[usp_Push_SendTableChanges] 
@TenantId = '880D7BC0-4BDD-4642-ACB1-293149A06236', 
@TenantName = 'tenant_B', 
@TableName = '[Reporting].[DBUpdateForPush]';
SQL

Sandbox Procedure

(info) This procedure is optional for if you want to test the Push procedure first:

Create DB Copy

  1. Go to your Azure SQL DB panel and select the DB to be updated
  2. Click on "Restore
  3. Create a restored DB.

    (lightbulb) → Depending on its size and Azure pricing level, the recovery process may take 5-10 minutes.

Check and Install delivered updates

  1. Once DB is created switch to it and run sql statement to install updates that were delivered:
  2. Enter the following SQL Statement to CHECK the pending installation:

    EXEC [Reporting].[usp_ReportingUpdate] 
    @Install = 0;
    SQL

    Replace @Install with 1 if you want to check and install pending table updates.


(question) What if an update fails?

Reporting table updates may fail for the following reasons:

  • The DB has been manually modified in between automatic updates, so the SQL statements may fail for not having the correct starting environment.
  • Updates must be performed in strict order (UpdateId column) and may rely on previous update entry SUCCESS. Check that previous (older) "Installstatus" indicators are are all showing a SUCCESS status. If not, execute their statements first to see a possible underlying problem.
  • For anything else that can't be immediately resolved, please do not attempt to manually resolve any details issues and get in contact with your Luware BI representative.