Azure – Migrating WordPress from MySQL to Azure Web Apps and Azure SQL

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.

Required tools:

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.

Wordpress on Azure errors
Once the WP site is up you can link the Azure SQL database from within the Azure Portal.  While in this screen you can unlink the MySQL database that was created during the website creation.

Wordpress on Azure SQL database
You’ll need a backup of your current WordPress site, the easiest way to do this is by using MySQLDump command this can be found in the “bin” directory of the MySQL 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.
  • WAMP
  • 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.

WordPress on Azure - Connect to Azure SQL
Start the SQL Migration tool and create a new project (note the “migrate to” option)

WordPress on Azure - Migration tool setup
Click on “Connect to MySQL” and enter the properites for connecting to the MySQL instance with the restored MySQL DB.

WordPress on Azure - Migration tool connect to MySQL
And repeat the same for connecting to Azure SQL

WordPress on Azure - Migration tool connect to Azure SQL
Select the WP MySQL database (just the tables container)

WordPress on Azure - Migration tool select tables
From the MySQL window right click on “Tables” and select “Convert Schema”

WordPress on Azure - Migration tool convert schema

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.

WordPress on Azure - Migration tool replicate schema
Note that they are all in BOLD text, to commit these changes right click on the database name in the Azure SQL window and select “Synchronise with Database”

WordPress on Azure - Migration tool start syncronisation
It will process the metadata then prompt you to synchronise, click OK

WordPress on Azure - Migration tool sync progress
Go back to SQL Server Management Studio and connect (if you’ve disconnected) to the Azure SQL database.

Expand the database and the tables and you see the WordPress structure.

WordPress on Azure - view tables in Azure SQL
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”

WordPress on Azure - Azure SQL schema changes
Edit the columns highlighted to accept NULLs  (i.e. remove the NOT)

WordPress on Azure - Azure SQL changes
Hit “Execute” to drop the current table and recreate it.

Repeat the process for the “dbo.wp_links” table and modify the [link_updated] to allow NULLs

WordPress on Azure - Azure SQL changes 2
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.

WordPress on Azure - Azure SQL Migration batch size
You have to sync the Azure database again by clicking on the “Azure SQL” database and select “Synchronize with Database”

WordPress on Azure - Azure SQL sync database
Once that is done return to the MySQL WP database and right click on the tables container and select “Migrate Data”

WordPress on Azure - Azure SQL migrate data
Hopefully it should now migrate but there might still be some issues which the tool will flag.

WordPress on Azure - Azure SQL migration report

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

use [blog_db]
update [dbo].[wp_options]
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)

Themes
Plugins

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.

Leave a Reply

Your email address will not be published. Required fields are marked *