MSSQL to MySQL migration
July 10, 2007 – 1:48 pmWell today I start the initial steps of migrating our MSSQL databases to MySQL. One of the first tasks here at the new job was to determine where our MSSQL licensing was at. We’re currently running a version of MSSQL 2000 Personal edition so we either need to migrate or get our servers legal. <rant> Our existing setup is 2 servers, 1 IIS server, and another dual processor system running MSSQL. Due to Microsoft’s awesome licensing setup, in order to be legal we need to purchase 2 MS Windows Server Unlimited Connector licenses. The part that makes no sense, is one is for the IIS server, the other is for the SQL server. I can understand one, but the second is ridiculous.</rant> So in the end, it’s pay $17,000+ to be legal, or move MySQL, which in the benchmarks I can find, smokes MSSQL.
After this, I went ahead and installed the MySQL migration tools on the MSSQL due to the application crashing on the CentOS box. I had to modify the grant tables to allow root to remotely connect to the system. After this, I ran into a small pitfall of the Migration tool. The default value of some of the fields in my table refer to MSSQL functions, which aren’t compatible with MySQL so I had a compatibility error. For example:
`beginningdate` DATETIME NULL DEFAULT convert(datetime,(convert(varchar,datepart(year,getdate())) + ‘-’ + convert(varchar,datepart(month,getdate())) + ‘-’ + convert(varchar,datepart(day,getdate())))),
Since getdate() and datepart() aren’t acceptable functions, I removed the default value for now. After that all of the data migrated fine except for a few truncated records. When I move this to production though I’ll be sure to solve that problem.
One Response to “MSSQL to MySQL migration”
I welcome the disco ball, and I welcome the frequent updates! I’d wrap my arms around both of them to convey my affection if they weren’t so infuriatingly intangible.
I also like the techieness of the updates. Now that all of us old friends are spread apart and keep in touch only digitally, it’s nice to see what tech stuff you’ve been up to. Plus, that’s how we learned in the old days, remember? Collaboration and sharing and a sick, but effective, desire to “outdo” one another. It’s a shame that mindset disappears as we get older, but maybe this kind of thing will keep it alive.
You just may have inspired me to create my own techieblog– since I think I’m back into computers again.
Hold on to the safety bars.
PS, OMG, I totally RSSed you.
By Brandon on Jul 11, 2007