Okay, so I struggled to get this going so I thought my very first blog post should attempt to help other people trying to host their own WordPress blog on IIS with a Microsoft SQL backend database server.

The problem came trying to get IIS to talk to the database server, the rest of the installation was easy, I will go into a lot of detail when I get to that part of the installation guide.

Under normal circumstances I would recommend installing WordPress on Apache with a MySQL database backend server, however, I have other hosted services on IIS *that could only be hosted on IIS* and already had a MSSQL database backend for other services and didn’t want to have multiple database servers of different flavors and I didn’t want to use a spare public IPv4 IP just for WordPress.

This guide is how to install WordPress on a Microsoft Server 2008 R2 IIS 7.5 server with a separate Microsoft Server 2008 R2 server with Microsoft SQL 2008 R2 database server.

Assumptions: Two Microsoft 2008 R2 servers on the same LAN subnet.

Register domain and point the A records to your hosting server

This should be obvious but I thought I would mention this just in case, you can register domains from many different suppliers around the world; I am not going to go into detail about that. Pointing the A record to your hosting server just comes down to logging into the control panel of the registrar you purchased the domain from and changing the A record to the public IP address of where you have your hosting server.

Configuration of IIS 7.5

There are some prerequisites that need to be installed on IIS, the steps are as follows:

  • Add the IIS Web Server Role on your server, when installing the role you will be prompted to install the .Net 3.5 Framework as it is a dependancy.

Installing IIS Role

Open up “Server Manager”, select “Roles” on the left hand side and select “Add Roles”.

Select “Web Server (IIS)” and click “Next”.

Click “Next”.

Add services “CGI” under “Application Development”.

Confirm Installation Selections and click “Install”

Click “Close” and restart the server

  • Install the Microsoft Visual C++ 2008 Redistribute Package. Check in Add/Remove programs whether it is installed if you are not sure.
  • Install the latest version of PHP. I used the non-thread safe version. The difference between the non-thread safe and thread safe is basically to increase performance alongside the FastCGI protocol setup in IIS. As of the 17th of April 2013 there is PHP version 5.4.16 as the latest version but it has no “Installer” so I used PHP 5.3.26 VC9 x86 Non Thread Safe (Installer) @ 45.74MB.

 Installing PHP 5.3.26

Click “Next”

Click the tick box “I accept the terms in the license agreement” and click “Next”

Browse to a location you wish to install. I left this as standard.

Select “IIS FastCGI” and select “Next”
FastCGI for IIS enables popular application frameworks that support the FastCGI protocol to be hosted on the IIS web server in a high-performance and reliable way. FastCGI provides a high-performance alternative to the Common Gateway Interface (CGI). CGI is a standard way of interfacing external applications with Web servers that has been supported as part of the IIS feature-set since the very first release.

If you get the following error message, you have done it wrong. Please read my instructions above

Install all components under “Script Executables” Option: Install all Extras if you want too. I installed LDAP extensions (not that it is needed for this but I might need it as I have a separate LDAP server).

Click “Install”

Installing SQL Native Client

Download the SQL Native Client 2.0.
Create a desktop folder called “SQL Native Client 2.0 Extract”

Click “Yes”

Click “Browse”

Browse to the folder you just created on your desktop and click “OK”

Click “OK”

The following files should be extracted

Copy the following files into the “ext” folder where the PHP was installed.

php_pdo_sqlsrv_53_nts_vc6.dll
php_pdo_sqlsrv_53_nts_vc9.dll
php_sqlsrv_53_nts_vc9.dll
php_sqlsrv_53_ts_vc9.dll

 Install PHP Manager

There are some PHP modifications to do; the best and safest way to do this is by downloading and installing PHP Manager.
You will need to restart the server for PHP Manager to be available

Creating the site in IIS

Create a folder you wish to host your website in, for this example I am going to use:

Open up Server Manger on the IIS server

Expand Roles -> Web Server (IIS) and click on “Internet Information Server” on the left hand side

Expand the server on the left hand side and expand “Sites”

Right click on “Sites” and select “Add Web Site”

Complete the following:

  • Fill in the “Site Name” field – I use the actual name, this helps identify the site
  • Browse to the physical path of the website – This is where you created the folder where you will hold the website files
  • Host Name: You must put the domain name of the website in here.
    If you want to resolve www at the begining you will need to add a second hostname
    eg: test.co.nz and www.test.co.nz
  •  Ensure “Start Web Site immediately” is selected

Configuring PHP Manager

Open PHP manager

Once you open the Manager for the first time it should detect your PHP installation automatically depending on where you have installed it. If it does not simply use the “Register new PHP version” link to browse the php-cgi.exe and register your installation.

There will be a small warning about optimal configuration of PHP. Feel free to use the “View recommendations” link to sort this out and enable all the applicable recommendations. If there is a recommendation for the default document, you will need to actually change it under the website by opening “Default Document” and moving the .php extension to the top.

Now we can register our drivers for SQL. To do this click on the “Enable or Disable an Extension” link to open the configuration window.

Select each of the disabled files and click on “Enable” on the right hand side.

Configuring PHP Manager – Optional

PHP Logging

If the user in the application pool is not configured for access then the PHP logging won’t work, I recommend changing the error logging path of PHP, I changed mine to the PHP path itself (and created a new folder called “logs”).

URL Rewrite

We will need to install URL Rewriting on our IIS machine for use with WordPress Permalinks.

On a new machine you will be prompted to install the Web Platform Installer 4.5 before continuing. Install this and then re-try the URL Rewrite install. You will find that the Web Platform Installer takes over the install once it starts. Simply press next to everything and continue with the install.

Configuring SQL Server Database

The assumption is you already have a Microsoft 2008 R2 Server with SQL 2008 R2 installed and configured correctly.

Firstly we need to create our WordPress Database. To do this open your SSMS (SQL Server Management Studio) and right click on your Databases folder. Select New Database from the menu to begin.

Give your database a name and leave all other options as default. Next we need to create our user to access our database from WordPress. To do this, expand your Security folder in your SSMS and right click on New Login option.

Give your login a Name and set the password for the user. Make sure you unselect “User must change password at next login”

Click on “User Mapping” on the left hand side. Select the database, ensure the username you just created is in the “User” field, and change the “Default Schema” to “dbo”.

Add the following database role memberships:

  • db_accessadmin
  • db_datareader
  • db_datawriter
  • db_ddkadmin
  • db_owner
  • db_securityadmin
  • public

 Install and configure WordPress

Finally!

Download the IIS version of WordPress and extract the files from the compressed file.

Copy the WordPress files to the location you created to store your website files.

Once you have extracted the WordPress site set the permissions for the IUSR user on the WordPress root folder to be full control.

Create a web.config file in your WordPress root directory and paste in the following config text:

<?xml version=”1.0″ encoding=”UTF-8″?>
<configuration>
<system.webServer>
<httpErrors errorMode=”Detailed”/>
<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>

 Install “wp-db-abstraction” plugin

This plugin allows WordPress install and gives the option to connect to a database, the default installation page for WordPress doesn’t give you this option and attempts to connect to a MySQL database.

Extract the compressed file to a folder and complete the following:

  • Upload the wp-db-abstraction.php and the “wp-db-abstraction” folder to “wp-content/mu-plugins” folder. This should be parallel to your regular plugins directory. If the mu-plugins folder doesn’t exist, you must create it.
  • Move the db.php file from inside the wp-db-abstraction folder to wp-content/db.php

Now we need to run the install wizard of WordPress and finish our Install.

To do this browse to the http://your domain/wp-content/mu-plugins/wp-db-abstraction/setup-config.php.

This will then prompt you with a configuration setup window for WordPress.

Click the “Let’s Go” button to move to the configuration page. This looks like a standard WordPress config page but this one has a DB Type field. Select the correct driver and enter in all the required information for the database and the user we created earlier. Then click submit.

At this point WordPress will be up and running and most blog posts end here and say “now you’re done”. Well actually that’s not true. What you will find is that all looks OK until you try and look at your Posts or Media lists.

You will notice that the counts say (1) but the number of posts showing is zero. This is because the current version of the WordPress DB Abstraction plugin ships with a bug. This bug is with a RegEx in the translations.php file. It adds a trailing “;” to some of the queries which causes SQL to fall over. To fix this browse to this file from your WordPress root folder.

Open up the translations.php in a text editor and go to line 726 in the file. Here you will find the RegEx line that needs to be changed.

And there you have it, a WordPress Blog on Microsoft IIS with a Microsoft SQL backend.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.