Migrating Databases Made Easy with Aqua Data Studio

by Nov 22, 2018

It can’t have escaped your attention that, in recent years, numerous new database platforms have been introduced on the market. Products such as Amazon Redshift, Apache Cassandra, Google BigQuery, and MongoDB are all new database products developed to support massive query workloads, complex analytical queries, high-end transactional workloads, and big data ingestion rates. Some of them have been designed to benefit from cloud-based hardware architectures and others have been optimized to use the massive parallelization power of GPUs.

To be able to exploit one of these new database servers, migration is necessary. But database migration exercises can be time-consuming and tedious work, so all the help you can get, is valuable. One of the products that can assist in such a migration project is Idera’s Aqua Data Studio (ADS), the Swiss army knife of SQL development environments.

First, the product supports reverse engineering capabilities of existing databases. For a successful database migration, you have to understand the existing data model. You must know all of the details of the source database, including the data model with all of its entities, attributes, and relationships. With ADS’ reverse engineering capabilities that data model can be recreated. The model by itself is already a valuable deliverable. ADS can reengineer the data model from a long list of database platforms, from classic ones to modern cloud-based databases.

The next step is that the target database is created in the new database server. With ADS, the reengineered data model can be converted to generate that target database. Again, this can be done for any database platform that ADS supports, since the database specifications and DDL scripts are generated specific to the target platform. For example, if the datatype of the source database is not supported by the target database, a similar one is used. More to the point, when tables are not supported an equivalent construct is used, for example tables in SQL Server become collections in MongoDB, and so on. After this step, a new empty target database is ready. ADS does allow changes to be made to the data model before it is used to generate the target database.

The last step involves copying the data from the source to the target database. ADS does this by exporting all of the data to a file. This file contains the necessary INSERT statements required to import the data in the target database. In other words, the file contains a long script to load all the data in the target database.

The key advantage of using ADS is that you won’t have to leave the tool to perform any of these steps. Therefore, developers working on such a migration project don’t have to learn multiple IDEs when they switch between database platforms. They can stay and work with their familiar IDE.

After the migration of the data, the visual query capabilities of ADS can be used to run queries on and analyze the source and target databases. This is to check if query results on the target database return the same results as queries executed on the source database. In other words, this is to check that no mistakes were made with lifting and shifting the data.

During this process, several data models, DDL files, scripts, and so on, are created. If versioning is required for these objects, ADS works with several familiar version control tools, including Git and Subversion.

To summarize, to benefit from all of the new database technology that has been released in recent years, a migration is unavoidable. Working with multiple different database platforms can be a complex task, and Aqua Data Studio simplifies the process. Although not all of the specifications can be migrated automatically, such as stored procedures, the support of so many database platforms by Aqua Data Studio definitely eases this migration effort.