Dedicated warehouse databases for tenants

1. Overview

Tenants can have their own dedicated warehouse databases. This allows for better security control and also helps isolate the load on the warehouse databases between tenants, when compared to a shared warehouse database.

This article provides the necessary steps to:

  • Create a dedicated tenant warehouse database and migrate tenant data from the global warehouse database
  • Remove a dedicated tenant warehouse database and migrate tenant data back to the global warehouse database

Note
The tasks detailed in this article are advanced and it is recommended that they be performed by users familiar with using .NET or REST APIs and the command line interface.

2. Create a dedicated tenant warehouse database

Creating a dedicated warehouse database and migrating tenant data from the global warehouse database to the tenant warehouse database involves the following steps.

2.1. Disable the tenant

Go to the main menu and click Admin. Expand the SaaS / Multi-Tenancy section and click Tenants. (If you don't see this option, it means you aren't licensed for this feature.) 

In the Tenants screen, select a tenant from the list and click Edit on the toolbar.

Edit a tenant
Edit a tenant

In the Tenant Details dialog uncheck the Enabled check box.

Disable a tenant
Disable a tenant

2.2. Set the configuration value from the command line

Set the AllowTenantWarehouses configuration setting value to True using the command line.

Using the command prompt, enter the following:

dt setConfigValue /settingId:cde27d97-d493-475d-aba2-783b4a200237 /value:True

For more information about using the command line to set configuration values, see here.

2.3. Add the tenant warehouse override using the API

You must add a tenant warehouse override using either the .NET API or the REST API. For more information about using the .NET API, see here.

Note
More than one TenantWarehouseOverride can be created for a tenant, each one corresponding to a different scope (similar to configuration settings). This flexibility would be required if you have a server farm in which different servers need to use different connection strings for the same physical database. The overrides here represent a different means of accessing the same database, rather than accessing different databases. In most cases this flexibility is not required, and the warehouse override should be applied only at the Global scope.

The following example demonstrates how to add a tenant warehouse database override using the .NET API.

// get tenant
var mtSvc = Engine.Current.GetService<IMultiTenancyService>();
Tenant tenant = mtSvc.GetTenant(/*pass tenant ID here*/);

// create tenant warehouse override
TenantWarehouseOverride whOverride = new TenantWarehouseOverride()
{
       Scope = AppSettingScope.Global, // desired scope 
       ScopeTarget = null, // desired scope target
       ConnectionString = tenantWHConnStr, // connection string to the future tenant warehouse DB
 };
tenant.SetWarehouseOverride(whOverride);
mtSvc.SaveTenant(tenant);

The following example demonstrates how to add a tenant warehouse database override using the REST API.

var warehouseOverrideData = {
    connectionString: "[connectionString]",
    password: "[password]",
    scope: "[scope]",
    scopeTarget: "[scopeTarget]"
};

var jqXHR = $.ajax({
    url: "/api/tenant/SetWarehouseOverride?id=[tenantId]&sessionId=[sessionId]",
    type: "POST",
    contentType: "application/json",
    dataType: "json",
    data: JSON.stringify(warehouseOverrideData)
});

2.4. Migrate tenant data using the command line

You can choose either to create the tenant warehouse database manually using your database administration tools and then run the migrateTenantData command to migrate tenant data to the warehouse database or you can just run the migrateTenantData command on the command line to do both, create the warehouse database and migrate tenant data to it.

Ensure that the connection string used here has the same server and database as the connection string used for the tenant warehouse override and its credentials need to have the appropriate privileges to create and/or instantiate the database and migrate data from your global warehouse to it.

For more information about using the command line to set configuration values, see here.

dt migrateTenantData /tenantId:[tenant ID] /direction:toTenantWh /tenantWhCs:[connection string to the tenant warehouse DB]

2.5. Enable the tenant

Go to the main menu and click Admin. Expand the SaaS / Multi-Tenancy section and click Tenants.

In the Tenants screen, select a tenant from the list and click Edit on the toolbar.

In the Tenant Details dialog select the Enabled check box.

3. Remove a dedicated tenant warehouse database

Removing a tenant database and migrating the tenant data back to the global warehouse database involves the following steps.

3.1. Disable the tenant

Before removing a dedicated tenant warehouse database you must disable the tenant. For instructions to disable a tenant , see here.

3.2. Remove the tenant warehouse override using the API

You can remove a tenant warehouse override using either the .NET API or the REST API.

The following example demonstrates how to remove a tenant warehouse database override using the .NET API.

// get tenant
var mtSvc = Engine.Current.GetService<IMultiTenancyService>();
Tenant tenant = mtSvc.GetTenant(/*pass tenant ID here*/);

// remove tenant warehouse override
tenant.RemoveWarehouseOverride(AppSettingScope.Global, null);
mtSvc.SaveTenant(tenant);

The following example demonstrates how to remove a tenant warehouse database override using the REST API.

var warehouseOverrideRemoveData = {
scope: "[scope]",
scopeTarget: "[scopeTarget]"
};
var jqXHR = $.ajax({
url: "/api/tenant/RemoveWarehouseOverride?id=[tenantId]&sessionId=[sessionId]",
type: "POST",
contentType: "application/json",
dataType: "json",
data: JSON.stringify(warehouseOverrideRemoveData)
});

3.3. Migrate tenant data back to the global warehouse DB using the command line

Use the following steps to migrate tenant data back to the global warehouse database.

Ensure that the connection string used here has the same server and database as the connection string that was used when setting up the tenant warehouse override and the credentials must have the appropriate privileges to migrate data from your tenant warehouse to global warehouse and remove the the tenant warehouse DB - if the dropTenantWh argument was provided used.

For more information about using the command line to set configuration values, see here.

dt migrateTenantData /tenantId:[tenant ID] /direction:toGlobalWh /tenantWhCs:[connection string to the tenant warehouse DB]

3.4. Enable the tenant

Go to the main menu and click Admin. Expand the SaaS / Multi-Tenancy section and click Tenants.

In the Tenants screen, select a tenant from the list and click Edit on the toolbar.

In the Tenant Details dialog select the Enabled check box.

4. Important notes

  • Tenant warehouse overrides are not transferred by export/import. If they are required, they should be created independently in each instance.
    • If both source and target instances have tenant warehouse databases, tenant data will be transferred from the source tenant warehouse database to the target tenant warehouse database.
    • If only the source instance has tenant warehouse database, the tenant data will be transferred from the source tenant warehouse database to the target global warehouse database.
    • If only the target instance has tenant warehouse database, tenant data will be transferred from the global warehouse database to the target tenant warehouse database.
  • It is recommended to include the tenant name or ID into the name of your tenant warehouse database for maintenance purposes.
  • The tenant ID is stored in the GlobalProperty table of the tenant warehouse database, under the TenantId property name. This ID is also used to validate the migration processes.
  • Processes such as upgrade or health checks that are applicable to the global warehouse database, will be applied to all existing tenant warehouse databases. Note that even though the process mimics the transactional behavior, integrity across multiple warehouse databases is not guaranteed. For example, the upgrade can succeed for the global warehouse database but could fail for tenant warehouse databases.
  • The TenantWarehouseOverride is active only if a matching scope and scope target is stored in the configuration settings. When tenant data is processed, the app tries to find TenantWarehouseOverride matching the effective application scope and scope target. If not found, the application tries to use the TenantWarehouseOverride created for AppSettingScope.Global, and if that is not available, the global warehouse database will be used.

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri