The developers at my company use a product called Team City to run their build server – a server that compiles source code into an installable package for testing, release, etc. Team City uses a database backend to keep track of things. I don’t have much experience with it, but it seems pretty flexible. We were initially using a MySQL database server to host the database and we decided to migrate this to SQL Server 2008. As part of the migration process, the development team was also upgrading the version of Team City. I ran into some interesting situations while working on this project and thought I’d share them in case anyone else has to do something similar.

The migration process seemed fairly straightforward – simply transfer the tables from MySQL to SQL Server, point Team City to the new server, and let it go. As usual, things are never quite as simple as they seem.

MySQL has a fairly simple way of creating backups. A backup of a MySQL database is simply a huge text file full of SQL commands – first commands to create the tables, then tons of INSERT statements to insert the data into the tables. Not very elegant or compact, but it works. Unfortunately, the SQL syntax used by MySQL is slightly different than T-SQL – most notably in how it defines primary keys, indexes, and data types. So my first step in the migration was to look through the MySQL backup file (which was about 300 MB), find the tables definitions, and recreate the tables in SQL Server.

Once I had the tables created, I created an SSIS package that connected to the MySQL database and transferred the data. In order to do this, I needed to install the MySQL Connector, available at http://dev.mysql.com/downloads/connector/net/. This allowed me to create a connection to the MySQL database in my SSIS package. My package was a simple one – I simply set up a straight data copy from the MySQL tables to the SQL Server tables. The database contained 56 tables, so I made 8 data flow tasks, each containing 7 tasks to transfer data from the source table to the destination table. I ran the package and the data transferred.

My last step was to create a login for Team City to use. That was fairly easy. Everything looked fine on my end.

Then the development team tried to run the Team City upgrade. It failed.

I’ll spare you all the troubleshooting steps and just give you the list of things I discovered.

1. The upgrade process modified the tables. Columns were added and deleted. Indexes were modified and created. This lead to two discoveries. First, when re-running the import, I could not just clear the data out of the tables and reload. I had to actually drop all the tables and re-run the script that created them. Second, index names mattered, as the upgrade called some out by name to modify them.
2. Only some of the tables in MySQL had an explicitly defined primary key. Several had a unique, clustered index on non-nullable columns, which MySQL will use as a primary key when one isn’t explicitly defined. When I recreated the indexes in SQL Server, I initially created these as primary keys. I suspect, but am not certain, this caused a problem. The problem was likely related to the previously mentioned naming issue, but just to be safe, I changed my scripts so that the indexes were created as unique, clustered indexes.
3. The account Team City uses to access the database needs dbo rights.

Item 3 was really tricky and the Team City upgrade process gave some misleading error messages. One in particular was:

Now, when I see this, I think two things: 1) when a program tells me its database is “empty,” it means the tables in the database have no data in them and 2) if the upgrade was able to determine a version number, it has to be reading that from the database somewhere.  It turns out, both of those thoughts were incorrect. I was never able to determine where Team City was getting the data version number from. I know there are some configuration files on the Team City server, but the developer’s swore that version info wasn’t in them, and I searched all the database tables for a version number and didn’t find one. As for item 1 – it turns out the upgrade program was not seeing any tables because it did not have dbo rights (I created the tables as dbo). Typically in cases like this, where a table can’t be found, I would have expected an error along the lines of “invalid object name <tablename>.” While that’s not a very user-friendly error message, at least it points me in the right direction for fixing it. But this nicely phrased error lead me down a wrong path. I applaud the software’s attempt to create user friendly error messages, but they also have to be useful for diagnosing the actual problem. Or maybe it’s just me and a quirk of how I interpreted “empty.” Either way, once we got these problems straightened out, the migration and upgrade went smoothly.

Update: I write these posts in advance and, a week after I wrote this one, Microsoft announced the release of their SQL Server Migration Assistant for MySQL v1.0. I have not looked into this as yet, but it probably would have saved me a lot of time and effort.