Migration automation software can take a lot of the tediousness out of migrating from Microsoft SQL Server to MySQL migrations. In the previous installment of SearchEnterpriseLinux.com's series on migrating from MS SQL to MySQL, Mike Sheffey, CEO of migration tools vendor Versora, gave in-depth descriptions of some manual migration processes. But it's no surprise that Sheffey, who is in the business of migration automation software, feels that manual migrations are a bit primitive. Here, Sheffey explains the benefits of automating the migration process and offers a step-by-step guide to migration with Versora's automated tool.
Can you explain how automation with Versora's product changes the processes related to moving the data structure and the data itself?
Mike Sheffey: Versora's Progression DB migrates all database structure, data, stored procedures, columns, tables, schema and triggers via an easy to use next-next-finished interface. With the automated migration, the re-implementing time is reduced significantly. All tedious steps of converting data, types, tables, [data] structure and data definitions will be auto-fixed. Migration without SQL statements (triggers, stored procedures) using the automated migration software is nearly flawless, significantly easier and will be almost entirely hands-off.
Express and custom migration options are available which allow the user to either automate the conversions or use a syntax highlighting editor to edit transferred SQL statements. Progression DB is offered under an Enterprise Source License (ESL) which allows the administrator the option of viewing and modifying the source code to meet any of the organization's specific needs.
The ESL also gives the organization control over future development of the code to avoid lock-in to a specific vendor. In brief, Progression DB copies and stores all necessary database files and tables as well as security and configuration settings for the Windows-based database in a Platform Neutral Package (PNP). The PNP can then be applied to a MySQL database running on Linux.
Can you describe exactly how the automated process might differ from the manual processes you described earlier in this series?
Sheffey: Here's step-by-step guide to an automated migration via Versora's Progression DB.
- Insert the Progression DB CD into the machine running Microsoft SQL Server. Though not required, this is the recommended process as it is faster. Alternatively, you can insert the CD into any machine, though the process will be slower.
- Select the database server to be migrated.
- Enter your user name and password.
- Select a location for the Platform Neutral Package (PNP) and click Next. Though not required, it's faster if the PNP is copied to the local machine. Depending on the size of the database, this process can take anywhere from five minutes to eight hours. Note that these time estimates apply to computer time, not technician time. This process can run unattended and thus requires approximately ½ hour of technician time.
- The next steps make the PNP file accessible to the Linux machine by using a remote file server (most secure method), using CDs, or by turning on file sharing on the database servers (recommended and the easiest method). Insert the CD for Progression DB in a Linux machine or sever (much faster if done locally).
- Double click on File and enter your password if prompted.
- Click Continue to start the installation wizard on the Linux machine.
- Enter connection information (server, user name, and database password) and click Continue.
Next, choose the type of migration, either express or custom. Express migration automatically performs the conversions. Custom migration lets you edit the automatically transferred SQL statements in a syntax highlighting editor. Custom migration also provides a warning if there are any database incompatibilities so you can make changes before the migration.
Select Continue to migrate the data. This migration step can take between five minutes to eight hours depending on the amount of information being migrated. If completed via a network connection, this step will take longer. Either way, the automated database migration requires only about half-an-hour of technician time versus hours/days of technician time with a manual migration.
Make sure you verify automatically converted statements such as stored procedures, triggers, views, etc. As mentioned previously, Progression DB offers editing in a syntax highlighting editor for this process. As with a manual migration, the user may need to modify programs to integrate with the new database.