Migrate database to different server

nhofkes

I am planning on moving my servers from Windows to Linux. As a first step, I want to transfer the database from the current Windows server (VPS) to a separate Linux server (also VPS). The new server is meanwhile up and running and MariaDB is installed on both servers. What is the best approach to transfer the data and the business spaces (I have several) from the original (development) server to the new database server? I am on V10, so I could export and then import the data, but that is ‘only’ the data and not the configuration of the business spaces. Would it be possible (or perhaps even better) to copy all databases with a database management tool? I am using dbForge Studio, which is connected to both databases so it wouldn’t be too difficult to make a copy from one to the other. Am I right in thinking that this would relate to the two ‘standard’ databases that are defined in the BASServer.props file as ‘DatabaseName’ and ‘DatabaseNameTest’, plus all separate databases for the business space data? And if I copy all the tables and then change the BASServer.props to point to the new MariaDB on the Linux server, would the AwareIM server then continue to work as before?

I would love to hear from those who did this before ( @customaware, @kklosson ?) about the best practices for this migration process.

To avoid misunderstandings: at this stage I would want to continue with my current AIM server and not install a separate AIM server on the Linux machine. The plan is to move only the database. When that is achieved, I may also move the AIM server from Windows to Linux but I consider that to be a separate step. Also because it may make sense to keep the AIM server and the DB server on different (virtual) machines anyway, for performance and security reasons.

kklosson

It’s probably easier than you think. Assuming your database(s) are of reasonable size, you can export to .sql file and import it to the new database. You can do this with larger databases but consider the transfer and import/export times. If you are moving into a different Maria version, this should automatically conform the data to the new version if that applies. In AIM, you can use the Control Panel database settings to point to the new server and you are pretty much done. That will write to BASServer.props. When you re-open the Config Tool, you’ll see your databases as per normal.

If you want to rehost the AIM server, make sure any local changes are reflected in the server setup, like third party tools such as MS Office or other utilities and configurations such as for SSL, if employed. The new license server is awesome but there is no longer a trial license, only a reduced function license. So consider that as you plan for the cutover. Note that when moving the license, you have to disable it on the license server, then restart your AIM instance to terminate the license. Then you can start the new instance, which will license itself up. Obviously, you have to change the DNS.

Also, consider using any of the AI tools to get answers to technical questions. Usually pretty accurate.

nhofkes

kklosson

Assuming your database(s) are of reasonable size, you can export to .sql file and import it to the new database.

This is what I intended to do - when I referred to ‘copying’ data from one database to another, dbForge Studio actually creates a SQL script that imports all data to the new database. That’s not the problem. My question was if this copying (or export and import, if you like) of the database also brings over the business space configuration(s), including previous versions. Because my understanding is that the configuration is also stored in the database, but separately from the data of the BS itself, and presumably AIM needs both the configuration and the data to work.

kklosson

By configuration if you mean the BSV data, yes, that’s in the database. If you have the database on one server and the AIM server on another server, the BSV data is completely separate from the AIM server. It’s in the database. I believe it is stored in the domainversions table on the basdb database.

customaware

If you cannot use the new Data Migration tools build into Aware in V10 then I would recommend the ESF Data Migration Tool Kit…. It is excellent and allows you a lot of control regarding Source and Destination database.

customaware

I meant to add….. You mentioned…..

“Also because it may make sense to keep the AIM server and the DB server on different (virtual) machines anyway, for performance and security reasons.”

I ALWAYS have my database on the same server as Aware and the application. Have found if you employ the appropriate precautions and backup strategies…. there is no security risk keeping them on the same server. Happy to hear any comment to the contrary.

Also, (I might also be way off target here) to my mind, the closer the data is to Aware the better the performance. I would have thought that having to communicate with another server would be logically slower… albeit marginally depending on where the servers are located relative to each other.

Additionally, I have an automatic backup of the entire server done daily and it takes seconds. With two servers, it means extra cos and extra maintenance.

Indeed, you are braver than me moving from Windows to Linux but I guess you are much more proficient with Linux than me.

Just my two cents worth.

nhofkes

I am happy to let you know that I have now succesfully transferred the database from my Windows server to the Linux server.

For others who might want to do the same, I’ll summarize the steps taken. Note that these are (in part) specific for my situation, being one server running on Windows, one server running on Linux Ubuntu (24.04), and MariaDB on both servers. Furthermore, at this stage I only transferred the database, not Aware itself nor the Apache webserver.

Prepare the Linux server and ensure that MariaDB is running.
Ensure that MariaDB can be accessed from Aware on the Windows server: create the applicable rule in the firewall and add the Aware database account user in MariaDB (i.e. the user that is configured in the Database Settings in the Control Panel). It is not necessary to create a corresponding user on the Linux server itself.
Make a snapshot of the Linux server and a backup of the database.
Stop the Aware server.
(Optional) Make a backup of the database on the Windows server.
Copy databases from MariaDB on Windows to MariaDB on Linux.
I used dbForge Studio for this, it has a “Copy Databases” function that can copy multiple databases at the same time. Very convenient. An alternative may be to export the database to an SQL script which can then be used to import it to the Linux server, but at least with dbForge Studio I think this can only handle one database at a time so it is more work. Of course there are also other tools available. Make sure that all databases are copied, except for the MariaDB system tables.
Create backup of BASServer.props file
Start Aware Control Panel and change the database settings. If the same database account that existed on MariaDB on Windows was also created in MariaDB on the Linux server, then only the machine name has to be updated in the database settings. In my case I simply put in the IP address of the Linux server.

I had to take two additional steps to ensure that Aware could succesfully access the database on the Linux server:

Make MariaDB on Linux case insensitive:
Unix-based systems are case sensitive, Windows is not. This means that the default setting for MariaDB on Windows is that lower case table names are used, whereas the default on Linux is that MariaDB compares table names and database names in a case-sensitive manner. Apparently, Aware uses casing in the names of the databases and tables, so it could not find the databases because it was looking for the name in uppercase whereas it was copied with a lowercase name. This can be resolved by adding the setting lower_case_table_names=1 in a configuration file for MariaDB (I created an additional file 60-server-additions.cnf in /etc/mysql/mariadb.conf.d/ which has this setting and a few others. (see MariaDB Server Reference.
Reset the bas_idgen_seq sequences for all databases:
When the databases were copied, also the definition of the bas_idgen_seq sequence was copied, but this does not mean that the sequence number is set at the last number that it had on the Windows server. Thus, when adding new data the object ID will start again from 1. This can create problems because the object IDs need to be unique. I solved this by stopping Aware, check the current sequence number on the Windows server by running the SQL statements USE {database}; SELECT NEXT VALUE FOR bas_idgen_seq; and then running the statement USE {database}; SELECT SETVAL(bas_idgen_seq, {number}, FALSE); in MariaDB on Linux. In these statements ‘{database}’ must be replaced by the name of the relevant database and ‘{number}’ by the sequence number that is displayed after the first SELECT statement. This must be done for all databases used by Aware. The USE {database}; is added because you cannot prepend the database name to a sequence like for normal tables. (An alternative for SELECT SETVAL() is the statement ALTER SEQUENCE bas_idgen_seq RESTART {number}. This will lock the table of the sequence object so is theoretically safer to use, but I made sure that there was no other activity on the database).

Restart Aware

Note that if Aware is started before MariaDB on Linux is set to use lower case table names, it can’t find the relevant databases and tables and automatically creates them with uppercase or camel case. This is mentioned in the Server Output in the Control Panel, but it’s also useful to check for any changes directly in the database. If this happens, shutdown Aware and manually delete those new databases. Ensure that MariaDB uses lower case table names (as described above) and restart MariaDB. Then (re)start Aware and check that it finds the existing databases/tables and does not create any new databases and tables.

After these steps, Aware is now happily running with the data from MariaDB on the Linux server. Everything seems to be working fine and the same as before the transfer. The only thing that doesn’t work anymore is the creation of a document from a MS Word template, but that was to be expected and is a separate issue unrelated to the database itself (documentation).