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.
