Share via

Using WordPress on Windows Azure with SQL Server instead of MySQL

  • Article

Introduction

Creating a WordPress based site on Windows Azure is already pretty trivial, you can very easily select WordPress from the gallery when creating an Azure Website.

The problem is, by default, Wordpress works with MySQL, which means for you that you will either need to get a database from ClearDB, which is more expensive and isn't included in say, your BizSpark $150 monthly credit allowance; or you need to create a Virtual Machine and install MySQL on it which would cost you more than you need.

It is much more cost effective and manageable to use SQL Server as a Service, which is managed for you and you can use your BizSpark credits against.

We’ll be using a database abstraction plugin for WordPress that allows the blog engine to use a SQL server as a database.

Overview

What we’ll need to do is:

  1. Install WordPress from the gallery
  2. Download the WP DB Abstraction plugin and extract it locally
  3. Modify a file to fix a bug in the plugin (I know, sorry!)
  4. Create a SQL Database on Windows Azure
  5. Connect to the website using FTP and delete the wp-config.php that comes with the installation
  6. Upload the plugin
  7. Configure the plugin to use our SQL Server
  8. Install WordPress regularly
  9. (Optional) Enable Url Rewrite for friendly links

I’m assuming this is a new WordPress installation. What I won’t be covering in this post is how to migrate your data from your old WordPress installation to the new one. There are a lot of tutorials to do that already.

 

Details

image

When creating the site, choose to create a new MySQL database, it doesn’t matter as we won’t be using it anyway. Delete it later.

Downloading (and fixing) the plugin

Go to the WP DB Abstraction plugin website, download the zip file and extract it somewhere on your computer. From the folder you unzipped, open up \wp-db-abstraction\translations\sqlsrv\translations.php

Inline 740, change:    

 elseif ( count($limit_matches) == 5 && $limit_matches[1] == '0')

to

 elseif ( count($limit_matches) >= 5 && $limit_matches[1] == '0' )

Create a Windows Azure SQL Database

On the portal, create a new database/server in the same region you created the website and take note of:
the username and password you specified as well as the database server hostname and the database name.

image

Installing the plugin

We’ll be uploading couple of files using FTP to install the plugin.

First, if you haven’t created FTP deployment credentials, go to your website Quick Start (that little blue cloud with a thunderbolt image) and click on “Set deployment credentials” to create a username and password that you’ll use with FTP.

After that is done, go to the Dashboard and take note of the FTP hostname and your deployment username.

image

Now using your favorite FTP client (or Windows File Explorer) connect to the website using that FTP host name and FTP username and password you specified earlier.

  1. Navigate to “site/wwwroot” and delete wp-config.php. This is a VERY IMPORTANT step, otherwise you’ll keep getting 500 Internal Server Error in the next step.
  2. Navigate to “site/wwwroot/wp-content” and create a mu-plugins folder.
  3. From the plugin folder you extracted on your computer, copy wp-db-abstraction.php and the wp-db-abstraction directory to “site/wwwroot/wp-content/mu-plugins” folder on the FTP.
    This is how your FTP structure would look like after that
    image
  4. Finally, navigate back to “site/wwwroot/wp-content”   and from your extracted plugin folder, under the wp-db-abstraction directory, copy db.php into wp-content.
    image

Configuring the plugin

  1. Now that the plugin is uploaded in place, open up its configuration URL:
    https://[yourazurewebsite].azurewebsites.net/wp-content/mu-plugins/wp-db-abstraction/setup-config.php
    image
  2. Enter the details of the database server and make sure you select PDO SqlSrv and not SQL Server using MS PHP Driver
    image
    Once this step is done, WordPress can communicate with your database, click on “Run the install” to run the regular WordPress installation

 

Install WordPress

image
image

Now your installation should be running fine and dandy.
image

Create a web.config file with the content below

 <?xml version="1.0" encoding="UTF-8"?>  
<configuration>  
    <system.webServer>  
        <rewrite>  
            <rules>  
                <rule name="wordpress" patternSyntax="Wildcard">  
                    <match url="*"/>  
                    <conditions>  
                        <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true"/>  
                        <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true"/>  
                    </conditions>  
                    <action type="Rewrite" url="index.php"/>  
                </rule>  
            </rules>  
        </rewrite>  
    </system.webServer>  
</configuration>

And upload it to the root of the WordPress installation, ie: Under wwwroot folder

Make sure your WordPress Permalink settings do not contain “index.php”

Note: This post is cross posted to my new blog at https://sabbour.me/

Comments

  • Anonymous
    November 13, 2013
    You are a rock star. Thank you for sharing this. The only thing I would specify is that for your username to login into the FTP, you actually have to use the "DEPLOYMENT/FTP USER" together as one word. I found this out from step number 6 on this Microsoft site: www.windowsazure.com/.../troubleshoot-web-sites-in-visual-studio

  • Anonymous
    January 04, 2014
    The comment has been removed

  • Anonymous
    September 22, 2014
    Ahmed thank you so much for these easy to follow instructions!

  • Anonymous
    November 11, 2014
    Hi Ahmed,I get following error after running through the wizard to install wordpress (last step of your tutorial):WordPress database error: [42000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ','.]DELETE a, b FROM hmb_options a, hmb_options b WHERE a.option_name LIKE N'transient%1$s_transient_timeout_%2$s_transient_timeout_', SUBSTRING( a.option_name, 12 ) ) AND b.option_value < 1415762851WordPress database error: [42000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ','.]DELETE a, b FROM hmb_options a, hmb_options b WHERE a.option_name LIKE N'site_transient%1$s_site_transient_timeout_%2$s_site_transient_timeout_', SUBSTRING( a.option_name, 17 ) ) AND b.option_value < 1415762851

  • Anonymous
    November 11, 2014
    Forgot to mention my email id : paritosharya007@gmail.com. Please let me know what's wrong with my setup. I got the error listed above but the installed succeeded. So I am kind of apprehensive about everything going on just fine.

  • Anonymous
    November 26, 2014
    All looked well until I tried to install WP (after specifying a username and pwd).Then I got this. Do you know whether this all procedure still work?I've tried installed WP 4.0.1.ThanksCould you get back to me at http://www.garagepc.net/ ?WordPress database error: [42000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ','.]DELETE a, b FROM wplich_options a, wplich_options b WHERE a.option_name LIKE N'transient%1$s_transient_timeout_%2$s_transient_timeout_', SUBSTRING( a.option_name, 12 ) ) AND b.option_value < 1417054941WordPress database error: [42000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ','.]DELETE a, b FROM wplich_options a, wplich_options b WHERE a.option_name LIKE N'site_transient%1$s_site_transient_timeout_%2$s_site_transient_timeout_', SUBSTRING( a.option_name, 17 ) ) AND b.option_value < 1417054941WordPress database error: [23000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of UNIQUE KEY constraint 'wplich_options_option_name'. Cannot insert duplicate key in object 'dbo.wplich_options'. The duplicate key value is (wplich_user_roles).]INSERT INTO wplich_options (option_name, option_value, autoload) VALUES ( N'wplich_user_roles', N'a:2:{s:13:"administrator";a:2:{s:4:"name";s:13:"Administrator";s:12:"capabilities";a:0:{}}s:6:"editor";a:2:{s:4:"name";s:6:"Editor";s:12:"capabilities";a:0:{}}}', N'yes')WordPress database error: [23000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of UNIQUE KEY constraint 'wplich_options_option_name'. Cannot insert duplicate key in object 'dbo.wplich_options'. The duplicate key value is (wplich_user_roles).]INSERT INTO wplich_options (option_name, option_value, autoload) VALUES ( N'wplich_user_roles', N'a:3:{s:13:"administrator";a:2:{s:4:"name";s:13:"Administrator";s:12:"capabilities";a:0:{}}s:6:"editor";a:2:{s:4:"name";s:6:"Editor";s:12:"capabilities";a:0:{}}s:6:"author";a:2:{s:4:"name";s:6:"Author";s:12:"capabilities";a:0:{}}}', N'yes')WordPress database error: [23000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of UNIQUE KEY constraint 'wplich_options_option_name'. Cannot insert duplicate key in object 'dbo.wplich_options'. The duplicate key value is (wplich_user_roles).]INSERT INTO wplich_options (option_name, option_value, autoload) VALUES ( N'wplich_user_roles', N'a:4:{s:13:"administrator";a:2:{s:4:"name";s:13:"Administrator";s:12:"capabilities";a:0:{}}s:6:"editor";a:2:{s:4:"name";s:6:"Editor";s:12:"capabilities";a:0:{}}s:6:"author";a:2:{s:4:"name";s:6:"Author";s:12:"capabilities";a:0:{}}s:11:"contributor";a:2:{s:4:"name";s:11:"Contributor";s:12:"capabilities";a:0:{}}}', N'yes')

  • Anonymous
    March 24, 2015
    The comment has been removed

  • Anonymous
    March 24, 2015
    Resolved!  database server name should be in format  *******cw0.database.windows.netGot an error after creation like and other users, but blog seems to work!

  • Anonymous
    June 03, 2015
    Setup-config.php not working. I followed your steps, but if I open http://[yourazurewebsite].azurewebsites.net/wp-content/mu-plugins/wp-db-abstraction/setup-config.php (of course with my azure-name) I get a blank page. What did I wrong? Thank you very much

  • Anonymous
    June 11, 2015
    Ali, delete dthe wp-config file..... and every time you have that problem do the same....

  • Anonymous
    June 29, 2015
    Getting a slew of "WordPress database error:[4200]..." when running the install. Then cannot log into the /wp-admin page, says that the username and password are incorrect. Obviously something is failing during the install. Followed the instructions to the letter, and several times. Any ideas? I really HATE to have to go back to MySQL database back-end... Thanks

  • Anonymous
    July 16, 2015
    I have used this process, successfully, many times, but today I am getting the same slew of SQL errors that others have reported. Has something changed / broken with later versions of WP?

  • Anonymous
    August 04, 2015
    Ali, I'm getting a blank page as well. How did you resolve the issue? Also, can someone please share their wp-config.php file as I can manually create it if needed. Can't figure out how to set some of the values. Thanks.

  • Anonymous
    August 05, 2015
    Thomas, Did you ever find a solution for your problem? I'm having the same issue when installing wordpress. WordPress database error: [42000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ','.] DELETE a, b FROM htfli_options a, htfli_options b WHERE a.option_name LIKE N'transient%1$s_transient_timeout_%2$s_transient_timeout_', SUBSTRING( a.option_name, 12 ) ) AND b.option_value < 1438787747 WordPress database error: [42000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ','.] DELETE a, b FROM htfli_options a, htfli_options b WHERE a.option_name LIKE N'site_transient%1$s_site_transient_timeout_%2$s_site_transient_timeout_', SUBSTRING( a.option_name, 17 ) ) AND b.option_value < 1438787747 WordPress database error: [42000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'FULL'.] SHOW FULL COLUMNS FROM htfli_options WordPress database error: [42000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'FULL'.] SHOW FULL COLUMNS FROM htfli_options WordPress database error: [42000 : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'FULL'.] SHOW FULL COLUMNS FROM htfli_options

  • Anonymous
    September 20, 2015
    All right sparky! You've made it through this part of the installation. WordPress can now communicate with your database. If you are ready, time now to… Run the install when i click run the install i get Error establishing a database connection This either means that the username and password information in your wp-config.php file is incorrect or we can't contact the database server at &&&&&&&.database.windows.net,1433. This could mean your host's database server is down.    Are you sure you have the correct username and password?    Are you sure that you have typed the correct hostname?    Are you sure that the database server is running? If you're unsure what these terms mean you should probably contact your host. If you still need help you can always visit the WordPress Support Forums. Any help would be great Thanks in advance

  • Anonymous
    September 20, 2015
    Sorry my e-mail is andre@hattechpos.co.za