Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

In the course of trying to move our website to Azure, we've found a problem with our PHP code talking to MSSQL.

On the current dedicated host (hosting both the PHP and the DB), going to a page typically takes less than 2 seconds. I've also run the queries necessary through SMS and they take less than 1 second. The amount of data being returned by the queries is very small - 10-20 records, certainly less than 100kb in total.

With the website hosted on Azure (connecting to an Azure SQL database) the same page takes around 40 seconds to load. Further investigation showed that this might be caused by the database not being on the same machine as the PHP - when the original website is pointed at a remote database (either another dedicated machine or the Azure SQL instance) the page load time goes up into the 40 second range.

In the Azure case, both the website and db are located in the same region (N Europe). When testing using our machines, both are located in the UK.

The current webhosting machine is running PHP 5.4 and Microsoft SQL Server Express Edition. The Azure website is running on PHP 5.4.

In both cases we're using the 3.0.1 Microsoft SQLSRV drivers

How can we improve the performance when connecting to a remote SQL DB?

I've cross-posted this here in response to the same question on stackoverflow: http://stackoverflow.com/questions/15835623/why-is-php-mssql-very-slow-when-the-db-is-not-on-the-same-machine-as-the-php

share|improve this question
1  
I don't know mssql that well, but does it try and look up dns entries for remote connections? –  NickW Apr 5 '13 at 14:54

1 Answer 1

There are lots of things that could cause this. The trick is to whittle it down to the correct one. The most likely causes are Network issue, or connection issue, or DB issue. In this order:

First thing I would try is to rule out a network issue: try pinging the DB server from the Web server. Is the connection string using dns or ip? Does pinging dns vs ip make a difference?

If ping times are normal and fast, could it be a DB login issue? Can you connect to the DB server from SSMS? Is it fast?

If still everything is fine, run the query the website runs using SSMS- is it still fast? If not, it's a DB issue- the new DB is probably not identical to the original. Perhaps certain indexes/constraints didn't get copied over.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.