CodeIgniter/PHP + IIS + MySQL + MSSQL: It Works!

There are a lot of people out there who call themselves “LAMP” developers — short for Linux, Apache, MySQL, PHP. That’s the standard configuration for production PHP applications. Recently, I ended up having to build a CodeIgniter application on Windows, IIS, Mysql+MS-SQL, and PHP. Sound like there are bound to be issues? You bet, and it especially hurts because now I’m a real-live WIMP developer.

And what made it even more interesting was that due to constraints, I had to develop the application in Ubuntu and deploy to Windows for production.

Please keep in mind that I didn’t opt for this setup by choice. The servers to be used were already in place, and well, it just had to be this way. I’d imagine this unholy mix can be found on server farms somewhere around the seventh or eighth circle of hell.

Anyway, the point of this post is to document a few “gotchas” that came up along the way. At this point I should say that application is now happily humming along in production. I knew from the start that mixing all of the above would be a headache, but luckily, things worked out without sapping too much time. I should also say this before I start: Thanks to some great work done by others over the past 2 years, this setup was actually possible.

Gotcha #1: PHP and MSSQL on Ubuntu

Thank god this was so easy. In order to use the standard database functions like mssql_connect, mssql_query, etc, all I needed to do within my existing LAMP installation was run:

$ sudo apt-get install php5-sybase

And sh-bang, I could connect to SQL Server without an issue. From CodeIgniter, I set the database to use mssql as my driver, and I was home free.

Gotcha #2: PHP and MSSQL on Windows

For years, forums and IRC rooms were filled with hopeless requests to get these two to mix. In 2008, Microsoft wrote a driver to help PHP developers seamlessly connect to SQL Server. For Win-PHP installations, just install this gem: http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=80e44913-24b4-4113-8807-caae6cf2ca05 . Also, read up on the docs.

That driver uses a different API then the regular PHP mssql_* functions. In fact, it uses sqlsrv_* functions instead. So CodeIgniter can’t work with it out of the box. Luckily I found an excellent 2 and 1/2 year-old post by a guy who wrote the CodeIgniter driver to work with the Microsoft drivers. Just download the code, and drop it into system/database/drivers. Read up here: http://www.kaweb.co.uk/blog/mssql-server-2005-and-codeigniter/

One thing you will have to do to make it work with the latest version of CodeIgniter is create a dummy function in sqlsrv_driver.php. Just drop ‘function db_set_charset() { }’ somewhere in the class declaration.

As a side note, that driver would make a great addition to CodeIgniter Reactor.

Gotcha #3: mssql vs. sqlsrv

Notice that I had to use different drivers for connecting to SQL Server between Ubuntu and Windows. This is why it’s handy to use some sort of database abstraction class like the one that comes with CodeIgniter. All I had to do to switch between drivers when I deployed to the new environment was edit the configuration.

Also, query result fields that are fetched using PHP’s mssql driver come back as strings. The Windows sqlsrv driver gets fancy and hands back field values as objects. Your code won’t have to change for the most part, but beware that MSSQL ‘datetime’ fields come back as native PHP DateTime objects using sqlsrv_*, not strings. In the code, I ended up doing something like: if($date instanceof DateTime) return $date->format(…); else return $date;

Edit: Commenter Brian Swan and Twitter user Juozas Kaziukėnas have pointed out that the sqlsrv_ PHP driver will take a boolean ‘ReturnDatesAsStrings’ option in sqlsrv_connect() that specifies whether datetime fields come back as strings or objects. More info is here: http://msdn.microsoft.com/en-us/library/ee376928%28v=sql.90%29.aspx. Thanks guys!

Gotcha #4: File Permissions and Logging

As the author of WPSearch, a Wordress search plugin which does heavy work with the filesystem, I can say that the number one cause of broken installations is that the permissions are too strict. On Windows, files can appear to be 777 when viewing them via FTP, but on the Windows end, they are set as ‘Read Only’, or ‘Archive’. This throws a serious wrench into things.

When I first deployed to the production server, all I would get for responses was a blank page. Worst of all, the log wasn’t writing anything. After a trace through the CodeIgniter bootstrap, I found that the application died when the logging class was loaded. If your application’s logging threshold is set to write anything, and the logging fails, the application might just crap-out. This is different behavior than I’ve seen on linux, where you just won’t see logs appear in the logging directory, but the overall application still works fine.

Just turn off logging to make sure that empty responses aren’t the result of file permission errors.

Gotcha #5: No .htaccess Fo’ You!

I hadn’t known that I needed to drop the final application into an IIS instance from the start. I learned about that 1 day prior to the launch. Before that, I though I’d be rollin’ on Windows/Apache. Wrong!

IIS doesn’t use silly .htaccess files, which I had only used for standard URL rewriting/prettified URLs. I was forced to decide between converting the .htaccess to an IIS web.config file, or just ditch the prettified URLs. Since it was a small application, I went with the latter. Here’s a nice StackOverflow thread discussing a translation though: http://stackoverflow.com/questions/702526/translating-an-apache-htaccess-file-to-an-iis-web-config

Edit: Juozas had some comments here too: “@_kennyk_  about .htaccess vs web.config – you can actually import .htaccess in IIS URL Rewrite section.”

Gotcha #6: 2 Databases, 1 Application

This is more of a CodeIgniter issue. For this application I had to lookup data in a MySQL database, then use some of that information to pull rows from a MSSQL database. The hurdle revolves around having two concurrent database connections open in CodeIgniter.

You can’t really load the database in traditional CodeIgniter style, like $this->load->database(), or by autoloading it. Something like this doesn’t work:

$this->load->database('mysql-group-name');

# Do Stuff with mysql $this->load->database('mssql-group-name'); # Do Stuff with mssql

Instead, you have to ask CodeIgniter to hand you back the actual database object with each connection. It’s a good idea to encapsulate each within the models where you’re using them. Do something like this:

class SomeMySQLModel extends Model {

  # the mysql db instance

  private $_db = NULL;

  function SomeMySQLModel() {

    $this->_db = $this->load->database('mysql-group', TRUE);

  }

}
class SomeMSSQLModel extends Model {

   # the mssql db instance

   private $_db = NULL;

   function SomeMSSQLModel() {

     $this->_db = $this->load->database('mssql-group', TRUE);

   }

 }

Gotcha #7: Mysterious lag time

IIS can exhibit some odd behavior regarding response times. When I was initially testing the application on my SliceHost dev server, I was getting pretty speedy responses in about ~100ms (keep in mind that I was connecting to both MSSQL and MySQL across the internet, not locally).

But when I moved to production, requests were taking 6 seconds each. Confused, I thought there must be some sort of bottleneck in the application. I used CodeIgniter’s profiling and benchmarking classes to investigate. I benchmarked the MySQL and MSSQL connections, queries, and overall application execution time.

The app was reporting that it was finishing responses within 100ms. So where was this strange delay coming from?

I then thought there might be some sort of redirect loop going on. From the shell, I ran:

$ time curl -v [address]

And something else became apparent. The full content of the page would come back, but there was a 6 second lag before the connection would finally close. After a little research on the Google, it turns out I’m not the only one who’s observed this with PHP and IIS: http://www.google.com/search?q=iis+php+response+lag

I would like to say I figured out why IIS was being so lame, but the in-house net-admin suggested moving the application to another production server on hand, where the issue mysteriously dissappeared. At that point, everyone was happy, so I didn’t look into it any further. I did read about some resolutions regarding DNS settings, and skipping name resolutions in the db configurations. That might have been it, but I’ll never know.

Recap:

Don’t willingly try this. That being said, everything worked out much better than it could have, and there were relatively easy solutions to the bumps along the way. If you have to put together a project like this, I highly suggest using some sort of database abstraction (or a framework like CodeIgniter), and planning out how you’ll work out URLs, permissions, and differing environments. Thinking these things over will save you a lot of time, and hopefully leave the stakeholders a whole lot happier.

This entry was posted in CodeIgniter, PHP Development, Tools and tagged , , , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.
  • http://www.davidrhoden.com David Rhoden

    “Don’t willingly try this.”
    Best way of indicating “not a best practice” I’ve seen in a tutorial.

  • http://blogs.msdn.com/b/brian_swan Brian Swan

    One headache that has an easier fix than you mentioned is setting the ReturnDatesAsStrings connection setting to 1. As you would expect, this setting makes the sqlsrv driver return dates as strings (instead of the default PHPDate object). The docs for connection settings are here: http://msdn.microsoft.com/en-us/library/ff628167(SQL.90).aspx

    -Brian

  • Kenny Katzgrau

    Ha, thanks Dave. Good to hear from you!

  • Kenny Katzgrau

    @Brian, That’s a really good point. Also, it’s something worth sticking in an edit block. I’ll do that in a bit.

  • Pingback: Tweets that mention CodeIgniter/PHP + IIS + MySQL + MSSQL: It Works! -- Topsy.com

  • Jared

    I too am experiencing the strange lag time you talked about in #7. My application runs fine in a Linux host, but migrating away from the current Windows server isn’t an option. I’ll post a solution if I find one, but if anyone else has any ideas please pass them along.

  • Jared

    I brought up #7 with my host and found out that they were running PHP through PHP-CGI instead of an ISAPI module in IIS. When they switched that setting it made a significant performance boost and fixed that weird lag I was experiencing with CodeIgniter on IIS too.

  • http://www.richard-dickinson.com Richard

    Wow-you sure have had some problems!
    My probs of trying to get CodeIgniter working for me in IIS7.5 on Win7 & in WebMatrix seem positively tiny in relation!
    Best wishes

  • David Tavarez

    Gotcha #3: mssql vs. sqlsrv

    That’s a real problem. Once I had to move a complete system from a windows server to a linux server. As you know, sqlsrv return dates as DateTime objects and mssql as String. At the end I had to rewrite a lot of code.

  • Pingback: CodeIgniter/PHP + IIS + MySQL + MSSQL: It Works! « Niroze's Weblog

  • http://www.lyquidity.com Bill

    .htaccess files can be imported to URLRewrite but another solution which may be more seamless is to use Helicon APE which adds an HTTPHandler to IIS which makes IIS able to read and use .htaccess files. I’ve used it to be able to run a Joomla site on Linux or Windows.

  • industrial_training_indore

    I have read your blog and i got a very useful and knowledgeable information from your blog.its really a very nice article.You have done a great job .
    Thank you so much for sharing.
    industrial training indore

  • Xddd

    x

  • Janhsa

    But i keep on getting the same error which was undefined function sqlsrv_connect().

  • kpr

    Hi,

    Thanks for the great post.

    But somehow I am having problems in loading another db group which is a connection to a mssql db.

    Here’s the error :

    A Database Error Occurred
    Error Number: 42S02
    [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name ‘company’.

    SELECT *
    FROM company

    Filename: C:applicationsCIsystemdatabaseDB_driver.php

    Line Number: 330

  • http://xenoswarren.wordpress.com/2013/05/29/with-codeigniter-development-a-dedicated-developer-helps-save-time/ Phillip Hebert

    Through your this post I find all the things which I’ve never heard about. In short all the things which you’ve done is mash-up, which was theoretically not possible. Simply interesting!

  • bari

    hi kenny i have problem to backup and restore mssql database. i use codeigniter in linux server and sql server 2012 as database in windows (separate server). the codeigniter’s db config use “mssql” $db['default']['dbdriver'] = ‘mssql’;
    i can do backup and restore with “sqlsrv” and have no idea to do that with “mssql” db driver. would you give me a direction? i really confuse

  • Diana Watson

    Thanks. that helped!