What is Aident SQL Migration?
Aident SQL Migration supports you in quickly setting up and executing migrations through automatically generated SQL queries. A central aspect is the automated creation of company, table and field assignments, which ensures that the data is transferred correctly from the source database to the destination database.
The automatically generated SQL queries take into account table schemas, collations, field data types and field lengths and enables fast data transfer that can be used for both single and multiple companies.
The SQL queries created in this way are transparent and can be repeated if necessary to ensure a smooth and accurate data migration.
How does the Aident SQL Migration work?
The Aident SQL Migration app is based on the automatic creation and execution of SQL queries. These are created automatically based on the known database information in Business Central and executed directly on the database server of the destination database. In order to be able to create such a query automatically, the necessary properties of the source database and the destination database must be known. These are also retrieved from the databases by creating and executing SQL queries and written in the setup tables of the Aident SQL Migration app.
Important Considerations & Best Practices
- This application is an on-premise app that uses DotNet components to establish SQL connections and execute SQL commands. It's therefore not possible to use it in a cloud environment.
- Individual queries, such as disabling or enabling indexes, can only be successfully executed in the context of the destination database. Therefore, it is recommended that you always run the app in the destination database.
- When data is transferred, the log file of the destination database can become very large. To avoid migration issues, we recommend that you create packages (Table Part Assignments) for tables with many records and migrate them individually. The log file should then be truncated or deleted if necessary. The recovery method of the destination database should be set to "Simple" during the migration and should only be switched to another recovery method after the migration is complete.
- In order to increase the performance of the migration, the size of the destination database should be sufficiently dimensioned. In addition, the automatic expansion of the destination database by a fixed value (e.g. in 20 GB increments) should be done and not as a percentage.
- Before commissioning, at least one test migration should be carried out with all databases, companies and tables that are also to be migrated during commissioning.
- During the migration, no triggers or events will be executed in Business Central. The data is simply read from the source field and written to the corresponding destination field. Any further data adjustments that may be necessary due to schema changes may have to be made by implementing and executing further post-processing procedures.
Important
Please note that the execution of SQL queries on the database server can't be interrupted from Business Central. Clicking the Cancel button during the execution of an SQL query only interrupts the process in Business Central but not the execution of the SQL query on the database server. The execution of the SQL queries must then be canceled on the database server itself.