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.
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
Access Administration from the main menu, 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.
In the Tenant Details dialog uncheck the Enabled check box.
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
You can find more information about this command in the article on setting a configuration value from the command line.
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.
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 from JavaScript, where a sessionId has already been obtained via logging on:
var warehouseOverrideData = {
connectionString: "[connectionString]",
password: "[password]",
scope: "[scope]",
scopeTarget: "[scopeTarget]"
};
var jqXHR = $.ajax({
url: "/api/tenant/SetWarehouseOverride?id=[tenantId]",
type: "POST",
contentType: "application/json",
headers: { "Authorization": "Bearer " + sessionId" }
dataType: "json",
data: JSON.stringify(warehouseOverrideData)
});
2.4. Migrate tenant data using the command line
The dt command line tool is used as shown below to migrate the tenant data. 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 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.
dt migrateTenantData /tenantId:[tenant ID] /direction:toTenantWh /tenantWhCs:[connection string to the tenant warehouse DB]
2.5. Enable the tenant
Access Administration from the main menu, 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.
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 examples below follow from the ones earlier that added overrides.
In .NET:
// 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);
Using REST:
var warehouseOverrideRemoveData = {
scope: "[scope]",
scopeTarget: "[scopeTarget]"
};
var jqXHR = $.ajax({
url: "/api/tenant/RemoveWarehouseOverride?id=[tenantId]",
type: "POST",
contentType: "application/json",
headers: { "Authorization": "Bearer " + sessionId" }
dataType: "json",
data: JSON.stringify(warehouseOverrideRemoveData)
});
3.3. Migrate tenant data back to the global warehouse DB using the command line
Use the dt command below 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. The credentials must also have the appropriate privileges to migrate data from your tenant warehouse to the global warehouse, and to remove the the tenant warehouse database if you use the dropTenantWh argument.
dt migrateTenantData /tenantId:[tenant ID] /direction:toGlobalWh /tenantWhCs:[connection string to the tenant warehouse DB]
3.4. Enable the tenant
Re-enable the tenant as described above.
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.