Self-Hosted Database Set Up

Marnel Catlett
Marnel Catlett


This article outlines the information that Cognition360 require to be able to connect and process data to an Azure database hosted within a partner's environment.


One of the options available to partners is to host the report database within their own Azure tenant.

 

This article refers to a Platform as a Service (PaaS) Azure SQL Database. Not a SQL Managed Instance or SQL Server installed on a (Infrastructure as a Service (Paas)) server within Azure. 

 

In order for Cognition360 to be able push the partners data from the Cognition360 data warehouse to the self-hosted report database the following information is required:


  • Azure SQL Server Name - {servername}.database.windows.net. 
    • Allow Azure services and resources to access this server - Yes
    • Deny public network access - No
    • OR
    • Deny public network access - Yes with Cognition360 IPs added to Firewall rules to allow access.

 

  • Azure SQL Database Name - as defined by the partner.

 

  • Compute - minimum tier S3 (100 DTU's)
  • Storage - minimum 250 GB
  • Retention period - 7 days minimum

 

  • SQL User Account

 

  • Username
  • Password
  • No MFA set on this SQL account

 

  • SQL User Permissions (Report Database)

 

  • Allow login to database
  • Added to db_owner role for initial set up and deployment
  • Added to Data Writer role
  • Added to Data Reader role
  • Granted Execute
  • Granted Alter


  • SQL User Permissions (Master Database)


  • Added to db_owner role for initial set up and deployment

Note - Master database access is required to set up the SQL login/user that will access the database from the Power BI service, this is used to refresh the report datasets.


When the database is provisioned and access is available to Cognition360, the next step will be to build out the Cognition360 report database schema to the database. Reference tables will be populated with their data e.g. date dimension.

The migration of the data to the self-hosted report database, will take place over a weekend. This is done to minimize downtime, as the processing times will be extended due to the full data load to the self-hosted report database. Once all data is transferred and cleanly processed, all reports will be redeployed with the connection set to the self-hosted report database. Any reports, in the partners Power BI tenant, that are not the latest version and used for dashboards or copied reports, will be manually redeployed. This will ensure the report data models remain the same and downstream reporting entities do not break. The aim will be to have all reporting available for use by 8am on the Monday after the migration weekend.