Windows Azure – SQL Server connectivity and Internal Endpoint

Hmmm…after a long weekend…came to the office…sitting idle; I was not having much work today. I was thinking what to do today and suddenly my colleague came to me with a sad face. J

Smile spreads all over my face thinking that finally the day is not going get wasted. As expected she had come to me with a problem she was facing and I was in full gear to help her OUT!!

Ok. Here goes the problem statement (now considering myself as a victim of the problem) – I have a SQL SERVER 2008 R2 installed on VHD and deployed as a VM role on Azure. (Let’s call it as SQL VM Role from now on).  In the same hosted service of Azure portal, I have another VM role having dot net application and SQL Server Management studio. (Let’s call it as VM Role 2). I was trying to connect from my dot net application of VM role 2 to SQL server of SQL VM role. But connection was getting failed. I tried to connect from Management studio to SQL server VM role; but still no success. The error which I was receiving was of named pipe error and related to remote connection.

In short I was trying to perform “Inter role communication”.

To resolve this error I tried following things at start –

1.     First I verified that, firewall is enabled on both VM roles. Then on SQL VM role, Opened port 1433 TCP in firewall (inbound and outbound both). I feel this step is very crucial as SQL Server service runs on 1433 port.

2.     Then I found that, I was not able to ping to both VM machines from each other. So opened all "file and printer sharing" options through "predefined" labeled drop down in firewall (inbound and outbound both). Then opened "File and Printer sharing" options on VM role 2 (inbound and outbound both).

3.     Verified that TCP-IP and named pipes are enabled on SQL server VM role. If it’s disabled then enable it and restart SQL Browser service and SQL Server service.

4.     Also make sure that remote connection is allowed in SQL server by viewing the properties of database server.

After following all above steps, the connection failure error was persistent. I was feeling that, all the roads of getting solution is closed and I read about INTERNAL ENDPOINT is AZURE roles.

After going through enough theory I found that, internal endpoint is the key for achieving inter role communication.

Ok, what is endpoint? – In order to listen for client requests, the host application provides an endpoint. An endpoint consists of an address, a binding and a contract.

Address – The address identifies where the clients can find the service.

Binding – Specifies how client and service communicate.

Contract – Specifies what operations the service support.

There are two type of Endpoint in Azure –

1.     Input endpoint – All roles can have any type of input endpoints. Input endpoints are used to communicate with role instances from outside of Windows Azure. Each input endpoint defined for a role must listen on a unique port. The port that is defined for an input endpoint is used by the load balancer of Windows Azure to make your hosted service available on the Internet. In short, if you want your service application to be exposed to internet then you need INPUT endpoint.

2.     Internal Endpoint - All roles can have any type of internal endpoints. Internal endpoints are limited to 5 per role. Internal endpoints are used for internal role communication.

In this case of inter role communication, Azure Connect will not help. As windows Azure Connect is designed for establishing communication between On-Premise resources and Azure Roles Virtual Machines.

Therefore define an internal endpoint 1433 for SQL VM role as shown below –

Once done, host the VM roles by following usual procedure. This will enable the connection from VM Role 1 to SQL server of VM Role.

Cheers…

Please give food to all my fishes swimming at the bottom. It's fun!! Try it!!
Thanks for reading!!

Happy coding!!