Databases using Azure SQL

This topic shows the necessary installation work when using the Azure SQL or Azure SQL Managed Instance. With these you can create (or import), and manage databases for Planning Space in the cloud without having to deploy or manage an SQL Server machine (physical or virtual).

Note: Please also review the information about Planning Space databases in the topic Databases and DBUPGRADE.

Prerequisite setup

Important: The latency requirement of below 1 ms between IPS and database infrastructure always applies. Therefore to use Azure SQL managed databases requires deploying IPS Server in Azure cloud virtual machine(s) within the same Azure region.

It is recommended to disable the IPS Server Monitor processes (see IPS Server monitoring), and instead use the Azure Monitor services for VM monitoring (except where monitoring is specifically required for individual Planning Space processes), which reduces the recurring data usage costs for Azure SQL.

Cloud Storage configuration in Azure

Cloud Storage must be made available to each tenant when Azure SQL is being used to run the tenant database.

Currently, the only option is Azure Blob Storage, because Azure SQL is only allowed to use 'bulk insert' functionality with Blob Storage.

The instructions below assume that a storage account has already been created in the same Azure region as the virtual machine(s) running the IPS Service.

1. Create a new container in the storage account, and set 'Public access level' to 'Private'.
2. In the container page, click 'Shared access tokens'. Adjust the settings as follows:
• Signing method: Account key
• Signing key: (leave as 'Key 1'; note that when the key is rotated a new SAS token must be generated)
• Stored access policy: None
• Start: (leave as is)
• Expiry: (set to the preferred date for your organisation; both IPS Server and Azure SQL will need re-configuration when the expiry happens)
• Allowed IP addresses: (you can specify public/private IP addresses depending on how the storage blob is accessed)
• Allowed protocols: HTTPS only
3. Click the button 'Generate SAS token and URL'.
4. Copy the contents of 'Blob SAS URL'

For the configuration of IPS and Azure SQL you need the given 'Blob SAS URL' in two parts:

• URL is everything before the '?' and not including it
• SAS Token is everything after the '?' ('sp=...')

Cloud Storage configuration in IPS

The Cloud Storage configuration is required for each tenant when Azure SQL is being used to run the tenant database.

In IPS Manager, click Tenants in the left-hand menu. There is a setting for each tenant:

Check the box for Cloud Storage. Click the Edit button.

In the Configure dialog, select Azure in Cloud Storage Type.

Paste in the URL and SAS token obtained above into the Container URl and SAS token fields. Click the OK button to finish.

The SAS Token setting must be updated every time a SAS token expires and a new one is generated.

Azure SQL configuration

It is recommended to set Master Key encryption for each tenant database.

Planning Space requires an SQL user account to perform operations in each tenant database. The account should not have SQL Admin permissions and it is recommended to create a dedicated SQL user account, for example 'psuser' (created at the master database level of Azure SQL), with the least privileges granted. These are 'DATABASE BULK OPERATIONS':

GRANT ADMINISTER DATABASE BULK OPERATIONS TO psuser;
            

(Note this is different to Microsoft SQL Server where the bulk operations permission is set at server-level.)

And the following specific roles:

EXEC sp_addrolemember N'db_datareader', N'psuser';
EXEC sp_addrolemember N'db_datawriter', N'psuser';
EXEC sp_addrolemember N'pes_datawriter', N'psuser';

The Azure SQL instance needs to be configured to access the Blob Storage. You can run the script below, with the first 2 lines containing the URL and SAS Token values obtained above for the Blob Storage.

DECLARE @sasToken AS NVARCHAR(MAX) = 'INSERT BLOB SAS TOKEN HERE';
DECLARE @location AS NVARCHAR(MAX) = 'INSERT BLOB URL HERE';

-- drop existing EXTERNAL DATASOURCE, SCOPED CREDENTIALS 
IF EXISTS ( SELECT 1 FROM sys.external_data_sources WHERE Name = 'RS_FileStore' )
BEGIN
    DROP EXTERNAL DATA SOURCE RS_FileStore;
END;
IF EXISTS ( SELECT 1 FROM sys.database_scoped_credentials WHERE Name = 'RS_FileStoreCredential' )
BEGIN
    DROP DATABASE SCOPED CREDENTIAL RS_FileStoreCredential;
END;
    
DECLARE @sql NVARCHAR(MAX)

SET @sql = N'CREATE DATABASE SCOPED CREDENTIAL RS_FileStoreCredential
WITH IDENTITY = ''SHARED ACCESS SIGNATURE'',
SECRET = ''' + @sasToken + '''';
EXEC(@sql);
SET @sql = N'CREATE EXTERNAL DATA SOURCE RS_FileStore
WITH (
TYPE = BLOB_STORAGE,
LOCATION = ''' + @location + ''',
CREDENTIAL = RS_FileStoreCredential
)';
EXEC(@sql);

The SAS Token setting must be updated every time a SAS token expires and a new one is generated. The following SQL command can be used:

ALTER DATABASE SCOPED Credential RS_FileStoreCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE' ,
SECRET = 'Insert New SAS Token here' ;