Multiple SQL instance in VM

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’.

Untitled

Right click on TCP/IP and choose properties.

TCP

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.

Firewall search

Firewall

Choose Inbound Rules, Create a New Rule

NewRule1

Enter the Port number in the Specific Local Port

NewRule2.png

NewRule3.png

NewRule4.png

NewRule5.png

Enter the description and Save.

NewRule6.png

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

NewVM

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.

NewResourceGroup.png

After creating new ‘Network Security Group’ you need to create an Inbound Rule.ResourcrGroup2.png

Choose Inbound Security Rules from Settings and fill new port value as showed below:

NewRulePort

Finally you are able to connect your SSMS by specifying the port along with the DNS name.

SSMS.png

Get the connection string for this instance and start using from your application too.

Happy Programming.

Advertisements
%d bloggers like this: