Setting up XAMPP to Work With MSSQL

In this article, I’ll show you how to set up XAMPP on a Windows 7 machine. The process is largely the same on Windows XP, if you’re repurposing an older machine. One thing to keep in mind, though, is the newest versions of XAMPP use a version of PHP for which there is not an MSSQL driver. If you are using a WinXP box, use the SourceForge site to grab an older version. I’m still using 1.7.5 on my production sandbox. An article in the not-too-distant future may be upgrading that box – there are some features in newer versions of XAMPP that I want, I just haven’t gotten around to upgrading yet.

Any of the three XAMPP packages will work fine – I’d recommend the VC9 version, although I’ll explain why that is in another article later. If you’d rather have an installer walk you through the process, grab that version. The ZIP archive can be unzipped by Win7 if you don’t have any other zip programs installed. My preference is the 7z archive, as I have a terrible internet connection at home. 7-Zip, as I’ve mentioned previously, is a truly awesome program for managing zip archives of many, many sorts. I highly recommend it over any paid zip program.

Keep things a little easier and create a folder in your download location and name it ‘sandbox’ or the like. There are a few things we’ll need to do this, and it’s much easier if you have everything in one place. When you download XAMPP, don’t miss the warning on the landing page that you’ll need the MS Visual C++ 2008 Runtime, which is located at http://www.microsoft.com/en-us/download/details.aspx?id=5582. Next you need the sqlsrv drivers for PHP, which are found at http://www.microsoft.com/en-us/download/details.aspx?id=20098. If you’re on Win7, you’ll want the SQLSRV30.EXE package. Otherwise grab SQLSRV20.EXE.

If you’re running a domain environment, create an account for this box with a non-expiring password. It’s easier if a little less secure to make it an administrator account – consult your IT people and follow their recommendation. While you’re about it, create a static reservation for the IP address, as well. This will be a fully-functional web server, and so you want to know where it is located. If you leave it as DHCP, someday it will lose its reservation and that will be bad times. If Windows Update isn’t managed by Group Policy, set it to automatically download and install. Following that, you’ll want to set the box up for auto-login, which is quite nicely explained at http://www.velikan.net/automatic-login-windows-account/. What we want in the end is a box that runs itself without user intervention as much as possible.

First, install the Visual C++ runtime. This will keep XAMPP happy. Unzip the XAMPP package and put it close to the root of the drive – I typically use C:\xampp (although there is something to be said for having this system on a separate drive/volume in case you have to reinstall Windows at some point.) Once you’ve put it where you want it, find the ‘xampp-control.exe’ program in that directory and start it up. You’ll be greeted by the XAMPP control panel:

I install Apache, PHP, and FileZilla as services with automatic startup. Check the boxes to the left of those three, and then hit the Start buttons on them. Open a browser, on this machine or any other on your domain. If you’re on the sandbox, navigate to http://127.0.0.1 (or http://localhost.) From a separate machine, navigate to the IP address assigned to the sandbox. If everything worked correctly, you’ll see the XAMPP status page:

Congratulations, you have a running web server.

Next, we’ll want to do a little configuration on our PHP installation. Open a text editor and find your php.ini file. If you installed XAMPP to c:\xampp, the path would be C:xampp\php\php.ini (You can also use the ‘Config’ buttons on the XAMPP Control Panel.) Navigate to the [Date] section and correct the timezone. The valid timezone options are found at http://php.net/manual/en/timezones.php. As a shortcut, though, you can likely get away with using America/Los_Angeles, America/Denver, America/Chicago, or America/New_York, assuming you live in the US and your location observes Daylight Saving Time.

Next, find the memory_limit key (Ctrl+F is your friend here) and bump it from 128M to 512M. You can set memory limits per-script, but this will avoid problems in most scripts.

The last bit is to install the sqlsrv drivers and enable them in PHP. For that, execute the sqlsrv30 package and extract the files. The two you want are named for the version of PHP you are running. In XAMPP 1.8.1, PHP is version 5.4.7, so I’d copy the php_pdo_sqlsrv_54_ts.dll and php_sqlsrv_54_ts.dll files and paste them into C:\xampp\php\ext. The latter file is the standard driver, while the former is the PDO driver. I’ll explain more later on the difference, but to keep things easy, put them both in the \ext directory.

Following that, go back into php.ini and navigate to Windows Extensions. At the bottom of the list, add two entries, extension=php_pdo_sqlsrv_54_ts.dll and extension=php_sqlsrv_54_ts.dll. Now restart Apache (either restart the service in services.msc or stop and start on the Control Panel.)

Open or refresh the page at 127.0.0.1 (or the sandbox IP) and click the phpinfo() link on the left navigation panel. Ctrl+F and search for ‘sqlsrv’ and you should find the following entries:

If this doesn’t work, the most common mistakes (or at least what I’ve done most often) are forgetting to put the dll files into the \ext directory or omitting/making a mistake in the extension entries in php.ini.

After all that, congratulations. You are now ready to use the best reporting system DDMS has ever had.