Hi Ben,

I'm not sure there is a direct way to migrate from MySQL across to SQL Server.  An easier way would be to create a package within your Umbraco install - taking all the content, doc-types, media-types, media, macros, etc.  Create a clean/fresh Umbraco install and then install that package?

But this depends a lot on how your Umbraco site is set-up... i.e. does it have multiple root sites/nodes? (as the packager only lets you select one of them).

Good luck, Lee.

I'll try your idea! I didn't think of using packages.

Well the package idea didn't work. I have 5000 nodes, and the packager dies halfway through the process.

I'm thinking I may need to write a .net app to migrate the data. Back to the drawing board...

Hmmm... maybe package-up everything but the content ... and then use CMSImport to migrate it? (Although the free version is limited to 500 records).

http://our.umbraco.org/projects/developer-tools/cmsimport

I could use a converter as well, both ways (MySQL<->MSSQL)

You may need to increase the timeouts in the web.config file so the process doesn't timeout.  The default is about 110 seconds.

 <httpRuntime executionTimeout="[seconds]" maxRequestLength="[kb]"  />

Also you could try breaking up the packages into smaller packages to reduce the size and process time to create the package.

-Chris

I am still curious if it is possible to migrate between MySQL and SQL Server directly? My database knowledge isn't that good, but they are both ODBC-compatible databases, and the schemas are the same... so is it possible?

Found this: http://www.codeproject.com/KB/database/migrate-mysql-to-mssql.aspx

Here are some other options:

http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

-Chris

So far the problems I've encountered are the differences in schema, and the use of foreign keys.

The biggest difference I know of is that mySQL doesn't have GUID column types whereas the SqlServer schema does.

The problem with exporting/importing as a package is that I believe all my nodeID's will change, and that will break things like macros and navigation that have specific node ID's hard coded in them.

The reason I'm going through all the motions is that MySql support is lacking in 4.5 and I'm worried that it will never catch up as newer Umbraco versions are created. I don't want to find myself stuck on an older version just for MySql support.

This isn't an impossible task, it's just going to take some work.

OK, I'm pretty damn impressed by this... I wasn't aware that SQL Server could have Linked Servers.  The link above covers this, here's a quick recap.

Ref: http://www.codeproject.com/KB/database/migrate-mysql-to-mssql.aspx

First make sure that you have the ODBC connector installed for "MySQL ODBC 5.1 Driver".

http://dev.mysql.com/downloads/connector/odbc/5.1.html

In SQL Server Management Studio (this also works in Express versions), open a New Query and use the following:

EXEC master.dbo.sp_addlinkedserver 
@server = N'MYSQL', 
@srvproduct=N'MySQL', 
@provider=N'MSDASQL', 
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; _
    DATABASE=umbraco; USER=root; PASSWORD=password; OPTION=3'

Replace the SERVER, DATABASE, USER and PASSWORD with the correct values.

Then create a new SQL Server database - lets say called "UmbracoCMS" and use the following query...

SELECT * INTO UmbracoCMS.dbo.umbracoNode
FROM openquery(MYSQL, 'SELECT * FROM umbraco.umbracoNode')

That should create a new table with structure and data in the new SQL Server database!

You'll have to do that for each table, unless someone knows a way to iterate over them?

Not sure what to do about the foreign keys and constraints, I'm sure there's a way to handle those too?

Good luck, Lee.

You might be on the right track there. Thanks for the tip! I'll hack away.

Is it possible to go the other way around? MSSQL -> MySQL ( I have hosting account where I have both MySQL and MSSQL database as an option, and the mssql part is pretty maxed out in terms of resource usage) 

Hi Simon,

Chris posted a link (page 1 of this topic) about moving from SQL Server to MySQL:

http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

Scroll down to the section on "Migration Tools".

Cheers, Lee.

Nice, thanks Chris and Lee!

After looking around for a while, I just blogged about how to do this - the main steps are:

1. Create a SQL database using the fresh installation scripts

2. Set up a linked database from SQL Server to MySQL

3. Migrate the data (using the script on my blog)

4. Change web.config to point to the new database

I hope this helps someone!  - http://adventuresindotnet.blogspot.com/2010/10/migrating-umbraco-from-mysql-to-mssql.html

I'm having trouble using Tim's script for Umbraco 4.5.2.

I've made some corrections but still having problems with foreign keys. I've posted the details on his blog: http://adventuresindotnet.blogspot.com/2010/10/migrating-umbraco-from-mysql-to-mssql.html. If anyone could take a quick look to see if I've missed some basics, I would be glad.

/Lars Skjoldby

is working on a reply...