Running WordPress on SQL Server

It seems to be a well kept secret that WordPress runs on a SQL Server or SQL Azure database. At least it was well kept from me until recently. (Perhaps that says something about my ability to follow current news, but that’s a topic for another day.) In any case, the cat is out of the bag with this new blog: WordPress on Microsoft. Now, you might ask why the BLEEP is Microsoft doing this? But that is answered here: Why the BLEEP is Microsoft doing this? You might also ask how is Microsoft doing this? The answer to that question is here: WordPress on SQL Server: Architecture and Design. Finally, you might ask how do I do this? Again, the answer is here: Installing WordPress on SQL Server. (These guys seem to have all their bases covered.) However, that set of instructions for installing WordPress on SQL Server assumes you are starting from scratch and it guides you through set up using the Web Platform Installer (WPI). I thought is would be relevant to look at how to get things set up assuming you already have PHP and SQL Server 2008 Express installed. I ran into a few “gotchas” in doing this – I’m hoping this post will help you avoid these. If you do install this WordPress patch, keep in mind that it is a beta release and that we'd appreciate feedback.

For reference, I created the instructions below with the following already installed on my computer: PHP 5.3.2, IIS 7.5, and SQL Server 2008 Express with Advanced Services.

Step 1 – Enable the MySQL extension: I know this seems counter-intuitive (aren’t we going to run WordPress on SQL Server?), but because of the way the SQL Server patch is architected (it is intercepting and re-writing MySQL queries), WordPress expects the MySQL extension to be enabled in your PHP installation. If you don’t enable the MySQL extension, you will see the following error later in the set up process:

Step 2 - Configure a Database: I followed the instructions for configuring a database here: http://wordpress.visitmix.com/development/installing-wordpress-on-sql-server#database. One thing I’d add to those instructions to to make sure the user hasd datareader, datawriter, and ddladmin permissions. (The user I created was WPUser.)

Step 3 - Download the WordPress on SQL Server Distribution: Select the distribution you want to download (single user or multi-user) here: http://wordpress.visitmix.com/download. Extract the files from the downloaded .zip file and copy the contents of the wordpress folder to a folder in your website’s root directory called wp-sqlsrv.

Step 4 – Enable IIS detailed error messages: Create a web.config file with the following content and put it in the wp-sqlsrv folder (more detail about why this step is necessary is available here):

<?xml version="1.0" encoding="UTF-8"?>
   <configuration>
     <system.webServer>
       <httpErrors errorMode="Detailed"/>
     </system.webServer>
   </configuration>

Step 5 – Enable write permission to the wp-sqlsrv directory: In general, WordPress expects to have write access to the wp-content folder (where plug-ins and themes exist) during set up and configuration. The sqlsrv patch takes advantage of this expectation by making a field_map.php file in that folder (at installation time) which maps the WordPress tables, column names, and types to their SQL Server equivalents.

There is more than one way to enable write permissions, but here is what I did (and undid after set up was complete):

Added my identity to the Users of the wp-sqlsrv folder (with write permission). For more information about how to do this, see How to set, view, change, or remove special permissions for files or folders.
Enabled Windows Authentication and disabled Anonymous Authentication for my website. For more information about how to do this, see Configure Windows Authentication (IIS 7).
Configured the application pool for my website to run under my identity. For more information about how to do this, see Specify an Identity for an Application Pool (IIS 7).

I’ll change this configuration (I’ll go back to Anonymous Authentication) after set up is complete. For some clues as to why this works, take a look at this post: SQL Server Driver for PHP: Understanding Windows Authentication.

Step 6 – Browse to http://localhost/wp-sqlsrv/. Follow the prompts to…

Create a configuration file…

Run the installation…

Be sure to note the admin password that is provided for you after the installation is complete, then you can login.

Step 7 – Remove IIS Detailed Errors and Configure URL Rewrite: Replace the content of the web.config file you created in Step 4 with this content:

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