Lightning Connect and SQL Integration Real Time Using Windows Azure Cloud Services-Part 1

Back Office Real Time Integration is need for any Enterprise Applications residing on salesforce platform .One of Key challenges of Salesforce Enterprise customers have been how to show data in salesforce 1 platform from back office or third party systems  without actually storing or consuming the storage space .

Salesforce External Object and Lightning Connect solves this .If you are not so familiar with External Objects ,I would recommend below links for further understanding

http://www.slideshare.net/developerforce/df13-using-external-objects-for-integration-mt
http://www.tquila.com/blog/2014/11/11/what-salesforce-lightning-connect-and-external-objects
http://www.tquila.com/blog/2014/11/17/setting-lightning-connect-and-your-first-external-object
http://reidcarlberg.com/2014/11/20/simple-salesforce-lightning-connect-example-external-objects/
https://blog.heroku.com/archives/2014/11/13/heroku-external-objects

All of the above blog links are really good and they explain Odata in particular .Lets take some time now in exploring whats Odata

OData

The Open Data Protocol (OData) is a data access protocol built on core protocols like HTTP and commonly accepted methodologies like REST for the web.

Standardization of OData has been going on for years (they are working on version  4), but it has suffered from a bit of a chicken-egg problem. Applications haven’t put a large priority on supporting the consumption of OData because there haven’t been enough OData providers, and data providers haven’t prioritized making their data available through OData because there haven't been enough consumers.

With the above theory and concepts now we are set to use windows azure cloud services to set up a SQL server and expose data and metadata in SQL as Odata service .we will use salesforce lightning connect to connect to this web service and see the data in SFDC .We will create Tab in SFDC and view the data .Sounds Interesting and exciting as well .Lets carry on !!!

Creating a SQL table and adding data using windows azure cloud services

I started my career with salesforce and cloud so I love to be working with only cloud platforms .windows azure is a microsoft cloud platform.Azure is the only major cloud platform ranked by Gartner as an industry leader for both infrastructure-as-a-service (IaaS) and platform-as-a-service (PaaS). This powerful combination of managed and unmanaged services lets you build, deploy, and manage applications any way you like for unmatched productivity.

I signed up for free trial with the help of below link

http://azure.microsoft.com/en-in/pricing/free-trial/

If you don't have windows account ,you will need to create one to complete the sign up.Please note it does ask for credit card info but you are not charged any amount from that.

Once you sign up the windows azure you will be able to explore and create various services .Below picture shows management portal of azure platform

The New button exists throughout the Windows Azure Management Portal and will enable you to provision new services within your Windows Azure subscription. This button is also context aware, so if your focus is currently within the SQL Database service, clicking this button will default the selections for creating a new Windows Azure SQL Database.

Click on the New button. You should now be presented with a menu to create a new Windows Azure SQL Database. Notice that the menu has a list of the Windows Azure service categories on the left, followed by the services within the selected category, and options for creating your new service in the third column. ClickCustom Create to begin setting up your Windows Azure SQL Database.

You should now see the New SQL Database – Custom Create wizard.

In the Name text box enter Websites.

In the Subscription drop down, select the Windows Azure subscription where you would like to create your SQL Database.

1)In the Server drop down, select the Windows Azure SQL Database server that you would like to use for your new database. If you have created a Windows Azure SQL Database server previously, you can select that server, otherwise, select New SQL database server.

2)The remaining fields you can leave set to their defaults.

3)Click the checkmark in the lower right corner to advance to the next step in the wizard.

4)If you chose to create a New SQL database server, you should now see the SQL Database server settings screen.

5)In the Login Name text box, enter the user name you would like to use to login to your SQL Database.

6)In the Login Password and Confirm Password text boxes, enter the password you would like to use to login to your SQL Database.

7)In the Region drop down, select the Windows Azure data center where you would like your new SQL Database server provisioned. For optimal latency and cost of data transfer between data centers, it is recommended that you provision all of the services for an application that are dependent on one another in the same data center.

8)Lastly, leave the Allow Windows Azure Services to Access The Server check box checked. This will allow other services in Windows Azure, like the Windows Azure Web Site that we will create in future steps to access your SQL Database.

After you have provided all of the details for your new SQL Database server, click the checkmark in the lower right hand corner to finish the wizard and create your database.

 You will see progress in the toolbar at the bottom, and once your database has been created, you should see a success message like the following. 

Click the OK button in the notification bar to dismiss the message.

Once your database has been successfully created, go ahead and click on the Name in the list of databases, which will display information about your database and additional options and configuration.

The next thing we need to do is configure access to your SQL Database. By default, Windows Azure SQL Database firewalls all IP address from connecting to your server. As a result, you need to grant access to connect and manage your server. To grant access to your IP, click on Set up Windows Azure firewall rules for this IP address.

Your database is now ready! So now you are ready to add some tables and data.

Creating Tables in Your SQL Database

While on the home screen of your SQL Database, click on the Manage button in the toolbar at the bottom.

This will display the SQL Database Management Portal login screen. Enter the username and password that you used when creating your SQL Database and click the Log on button.

Select your newly created Table1 database and click on the Design button in the menu on the left.

You should now see options to design the schema of your database including Tables, Views and Stored Procedures.

Click on the New table button in the middle of the screen.

1)Enter Table1 in the Table Name text box.

2)On the ID column that was added by default, check the Is Identity? check box.

3)On the Column1 column that was added by default, enter FirstName under Column, leave the defaults forType set to nvarchar and the Length to 50.

4)On the Column2 column that was added by default, enter LastName under Column, leave the default for Typeset to nvarchar and set the Length to 50.

Click the Save button in the toolbar at the top to save the Person table.

Adding Data to Your Database

Now that you have the structure of our database setup, it is time to enter data into your tables. The same web-based SQL Database Management Portal that you used to setup the structure and schema of your database, you can use to add data to your database.

To begin, you will add data as shown

Now that your table is selected, click on the Data tab at the top and a list of rows from the Table1 table will be displayed. At this point, the list of rows should be empty, because you should have an empty table.

Click the Add row button to add a new row to the Person table.

The ID column is setup as an identity column and it will get automatically incremented and assigned when a record is added, which means you should leave this column blank.

Enter some text in the FirstName column.

Enter some text in the LastName column.

Repeat these steps two more times to add fields to the  table. 

Once finished adding rows to the table, click the Save button in the toolbar at the top to save your new rows to the table.

Now that you have fields  in your table named websites, you need to add Items to their Shopping Lists.

Click on the Item table in the menu on the left.

Click on the Data tab at the top and an empty list of rows from the Item table will be displayed.

Click the Add row button to add a new row to the Item table.

Once finished adding rows to the Item table, click the Save button in the toolbar at the top to save your new rows to the table.

Congratulations! You have now built a database and stored data in the cloud!

Now that you have people in your Person table and shopping list items in your Item table, you are done setting up your database and can now close the SQL Database Management Portal and move on to building your web service and application.

Summary

If you followed along with this article, you just:

1. Created a database in the Cloud using Windows Azure SQL Database

2. Set Up a table and defined your database’s schema

3. Added data to your database using the web-based SQL Database Management Portal

Next blog will demonstrate on how to expose this SQL table as an ATOM feed and then use lightning connect to display data .