The commoditization of technology has reached its pinnacle with the advent of the recent paradigm of Cloud Computing. Infosys Cloud Computing blog is a platform to exchange thoughts, ideas and opinions with Infosys experts on Cloud Computing

« Basic Azure enabling guide | Main | Big Data and Cloud Computing »

Step by step approach to expose on-premise database using Azure infrastructure

With the advancement of Azure cloud infrastructure, there arise many heterogeneous requirements which are of type of system(s) having combination of on-cloud and on-premise components. Specially from the on-premise database point of view for certain scenario, this blog series is intended to explain the options, steps, concerns and benefits of different approaches.

Overview

Lets us consider an existing on-premise scenario where there is a system consisting of database(s) server(s) and few WCF services in application server(s). And the requirement is to move these services to the Azure infrastructure (probably also to support some prospective external service consumer and side by side to leverage the benefits of cloud infrastructure like low cost of ownership, low cost of maintenance, etc) and keep the database(s) on-premise (i.e. within corporate firewall may be because of some constraint/policy of the company) and expose it to the said WCF services which are in Azure deployed as web role. The option may not be the only one but certainly the among the quick-to-adopt and the best ones with some benefits.

Later in the post, we will understand the different obligations/benefit that one should take into consideration before making the decision for this approach.

Option 1- Expose the on-premise database to the consumer (WCF service) in the Azure webrole and over TDS using Azure-connect.

Steps to be followed

To keep the backend database on-premise and expose it over "TDS" protocol so that once the concerned WCF services are moved to Azure webroles, the code logic to access the database is not required to be changed (i.e. using SQL client API), we need to create a kind of "local virtual network" with IP-sec protected connections between computers (on-premise database server) and virtual machines (hosting the WCF Azure web role). For this to achieve, Azure-connect could be leveraged.

1.      Login to the Azure management portal

      2. Select "Connect" icon from the top panel:

july_1_1.png

3.      Select the subscription under which the said virtual network is to be created and where the WCF Azure web role will be deployed:

july_1_2.png

4.      Click the "Install Local Endpoint" icon from the top and then copy the URL provided from the pop-up:

july_1_3.png

 5.    Now go to the physical machine where the database is present, try browsing this URL and install the Azure-connect local end point:

july_1_4.png 

6.      Once the end point is installed in the machine, in the task tray one icon will be shown but with message "limited connectivity" because the virtual network is yet not created

july_1_5.png

7.      Now in the same SQL server machine, in the firewall open, the TCP port 1433 for inbound request to be allowed:

 

july_1_6.png

8.      For the SQL instance in concern, allow remote connection:

 

july_1_7.png

9.      Now we have to configure the webrole to be used to host the WCF service to connect to the same virtual network under the concerned subscription. To do this in the Azure management portal, select the "Get Activation Token" from the top panel and the copy the token provided:

 

july_1_8.png

10.      In the Visual studio open the solution having the WCF project and add a blank Azure Cloud project (make sure to install the Azure tools for visual studio):

 july_1_9.png

11.      In the newly added blank project go to the "Roles", right click on it, select "Add" and select "Web Role Project in solution..." menu:

 july_1_10.png

12.      This will list the WCF project, select it and click "OK". This will add a role entry in the initially added blank Azure cloud project.

13.      Right click on the newly added entry in the "Roles" folder select properties menu to be shown. Then go to the "Virtual Network" tab, check the "Activate windows Azure connect" and provide the activation token copied in the step-9 above:

 july_1_11.png

14.      In the WCF project, modify the database connection string to include the user name and password for SQL authentication as windows integrated authentication will not work in Azure-connect network. So make sure to enable mixed-mode authentication in the SQL instance in concern.

15.      Build the cloud project and deploy it to Azure. Once the role instance(s) is (are) started, in the Azure management portal (for Azure connect), for the concerned subscription, select "Activated endpoints". It will list the recently deployed web role (s) and the machine(s) name in which the local end point is installed (step- 5):

 july_1_12.png

16.      Now we need to create a group and then only the interconnection between the different role instances and machines can be established. Select the "Groups and Roles" menu and click on the "Create Group" icon from the top panel:

 july_1_13.png

17.   In the "Create a New Endpoint Group" window "Add" the different machines (having the end point installed) from the first section and the web roles from the second section:

 july_1_14.png

18.   Once the group is created successfully, in the machine(s) having the Azure-connect end point installed, in the task tray, the azure-connect icon will change to:

july_1_15.png In case status is not shown as connected, right click on it and select to refresh the policy:

july_1_16.png

19.      Once the connection is successfully established try consuming the WCF service deployed in any client and make the required service operation call.

20.      Inter-connection could also be verified by enabling remote connection in the web roles VM as well as the on-premise machine having the database and trying to "ping" each other. Make sure to run the following command (in the elevated command prompt) in each of the machines (om-premise machine having the SQL server and VM having the WCF web role) before trying to ping:

netsh advfirewall firewall add rule name="ICMPv6" dir=in action=allow enable=yes protocol=icmpv6

Points to be considered

The below few paragraphs will try to highlight some points that may be considered while making the right decision:

1.      We need to have the admin right and direct access to the physical machine(s) where the database(s) is(are) residing (third party or internal)  to install Azure connect end point which is needed so that these machines could be linked in the virtual network which is also having the VM hosting the wcf web-role.

2.      Need to open the TCP port 1433 for inbound requests in the firewall of the machines having the databases. For this also we need to have the admin right and direct access to the physical machine(s).

3.      The connection string being used by the wcf service (in the azure web role) to connect to the on-premise database needs to have user name and password defined as windows integrated authentication will not be supported. Hence need to encrypt the concerned connection string properly.

4.      Since the database is on-premise and the consumer of the database i.e. wcf service is in azure role, the data is sent over the wire and hence it needs to be encrypted or setup needs to be in place to avoid illegitimate data access/purging.

5.      An extra latency will be incorporated now as the database server and the app-server hosting the wcf service are not on the same physical network.

6.      If the service to be moved depends upon on-premise resources other than database like Active-Directory, SMTP server, etc then we need to look for the corresponding substitute (or needed to enable these as well to connect to Azure-connect virtual network) which could then be accessible from azure web role.

Benefit

One may use/retain (in case of existing set-up) the simple SQL client API based database access code logic to connect to the on-premise SQL server from consumer outside the corporate firewall.

Next post in the series

Option 2 - Expose the on-premise database to the consumer (WCF service) in the Azure webrole (or any external consumer) and over HTTP(s) using Azure appfabric Service Bus... 

For the complete next post, please refer to this.

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.