How to Transfer Your Database from Microsoft SQL Server to MySQL

Author: James Ressler

Date: 2/18/2025

Audience: Everyone

Environmental details: KOTS, self-hosted, migration 

Summary

How to Transfer Your Database from Microsoft SQL Server to MySQL

Solution

To move from Microsoft SQL Server to MySQL, you can export your database as a .xml backup from the Jama Connect root account and restore it to a MySQL database during the upgrade process. If you wish to stay on the same version of Jama Connect, you can perform the same steps using a new database server but reusing your existing Jama Connect installation. 

Export a .xml file from the backup tab in the root account

From the "Backup" tab in the root account, select Option 2. This will download a .xml backup of your database to your local machine.

Backup tenant assets

If the old server was a traditional install

Make a directory to collect the assets

mkdir assets

Copy the assets to the new directory

cp -rf /data/contour/attachments assets
cp -rf /data/contour/avatars assets
cp -rf /data/contour/diagrams assets
cp -rf /data/contour/reports assets
cp -rf /data/contour/equations assets
cp -rf /data/contour/tempreports assets

Create an archive of the assets

cd assets
tar -zcvf assets.tar.gz attachments avatars diagrams reports equations tempreports

If the old server was a KOTS install

Start a shell session inside the running core-0 pod.

kubectl exec -it core-0 -- /bin/bash

 Move to the assets directory and make an archive of the assets.

cd /home/contour/tenant/jama

tar -zcvf assets.tar.gz avatars/ attachments/ diagrams/ reports/ equations/ tempreports/

 Exit the core-0 pod shell and copy the assets.tar.gz file onto the host filesystem.

exit

kubectl cp core-0:/home/contour/tenant/jama/assets.tar.gz ./assets.tar.gz

 Move the assets.tar.gz to the new server.

Install the desired Jama Connect version on a new server

Jama Connect User Guides

Prepare the new application server according to our User Guide for the version you are installing and perform the installation steps. The command to install the latest version is included here for convenience:

curl -sSL https://kurl.sh/jama-k8s-standardkots | sudo bash

Install MySQL on their database server according to our "Things to do before installation" section of the User Guide

Jama Connect User Guides

Items of note:

• Do not forget to update your my.cnf
• The "password" fields in the setup steps can be updated to passwords of your choosing
• The database server needs to be accessible from your new application server
• The database server can not be the same machine as your application server

From the KOTS Admin Console, disable tenant manager and deploy

Once the new application server finished installation, go through the usual steps of uploading your license and TLS certs until you reach the KOTS Admin Console configuration. In the config, update:

• Database settings
• Hostname
• Disable tenant manager ← this step is critical; it prevents provisioning of the database. Jama Connect only checks for a backup file if the database is unprovisioned. If you accidentally provision your database, you will have to drop them and recreate them to continue.

It should be unchecked as shown below

Click Continue and deploy the initial sequence (sequence 0)

Move the tenant assets onto the running core pod

Once the application deploys and kubectl get pods shows core-0 running, you can move your tenant assets onto the core pod and unpack them.

On your new application server, export the TENANT_NAME variable. The tenant name is usually the same as the name for your Jama database.

export TENANT_NAME=<tenant_name>

 Copy the tenant assets archive onto the running core-0 pod.

kubectl cp -c core /path/to/your/assets.tar.gz default/core-0:/home/contour/tenant/${TENANT_NAME}/

 Check that the archive was copied onto the running core-0 pod.

kubectl exec --tty -c core pods/core-0 -- ls -al /home/contour/tenant/${TENANT_NAME}/

 Unpack the archive.

kubectl exec --tty -c core pods/core-0 -- tar -xvzf /home/contour/tenant/${TENANT_NAME}/assets.tar.gz -C /home/contour/tenant/${TENANT_NAME}/

 Change the access mode of the assets.

kubectl exec --tty -c core pods/core-0 -- chmod -R 755 /home/contour

 Change the ownership of the assets.

kubectl exec --tty -c core pods/core-0 -- chown -R tomcat:tomcat /home/contour

 Remove the archive that was copied onto the running core-0 pod.

kubectl exec --tty -c core pods/core-0 -- rm /home/contour/tenant/${TENANT_NAME}/assets.tar.gz

Add .xml file path to the backup restore field, enable tenant manager, and deploy the new sequence

Copy your .xml file onto the application server and move it to /data/restore, creating the directory path if necessary.

From the KOTS Admin Console, navigate to the config tab and add the path to your backup.xml to the Restore Jama Backup field, including the full path.

Re-enable the tenant manager

Save and deploy the new sequence (sequence 1). The application will populate the database from your .xml file and perform a reindex. This will take some time and the application will be unavailable even if the KOTS Admin Console show "Ready" until indexing is complete. You can check the progress of the reindex in the core pod logs.

kubectl logs -f core-0

References

• Success Programs
• Success Catalog
• Datasheets
• Request a Solution Offering or Training from the Success Catalog

Please feel free to leave feedback in the comments below.