Custom database migration: how to eliminate production bottlenecks?
By utilizing data from both MySQL and Microsoft SQL, an industrial manufacturer could streamline their workflow while keeping historical CIMCO project data. The key to success was a proprietary custom software developed by CCA Europe.
How to eliminate bottlenecks in business processes? Often, their root cause is poor communication between departments, which leads to information silos. This can cause data to be scattered across different repositories. Once teams commit to a joint project and attempt sharing the database files, collaboration in such silos becomes impossible. Too often, this is a common reality for many production companies, in which IT resources are often insufficient. What can be done to back up from this data “dead end”?
Two databases, one problem – and a big one, too!
Production and machinery management software allows users to select their preferred database system. This convenience, however, can cause issues when multiple departments or design teams within a company install the software for their own use and, in addition, when they select different types of supporting databases.
A situation like this makes collaboration complicated: which database should be eliminated to facilitate data sharing and to restore a single data repository, in line with IT best practices?
Naturally, each team wants to hold on to their hard-earned information. Therefore, to ensure that both repositories can be used seamlessly and with no loss of database, it is necessary to migrate both databases into a single one.
Migrating databases can become significantly more challenging when each of them has a different architecture, and when there are no ready-made commercial solutions or scenarios to run data migrations easily. In such cases, a custom migration becomes necessary.
To approach such a project, individual steps of the process must be first designed. The starting point is a comprehensive technical analysis and in some cases a parallel business analysis, as in the case of one of our recent database migration projects.
We wrote about the future of data integration here: Future of data integration: traditional, cloud or hybrid? (ccaeurope.pl)
Experience from banking leveraged in industry database migration
Over the years, we have completed a number of successful banking database migrations projects, featuring popular database systems like Microsoft SQL Server, Oracle database and MySQL. Therefore, we understand that the main obstacle in personalized data migration is to guarantee consistency and integrity of the data.
Of course, the banking business is not the only one that must handle merging different data formats and incompatible logic (structure) of databases. Industry, and especially manufacturing, faces similar challenges.
Industrial equipment and machine control systems generate copious volumes of data from a range of sources. These include dedicated CNC software, as well as sensors and control systems. These industrial systems are not supported as strongly in terms of IT as banking operations, as they are less often used and are seen as non-critical.
However, more and more often this IT support becomes necessary. Unfortunately, finding a specialist who can effectively resolve the issue is neither easy, nor cheap. It’s not surprising, therefore, that production companies often resort to taking drastic measures to solve the problem of scattered data.
In the case of a minor repository, companies can simply abandon it and relinquish the data stored. Where they cannot afford it, database migration process is often postponed for several years. Unfortunately, this ultimately impedes their business growth.
Facing a MySQL and Microsoft SQL data silos in an industrial company
In September 2022, we were approached by an IT integrator for the manufacturing sector. The partner needed our support in custom migrating of two databases in a company operating in the metallurgical industry. The client wanted to upgrade their CNC software version to take advantage of the new, attractive features. However, this would require updating the software version for each team separately.
The client was increasingly troubled by the lack of cooperation between departments. Even though their projects often required it, both departments could not exchange the data they generated over the years. By solving the problem of unrelated databases, the client could also avoid the double cost of upgrading the system version.
Following a thorough analysis, we discovered that for several years, one production team at the client’s site used a commercial Microsoft SQL relational database, while the other team had opted for an independent MySQL database.
As a result, when employees logged into the CIMCO application, which manages production documentation for CNC machine tools, the data were entered in either of the two separate databases, depending on the team member. Unfortunately, the lack of communication between these databases led to incorrect messages about machine availability, making it difficult to use the machines effectively in everyday production.
An unconventional solution for a bottleneck in the production process
To ensure that the client doesn’t lose a wealth of important documentation, it was essential to develop a data migration method that would preserve the data integrity.
Both data sources were relational databases. Apart from explicit relationships provided by the database itself (indexes, foreign keys), they also maintained implicit relationships, implemented and maintained at the application level. These implicit relationships are more difficult to identify. Without detailed documentation, especially the database scheme, they could be identified only via test-based analysis.
The documentation, however, was unavailable, which is very common in manufacturing companies using production applications. As a result, during the analysis phase, we had to conduct both technical and business analysis of the databases to determine which data needed to be transferred.
To convert the MySQL to MSSQL architecture and migrate using Microsoft SQL Server Migration Assistant software, we used proprietary software, tailored by the CCA team to address theproject challenge.
Benefits of the implementation: efficient teamwork
Following the successful database migration project, both teams can now work on a single, unified database. All users, projects, and related documentation have been migrated into a single, coherent database. In the “new” database, each existing record received a new ID, and all notes and data were preserved. As a result, we have eliminated the need to maintain two separate databases (which include additional server space and backups). The migration also allowed to cut the costs of software upgrades.
When migrating data, in-house IT departments of manufacturing companies usually need support with more complex projects, especially in supplementing the missing skill set. They also cannot rely on software providers, who are unwilling to engage in migration projects. Unfortunately, IT solutions used in other areas, such as low-code, are not applicable here. Therefore, it is so important to entrust database migrations to experienced specialists,” explains Jacek Nowak, CEO of CCA Europe.
If you need support in business analysis to migrate custom databases, join us for a free consultation, no strings attached!