Maestro, SQL Server and IIS

With the increased level of support by Microsoft for not only PHP on IIS, but also getting SQL Server support for Drupal 7, the barriers to entry for Enterprise level customers for Drupal are diminishing rapidly.

Case and point – we recently deployed a Maestro managed workflow process for an Enterprise level customer who would have never entertained the notion of using Drupal or Maestro if it ran only on MySQL. As sad as that sounds, it’s a stark reality for many large companies who will outright refuse even the best solution due to their perceived increased support costs for “yet another database engine” (MySQL).

With that said, my time on our Drupal 7/SQL Server/Maestro project went smoothly as I could have hoped for. There were a few curveballs here and there that hopefully this posting can help someone else overcome. So here I go:

Initial Setup for PHP and IIS

I’ve been a Windows Desktop and Server user for years. I’ve run PHP on IIS for as long as I can remember for my local development. During the “dark ages” of PHP4 IIS support, it was a real treat to see a PHP application run on IIS brilliantly with 1 person connected and fail miserably with 2 or more connected. Ok, so a bit of a stretch there, but IIS support for PHP4 was limited at best and performance wasn’t stellar. 

For testing purposes I was running a development instance on my laptop – configuring IIS to use a virtual directory vs. a web site is something I leave to you to figure out. There’s plenty of documentation out there to help you along.

Installation went well! I was sufficiently impressed. 

Trust Microsoft

The nice thing about the Web PI is that the specific configurations of php.ini for IIS are done for you – such as the ever elusive fastcgi.impersonate and cgi.force_redirect settings. PDO drivers are installed in the right location.

web.config

The web.config shipped with D7 is fine to use with a “full web site” in IIS. However if you’re running D7 in a test environment on Vista or Windows 7, the web.config file plunked into a virtual directory may cause IIS to fail with something that looks like this:

HTTP Error 500.19 - Internal Server Error

The requested page cannot be accessed because the related configuration data for the page is invalid.

No problem. For now, just disable the web.config file by either deleting it or renaming it. Drupal should come up just fine.

NOTE: I’m not saying that this is a good idea for production sites! By totally removing web.config, you won’t be able to use things like URL redirects or properly redirect 403 errors. Disabling web.config for now just lets you get up and running IF and ONLY IF you receive the 500.19 error message shown above. 

SQL Server Configuration

This is where I deviated from the “norm”. I didn’t use the Web PI to install Drupal. I did this manually as I have a specific setup requirement for my SQL Server instance (In other words I didn’t want to install D7 in my SQLEXPRESS\SQLEXPRESS instance!). 

First, install SQL Server. SQL Server 2008 R2 Express is what I use on a laptop/desktop for development. 

Next, create a database and create the appropriate SQL authenticated user. This takes a bit of know-how to navigate the SQL Server Users and Security and areas.

I installed D7 exactly the same way I would with MySQL – Except you choose SQL Server from the database selection mechanism. 

Eventually, my settings.php in Drupal 7 file has the following in it:

$databases = array (
  'default' =>
    array (
    'default' =>
    array (
    'database' => 'NAMEOFTHEDB',
    'username' => 'sqlusername',
    'password' => 'sqlpassword',
    'host' => 'MACHINENAME\\INSTANCENAME,
    'port' => '',
    'driver' => 'sqlsrv',
    'prefix' => '',
    ),
  ),
);
 

I suppose you could take a shortcut and plunk the $databases array as shown above into the settings.php file and alter the database, username, password and host to suit your needs prior to running the installer. However during the installation routine, you will have the opportunity to manually fill in all of the required information – specifically when you’re filling in the database information, click on the “Advanced Options” information area and ensure you enter in your proper hostname (as it shows up in your SQL Server Management Studio connections panel). 

Drupal 7 is Up! Tidy up the Configuration now…

At this point, you’re probably staring at a freshly installed instance of Drupal 7 running on SQL Server. You may or may not have installed other optional components for IIS early on – such as URL Rewrite. 

URL Rewrite in IIS will give you similar functionality as Apache rewrite rules gives you. Most importantly, you’ll be able to activate Clean URLs in Drupal. 

In my specific case, Clean URLs were not a requirement – thankfully so, as the production server I was given for deployment didn’t have the IIS URL Rewrite mechanism installed (I’m not the admin!). 

However I ran into some interesting issues. During the installation of D7, Drupal seemed to recognize that URL Rewrite was supported and thus enabled Clean URLs. That would be fantastic if not for the fact that it wasn’t supported on that server! Generally you can disable the Clean URLs by simply navigating to the following URL:

http://fqdnOfMyServer/?q=admin/config/search/clean-urls

Worst case scenario, you can force Drupal to not use clean URLs by tacking the following line of code at the bottom of your settings.php file:

$conf['clean_url'] = 0;

Word of warning: by adding the $conf line above to settings.php, you’re hard coding Clean URLs to be off. 

Issues You May Come Across

1.     Access denied redirects don’t work!

I found an interesting issue during deployments between multiple IIS instances (usual porting process from Dev to staging to production). 

The desired functionality: Anonymous user clicks on a link to create some content. Since the content type is secured by permissions to only allow logged in users to create said content, the user gets redirected to the /user page to log in.

The issue: Testing directly on the server (using the browsers right on the system) the anonymous redirect to /user worked flawlessly. Using a browser on a separate machine caused 403 errors (Access denied) to appear even though D7 was configured to redirect to /user in the site config.

The solution: Either by editing the web.config file, or by editing the “Error Pages” mechanism in the IIS Manager app, you’ll have to add in the proper redirects for 403s. Here is an example of what a web.config file might look like for you:

 
<xml version="1.0" encoding="UTF-8">
<configuration>
  <system.webserver>
    <httperrors>
      <remove substatuscode="-1" statuscode="404"></remove>
      <error responsemode="ExecuteURL" path="/index.php" prefixlanguagefilepath="" statuscode="404"></error>
      <remove substatuscode="-1" statuscode="403"></remove>
      <error responsemode="ExecuteURL" path="/index.php?q=user" prefixlanguagefilepath="" statuscode="403"></error>
    </httperrors>
  </system.webserver>
</configuration>

The configuration above shows that the 404 and 403 errors will get redirected to /index.php or /index.php?q=user respectively. You can achieve the same result by editing the 404 and 403 error pages in the IIS manager Error Pages configuration area.

2.     Windows doesn’t have cron! How do I run Maestro’s Orchestrator?

On a UNIX system, using a cron job is trivial to simply run a wget of the Orchestrator as noted in the Maestro README file (you did read that didn’t you?).

The desired functionality: Run the Orchestrator on Windows!

The issue: Windows only has the task scheduler and is clearly missing the wget command.

The solution: Use a simple vbscript that can easily be supported on Windows, and can be launched through the Task Scheduler. 

 - Create a text file somewhere on the server in question. Name it what you’d like, but make the extension “.vbs”

 - Edit the vbs file and place the following code in to it. Edit the FQDNofTHEserver to match that of your server. Alter the CONST SLEEPTIME=2000 line to suit how often you want the orchestrator to run. In my example below, the 2000 means 2000 milliseconds (2 seconds):

- Now navigate to the Task Scheduler and create a new task that starts up when the machine starts. The script above is coded to stay resident in memory and execute every 2 seconds. Ensure you don’t schedule a task to run every minute or so otherwise you will have many, many instances of the orchestrator running.

- When configuring the task, use the following parameters (altered to suit your Windows installation of course). The optional arguments will fire up the task in the background with no visible window:

Program/script: c:\windows\system32\cscript.exe

Add Arguments (optional): //B //Nologo path\to\your\vbs\orchestrator.vbs

- Edit the Maestro Configuration to turn off the Orchestrator running through the task console.

- Start the scheduled task.