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: https://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: https://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 https://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>
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
Comments
- Anonymous
June 09, 2010
thanks for information....great post... amazingthanks for share... - Anonymous
June 09, 2010
pardon my website is falsethanks for information....great post...thanks for share... - Anonymous
June 09, 2010
Glad it was a helpful post.-Brian - Anonymous
June 27, 2011
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? - Anonymous
June 26, 2012
Brian: you know if can i install Wordpress in a diferent SQL server running SQL Server 2008 ? - Anonymous
June 27, 2012
@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.) - Anonymous
July 11, 2012
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? - Anonymous
July 16, 2012
@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 - Anonymous
October 03, 2012
Enable the MySQL extension? Is this a plugin, a patch, a feature? - Anonymous
October 04, 2012
@Daniel- This is the MySQL extension I'm referring to: us3.php.net/.../book.mysql.php.-Brian - Anonymous
April 13, 2013
very useful post ... <a href="dbakings.com/.../Default.aspx">sql dba</a> - Anonymous
June 13, 2013
Brian, there's a new option available for running WordPress on SQL Server 2012 or Windows Azure SQL Database -- http://projectnami.org - Anonymous
November 04, 2013
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. - Anonymous
January 10, 2014
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! - Anonymous
January 13, 2014
Have you seen this? http://projectnami.org/ Haven't tried it myself, but looks like it might help. - Anonymous
January 13, 2014
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? - Anonymous
May 17, 2016
I actually wanted to develop a brief remark to say thanks to you for some of the great secrets you are giving out at this website. My time consuming internet research has at the end of the day been paid with reputable know-how to write about with my classmates and friends. I ‘d tell you that many of us site visitors actually are truly blessed to live in a good site with very many brilliant professionals with insightful solutions. I feel truly privileged to have come across your webpage and look forward to many more enjoyable times reading here. Thank you once more for everything.