Solved : Run Wordpress on Windows 2008 with MSSQL

I  was recently approched by my superior with a crazy idea that one of our client had : Migrate their current linux with mysql wordpress blog to  a windows 2008 server with a remotly hosted database under MSSQL.

First off I thought this was looking for troubles, but if thats what the client wants I can certainly make it work… in the end this choice was dropped for obvious reason : it is not supported by wordpress for the time being and also for this reason :

Ask yourself this, when you install worpress on a 2008 server from the microsoft web platform installer and tell it you want to install wordpress why doesnt microsoft offers you the possibility to use an MSSQL version for database but instead offers you a MYSQL installation for it ? (could it be they know that some features uses queries that they invoke do no have equivalent in the MSSQL query, 1 that come to mind is the LIMIT value as i had to scramble around forum to get this to work.

But since there is almost no good information on the subject and since i have spent so much time on this might as well share this with you. So here is how to Install worpress on Windows 2008 server done 3 way: first on MYSQL using the web platform installer, 2nd on MSSQL Express and finally on a real corporate MSSQL server 2008

This document describes in detail the process to install successfully Worpress on a Windows 2008 under IIS 7.

     Extent of the document

The subject that will be covered by this document are:

 

  • Installation of WordPress with MySQL

 

  • Installation of WordPress with MsSQL
    • SQLExpress
    • MSSQL 2008

 

  • Migration / Export / Importof an external site
    • Linux MySQL to Windows MsSQL

WordPress

Requirements:

 

Create the destination folder for Worpress :

–       C:\inetpub\Websites\%WebsiteFolder%

–       Give full access rights to the IUSR user on the wordpress destination folder.

Use the« Web Platform Installer » from  Microsoft to install the following pre-requisite:

–       PHP 5.3.x

–       PHP Driver for SQL Server v3.0

–       URL Rewrite 2.0

WordPress  Installation :

MySQL :

Use the « Web Platform Installer » from Microsoft to install the following supplemental software:

  • WordPress
  1. MySQL and all its dependencies (will be added automatically)
  1. Enter a password for the « Root » account
  2. Press « Install »
  3. Accept the « Terms of licensing »
  • IIS configuration :
  1. Choose « New Web site »
  2. Enter « / » as « Wordpress Application Name »
  3. Website Name Enter your desired Website name« Chosen Name »
  4. Enter the path to the worpress destination folder previously created
  5. Enter the value for the IP and the required port
  6. Enter the hostname the website will respond to
  • Configuration of WordPress :
    • Access you Worpress URL :

http://$your_wordpress_url/

  • Follow the onscreen instruction to finish the installation
  • Enter the Site name in « Site Title »
  • The username if admin must be changed
  • The password to be used with the above user
  • The email address to be used for the admin account
  • Check or uncheck depending on your own choice « Allow search engines to index this site »
  • Press «Install WordPress »
  • After a successful installation press «Log In »
  • The last part is the configuration of the module «URL Rewrite » :
    • Edit the file Web.config and replace the content with this:

<!–?xml version=”1.0″ encoding=”UTF-8″?>

<configuration>

<system.webServer>

<rewrite>

<rules>

<rule name=”wordpress” patternSyntax=”Wildcard”>

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>

 

SqlExpress (Local Installation):

Download the  Wordpress Plugin : « wordpress-database-abstraction » :

http://wordpress.org/extend/plugins/wordpress-database-abstraction/

Use the « Web Platform Installer » from Microsoft to install the following supplemental software:

  • SQL Server Express 2008 w/Tools or above

While under going the configuration / Installation phase of SQLExpress :

–       Choose « Mixed Mode Authentication » and enter a password for the account « sa »

 

SqlExpress Configuration Base de Donnée :

  • Start the SQL Management Studio
  • In server Type enter : « Database Engine »
  • In server name enter : « ./SQLExpress »
  • In Authentication enter : «  Windows Authentication »
  • Create a new user for the database that will be created in the next step and choose:
    • SQL Server Authentication and uncheck « User Must Change password at next login »
  • Create a new database and assign the user created in the previous step as the « Owner ».
  • Extract the WordPress zip in  the worpress destination folder C:\inetpub\Websites\%WebsiteFolder%
  • IIS Configuration :
    • Create the web.config file in the root of the worpress folderwith the following content :

<!–?xml version=”1.0″ encoding=”UTF-8″?>

<configuration>

<system.webServer>

<httpErrors errorMode=”Detailed”/>

</system.webServer>

</configuration>

  • Give all rights to« IUSR : » on the WordPress folder
  • Add / Configuration of the plugin « wp-db-abstraction »
    • Create a folder named « mu-plugins » in the folder « wp-content » and copy the content of the plugin into it.
    • copy the file « db.php » (wp-content\mu-plugins\wp-db-abstraction\db.php) into « wp-content\db.ph »
    • Edit the file « wp-content\mu-plugins\wp-db-abstraction\translations\sqlsrv\translations.php » to remove the « No Post found » error
      • Find the following line:
      • $pattern = ‘/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)(;{0,1})$/is’;
      • Change it for this:
      • $pattern = ‘/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*);{0,1}$/is’;
  • WordPress configuration:
    • Access the wordpress initial installation URL :

http://$your_wordpress_url/wp-content/mu-plugins/wp-db-abstraction/setup-config.php

  • Follow the onscreen instruction for the initial installation
  • Enter the database name previously created
  • Enter the database username previously created
  • Enter the associated password
  • Enter « .\SQLExpress » for the database server
  • Choose the driver to be used : PDO or MS for PHP
  • if everything went well you should now be able to press « Run the Install »
  • Enter the website name under « Blog Title »
  • The username if Admin must be changed
  • The password to be used for the admin account
  • The email adress to be used for the admin account
  • Check or uncheck depending on your choice« Allow my blog to appear in search engine »
  • The last step is the configuration of the module «URL Rewrite » :
    • Edit the file Web.config in the worpress root and replace the content with this:

<!–?xml version=”1.0″ encoding=”UTF-8″?>

<configuration>

<system.webServer>

<rewrite>

<rules>

<rule name=”wordpress” patternSyntax=”Wildcard”>

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>

MSSQL 2008 :

Download the WordPress Plugin : « wordpress-database-abstraction » :

http://wordpress.org/extend/plugins/wordpress-database-abstraction/

  • Start SQL Management Studio
  • Create a new user for the database that will be created in the next step and choose:
    • SQL Server Authentication and uncheck « User Must Change password at next login »
  • Create a new database and assign the previous user as the « Owner » of it.
  • Extract the WordPress zip in the worpress destination folder
  • IIS Configuration:
    • Create a file named web.config in the root of the worpress folder containing :

<!–?xml version=”1.0″ encoding=”UTF-8″?>

<configuration>

<system.webServer>

<httpErrors errorMode=”Detailed”/>

</system.webServer>

</configuration>

  • Give full rights to the user « IUSR : » on the WordPress folder
  • Add / Configuration of the plugin « wp-db-abstraction »
    • Create a folder named « mu-plugins » in the « wp-content » folder and copy the plugin content into it.
    • copy the file « db.php » (wp-content\mu-plugins\wp-db-abstraction\db.php) in « wp-content\db.php »
    • Edit the file « wp-content\mu-plugins\wp-db-abstraction\translations\sqlsrv\translations.php » to remove the « No Post found » error.
      • Find the following line:
      • $pattern = ‘/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)(;{0,1})$/is’;
      • Change it for this:
      • $pattern = ‘/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*);{0,1}$/is’;
  • WordPress Configuration:
    • Access your wordpress inital configuration page at :

http://$your_wordpress_url/wp-content/mu-plugins/wp-db-abstraction/setup-config.php

  • follow the onscreen instructionfor the installation
  • Enter the database name
  • Enter the database username
  • Enter the database username password
  • Enter the  « IP Address » for the database server
  • Choose the driver to be used : PDO or MS for PHP
  • If the connexion is successfull the you should be able to press « Run the Install »
  • Enter the Website name under « Blog Title »
  • Enter the username if Admin must be changed
  • Enter the password for the Admin account
  • Enter the email address to be used for the admin acount
  • Check  or uncheck depending on your choice« Allow my blog to appear in search engine »
  • The last step is the configuration of the module «URL Rewrite » :
    • Edit the file Web.config in the worpress root and replace the content with :

<!–?xml version=”1.0″ encoding=”UTF-8″?>

<configuration>

<system.webServer>

<rewrite>

<rules>

<rule name=”wordpress” patternSyntax=”Wildcard”>

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>

Migration:

Export / Import :

In order to migrate a Worpress blog the following steps are needed:

Pre-requisite :

1-     Modification of the PHP.ini :

  1. Change the following value:
  • upload_max_filesize = 2M
  • post_max_size = 8M

for this :

  • upload_max_filesize = 64M
  • post_max_size = 64M

Save changes.

Restart IIS for thePHP changes to be taken into effect

Export :

1-      In the Dashboard of the actual website under « Tools » ==> « Export ».

2-     Choose « All content »

3-     Press « Download Export File »

4-     Save the resulting file.

5-     Make a copy of the plugins that are in the folder«wp-content\plugins »

6-     Make a copy of the thems contained in « wp-content\themes »

7-     Make a copy of the folder « wp-content\uploads »

Import :

1-     Manually copy the previously copied folder in their respective place on the destination server.

2-     Go on your new worpress server in the admin portion under the menu « Appearance  ==> Theme »

3-     Activate the theme

4-     On the new site go to « Tools » ==> « Import ».

5-     Choose « Wordpress »

6-     If the plugin is not installed the press on « install Now » else

  • Press « Activate plugin & run Importer »

7-     Press « Browse » and navigate to the previously created xml file

8-     Press « Upload file and Import »

9-     Follow the onscreen instruction and let the import complete.

10-  Validate the « settings » by comparing every settings in old site with the one in the new site and adjust in consequence the widgets,  theme and activate/configure the plugins accordingly.

Advertisements

2 thoughts on “Solved : Run Wordpress on Windows 2008 with MSSQL

  1. Installing Wordpress on Windows 2 February 17, 2013 at 2:47 am Reply

    […] Solved : Run WordPress on Windows 2008 with MSSQL  […]

  2. ForbiddenSoul May 16, 2013 at 1:07 pm Reply

    Thank you!

    Instructions work perfectly for Windows Server 2012, and SQL Server 2012.

    I like to have everything running on one database server. There’s no need to install secondary database software if you already have one running (even if it may be free), so running WordPress with MSSQL was defiantly the way to go.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: