Using WordPress on Windows Azure with SQL Server instead of MySQL


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

Installing WordPress from the gallery

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

In line 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:
    http://[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

Optional step, to enable URL Rewriting for pretty Permalinks without “index.php”

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”

Comments (16)

  1. bluefire2121 says:

    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: http://www.windowsazure.com/…/troubleshoot-web-sites-in-visual-studio

  2. Rama Dwarapudi says:

    I've tried to enable revolution slider and getting the below errors.

    Revolution Slider Error:no update action taken! Fatal error: Uncaught exception 'Exception' with message 'no update action taken!' in

    C:DWASFilesSitesWPAzureVirtualDirectory0sitewwwrootwp-contentpluginsrevsliderinc_phpframeworkfunctions.class.php:9 Stack trace:

    #0 C:DWASFilesSitesWPAzureVirtualDirectory0sitewwwrootwp-contentpluginsrevsliderinc_phpframeworkdb.class.php(101): UniteFunctionsRev::throwError('no update actio…')

    #1 C:DWASFilesSitesWPAzureVirtualDirectory0sitewwwrootwp-contentpluginsrevsliderinc_phprevslider_operations.class.php(851): UniteDBRev->update('wp_revslider_cs…', Array, Array)

    #2 C:DWASFilesSitesWPAzureVirtualDirectory0sitewwwrootwp-contentpluginsrevsliderrevslider_admin.php(308): RevOperations::importCaptionsCssContentArray()

    #3 C:DWASFilesSitesWPAzureVirtualDirectory0sitewwwrootwp-contentpluginsrevsliderrevslider_admin.php(127): RevSliderAdmin::createTable('revslider_css')

    #4 C:DWASFilesSitesWPAzureVirtualDirectory0sitewwwrootwp- in

    C:DWASFilesSitesWPAzureVirtualDirectory0sitewwwrootwp-contentpluginsrevsliderinc_phpframeworkfunctions.class.php on line 9

    Please let me know how to resolve this issue, please email to Dwarapudi@live.in

    Regards

    Rama

  3. Jade says:

    Ahmed thank you so much for these easy to follow instructions!

  4. Paritosh Arya says:

    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 < 1415762851

    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'site_transient%1$s_site_transient_timeout_%2$s_site_transient_timeout_', SUBSTRING( a.option_name, 17 ) ) AND b.option_value < 1415762851

  5. Paritosh Arya says:

    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.

  6. Thomas says:

    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.

    Thanks

    Could 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 < 1417054941

    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'site_transient%1$s_site_transient_timeout_%2$s_site_transient_timeout_', SUBSTRING( a.option_name, 17 ) ) AND b.option_value < 1417054941

    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: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')

  7. Rockie_ says:

    Hi Ahmed!

    Can You give examples of values near "PDO SqlSrv"? Don't clearly understand – there should be MySQL or MSSQL parameters? I recreate site second time and cannot launch plugin, but in file there is

    // ** MySQL settings – You can get this info from your web host ** //

    • So where is MSSQL parameters? oO
  8. Rockie_ says:

    Resolved!  database server name should be in format  *******cw0.database.windows.net

    Got an error after creation like and other users, but blog seems to work!

  9. Ali says:

    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

  10. Manuel says:

    Ali, delete dthe wp-config file….. and every time you have that problem do the same….

  11. ToddChitt says:

    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

  12. Mark Stokes says:

    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?

  13. CompuZest says:

    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.

  14. CompuZest says:

    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

  15. Error Connecting to Database says:

    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

  16. Error Connecting to Database says:

    Sorry my e-mail is andre@hattechpos.co.za