As we were in the process of unifying the technologies we used, we decided to move our Umbraco sites to MS SQL from their current MySQL database. This was done while the Umbraco installs needed to be upgraded anyway, as we were currently running 4.9, and we wanted to upgrade to 7.0. I quite a bit of trouble finding a guide which worked for me, resulting in a combination of a few plus some things I came up with myself. Since I had to write a guide anyway, as we had to do this multiple times, I thought it could potentially help others in a similar situation.
I’m not sure if there is a better version to upgrade in, but the steps I ended up taking seems like there is not a real difference, though I suppose it could potentially get more difficult the longer you wait, if more tables are added. Since we were moving from 4.9 to 7.0 I chose to do it here.
Download the Microsoft SQL Server Migration Assistant (SSMA). Connect to the source MySQL server and the destination MS SQL server using the SSMA UI. For some reason you have to specify a database on the MS SQL server even though you will not be using it for this, but you’ll need a dummy DB later for something else anyway, so I’ll advise creating a dummy DB. Click on the source MySQL DB and choose “Convert Schema” which will copy the DB schema to the MS SQL server, creating a DB of the same name as the MySQL db. You will be able to see the new DB in the SSMA UI. Click the new MS SQL database and choose “Synchronize with database”. Doing the synchronization finalizes the creation the MS SQL server, and you will now be able to see the database in Microsoft SQL Server Management Studio (MS SSMS).
You now have a completely empty database with the (almost, we will get back to this) correct schema and foreign key constraints. You can now click the MySQL database and choose migrate Data, which will move the data to the new MS SQL database. Check through MS SSMS that the data has actually been migrated. If not, you might need to click the MS SQL DB in SSMA and choose synchronize with database.
Now the data has been successfully moved, but if you launch your umbraco website with the MS SQL database connection string, you’ll notice you’re getting errors. Personally, I started out getting an InvalidCastException as umbraco could not retrieve a boolean from the database. This is because that SSMA can’t properly convert all columns’ data types, but fear not, help is close at hand.
In order to figure out which data types umbraco expects in the MS SQL version, I created a dummy umbraco website running a barebone 4.11.10 and accessed its umbraco which began the initialization process. You’ll only need to complete the steps up until and including database creation, where you’ll have to specify the database which you want to use for your new umbraco website. It is here you should use your previously created dummy DB. If you run into any issues doing this, you might have a connection issue to the DB. Try connecting with the user you’re using through MS SSMS as this will give you better error details that you can google. Once the DB initialization is complete, you have an empty 4.11.10 MS SQL umbraco database.
Now for the cumbersome part. I compared the migrated database’s tables to the ones created by the umbraco 4.11.10 tables and updated data types where need be. For instance binary(1) had to be changed to bit, datetime2(0) to datetime and so on. I don’t know if there are some types that you can leave as if, but I changed all of them just to be safe. MS SSMS will complain about potential loss of data when you change the columns’ data types. Our umbraco is quite a big one but as far as I could tell, the data was successfully converted. Once I’d gone through all the tables one by one, which to be honest did not take that long, I ran the following script on the database:
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
which enabled all foreign key constraints for existing data as well.
I changed the connection string in the web config so it looked like this:
I know this is an old post but with Umbraco dropping the support for MySQL it's a very pertinent one.
I followed your instructions and I could migrate my database. However, I had to do a lot of other things to have a schema as close as possible as the one of Umbraco, so I decided to write a post based on your explanation to explain in details all the steps I had to perform to have a correct database. Here it is: https://ssougnez.medium.com/migrate-a-mysql-umbraco-database-to-sql-server-20861f55d29d
Migrating Umbraco from MySQL to MS SQL
As we were in the process of unifying the technologies we used, we decided to move our Umbraco sites to MS SQL from their current MySQL database. This was done while the Umbraco installs needed to be upgraded anyway, as we were currently running 4.9, and we wanted to upgrade to 7.0. I quite a bit of trouble finding a guide which worked for me, resulting in a combination of a few plus some things I came up with myself. Since I had to write a guide anyway, as we had to do this multiple times, I thought it could potentially help others in a similar situation.
I’m not sure if there is a better version to upgrade in, but the steps I ended up taking seems like there is not a real difference, though I suppose it could potentially get more difficult the longer you wait, if more tables are added. Since we were moving from 4.9 to 7.0 I chose to do it here.
Download the Microsoft SQL Server Migration Assistant (SSMA). Connect to the source MySQL server and the destination MS SQL server using the SSMA UI. For some reason you have to specify a database on the MS SQL server even though you will not be using it for this, but you’ll need a dummy DB later for something else anyway, so I’ll advise creating a dummy DB. Click on the source MySQL DB and choose “Convert Schema” which will copy the DB schema to the MS SQL server, creating a DB of the same name as the MySQL db. You will be able to see the new DB in the SSMA UI. Click the new MS SQL database and choose “Synchronize with database”. Doing the synchronization finalizes the creation the MS SQL server, and you will now be able to see the database in Microsoft SQL Server Management Studio (MS SSMS).
You now have a completely empty database with the (almost, we will get back to this) correct schema and foreign key constraints. You can now click the MySQL database and choose migrate Data, which will move the data to the new MS SQL database. Check through MS SSMS that the data has actually been migrated. If not, you might need to click the MS SQL DB in SSMA and choose synchronize with database.
Now the data has been successfully moved, but if you launch your umbraco website with the MS SQL database connection string, you’ll notice you’re getting errors. Personally, I started out getting an InvalidCastException as umbraco could not retrieve a boolean from the database. This is because that SSMA can’t properly convert all columns’ data types, but fear not, help is close at hand.
In order to figure out which data types umbraco expects in the MS SQL version, I created a dummy umbraco website running a barebone 4.11.10 and accessed its umbraco which began the initialization process. You’ll only need to complete the steps up until and including database creation, where you’ll have to specify the database which you want to use for your new umbraco website. It is here you should use your previously created dummy DB. If you run into any issues doing this, you might have a connection issue to the DB. Try connecting with the user you’re using through MS SSMS as this will give you better error details that you can google. Once the DB initialization is complete, you have an empty 4.11.10 MS SQL umbraco database.
Now for the cumbersome part. I compared the migrated database’s tables to the ones created by the umbraco 4.11.10 tables and updated data types where need be. For instance binary(1) had to be changed to bit, datetime2(0) to datetime and so on. I don’t know if there are some types that you can leave as if, but I changed all of them just to be safe. MS SSMS will complain about potential loss of data when you change the columns’ data types. Our umbraco is quite a big one but as far as I could tell, the data was successfully converted. Once I’d gone through all the tables one by one, which to be honest did not take that long, I ran the following script on the database:
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
which enabled all foreign key constraints for existing data as well.
I changed the connection string in the web config so it looked like this:
And that was it, I could boot up my umbraco site using the MS SQL database.
I know this is an old post but with Umbraco dropping the support for MySQL it's a very pertinent one.
I followed your instructions and I could migrate my database. However, I had to do a lot of other things to have a schema as close as possible as the one of Umbraco, so I decided to write a post based on your explanation to explain in details all the steps I had to perform to have a correct database. Here it is: https://ssougnez.medium.com/migrate-a-mysql-umbraco-database-to-sql-server-20861f55d29d
I hope it will help others.
Hi ssougnez, Im very pleased to hear that you were able to use this for your own migration!
is working on a reply...