Running WordPress with SQL Azure

As you may have already read, a couple of months back I migrated my blog to Windows Azure WebSites and changed my blog engine to WordPress. The WordPress gallery template comes with a MySQL database 20MB free offer from ClearDB which was more than I needed (or so I thought) so I went alone and used it.

Lately though I”ve been experiencing a strange behavior, at undeterminable intervals my changes stopped being persisted at the database. After getting in contact with the ClearDB guys, I learned that the problem was that the MySQL engine was generating temp tables in order to facilitate the queries being made to it and the size of the data in these tables was greater from my quota leading to a block. The conversation reached a point where the ClearDB guy said

[pullquote]…I”m sorry for the inconvenience. If you”re not able to optimize your queries, and I gather that you”re using a stock WordPress install, the solution is simply to upgrade to the next service tier… (You can read the full transcript here)[/pullquote]

I got so frustrated by this that I decided to investigate if moving to SQL Azure was an option and guess what… you can easily use SQL Azure with your WordPress Azure WebSite here”s how.

Step 1

Create a new WordPress site using WebMatrix localy by clicking new and selecting App Gallery from the menu.

Step 2

Download WP Db Abstraction. This plugin provides db access abstraction and SQL dialect abstraction for SQL Server. It is an mu (Must-Use) plugin AND also a db.php drop-in.

Step 3

Put the wp-db-abstraction.php and the wp-db-abstraction directory to wp-content/mu-plugins.  This should be parallel to your regular plugins directory.  If the mu-plugins directory does not exist, you must create it. Put the db.php file from inside the wp-db-abstraction.php directory to wp-content/db.php. Rename the wp-config.php file in the root folder to wp-config_old.php since this is going to be written by the setup wizard at a later step.

Step 4

Publish your newly created WordPress site to an empty Azure WebSite slot.

Step 5

Create a new SQL Azure database

Step 6

Once the site is published visit http://your_wordpress_url/wp-content/mu-plugins/wp-db-abstraction/setup-config.php to generate your wp-config.php file and follow the instructions to connect WordPress with the database you created in the previous step. In the database type field you’ll have to select BDO_SQL as the type to connect to SQL Azure. Note that WordPress should be able to connect to the database otherwise you”ll get an error.

Step 7

Complete the normal WordPress installation

Step 8

There is a known bug in the  WP Db Abstraction that causes your posts and media files to not appear in their corresponding lists in the admin. You can read more about it here. So you’2015-04-28 07:02:14’ll have to edit the function is translate_limit in the wp-db-abstraction/translations/sqlsrv/translations.php file

// Check for true offset
if ( (count($limit_matches) == 5  )  && $limit_matches[1] != ''0'' ) {
    $true_offset = true;
} elseif ( (count($limit_matches) == 5 )  && $limit_matches[1] == ''0'' ) {
    $limit_matches[1] = $limit_matches[4];
}

The “$limit_matches” returns 6 items and in a validation is asking for 5 that”s all.

// Check for true offset
if ( (count($limit_matches) == 5  )  && $limit_matches[1] != ''0'' ) {
    $true_offset = true;
} elseif ( (count($limit_matches) >= 5 )  && $limit_matches[1] == ''0'' ) {
    $limit_matches[1] = $limit_matches[4];
}

just change the == for a >= and it works like a charm.

If you already had a WordPress installation like I did then you can easily migrate your data to your new SQL Azure backed installation. You just export all your data from you old site and import them to the new one.

Now, with half the price (4.99$ per month) of what I would have had to pay ClearDB to upgrade to the next service level I”ve got all the space I”ll ever going to need (up to 100MB) and all available for my data not temp.