Along with the default instance if you want to create another instance (Named Instance) then the following article would be helpful.
SQL server uses port 1433 as default TCP/IP port for communication. This port cannot be used for the new SQL instance you create. Because only one instance can listen to a port at a time. That means you need to stop one of the SQL Sever service instance to use the default port. In such scenarios you can create a new port and map that port in SQL Server TCP/IP connection and allow that in the firewall too.
To complete the process you may have to go through the following steps.
- Specify the new port in SQL Sever
- Allow new Port in Windows Firewall
- Add the new port in Azure Portal as endpoint
Specify the new port in SQL Sever
From the SQL Configuration Manager, open the node SQL Server Native Configuration and choose the Protocols for <named instance> In the below screen shot refer Protocols for ‘PRODUCTION’.
Right click on TCP/IP and choose properties.
Enter the TCP Port number. eg:57500 and save. At this point you have asked SQL server to receive the incoming packets from port 57500. Next stage is to allow this port in Firewall.
Allow new Port in Windows Firewall
Open Windows Firewall with Advance Settings from run.
Choose Inbound Rules, Create a New Rule
Enter the Port number in the Specific Local Port
Enter the description and Save.
The new Rule has been created and added into Firewall list.
Add the new port in Azure Portal as endpoint
Login to new Azure portal and choose VM
There are 2 options to create a new port either using an existing ‘Network Security Group’ and add an In-Bound Rule or create a new ‘Network Security Group’ and add new rule into it.
For creating a new rule choose Add +New option from portal as showed below.
After creating new ‘Network Security Group’ you need to create an Inbound Rule.
Choose Inbound Security Rules from Settings and fill new port value as showed below:
Finally you are able to connect your SSMS by specifying the port along with the DNS name.
Get the connection string for this instance and start using from your application too.