How to Connect PHP Application to MSSQL, Setup on 2 Different Servers

So we had this enterprise mobile application for a client, that was running with PHP web application as backend and web services and MSSQL database. Both web application and database were hosted on same server (windows server). The windows server went out of life last year, which means we had to move everything to new server, with no downtime and also client wanted to change the domain name that was used to serve the backend.

The Solution…

As all the assets were being stored on AWS S3 Storage, all we had to do was move the application files and DB to new server but to ensure that our users who will be old version of mobile application continue to use it without any downtime and when they upgrade, there should be no downtime or data loss.

The image above should give you an idea on how we decided to complete the migration.

The challenge we faced in the above architecture was to connect old server (both running legacy database and PHP) to new server. Luckily both our server were behind the same firewall, which meant that all ports were open at network level for them to communicate with each other. But we still had to open/allow required ports to be opened at OS level.

Below i am sharing the steps i followed to complete the setup.

1- Connection settings at PHP — as we are connecting remotely to database, we will use the Public IP and port (1433 default port for MSSQL)

2- If you try to run the above file on browser, it will most likely give you error. We now need to make some configuration changes on new server that hosts MSSQL 2019.

3. As we are connecting using IP address, we will have to reconfigure the server to use static TCP port. To do it please perform the following:

a) open SQL Server Configuration Manager;

b) switch to the SQL Server Network Configuration | Protocols for SQLEXPRESS;

c) double-click the TCP/IP protocol;

d) select the Yes value in the Enabled field;

e)switch to the IP Addresses tab;

f) find the IPAll section;

g) clear the TCP Dynamic Ports field in that section;

h) specify the 1433 value in the TCP Port field:

i) Restart SQL server.

That’s it! now if you run the connection file in browser it will work.

There few checks/configuration that may be needed —

1- In SQL Server Management Studio, ensure ‘allow remote connection to this server” is enabled

2- Ensure TCP/IP is enabled.

I hope this helps someone 🙂