MSSQL Server and PHP: Getting Started

Well, before we get started lets look at what we shall need:

1) MS SQL Server 2008 R2 Express Edition
2) MS SQL Server Management Studio
3) A WAMP Setup
4) The MS SQL Driver for PHP

You can get the first two products in your desired package from this link: http://www.microsoft.com/express/Database/InstallOptions.aspx

Since we need both the database engine and the management studio, we should pick the 2 in 1 pack. Now install the tools. While installing the SQL Server, at a point it’ll ask for an “Instance” name. Use the default instance. If you already have MSSQL Server with Visual Studio, please note that I had issues with the VS installation (SQLEXPRESS instance). I couldn’t connect to that instance even from Visual Studio. So I had to install another instance with the default instance name which worked for me. If you don’t have a previous installation, then you don’t need to bother about it. Just remember to set the instance to default instance name (MSSQLSERVER).

After the management studio and the server is installed. We need to install SQLSrv – a driver written by Microsoft for PHP connectivity to SQL Server. If you’re using PHP on Windows, I recommend this one in contrast to the old mssql.dll driver. A good article about the difference of the drivers can be found here: http://blogs.msdn.com/b/brian_swan/archive/2010/03/08/mssql-vs-sqlsrv-what-s-the-difference-part-1.aspx

OK now, so download SQLServ from this URL: http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=80e44913-24b4-4113-8807-caae6cf2ca05 🙂

After downloading double click to extract the contents. It contains different drivers for different builds of php. Assuming that you already have a WAMP server setup, view your phpinfo() and look for “PHP Extension Build”. In my case it reads – “API20090626,TS,VC6”. That means I have to pick the Thread Safe (noted as “ts”) version which was compiled using VC6. Given that my PHP version is 5.3, I chose: “php_sqlsrv_53_ts_vc6.dll . Choose the one appropriate one for your PHP build and drop it in the “ext” folder. I shall not cover how to load an extension since as a php dev you already know that. Edit your php.ini, enable the extension and restart the WAMP server.

Now, launch SQL Server Management Studio and login using Windows Authentication. Right click on the server name “(local)” and go to properties. You shall have a window like this:

From the “Security” section, enable “SQL Server and Windows Authentication mode”. This will let other tools like PHP to connect to MSSQL Server which can not use Windows Auth easily. After you press “OK”, the SQL server will need to be restarted. After restarting, again log into the management studio. Expand the “Security” node under the server name. Right click on “Login” and select “New Login”. Type a login name, select SQL Server authentication and create the user.

Once you are done with these steps, now we write some codes to see what we’ve done so far:

Output:

I copy-pasted the codes from the SQLSrv manual. Ah, I forgot to tell you the driver has a nice manual for learning the available APIs. The driver also has a PDO version. If you’re a PDO fan, don’t forget to check out the PDO driver and write some review.