MySQL

MySQL and PostgreSQL Database as a Service in Azure

Richard Green on

Today is the day that ClearDB users rejoice. Today is the day that a viable platform as a service offering for both MySQL and PostgreSQL exist in Microsoft Azure. Announced last night, Microsoft have now launched their own platform as a service offerings for the two database engines.

For years, ClearDB have offered a PaaS solution for MySQL. I had the misfortune of trying it out first hand recently on a web project and I can tell you that the performance was shocking. So bad was the performance that we actually deployed a Linux VM in Azure to run the MySQL service in IaaS and take the management hit on IaaS vs. PaaS. Even the support offered was terrible, blaming the performance on Azure itself when there were no issues with the Azure platform globally at the time.

The announcement puts these new services in preview. This means that the services and features aren’t going to be ready for your production workloads nor are all of the features going to be available right now. For example, I deployed an Azure Database for MySQL server last night to try it out and the Basic pricing tier is the only tier available right now. The ability to force all connections to secure and to define firewall rules for access is important and good to see there from day one.

All in all, it looks like a good first release. As I have been using In App MySQL database for Azure Web Apps to run the MySQL database on this site for sometime now (since preview in fact), and I have been debating whether to step back to IaaS for MySQL because of the fact that In App MySQL limits my ability to use features like Azure Load Balancer or Azure Traffic Manager with multiple site instances, this is going to be something I can definately see me using in the near term for real.

You can check out the documentation, pricing and scaling details for yourself at https://docs.microsoft.com/en-gb/azure/mysql/concepts-servers.

WordPress Database Index with SQL Azure

Richard Green on

As part of a moving my online services between two Windows Azure subscriptions last week, I did some upgrades to the blog including moving the database to Windows Azure SQL (SQL Azure). To facilitate this, I’m using the WP DB Abstraction plugin for WordPress available from http://wordpress.org/plugins/wordpress-database-abstraction/. Using this plugin does take a bit of guts I hasten to add as it hasn’t been updated in over two years and it will prevent some plugins from functioning but for core WordPress it’s great.

After migrating the site to the new subscription I was doing some validation checking in the SQL Azure Management portal. I was querying the database for various things and I noticed that there were no indexes on any of the tables, a byproduct of the WP DB Abstraction plugin translating the native WordPress MySQL syntax into MSSQL I suspect. Luckily for me, WordPress have a great in-depth article on their Codex for the database schema, mappings for all of the primary and foreign keys and most importantly, all of the indexes.

Using the SQL Azure Management Designer, I was able to create the indexes in SQL Azure to match the WordPress MySQL specification. If you are using WP DB Abstraction for your Widows Azure Web Sites WordPress installation with SQL Azure, I strongly recommend you take a look at your own indexes to see if any exist and if not, look at all of the details on the WordPress Codex article at http://codex.wordpress.org/Database_Description for what indexes should exist.

If I get a chance in the coming days, I’ll update this post with a T-SQL snippet which you can dump into SQL Server Management Studio to create the indexes for you.

The Blog….Fixed

Richard Green on

So it seems that the blog has been offline for about three or four weeks without my knowledge.

The only thing that actually led me to the problem was when logged into LinkedIn last week, I saw that the site wasn’t able to pull my RSS feed.

After much investigation and troubleshooting with the PHP side of things, it turns out that a problem with the hosts MySQL database engine caused one of the tables to become dirty and needed repairing.

Soon, I’ll be moving the blog to a new dedicated MySQL database as it currently shares its database with another product, Simple Machines SMF Forum, however I’ve just been given some extra databases from the host for gratis.