Scenario
Once upon a time, there was a poor person who worked as a database administrator (DBA) for a rich company. One day, the DBA was asked to set up a new instance with a specific name in the existing testing environment. Following the instructions, the DBA successfully created the new instance, and it was assigned a random port number, 51207. The regular/default instance of the SQL Server was already running on the standard port 1433, while the new instance happily operated on port 51207.
Time went by, and the company decided to save money by shutting down the testing server in the Azure virtual machine every night at 10:00 PM. It would start up again at 10:00 AM the next morning. To ensure the safety of the database, the DBA was asked to configure a job that would shut down both SQL Server services at exactly 9:55 PM. Since the SQL Server services were set to start automatically, both instances would begin running again every morning.
Issue
One morning, the DBA’s team started receiving a flood of emails. The application team was facing a problem connecting to both instances in the testing environment. The poor DBA immediately began investigating to resolve the issue. He tried to manually start both SQL Server services, but each time, an error message appeared: “The request failed or the service did not respond in a timely fashion.”
Solution
After examining various logs, the DBA concluded that the service account used for both instances had an expired password. He hurriedly approached the Windows team and requested them to enable the “password never expires” policy for that specific domain login. They also reset the password to the existing one. Fortunately, both SQL Server instances in the testing environment started successfully, and everyone became happy again.
Another Issue
After a few months, one morning the DBA team discovered that the second named instance was not working. Over the weekend, the server had been updated and restarted, but the named instance did not come back online. However, the default instance was running fine. The DBA tried to manually start the named instance service, but it didn’t work this time. The password was also fine. Same error message appeared: “The request failed or the service did not respond in a timely fashion.”
Solution
The DBA began investigating by reading the logs in the Windows Event Viewer and the SQL Server error log. He discovered that the port needed for the named instance was already being used by some other services. These services were occupying the port that the named instance used to use.
The DBA explained the situation to the Windows team and the Azure team. Both teams suggested using a new port for the named instance. However, this presented a problem because most applications were configured to connect using the IP address and port number in their connection strings. If they changed the port, the applications would no longer be able to connect to the named instance.
After considering the options, everyone decided to update the application connection strings with the new port number, which was 61207. The DBA made the necessary changes and started the named instance service. Miraculously, it started working again! Everyone was relieved and could continue their work happily.