How to make Drupal work well on Microsoft technology stack

I was recently on DrupalCamp Wrocław, where some people suggested that we should share at least part of our experience with MS technologies. We are working on a big project, where we have to use Microsoft technologies - namely Windows Server 2012, MS SQL Server and IIS. The system we build is big and complex - and Drupal is just one of the modules there. We faced many issues which need to be solved before our production system starts.

One of the issues we have solved is performance. Drupal, in its standard configuration, is not very efficient on MS SQL Server. After some debugging we found that some queries - especially the ones related with locale tables - didn't use indexes nor statistics. Problem is caused by data types - in order to make search in MS SQL efficient, character columns must have a limited size. In our case, changing locale_sources' source column type to nvarchar(4000) allowed us to get 5 times better performance of related query. And - because this table is read many times for each page load - it had terrific impact on performance. That's because locale_sources table for multilingual sites is queried everytime for every untranslated word in system (translated words are cached).

We have also created and executed some SQL code that created better indexes and statistics. In fact, I really don't understand how they work - our brilliant SQL Server guru just made some magic and gave me the file to run.

When it goes to caching - we tested two cache acceleration solutions: wincache and memcache, Finding binaries for memcache PHP extension was a bit difficult, but we finally managed to download it from windows.php.net site. After some tests we estimated that memcache has slightly better performance than wincache. Unfortunately, memcache on windows is not supported well, so we recommend using wincache.

If you are working on performance, ensure that you use the latest version of PHP - we've updated old PHP 5.3 to 5.4. We didn't measure it, but we feel that it was another source of performance improvement.

The last thing to do is to set proper "expires" headers on all static resources. This includes themes, uploaded files etc. In our architecture we have a proxy in front of web server; expires header allows us to optimize caching of data and decrease load on PHP server.

If you want to improve performance of your Drupal site, see our professional Drupal tuning services or click here and contact us.

Do you have any questions?

Ask us!