Migrate from Microsoft SQL Server to PostgreSQL or Vice Versa

1. Overview

This article will detail the process of moving from one Application Storage Engine type to another. 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. Method One - Create a new instance, and transfer the items over.

2.1. Create Destination Instance with new Application Storage Engine Type

The first step is to create a new destination instance. When creating this instance on the select database 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 Items from One Instance to Another

2.2.1. Migration File

Copy Migration File
Copy Migration File

Log on to the source instance, click the admin section, click Import / Export, and then click Export. Select all the accounts, and then click next. Select all the groups, and then click next. Select all the projects, and then click next. Finally select all the advanced options, and then click Finish. Click to download the dbie file.

Now that you have created the dbie migration file you can then import this into your destination instance. Log on to the destination instance, click the admin section, click Import / Export, and click Import. Select the dbie file you just created on the source instance. Select all the groups, and then click next. Select all the projects, and then click next. Finally Select all the advanced options, and then click Finish.

For more information see: Import and Export a Project.

Tip
In databases that are larger, and when transferring objects from the source instance to the destination instance, it is best practice to divide the objects into multiple dbie files, and transfer one at a time.

2.2.2. Copy files from Source Instance to Destination Instance

Copying Override files

The following override files should be copied from the source instance to the destination instance if they were modified

  • [InstanceDir]\www\BIWebsite\Scripts\Override\javascript.override.js
  • [InstanceDir]\www\BIWebsite\Content\Override\export.override.css
  • [InstanceDir]\www\BIWebsite\Content\Override\style.override.css
  • [InstanceDir]\\www\BIWebsite\Override\Html.Override.html

Any other files that were added for customization should also be migrated to the destination instance. These could include images, custom extensions, etc.

Copying files to Source instance to Destination instance.
Copying files to Source instance to Destination instance.

2.2.3. Transferring Application Configuration Settings

To migrate the application configuration settings on the source instance go to the admin screen. Next under setup click the config button. You should now be at the Application Configuration screen.

The application configuration settings screen in the admin screen of Dundas BI.
The application configuration settings screen in the admin screen of Dundas BI.

Write down all the setting values you intend to migrate to the destination instance.

Tip
Non default settings will be marked with a black circle. These are the ones you should be concerned about.

Now log on to the destination Dundas BI instance, and go to the same application configuration screen. Next update all the setting values you wrote down in the step above.

For more information, see View or modify configuration settings.

2.2.4. Limitations

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

  • Log entries
  • job history
  • Annotations
  • Measure correction values

3. Method Two - Convert Existing Databases to

3.1. Removing the existing warehouse database connection string

Log on to the Dundas BI instance as an admin. Next, select the admin item in the left menu. Select the setup, menu item and then click the "Config" button. Now under the warehouse connection string setting select edit, then select the reset to default option and then click the submit button. You will now receive a message "Connection string is empty. No connection is possible."

3.2. Stopping the Application

3.2.1. Stopping the Web Application(s)

In order to stop the Dundas BI Web Application you will need to go to IIS. This is available in the administrative tools item in the control panel, under Internet Information Services (IIS) Manager. On the left side menu, find the item that is named application pools. Now select the application pool or pools with the instance name in round brackets, and stop them. This can be done by right clicking on the application pool and then clicking stop in the context menu.

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

3.2.2. Stopping the Scheduler Service

In order to stop the Dundas BI Web Application you will need to go to Services. This is available in the administrative tools item in the control panel, under Services. Once the services window is open. 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 please ensure that the schema created in the conversion would be the same as one created by Dundas BI when creating a new database. This step can be time consuming but it must be done. This is the reason why method one is the preferred way to switch the type of application databases.

3.4. Edit the DBI configuration

To edit the DBI configuration file you will need to first decrypt it. To do this run the following command at the command line:

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

Update the following file with the new connection string information for:

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
Two things will have 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;Integrated Security=True"
     />
</connectionStrings>

After updating the DBI configuration file. It is best pest practice to then encrypt it. You do this by running the following command at the command line:

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

3.5. Start the Application

To start the Dundas BI application(s) do the opposite of what was done in the Stop the Application section above. Where it says stop, now start instead.

3.6. Set the Warehouse Connection String

Log on to the Dundas BI instance as an admin. Next, select the admin item in the left menu. Select the setup, menu item and then click the Config button. Now under the warehouse connection string setting select edit, enter the connection string to the new converted warehouse connection string and then click the submit button.

Note
The recommended way is to create a new instance and transfer the items over using the transfer service. The downside to this is you would need to migrate application configuration settings over manually, and log entries, job history, annotations, and measure correction values will not be transferred.

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: 7am-6pm, ET, Mon-Fri