Last weekend I had the unenviable task of moving a legacy ASP.NET application to a new server, and as part of the move I had to migrate the application’s database from SQL Server to MySQL, the only SQL flavor supported by the new server.

What I thought would be a fairly routine switchover turned out to be a rather involved couple of days, and none of the resources I could find online were able to illuminate the whole process for me. So I’m writing the post I wish I could have read before embarking on this adventure.

Here’s what I did:

Step 1. Copy over the Database

To copy over the data from the old SQL Server database to the new MySQL database, I used MySQL Workbench’s Migration Wizard, connecting to the old database through a SQL Server ODBC driver.

One would hope that this would be an automatic process, but unfortunately, it didn’t quite work without manual intervention, for a few reasons:

• The SQL generated by the Migration Wizard to recreate the tables failed with a cryptic “Error Code 1005. Can’t create table”, giving “Specified key was too long; max key length is 767 bytes” as he reason. It ended up being an encoding issue – for whatever reason, the generated SQL assigned an unusual character encoding to each field. This issue went away after I removed all of the ENCODING clauses in the generated SQL and re-ran it.
• Timestamp literals (e.g. in default values for TIMESTAMP fields) were not correct in the generated SQL, I assume because of differences in how dates and times are represented in SQL Server vs MySQL. I had to fix these by hand.
• After all this, some indices and constraints still got messed up, and needed to be manually fixed before copying over the data from the original database.

Step 2. Install New Packages

NuGet wasn’t really a thing when I first created this application (in 2011), so I wasn’t looking forward to this step, but I was pleasantly surprised by how easy NuGet is to use and how solid the Visual Studio integration for it is.

One thing that was a little tricky was that my application uses .NET Framework 4.0 (and I’ve been unable to upgrade it to 4.5+ for a variety of reasons), so I couldn’t use any of the last fewpain versions of MySql.Data (both v8 and v6.10 require .NET Framework 4.5). It took some trial and error, but I ended up installing the following packages:

• MySql.Data 6.9.12
• MySql.Data.Entity 6.9.12
• MySql.Web 6.9.12 (for membership, see Step 4 below)
• WebMatrix.WebData (for membership, see Step 4 below)

Step 3. Configure MySQL in web.config

Now that MySql.Data.MySqlClient is installed, switching over to it is actually quite painless, and requires no code changes. I made the following changes to my Web.config file:

• In configuration/connectionStrings, update all the connection strings to point to the MySQL database and swap out providerName="System.Data.SqlClient" for providerName="MySql.Data.MySqlClient".
• In system.data/DbProviderFactories, add the following (this may be automatically added for you by NuGet):

<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.12.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />

• Replace the entityFramework section with the following:

<entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
  <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6" />
  <providers>
    <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.12.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"></provider>
  </providers>
</entityFramework>

• In runtime/assemblyBinding, add the following:

<dependentAssembly>
  <assemblyIdentity name="MySql.Data" publicKeyToken="c5687fc88969c44d" />
  <bindingRedirect oldVersion="1.0.0.0-6.9.12.0" newVersion="6.9.12.0" />
</dependentAssembly>

And now …

After I performed steps 1–3, I tried running my application. Everything worked! I tried changing up some values in the MySQL database to make sure that we weren’t still pointing to the old SQL Server database. It picked up the new values! Well, that wasn’t so bad.

So I shut off SQL Server, reload the page, and …

A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Wait, what? We’ve switched over the connection strings to use MySqlClient – how could we still be trying to access SQL Server after?

After some disbelief, I finally realized that:

1. While testing my changes, I (foolishly) set up the new MySQL database on the same box, with the same database name and with the same admin user credentials as the old SQL Server database (albeit on a different port) – in other words, with an identical connection string, and
2. I’d neglected to make any changes to the <membership> section of Web.config, so the default System.Web.Security.SqlMembershipProvider provider was still being used – and SqlMembershipProvider only supports SQL Server.

So, by a unfortunate confluence of events, even though the application was correctly connecting to the new MySQL database via MySql.Data.MySqlClient, the membership provider was reading the same connection strings as SQL Server connections and connecting to the SQL Server database that happened to be on the same box, with the same admin user credentials – a SQL Server database that had still existed in my initial tests, before I shut it down!

If, like me, you’re using SqlMembershipProvider, there’s one more step you’ll need to take:

Step 4. Switch over to MySqlMembershipProvider (if needed)

First, add the MySql.Web and WebMatrix.WebData packages with NuGet if you haven’t already.

Then, switch to MySqlMembershipProvider in the <membership> section of your Web.config:

<membership defaultProvider="MySQLMembershipProvider">
  <membership>
    <providers>
      <add name="MySQLMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.9.12.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="..." ... />
  </membership>
</membership>

You may also need to configure MySQLProfileProvider and MySQLRoleProvider in Web.config, or this may automatically be done for you by the NuGet installation. In any case, I didn’t have to do anything with these.

After doing this, my application was finally able to start up without crashing (because it no longer was trying to connect to a SQL Server database that no longer existed), but none of the user-related logic was working, as Membership.GetAllUsers() would always return an empty collection.

It turns out that MySQLMembershipProvider, sensibly, uses different tables in your database than the default SqlMembershipProvider does: e.g. instead of an aspnet_users table it uses a my_aspnet_users, and similarly all other tables are prefixed with my_aspnet_. Migrating the data over from the old aspnet_ tables was not completely straightforward either, because the schema is slightly different, with columns renamed and reordered, and in some cases a few columns added. I did some janky SQL-fu to get the user data moved over because I couldn’t find a more automated solution (old StackOverflow threads mention a ASP.Net configuration tool that could help with this user data migration, but it doesn’t seem to exist anymore).

And you’re done!

It was a slightly more perilous journey than anticipated, but after following these steps I was able to get my legacy ASP.NET v4.0 application running with MySQL. And now that it’s on a database platform with as rich an ecosystem as MySQL, hopefully I won’t have to do anything like this again.