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 2.4 Plovdiv or higher is required, alternatively, 2.3 Matera DentalDB hotfix (Matera2.3 2019-10-02).
• DentalDB versions prior to 3.2 Elefsina only support MariaDB server up to version 10.6.x (later MariaDB 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 the required tables have been created.
6. If necessary, adjust database encoding as described here
7. 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>

Chosing right character encoding for MariaDB/MySQL

The character set for the respective database should be utf8mb4. Otherwise, cases containing certain special characters (e.g. in the Notes field) will fail to save. Unless your default collation is already set accordingly, you need to set the appropriate character set for the respective database, execute the following SQL command:

ALTER DATABASE dentaldb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Where dentaldb is the name of the database you're using for the DentalDB software.

Troubleshooting: In cases where setting the correct character set was initially missed, you can retroactively convert existing tables to utf8mb4:

ALTER TABLE dentaldb.CustomWorkDefinition CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.DependentOnNumericToothWorkParameters CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.DependentToothWorkParameters CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.NumericToothWorkParameter CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.TextualToothWorkParameter CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.ToothWork CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.ToothWorkParameters CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.Treatment CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.TreatmentValuedCustomInfo CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.TreatmentValuedParameters CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.ValuedMaterialParameters CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.WorkParamsInfoDentalShare CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.WorkParamsInfoLocal CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.WorkParamsInfoLocalImport CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.client_allowedImportedIds CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.clients CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.databaseInformation CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.laboratories CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.patients CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE dentaldb.technicians CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Where dentaldb is the name of the database you're using for the DentalDB software. As a reminder, this only needs to be done to recover a database that was filled without initially setting correct character set.

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.