The migration to the cloud continues, today I look at moving WordPress from On Premise hosted on Windows Server 2008R2 with IIS and MySQL to Azure Web Apps and Azure SQL. Although this was a bit of a challenge, as you’ll see, it all works and so long as the plugins you are using are written correctly you shouldn’t have any major problems.
You’ll need a Windows PC/server that can be used as a staging platform. This will need access to the internet, the Azure Portal and having the Azure PowerShell installed won’t be a bad thing.
Microsoft SQL Server Migration Assistant for MySQL – I used version 6 that is available as a download from Microsoft download site HERE.
MySQL Server (WAMP) – this is a nice little community solution that I use for developing WP sites. It has a simple MySQL installation and phpMyAdmin for MySQL administration. Download from HERE
MS SQL Management Studio (SQL Express) – I used 2014 as this, like 2012, has some Azure integration and like the Migration Assistant is downloadable from Microsoft HERE.
Ok, lets go…
Log into your Azure tenant and create a Azure SQL database from with the Azure Portal, instructions on how to do this can be found HERE
We now need to create a new WordPress site within Azure. To save my fingers there is a superb blog article for this HERE
I have seen an error on the completion of the WP installation (see below), it doesn’t seem to cause any issues with the installation however if this is going to be a production system I would recommend looking up the tables, columns referenced in the error and assessing if it’s an issue for your installation.
Mysqldump –uUSERNAME –pPASSWORD DATABASENAME > DATABASENAME.SQL
You’ll also need to backup the web files from the original WP site, we’ll only need some of them but it is good practice to back everything up.
On your staging PC you’ll need to install:
- Microsoft SQL Server Migration Assistant for MySQL.
- SQL Express 2014, you only need the management studio but having a functioning MSSQL database engine available isn’t a bad idea.
Restore the original WP MySQL database into the new MySQL server
Start MS SQL studio and connect to the Azure SQL database, this will confirm your workstation can see the Azure SQL instance.
This will replicate the schema into the target Azure SQL database. If you expand the Azure SQL database you should now see the new tables. Make a note of the Schema name as you’ll need to make sure you address the right tables later.
Expand the database and the tables and you see the WordPress structure.
We need to enable two columns in the wp_posts columns to allow NULLs. The easiest way to do this is to right click the “SCHEMANAME.PREFIX_posts” -> “Script Table as” -> “DROP and CREATE to” -> “New Query Editor Window”
Repeat the process for the “dbo.wp_links” table and modify the [link_updated] to allow NULLs
Once that is done go back to the Migration Assistant. If you have a lot of posts in your blog be sure to change the batch job to a number larger than your number of blogs. If this is not done for some reason the connection gets closed on the Azure SQL and the migration will only partially complete.
The error tab at the bottom of the screen will provide more detail to why it failed.
These will need fixing to perform a full migration. The most common one is the “not allowing NULLs”. Just follow the guidance above to recreate the table with the correct NULL setting. You can migrate the problem table on its own by right click on it and then selecting “Migrate Data”. Do remember to sync the Azure database before you do the migration if you’ve dropped/recreated tables. It normally takes a couple of shots to get all the data across.
Top troubleshooting tip here is to try to migrate the MySQL DB into a DB on the local SQL Express instance, this will normally expose the errors better than SQL Azure.
Last thing to do is update the site URL in the Azure SQL
set option_value = ‘http://awebsite.azurewebsites.net’
where option_id = x
x = option_ID number
Now the database side of things is done you need to copy (FTP) the old content from the previous WP site to the new Azure WP site. The folders to copy are (care should be taken not to overwrite files, recommend backing up current Azure WP site before copying data)
I have struggled to get non-DBO schemas working so the easiest thing I found was to reset the schema that was migrated back to DBO, remember to reset the DB prefix in Config.PHP if that is different from default.
alter schema dbo transfer [migratedschema].[tablename]
You should now be able to login to your site. If you want to keep the current URL to the site you’ll need to add a domain name via the Azure Portal and configure a CNAME on your DNS.