Installing Drupal on Windows and SQL Server
Installing Drupal on Windows and SQL Server
This post is outdated.
A new post has been updated with specific instructions for Drupal 8 and PHP7 here
This article explains how to install Drupal on IIS and SQL Server with the basic performance settings. It is aimed at understading the different pieces that compose the environment needed to run Drupal on Windows and intended to audiences that have at least an intermediate level of confidence with Windows, IIS and SQL Server.
- Frequently Asked Questions
- Drupal is Coded in PHP, what is that?
- The IDE: Visual Studio + PHP Tools
- How PHP works on Windows (and how to install it)
- Serving PHP scripts from IIS
- Preparing IIS for Clean URL's
- Enabling Wincache and SQL Server PDO extensions
- Preparing the Database
- Installing Drupal
- Calling .Net code from PHP
- What to do next
Can I run Drupal on Windows with Apache?
- You can, but this is not production ready. This setup is usually recommened for users developing on Windows but that are going to deploy their applications on Linux machines running Apache (or nginx).
- The only production ready thing on Windows is IIS (Internet Information Services) - that comes free bundled with any version of Windows, even personal and home editions.
Is deploying/developing on Windows + SQL Server more expensive?
- Cloud virtual windows machines are about 15% more expensive, but you can easily save that money in operation costs. The best Windows VPS in terms of price per performance is Vultr or Azure. Other providers such as AWS consider supporting Windows not a key part of their business model and thus penalize the use of Windows based software with an over price.
- SQL Server has a free edition that is full featured but has a 10GB per database limit. You won't even get close to this limit on regular websites. And you get a business ready database engine that is much more powerful, fast and scalable than any other thing on the market.
- Visual studio is now free.
- Visual studio has excelent PHP development plugins such as PHP Tools For Visual Studio
- Visual studio has excelent Web Development extensions such as Web Essentials.
What is the Drupal on Windows experience like?
- The community is small but growing. You are going to find more people on the other side running managed devops on platforms such as Acquia, Platform.sh and Pantheon, but big projects cannot be hosted on these platforms because of the extreme overcost. They are just devops services built on top of Amazon (some), and you pay a very big premium for that. Having you own devops/autoscaling/etc.. is cheap (or free) while still you can work on the cloud with providers such as Azure or Rackspace.
Why would I mind managing my own application environment? Isn't that a waste of time and resources?
- Tell me that when moving something away from Acquia to dedicated servers saves you a five figure number per month (and that was not a big project at all...). You will not notice the premium price for small projects, but if you happen to grow, you will feel the pain of not having paid proper attention to application deployment and devops.
What kind of compatibility issues can I expect?
- Although Drupal has a database abstraction layer, sometimes contributed modules or even core use it incorrectly. Because there are less users using Drupal on Windows, some of these issues might not have been sorted out or spoted yet.
What is the WAMP stack?
- Something you want to avoid because the only production ready web server on windows is IIS. If you want to use apache (or niginx) I recommend moving to Linux. WAMP is very common between hobbyists because it is usually deployed with one click installer packages.
PHP is a server-side scripting language designed for web development but also used as a general-purpose programming language.
PHP code is usually processed by a PHP interpreter, which is usually implemented as a web server's native module or a Common Gateway Interface (CGI) executable.
If you come from a .NET corporate development environment and look at Drupal's 7 source code and it's use of PHP your first opinion will be that PHP is just a toy and more than probably a complete nightmare to use in complex and serious server applications. The truth is that PHP is very wrong, well, it was 7 years ago when Drupal 7 was conceived. During it's lifetime PHP has been through many lights and shadows, but has now nearly everything you would expect from a modern programming language. It is also extremely slow when compared to ASP.NET, but looks like that will also get fixed in the next major release: PHPng or PHP 7.
What you are seeing is not photoshopped. This is an actual photo of a real world, honest to God double-clawed hammer. Such a thing exists. That wondrous hammer is a delightful real-world acknowledgement of the epic blog entry PHP: A Fractal of Bad Design that explains what is wrong with PHP. Once you are convinced that PHP is crap, don't panic, 10 years ago corporate web developers used to create COM+ components in Visual Basic that integrated with ASP scripts. That was much worse, and we survived.
Although that might not be far away from the truth, the reality is that PHP does have some good things, and those good things have made it a success, powering now more than 81% of the websites on Earth compared to the 18% share for ASP.Net. What has made PHP so succesful is, like many things in life and business, not realted to it's technical quality (if any...) but other traits related to community adoption, "freeness" (at the expense of countless of cheap developer hours to compensate....) and interoperability. It can be deployed on most web servers on almost every operating system and platform, free of charge.
PHP extends is functionalities by the means of compiled extensions. For performance and/or arquitecture reasons, key pieces such as PDO drivers needed to connect to databases are compiled and deployed as plugable extensions.
PHP scripts are single threaded, and the only way to have some multithread capabilities is by means of the PThread extension, wich I have never been able to make work: so forget about the easy performance optimizations that the Parallel libraries brought to .NET a few years ago.
The perspective is clear, PHP transforming fast to be ready to eat the whole web, making it's way slow but steady into the enterprise niche.
Before quiting this brief PHP introduction, there is a honorable mention to do for the developers at Devsense, that have turned PHP into another .Net language, being able to compile PHP code directly into .NET assemblies the same way you would do with C#. Unfortunatetly, the platform is yet inmature and is laking PHP features, not to mention that you are alone when it comes to basic extensions such as Memcache or PDO's for SQL Server. Another great thing from Devsense is that they have integrated PHP development into the Visual Studio IDE, and that is one of their products you will not want to miss.
Before you start working on Drupal you will need an IDE. The IDE is the tool that makes working with PHP productive. Autocomplete, quality assurance, sintax highliting, etc...
The best tool to work with PHP on Windows is Visual Studio with the PHP Tools Add-On. You can get Visual Studio for free with the Visual Studio Community Edition. After installing Visual Studio make sure that you install the plugins required for a productive Web Development experience:
Another commonly used IDE is PHP Storm that I personally do not recommend. It has many more features than PHP Tools, but 99% you will never use and the 1% that you use on a daily basis are far much more better implemented in PHP Tools for Visual Studio.
You can download a copy of PHP Tools from Devsense.
Important: Stay away from the PHP platform installer.
If you intend to run PHP on Windows take the time to deploy it manually step by step and understand how everything works.
For the scope of this article, you need to know that PHP scripts are interpreted, and that on windows this interpretation is performed by an executable application that you can download from here.
The first dilema you will come across is wether you should download the Thread or Non Thread Safe (NTS) version, or the x86 or x64 binaries. For now, stick to x86 Non Thread Safe. To make the story short, Thread Safe version has some issues with several extensions (we'll see what an extension is later) and is suposedly slower, and x64 support is still buggy on some platforms.
You will always want to stick to the latest version of PHP but that depends on your application compatiblity and the availability of compiled extensions for the PHP version you want to run. At the time of this writting you should be using PHP 5.5 if you intend to use MS SQL because there is yet no official PHP 5.6 of the component needed to comunicate PHP and MS SQL.
Download the binaries and drop them anywhere in your system (ex. C:\PHP\).
The binaries folder has all the required DLL's for PHP to work. Depending on the combination of Windows OS version and PHP you chose, you might need to download the C++ redistributable package that matches the version of Visual Studio that was used to compile the PHP binary you downloaded.
More recent versions of PHP are built with VC9 or VC11 (Visual Studio 2008 and 2012 compiler respectively) and include improvements in performance and stability.
The VC11 builds require to have the Visual C++ Redistributable for Visual Studio 2012x86 or x64 installed.
The PHP runtime settings are managed through the php.ini file. The downloaded package has a demo "php.ini - development", just rename it to "php.ini" so that the PHP runtime can boot.
You are now ready to run PHP scripts!
Drop these lines in a d:\hello.php file:
<?php print phpinfo(); ?>
And run your first script with:
This is not exactly how web page serving works, but is helpful in clarfying the underlying communication setup between IIS and PHP. The function phpinfo returns an HTML formated string that contains information about the current PHP environment: version, settings, what extensions are loaded and their settings, etc. This is not very helpful shown through the console, but will make more sense when we access that script from a web server.
IIS comunicates with PHP by means of CGI. Common Gateway Interface (CGI) is a standard method used to generate dynamic content on Web pages and Web applications. CGI, when implemented on a Web server, provides an interface between the Web server and programs that generates the Web content. These programs are known as CGI scripts or simply CGIs; they are usually written in a scripting language, but can be written in any programming language.
Executing PHP scripts with a CGI application is highly inefficient and rarely used. It was originally introduced in the 1990’s but was deemed to be too inefficient to use on anything other than very small sites. What we are really going to use is FastCGI, similar to CGI but with many of it's problems solved.
Start by setting up a new empty website in IIS at c:\inetpub\wwwroot\mydrupal\ on port 8080, so that the access URL is http://localhost:8080/, and drop in it the hello.php script we made before.
To make your life easier, there is an IIS complement you can install that provides a visual interface to configure basic PHP settings in IIS wen applications, the PHP Manager for IIS. This manager allows for easy registration of the PHP handler, along with other basic PHP settings.
Edit: As of the 4th January 2016 the PHP Manager for IIS installer provided officialy by MS does not install on Windows 10. You will find a working installer at the end of this post:
Once installed you will see the PHP Manager icon in the IIS configuration pane for all your sites.
Connecting IIS and PHP is a piece of cake, just open the PHP Manager in the UI and navigate to the php-cgi.exe executable:
Well...it's not actually that easy, having a good PHP deployment that you understand and ready for production requires an article in itself, but for now this will suffice.
Note that for this to work you must have the FastCgiModule installed in IIS. This is an optional component when you install windows (Add or Remove Features), and it is actually called CGI in the installation UI.
You are now ready to serve your first PHP script, just browse to http://localhost:8080/hello.php, and you should bee seing something like this:
Drupal works in such a way that all (well, nearly all) requests are forwarded to the index.php script, and Drupal uses a query string parameter to retrieve the information about what the user is really trying to access.
By default, Drupal uses and generates URLs for your site's pages that look like "http://www.example.com/?q=node/83". With so-called clean URLs this would be displayed without the "?q=" as "http://www.example.com/node/83".
The style of URLs using "?q=" can be hard to read, and may even prevent some search engines from indexing all the pages of your site. In Drupal 7 you can turn clean URL's on and off, but in Drupal 8 clean URL's are enabled by default and cannot be turned off.
The magic behind clean URL's is Microsoft's URL Rewrite Module in IIS. This modules allows the user to define a set of application level URL rewriting rules, so that you can have custom transformations to URL request before they reach your application. The IIS module can be downloaded from here.
Once installed, rewriting rules are setup in you web.config or through a friendly UI in IIS. In any case, you don't have to bother any futher because the web.config that comes with Drupal Core has already the rewriting rules that Drupal uses in it.
Due to a recent update to Drupal 8, you need to allow the HTTP_PROXY header to be manipulated by the rewrite module. To do so, open IIS manager and select your website and the Rewrite module:
Then choose the "view server variables" option:
Finally add the HTTP_PROXY header:
We mentioned before that PHP can be extended by the means of compiled extensions. These extensions are configured through the PHP.ini file.
For a basic setup you will at least need:
The Wincache Extension: This is a Microsoft developed extension that used to (prior to PHP 5.5) provide opcode caching and other platform specific performance boosts. From PHP 5.5 and beyond, opcode caching is part of the PHP core. Opcode SHOULD always be turned on as it provides a rough x2.5 performance increase because it prevents PHP from interpreting the scripts on every page request. You can download it here.
The SQL Server PDO Extension: Drupal connects to databases by means of PHP PDO drivers. PDO is an acronym for PHP Data Objects. PDO is a lean, consistent way to access databases. This means developers can write portable code much easier. PDO is not an abstraction layer like PearDB. PDO is a more like a data access layer which uses a unified API (Application Programming Interface). The minimum required version of this driver for Drupal is 3.0. and you can download it here.
Extensions need to match you current PHP deployment, that is:
- PHP Version
- Thread Safe or Non Thread Safe
- x86 or x64
At the time of this writing all the available binaries for Wincache are NTS (Non Thread Safe) and x86, so you need only to chose you PHP Version.
Drop both the Wincache and the SQL Server PDO dll's inside the extension folder (c:\PHP\ext\ if you followed this tutorial) and enable them by adding the following two lines to your PHP.ini:
If you get lost just follow these instructions on how to enable a PHP extension.
Make sure that you enable OPCODE caching, for PHP 5.5 and above, use this in php.ini:
wincache.ocachesize =150 wincache.ocenabled=1
NOTICE: We highly discourage the use of Zend Opcache on Windows platforms. It is unstable under heavy load. Wincache has a better and more stable Opcode Caching capabilities. It is full of stability bugs that render PHP unusable on production environments:
The average opcache size for a big size drupal installation is about 125Mb.
You can verify that the extension have been properly enabled by browsing the hello.php file we prepared before.
Important: After making any changes to PHP.ini, if you don't see these changes reflected in PHP, make sure to restart the application pool in IIS. This is not always required.
You've probably heard that Drupal is slow (it is indeed if wrongly deployed/configured!), but here we are the Drupal performance experts and want you to have a smooth Drupal experience after doing a vanilla setup, so here go a few basic settings/checks you must do.
Configure the Wincache extension, to do so enable these settings in php.ini:
; Wincache ini configuration ; Disable Opcode cache (only for PHP >= 5, otherwise use 1) wincache.ocenabled=1 ; Enable file cache wincache.fcenabled=1 ; Enable user cache wincache.ucenabled=1 wincache.fcachesize = 255 wincache.maxfilesize = 2048 wincache.ocachesize = 150 wincache.filecount = 8000 wincache.ttlmax = 2000 wincache.ucachesize = 85 wincache.scachesize = 128
I will asume that you already have the knowledge and tools to setup and administer a SQL Server Database. Create a new database named 'mydrupal', and make sure that he database is created using a unicode case-insensitive collation (for example the default SQL_Latin1_General_CP1_CI_AS).
To prevent any future headaches, make sure that you test your connection to the Database (username, password, name, port, etc.) using SQL Server Management Studio from the same box that is hosting the Website, and that the user you will be using to connect to the database has privileges to create/alter/delete database objects such as tables and indexes.
You must also install in the box that will be connecting to the database (the one hosting your webserver) the SQL Server Native Client.
Microsoft has been very bad at making finding the installer for this component easy as it used to be a stand alone element, but is now part of the SQL Server Feature pack. This pack has many files, but the one you are looking for is SPA\x86\sqlncli.msi. (there is also a x64 version of it in the package).
You are now ready to install Drupal, download the latest's core version from here and unzip it into c:\inetpub\wwwroot\mydrupal\ or whatever directory you website's root is pointing to.
We recommend to stay away from pre bundled Drupal packages such as Acquia Drupal, or Acquia dev desktop. You don't know/learn and have no control of what happens behind the scenes with those setups.
I highly recommend the 7.x-2.x branch of the SQL Server driver, because it addresses an important performance issue related to column collations (among many other fixes). Take a look at this issue in the Drupal queue that explains the current state of the development branches. Unfortunately, if you choose the 7.x-2.x branch you need to make a small change in Drupal's code to be able to install it. Here is the link to the issue that contains instructions and the patch.
The 7.x-2.x branch will not work without having the Wincache extension enabled and the Wincache cache storage backend configured in the settings.php file.
Before browsing your site to launch the installer:
- Configure the settings.php file just as you were doing a standard install of Drupal (in sites/default/settings.php by renaming default.settings.php).
- Grant the IIS_IUSRS user write permissions to the settings.php file and make sure it is not marked as readonly.
- Ensure that settings.php is set back to read-only after the installation is completed.
- Create a files directory in sites\default\ then grant a modify permission to the IIS_USRS user.
- Create a "tmp" directory inside the files directory you just created.
- Unzip the SQL Server and Wincache modules into the sites/all/modules/ foler.
- Copy the sqlsrv directory (found inside the module's zip package) to the includes/databases directory.
Imoprtant Notice: By default, Drupal uses the "files" folder you have just setup as a content storage for temporary, private and public files. Commonsense dictates to keep application files away from content files, so probably you will need to refactor this structure after setup and use Symlinks or other strategies to keep your content files separate. We will be preparing an article on best practice regarding deployments in the future.
Finally, configure the Wincache cache backend in settings.php:
# If you have more than one cache Backend at the same time, use this: $conf['cache_backends'] = 'sites/all/modules/wincachedrupal/drupal_win_cache.inc'; # If you have a small site then just set wincache as the default backend # and forget about memcache. $conf['cache_default_class'] = 'DrupalWinCache'; # Keep forms in persistent storage. $conf['cache_class_cache_form'] = 'DrupalDatabaseCache'; # I don't see any point in keeping the module update information in Memcached. $conf['cache_class_cache_update'] = 'DrupalDatabaseCache'; # Locking is way faster with Wincache. $conf['lock_inc'] = 'sites/all/modules/contrib/wincachedrupal/wincache-lock.inc'; # Session is also way faster. $conf['session_inc'] = 'sites/all/modules/contrib/wincachedrupal/wincache-session.inc';
Important note: this setup is sending all caches to Wincache except cache_form and class_cache_update which are kept on database. This will be more than good for small to medium sized websites. On big sites you total caching size needs can easily exceed the few GB's of data, and this cannot be completely handled by Wincache. In those cases you should be considering a drual WIncache-Memcache intergation.
Now browse to yor site's root directory http://localhost:8080/ (the default document should be index.php) and follow the Drupal Setup instructions.
See our Calling .Net from PHP article to:
- Use any .Net binaries (even without COM Visibility) and for any version of the .Net framework.
- Iterate over .Net collections directly from PHP
- Automatic propagation of .Net errors into native PHP exceptions that can be properly handled
- Acces native enums and static methods
- Use class constructors with parameters
- Debug PHP and .Net code at the same time as if it was a single application.
- Specify the location of you binaries, so there is no need to register anything in the GAC and you can have version control along with your application.
A piece of code writen in C# like this:
Will look like this on PHP:
$minifier = netMinifier::Minifier_Constructor(); $settings = netCodeSettings::CodeSettings_Constructor(); $csssettings = \ms\Microsoft\Ajax\Utilities\netCssSettings::CssSettings_Constructor(); $settings->OutputMode(\ms\Microsoft\Ajax\Utilities\netOutputMode::SingleLine()); $settings->PreserveFunctionNames(FALSE); $settings->QuoteObjectLiteralProperties(TRUE); $result = $minifier->MinifyStyleSheet($css, $csssettings, $settings)->Val();
If you properly followed these instructions, you should have a smooth and fast Drupal website and have a basic understanding of how the different components needed to run Drupal on Windows interact, but as your site gets bigger and more complex, you will need more advanced configuration.