DentalDB Migrating SQLite

Introduction

This guide covers migration of DentalDB data from SQLite database to MySQL/MariaDB or SQL Server (also known as MS SQL).

There are many free and commercial tools available that support migration of data from SQLite database to MySQL/MariaDB and other database engines.

Our short experience with the tools was, that the commercial tool ESF Database Migration Toolkit did a quite good job and performs better in this regard than SQLite Migration And Sync.

Prerequisites

  • Basic knowledge of editing XML files
  • Knowledge of database administration
  • For SQL Server DentalDB Plovdiv 2.4 or higher is required, alternatively, Matera 2.3 DentalDB hotfix (Matera2.3 2019-10-02) is required.
  • There is a known limitation such that the current DentalDB version only supports MariaDB server up to version 10.6.x (newer versions may not be compatible).

Instructions

  1. To prevent data loss, create a backup of SQLite database by creating a copy of the file DentalDB_V3.sqlite (default location is c:\CAD-Data, but the location may vary depending on your reseller.
  2. Create a database schema in the target database:
    1. Note the schema name, user and password. In the example below, the schema name is dentaldb, user is test, and password is somepass
    2. Make sure you grant the correct permissions to the database user. For details, refer to the database documentation
  3. Create the file settings-db.xml in DentalDB\config folder if it is not already present there.
  4. Edit settings-db.xml as follows:
    1. If the tag SQLSettings already exists, replace it with the one below.
    2. If the tag SQLSettings is missing, add it as per the example below.
  5. Open a Command Prompt and run DentalDB using the argument /CreateDatabase
    1. Note: the DentalDB window will not open and the application terminates once a database scheme has been created.
  6. Run the migration tool of your choice using SQLite file as source and the database configured above as the target.

MySQL/MariaDB configuration

<SQLSettings>
<ConnectionString>Server=localhost;Database=dentaldb;User ID=test;Password=somepass</ConnectionString>
<SQLDialect>NHibernate.Dialect.MySQLDialect</SQLDialect>
<DriverClass>NHibernate.Driver.MySqlDataDriver</DriverClass>
</SQLSettings>

SQL Server configuration

Connecting Through SQL Server Authentication

Recommended process

<SQLSettings>
<ConnectionString>server=MY_DENTAL_SERVER_PC_NAME\SQLEXPRESS01; database=dentaldb;user=test;pwd=somepass</ConnectionString>
<SQLDialect>NHibernate.Dialect.MsSql2008Dialect</SQLDialect>
<DriverClass>NHibernate.Driver.SqlClientDriver</DriverClass>
</SQLSettings>

Connecting Through Windows Authentication

<SQLSettings>
<ConnectionString>server=MY_DENTAL_SERVER_PC_NAME\SQLEXPRESS01; database=dentaldb;Integrated Security=SSPI;</ConnectionString>
<SQLDialect>NHibernate.Dialect.MsSql2008Dialect</SQLDialect>
<DriverClass>NHibernate.Driver.SqlClientDriver</DriverClass>
</SQLSettings>

DISCLAIMER

The above migration instructions are provided without any warranty with regards to data loss or validity. You therefore follow these steps at your own risk.