Usually when it comes to database migration these days, you are moving to the cloud. Top cloud vendors provide utilities and incentives to ease that process. What if you want to switch databases to save on premium licensing costs? For example, migrate an Oracle database to MySQL. What if you develop software that needs to support multiple target database environments such as Oracle, SQL Server, MySQL and so on. Those are ideal use cases for a cross database utility.
Most of the popular database migration and synchronization utilities in the market today only support a single database vendor type. There seem to be only a few truly enterprise quality, cross database migration solutions that can translate schemas and synchronize data between different types of databases.
Recently I tested replication solutions, ETL and migration utilities starting with open source MySQL Workbench. I liked the price – free. I also like the simple wizard. However, I found it to be terribly slow and buggy. I could not complete migration of a tiny demo AdventureWorks database on my fancy 32GB RAM development laptop. Thus I looked for alternative solutions for database migrations.
SQL Examiner Suite
One of the best solutions at a reasonable cost that I tested was SQL Examiner Suite by SQL Accessories. Historically in SQL Server realm, SQL Examiner has received good reviews and impressive, big name client references hidden at the very bottom of their web site.
Being an avid solution tester, I wanted to give SQL Examiner a try. Here are my impressions and notes from a “hands on” review. All in all, I really liked it and will use it in the future myself. As cloud gets adopted and pricing becomes more competitive, I am betting that there will be more migrations of software and databases. In the meantime, even small companies that want to reduce database licensing costs can benefit from moving to less expensive MySQL that has come a looooooooong way since I first learned it fifteen years ago.
Installing SQL Examiner Suite was quick and easy. The software design quality and user experience were strong. I was impressed by the cross database migration support for the following databases including raw back up files.
- Microsoft Access
- Microsoft SQL Server
- OLE DB or ODBC databases
- Database back up files
SQL Examiner’s schema and data migration was both fast and accurate. The data compare with source and target backup files was nice. I also appreciated customization of migration templates for data filtering, ignoring what to compare and script generation styles. Other features include basic scheduling for tasks, command line tools for automation or DevOps, cloud backups, and detail level reporting.
Migrating or Restoring Data
In one of my tests, I wanted to see how I could migrate or restore data from a backup file. Notably this is something that most reputable utilities in this niche market can accomplish within the same database but not to a different database. You might also be able to recover specific records using bundled database management utilities. Regardless, I thought this would be a good test to familiarize myself with SQL Examiner. It passed with flying colors.
The first step involved connecting to the source database backup file and selecting a database destination.
From there I selected the specific table objects that I wanted to compare. Although filtering and customization options are available, I chose the defaults in my test.
After clicking Finish, SQL Examiner rapidly iterated through the compare exercises in 1.6 seconds!
Then I was shown the differences in data between my source and target database and couple optionally export a report.
If I want to synchronize the data, I can click Synchronize, select options to enable or disable triggers, foreign keys, identity columns and so on. SQL Examiner can immediately run the synchronization operations, schedule it or you can save them to a script file.
For cloud and larger database synchronization, there are timeout settings under Project Options.
Additional tests included comparing and synchronizing schema, as well as, comparing and matching data across different databases. In my tests, I migrated SQL Server 2014 to MySQL 6.3.
SQL Examiner Suite 2016 scored very high. It is one of the only cross-database platform data and schema synchronization utility that I tested that could also use back up files as a source. SQL Examiner Suite 2016 was simple to set up, easy to use, designed superbly from a user interface perspective, provides nice depth of options, and seems to be a high quality, proven solution at a competitive price point.
To be fair in my assessment, I need to provide areas for improvement too. Personally I’d like to see added support for destination cloud databases like Amazon RedShift , Google BigQuery, and Snowflake in the future. Spark would be also fantastic since it is gaining serious market momentum displacing early Map Reduce implementations.
If you’d like more information on SQL Examiner Suite cross database migration, check out the following resources.