AcumatiCaresWhether you are looking to create a new Acumatica instance on MySQL, or whether you have a running Acumatica instance with MSSQL that you are looking to transition to MySQL, the following blog post will shed some light on this process. The use of MySQL for Acumatica is not as well documented as Microsoft SQL server’s which makes this post especially valuable if the selection of this DB engine is the option – economic and technologically wise – most convenient for you or your customer.
Further, I will walk you through the process of creating a new MySQL server, and will show the key aspects and hidden tricks to migrate your data..
Acumatica is a Database-Agnostic ERP. This means that an Acumatica instance can be run on Microsoft SQL Server or in MySQL server.
In order to guarantee this, there are a few things to consider from the development perspective:
- No specific database objects should get created i.e. SQL Views, Stored Procedures, etc.
- All communication with the database should be done using Business Query Language (BQL) and the framework methods.
- The creation/modification of DB tables in customization packages should be managed as XML data rather than using a direct DB query.
- etc…
The greater percentage of Acumatica customers currently use Microsoft SQL Server. This article, however, aims to shed some light on how to configure and migrate Acumatica for MySQL. Specifically, with an AWS RDS My SQL Server.
Per AWS’s description, “Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups.”
RDS supports – among other DB engines – MySQL, SQL Server and Aurora. Aurora is a relational database service developed by Amazon’s AWS team themselves. It supports both MySQL & PostgreSQL and it’s certainly worth reviewing as it’s “…up to five times faster than standard MySQL databases and three times faster than standard PostgreSQL databases* ”.
However, although I am very keen to learn more about Aurora and how to use it for Acumatica, the purpose of this blog post is focused on using MySQL. Perhaps I’ll follow up with a post on Aurora in the future.
Configuring MySQL in RDS
RDS simplifies configuring MySQL by providing a wizard to prepare the server. Here, in what follows are most relevant settings to take in consideration.
The version of MySQL. I chose to use 5.7.22 for a few reasons. One, It’s still the most popular MySQL version used in the market as the data below illustrates and it’s a free open-source version of the database.
Source: EverSQL
Additionally, although very tempting, using the latest available version (currently MySQL 8.0.17) throws the following error when we try to establish a connection to access it via the Acumatica configuration wizard.
Make the MySQL publicly accessible by selecting this option in the wizard. Otherwise, the configuration Wizard will fail to connect as well as any DB client for that matter.
Next, make sure that the Security group can access the MySQL port: 3306 and change the default DB parameter group. If you use the current default (default.mysql5.7), the Acumatica wizard yields the following error:
As described in the error message, we need to make sure that the log_bin_trust_function_creators variable is set to true. This can be done by duplicating the default parameter group and assigning that property.
After confirming all the settings, the server will start getting created, and voilà! In a matter of minutes, we will have a successful MySQL server up and running in no time.
Transitioning Data from MSSQL to MySQL
Now, so far so good, but what about the customers that will be transitioning data from MSSQL to MySQL?
There are lots of data-migration applications available. Even AWS themselves have one of their own (aws.amazon.com/dms/). So, it’s time to invest quite a few hours, read reviews and identify the option that is more suitable. Spoiler: I already have that answer, and the preferred option is ** DRUM ROLL ** none of the above! Talk about an anti-climatic answer.
This time we are going to be relying in the very very useful, Acumatica snapshots. We are going to take advantage of having the DB structure definition managed by the Acumatica Wizard:
– An Acumatica snapshot will be taken from the MSSQL instance (let’s call it Instance A). We should make sure that the custom data is included in the snapshot as well.
– A new Acumatica instance compatible with MySQL will get created (let’s call it Instance B).
– All the customization packages should get published in Instance B as we will be migrating custom data as well and we need to have all custom tables and columns created in the destination instance.
– Log on to Instance B and import the snapshot created in the first step.
Here are some images from our results:
– A Purchase Order was initially created in Instance A
– The steps were followed, and the data imported in Instance B, and here is the result:
We can have a new MySQL server with the migrated data in just a few hours.
After this, make sure thorough tests are done in a beta environment prior to making your changes in Production. This post is for reference purposes. There are lots of scenarios that should be tested on your end. In addition, if you’re considering migrating your data from MySQL to PostgreSQL in the future, it’s important to note that this process might require a different approach due to the differences in database structures and query syntax between the two systems.
Summary
In conclusion, Acumatica is a database agnostic ERP. As such, if the budget is a critical factor for you, or if technologically wise you prefer MySQL as a DB engine, then it’s worth investing some time in order to consider this scenario. Keep in mind that you will be able to do it, not only for new instances but for ongoing instances running MSSQL as well.
*https://aws.amazon.com/rds/aurora/
