2

I am using the following connection string to connect an Azure Web-Role to an instance of SQL running on a Virtual Machine. It's working fine!

<add key="VMConnectionString" value="Server=tcp:<mydomain>.cloudapp.net,1433;Database=myDatabase;User Id=myUserID;Password=**********;" />

My question is, should I be using the internal IP address of the VM (e.g. 10.1.0.0/16) instead of the public DNS to reduce latency? Do I need to put the web-role and the VM on the same virtual network in Azure? I tried to use the internal ip address of the VM as stated in the portal but it failed to connect, despite having port 1433 fully open. I should add that's without a Virtual Network.

QFDev
  • 8,668
  • 14
  • 58
  • 85
  • I don't think you can rely on the internal IP address as they may change on VM reboots. – Chandermani Mar 09 '13 at 11:57
  • What about Azure Virtual Networks? I'm currently looking at this article: http://michaelwasham.com/2012/08/06/connecting-web-or-worker-roles-to-a-simple-virtual-network-in-windows-azure/ – QFDev Mar 09 '13 at 15:09

3 Answers3

4

I have now managed to reference the Virtual Machine running SQL Server with an internal IP 10.0.0.4 by first creating a Virtual Network in the Azure Portal and deploying a new VM to that network.

With the web-role you cannot specify virtual network settings on provisioning through the portal. So to bring the web-role into the network you need to define this in the .cscfg file:

<NetworkConfiguration>
  <VirtualNetworkSite name="VirtualNetworkName" />
  <AddressAssignments>
    <InstanceAddress roleName="MyWebRole">
      <Subnets>
        <Subnet name="AppSubnet" />
      </Subnets>
    </InstanceAddress>
  </AddressAssignments>
</NetworkConfiguration>

You can now determine the internal IP of the VM SQL Server in the portal and add this to the connection string defined in my application web.config. Publish your application to Azure and providing you've correctly configured the end points and opened the firewall it should work fine.

After running a few performance tests there is a margin gain to be had from using the internal IP over the public DNS. I recommend the guide connecting web roles to a VM as a good reference point.

One other thing. When using a DNS (e.g. myvm.cloudapp.net) in the connection string I could not find a predictable way to know which IP the web role would call from. It was therefore difficult to open port 1433 on the VM to just allow the web role through. With the internal IP I just opened up the whole subnet.

QFDev
  • 8,668
  • 14
  • 58
  • 85
2

Did you enable the endpoints for VM's in the portal? This is a simple operation as you just pick the port and the protocol. Here is an article that explains how:

http://www.windowsazure.com/en-us/manage/windows/how-to-guides/setup-endpoints/

I would also recommend that you use the A record, mydomain.cloudapp.net, instead of the vip as the vip may change when you redeploy. The vip will not change on reboot.

traceagent
  • 61
  • 1
  • Yes the endpoints are there in the VM for 1433 (SQL) and 3389 (RDP). It's not that I can't connect to the SQL Server on the VM, the connection string stated works fine. But I feel that using an internal IP (e.g. 10.1.0.0/16) would be quicker and more direct. – QFDev Mar 09 '13 at 15:54
1

You would need to use the Public Virtual IP Address: Does Windows Azure offer static IP for VMs?

However I am going to ask, why are you installing your own instance of SQL server on a VM in Windows Azure and not using SQL Azure?

If you want to host your own SQL Server, at this stage I would recommend going with an Amazon EC2 instance as they have more features such as a permanent static IP. However you would also then need to transfer everything else over to the same data center as having a site hit a SQL Server across different data centers is really bad latency wise.

Community
  • 1
  • 1
Adam
  • 16,089
  • 6
  • 66
  • 109
  • SQL Azure would be our ideal choice and it's certainly much cheaper than running SQL on a VM. The problem is the performance downgrade is pretty severe! No fault of SQL Azure by the way, it's just our application is not optimised for cloud and VM + SQL is a comfortable middle ground, at least buying time to rebuild and optimise for SQL Azure. AWS is off the table now, we're too far down the line with Azure. – QFDev Mar 09 '13 at 15:06
  • 1
    Adam, since posting this question we have returned to SQL Azure. Largely due to the costs of running multiple VMs for high availability. We've profiled our DB and found that by adding a few indexes and batching up queries we have been able to dramatically improve performance. We're now looking at millisecond differences between our on-premise server and our solution in SQL Azure! Although I eventually solved the problem here it turned out not to be the optimum solution for us. – QFDev Mar 11 '13 at 09:53
  • For anyone else, my answer to the above question is that Azure SQL does not have SSRS. – Sentinel Feb 03 '16 at 13:11