Migrate from Microsoft SQL Server to PostgreSQL or vice versa

1. Overview

This article will detail the process of moving the Dundas BI application and warehouse databases between Microsoft SQL Server and PostgreSQL database types. There are two strategies for doing this:

  • Create a new instance and transfer the items over. (Recommended)
  • Convert the application and warehouse databases using another tool.

2. Create a new instance and transfer the items over

2.1. Create destination instance with new application database type

The first step is to create a new destination instance. When creating this instance, on the Select Type screen, you should select the type of database you would like to migrate to.

For more information on creating an instance, see: Installing Dundas BI.

2.2. Transferring content

Most content in a Dundas BI instance can be exported and imported easily through a DBIE file.

Use a DBIE file to export and then import content
Use a DBIE file to export and then import content

Log on to the source instance, click Admin in the main menu, click to expand Import / Export and choose Export. Select all of your desired content, including accounts, groups, projects, settings, and the other options provided in the Advanced Options step. Click Finish and download the file, which has a .dbie extension.

Now log on to the destination instance to import this content. Click Admin in the main menu, click to expand Import / Export and choose Import. Drag onto the screen or browse to find the DBIE file you just created. Select all of the exported content, including accounts, groups, projects, settings, and the other options provided in the Advanced Options step, then click Finish.

For more information on importing and exporting between Dundas BI instances, see: Import and export a project.

Tip
In databases that are very large, it is best practice to divide the objects into multiple DBIE files and transfer them one at a time.

The following items will not be transferred as part of this process:

  • Log entries
  • Job history
  • Annotations (notes)
  • Measure correction values

Note
Application configuration settings can be exported and imported using this process when using Dundas BI version 7 or newer. Consider upgrading first if necessary to make this process easier.

2.3. Copying override files and extensions

If you are using custom app styling, white labeling, or override files to add custom CSS, JavaScript, HTML, or localization, they will need to be copied from the source instance to the destination instance. If you have created or installed any extensions yourself, they will also need to be copied.

In version 7 and up, these can be managed within Dundas BI in Admin by clicking to expand Setup, then choosing App Styling, Localization, or Extensions.

  • In App Styling, double-click or click to edit the CSS, JavaScript, and HTML resources, and copy their contents if you had added any to these resources.
  • In Localization, look for overrides or additional languages that you added previously that will need to be re-added.
  • In Extensions, custom extensions (i.e., not with Dundas Data Visualization as the author) can be re-downloaded from the Extension Details dialog by double-clicking an extension or opening its details, then added to the new instance.

If you placed additional files yourself within the Dundas BI installation folder, they will need to be copied over. For example, the following Override or whitelabel folders may have been used as described in the white labeling article:

  • [InstanceDir]\www\BIWebsite\wwwroot\Scripts\Override\
  • [InstanceDir]\www\BIWebsite\wwwroot\Content\Override\
  • [InstanceDir]\www\BIWebsite\wwwroot\Content\Images\whitelabel
  • [InstanceDir]\www\BIWebsite\wwwroot\Override\

Copying files from source instance to destination instance
Copying files from source instance to destination instance

3. Convert existing databases

3.1. Remove warehouse database connection string

Log on to the Dundas BI instance as an administrator and click Admin in the main menu on the left. Select Setup, and choose Config. Edit the warehouse connection string setting, then select the option to reset to default and click the submit button.

3.2. Stopping the application and scheduler

The Dundas BI web application (or its application pool) and its scheduler service should both be stopped. The steps below detail this for Windows servers, while on Linux you can find details in the article Dundas BI Linux services.

On Windows, search for the Internet Information Services (IIS) Manager in the start menu. In the left side tree view, click Application Pools. Now select the application pool or pools with the instance name in parentheses, and choose the Stop action.

Stopping the Dundas BI application pool
Stopping the Dundas BI application pool

Search for Services in the start menu. Find the service with the name Dundas BI Scheduler ({Your Instance Name}), right click on it, and select Stop.

Stopping the scheduler service
Stopping the scheduler service

3.3. Convert the application and warehouse databases

Dundas does not have any supported applications to do this. If you are using a third party tool to convert the database schema, the schema created in the conversion must be the same as one created by Dundas BI when creating a new database. This may be time consuming, which is why creating a separate instance and transferring content to it using a DBIE file is the preferred method for migrating between database types.

3.4. Edit the configuration file

To edit the Dundas BI configuration file, you will need to first decrypt the connection information if it was encrypted. To do this, use the dt tool with a command like the following:

C:\Program Files\Dundas Data Visualization Inc\Dundas BI\Instances\{InstanceName}\tools\dt.exe connectionConfig decrypt

Update the config file with the new connection string information. This file is typically found at:

C:\Program Files\Dundas Data Visualization Inc\Dundas BI\Instances\{InstanceName}\www\BIWebsite\App_Data\dbi.config

OR

C:\Program Files\Dundas Data Visualization Inc\Dundas BI\Instances\{InstanceName}\www\BIWebsite\App_Data\dundasBIConnection.config

The ApplicationStorage value as well as the corresponding connectionString will need to be updated:

<!-- 
The type of database server hosting the Application database. 
Can be "SqlServer" or "Postgres". 
-->
<add key="ApplicationStorage" value="SqlServer" />
<connectionStrings>
    <add name="AppConnectionString" 
       connectionString="Data Source=.;Initial Catalog=Dundas BI InstanceName;User ID=DundasUser;Password=1234"
     />
</connectionStrings>

After updating the configuration file, it is best pest practice to then encrypt its connection information. You do this with a command like the following:

C:\Program Files\Dundas Data Visualization Inc\Dundas BI\Instances\{InstanceName}\tools\dt.exe connectionConfig encrypt

3.5. Start services

To start the Dundas BI application(s) follow the same steps as when stopping services above but choose Start instead.

3.6. Set the warehouse connection string

Log on to the Dundas BI instance as an administrator and click Admin in the main menu. Click to expand Setup and then choose Config. Edit the warehouse connection string setting and set it to the new converted warehouse connection string, and then click the submit button.

4. 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