Wednesday, July 25, 2007

Migrating SQL Server 2005 with the Migration Toolkit

I have a small website that I've built for my wife who is a realtor.  It's a simple site that tracks properties, showings, etc and it was built using ASP.Net and Sql Server.  Recently I decided to move it to Connector/Net and MySQL 5.1 so I needed a way to migrate the data.  Enter the MySQL Migration Toolkit.

This is a terrific tool that can migrate data from various databases into MySQL.  My SQL Server instance was setup to support mixed mode authentication and I had enabled the SQL Server Browser service and the TCP/IP protocol so I didn't anticipate any problems.  I was almost right.

The one thing that tripped me up was that I was using a named instance with SQL Server.  My instance was named SQLExpress so the hostname of the instance was .\SQLExpress.  The problem is that the JDBC driver that the toolkit uses doesn't accept that as a hostname.  To get it to work you have to enter a custom JDBC connection string and give the instance name as a property at the end of the string.

Once that was done the migration worked reasonably well.  A couple of tables generated syntax errors but I've been told that bug has been fixed and I  had to manually migrate my single stored function and single view.  Even with that, the toolkit saved me quite a bit of time.

9 comments:

  1. "give the instance name as a property at the end of the string"

    How? What do you enter as the parameter?

    Thanks,
    Rich

    ReplyDelete
  2. Rich,

    Have a look at this website. http://jtds.sourceforge.net/faq.html. Check out the item about instance.

    ReplyDelete
  3. great stuff dude..
    thanks a lot..

    ReplyDelete
  4. busby seo test blog tipsJanuary 9, 2009 at 6:19 AM

    It must be a great news for someone who needs migrating SQL Server 2005 with the Migration Toolkit. Great tools.

    ReplyDelete
  5. nice tips..

    ReplyDelete
  6. Waukesha Website DesignMarch 18, 2009 at 12:42 AM

    The link indicated above ( http://jtds.sourceforge.net/faq.html. ) doesn't work. It should be http://jtds.sourceforge.net/faq.html . Anyway, this link did provide the connectionstring info I was seeking. Thank you for the tip.

    I just got done migrating an Access database into MySQL. Worked like a charm.

    ReplyDelete
  7. i want to built a website for my wife too:)

    ReplyDelete
  8. replica Louis Vuitton handbagsJune 15, 2009 at 4:24 PM

    was that I was using a named instance with SQL Server. My instance was named SQLExpress so the hostname of the instance was .\SQLExpress. The problem is that the JDBC driver that the toolkit uses doesn't accept that as a hostname. To get it to work you have to enter a custom JDBC connection string and give the instance name as a property at the end of the string.

    ReplyDelete
  9. Please let me know if you are interested to work as article writer for me? I can offer $10/article.

    ReplyDelete