Database servers are tricky. Before jumping to the conclusion that more hardware is needed, make sure you’re getting the most out of the hardware you already have. Hiring a DBA is definitely the right way to go. They’ll quickly be able to point out where the pain points are in your configuration. If you can’t afford a DBA, you can use this quick guide to get a starting point on what might need adjustment.

At the system level, start by checking over the following items.

• Does the machine have sufficient RAM?
• How much idle CPU time is available?
• Is the hard disk okay?
• Is the networking speed sufficient?
• Are there services that should run somewhere else?

Next investigate the application itself.

• Is the RDBMS allowed to use all of the RAM available?
• Are the tables indexed; properly?
• Are the indexes up to date?
• Are the statistics up to date?
• Are there queries that can be offloaded; refactored; eliminated?

This, at minimum, will save time when your thinking about sticking more power in-front of the database. If you still decide to make the move, make sure you check over these values as soon as you are done or you may find your performance decreases when it should have increased.

In the last post, I explained my work around to get data out of a Microsoft SQL Server using stored procedures. Since then, I’ve continued learning more about the limitations of the FreeTDS driver in PHP and hit another snafu when working with the data. Fields that are exported as VARCHAR are truncated at 256 characters! Whoa! big gotcha when today’s database tables might contain values in VARCHAR(1024) or even bigger! So what’s a PHP script to do? CAST the values!!!

 
-- original statement
SELECT myBigVarChar 
     FROM myTable 
     WHERE myId = 1;
 
-- revised statement
SELECT CAST(myBigVarChar AS text) AS myBigVarChar 
     FROM myTable 
     WHERE myId = 1;

Notice the statement’s a little more unruly to look at, but is functionally the same. The case to text allows the FreeTDS driver to properly convert the data. I also noticed this issue with UNIQUEIDENTIFIER type columns coming through as a BLOB. My fix was to CAST the identifier to a VARCHAR(36) to safely hold the string representation of the UUID.

Hope this saves you some head scratching!

After some effort getting the MSSQL drivers in PHP, I was ready to hook into the RDBMS and use my nicely developed stored procedures. FreeTDS required minimal setup and configuration and I was able to establish a connection and run simple queries against tables in no-time.

However, when I attempted to execute stored procedures, they would fail. To make troubleshooting harder, the MSSQL functions do not return error messages — at least none that I could access in PHP 5.1. My work around to the proper methodology init(), bind(), and execute() process was to use query() and pass in the procedure and parameters as needed. It’s slightly less elegant but opens the data up to you.

// Establish the connection and run the procedure
$connection = mssql_connect("123.123.123.123","db_login","db_password");
mssql_select_db("db_schemaname",$connection);
 
// Run the procedure
$username = "user"; // sanitized!!!
$password = "secret_password"; // sanitized!!!
$result = mssql_query("CheckLogin '$username', '$password'", $connection);
 
// handle the result as needed

The standard disclaimers apply about validating, escaping and bullet proofing any data passed into Microsoft SQL Server using this method. Seriously, heed these warnings, sanitize any data, or you will find your site vulnerable to SQL Injection attacks which are serious business.