Porting to MySQL
We should have known better than to emit phrases such as ‘That sounds like an interesting problem’ or ‘I can’t see why you couldn’t do that’ or, the real killer, ‘That should be quite easy’. We can’t quite remember which of these three it was precisely that got us into trouble, but it was almost certainly that last one, when we were discussing a problem with a client. The problem – the one that was going to be ‘easy’ – was porting a web-based ASP application based on Microsoft’s SQL Server 2000, about which we knew almost nothing, over to MySQL. Obviously, we made the attempt, and in the end the exercise went fairly well or we wouldn’t be writing about it here, but there were some fun and games along the way.
It’s often the case that one person’s real-world problem is someone else’s totally obscure problem that could never crop up. To many eyes, porting an application from one database management system (DBMS) to another might seem like an act of extreme futility, but there was some method to our madness. Many people have applications that are based on a particular DBMS: that application works well and the DBMS they’re using does everything they want it to. Then, at some point, the machines running the application need to be updated, and so a new operating system requires a new DBMS version.
Now, the best-known commercial DBMSes are by and large expensive products – an SQL Server installation will often cost more than the hardware you run it on. So at this update point, porting to a different database may well become a worthwhile activity. We ought to stress that, for the purposes of this article, the idea of porting an application to a different DBMS was more about bragging rights and less about a rational business decision.
The application we’d talked ourselves into porting was the Demographix online survey system. There were various reasons we got involved with Demographix: first of all, our company hosts the Demographix servers at one of our facilities, so we had ready access to the application. Second, the application was developed by Derek Cohen, a former editorial director at PC Pro’s publisher Dennis Publishing – hence he was both our boss and client.
So the challenge had been laid down – could we port the Demographix application from SQL Server to MySQL. It was going to be interesting, because we hadn’t used SQL Server ourselves for a couple of years, and also due to the fact that the application was written in Visual Basic using ASP, which isn’t something we encounter too often. The most obvious approach to the problem was to port the data from one DBMS to the other, and then to take a look at the code of the application.
Porting the data
We did have one ace up our sleeves when we said this was going to be easy, which was that we knew of a product called the MySQL Migration Toolkit. This application is specifically designed to allow a user to port their data from another DBMS into MySQL, and we knew the toolkit could solve a number of problems for us, so that was our starting point. Like all MySQL-related software, it’s downloaded from the website and simply installed. In our case, we decided to install the migration software on a Windows machine, but to use a Linux machine to run the new DBMS. Our source was SQL Server 2000 running on Windows 2000, and our destination database was MySQL 4.1.
Before we started the process, we took a few simple precautions. We took a copy of the existing Demographix data from a backup and restored it to a completely separate server. We also wrote a simple script which went through all the data and removed any personal information such as people’s names. Although all the correct legal permissions were in place for us to process any personal information we found, it is always safer to take this step when you are working on a development. Beyond that everything else was the same – the number of tables and their definitions.