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:



image


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



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…


image


image


image


Run the installation…


image



image



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>


And now, you are ready to start blogging.


Again, keep in mind that this WordPress patch is a beta release and that we’d appreciate your feedback.


Thanks.


-Brian


Share this on Twitter

Comments (16)

  1. Anonymous says:

    thanks for information….

    great post… amazing

    thanks for share…

  2. Anonymous says:

    pardon my website is false

    thanks for information….

    great post…

    thanks for share…

  3. Glad it was a helpful post.

    -Brian

  4. Anonymous says:

    Every time I try to use that XML, IIS gives me a 500.19 error and give me this error message: Configuration file is not well-formed XML.

    Any idea why this is happening with my web.config file?

  5. Anonymous says:

    Brian: you know if can i install WordPress in a diferent SQL server running SQL Server 2008 ?

  6. @Leonardo: I'm not sure I understand the question. Do you mean is it possible to run WordPress on one machine and SQL Server 2008 on another? (Yes.)

  7. Anonymous says:

    Hi Brian, We're looking at doing just this but as you mentioned, we'll have WordPress on one server and MS SQL2008 on another.  Can you provide me some depth into what we might need to configure in order to link the WordPress on one server to the MSSQL sitting on the other?

  8. @Andre: Quite honestly, I can't think of anything special you will need to do. The only think I can think of that might trip you up is that you will need to make sure the SQL Server Native Client (SNAC) is installed on the same machine that is running WP/PHP. Details and links are here: us3.php.net/…/sqlsrv.requirements.php. Otherwise, if you specify the correct server name when installing WP, you shouldn't run into problems. If you do, let me know.

    -Brian

  9. Anonymous says:

    Enable the MySQL extension?  Is this a plugin, a patch, a feature?

  10. @Daniel- This is the MySQL extension I'm referring to: us3.php.net/…/book.mysql.php.

    -Brian

  11. Anonymous says:

    very useful post … <a href="dbakings.com/…/Default.aspx">sql dba</a>

  12. Anonymous says:

    Brian, there's a new option available for running WordPress on SQL Server 2012 or Windows Azure SQL Database — http://projectnami.org

  13. Anonymous says:

    Does it matter what version of MSSQL ??  We are currently using 2005 Standard and 2008 standard.  We would want WordPress to remain on a Linux server.

  14. Anonymous says:

    I'm running Windows 7 Professional and SQL Server 2008 Express. I'm trying to get WordPress installed to use SQL Server. (I was able to get it installed with MySQL.) Every way I try to Google help for getting it running with SQL Server, I always end up at a download site and when I try to download either file there, SourceForge tells me they're no longer available. Can you point me to some simple, straightforward instructions for doing this that work? Thanks!

  15. Have you seen this? http://projectnami.org/ Haven't tried it myself, but looks like it might help.

  16. Anonymous says:

    Thanks, Brian! I'll look into that. Does that mean that the tools to get WordPress running with MS SQL are not available anymore? Or do I just need to look to a third-party solution such as the one you mentioned?