Salesforce Lightning Connect to SQL Azure in 12 clicks!
Another blast from the past - "Name That Tune". Contestants compete to identify songs after just hearing the first few notes of the song. "I can name that tune in 5 notes!" "Well, I can name that tune in 3 notes!" "Well, go ahead and name that tune!"
Thanks to MBK1997 for this Youtube clip.
Scoping technical requirements can be very much like "Name That Tune". One solution may scoped to take a certain number of steps or "notes"; a different approach could solve the same problem with many fewer "notes". (Ok, I admit that this whole "Name That Tune" analogy is a stretch, but work with me!)
In March of this year, Sara Morgan, a blogger and author on both Force.com and .NET development, wrote an expertly-detailed tutorial on the Salesforce Developer Blog, explaining the necessary steps to connect to SQL Server Azure with Lightning Connect.
Sara stepped readers through everything from initial creation of an Azure database and table through exposing the data in a custom Salesforce tab. Very clear and understandable.
HOWEVER... steps #2 and #3 may be somewhat challenging for readers who don't have her .NET knowledge. Not comfortable or familiar with Microsoft's "stack" and things like ASP.NET, C#, Entity Frameworks, Visual Studio, etc.? For Mac users, requiring Visual Studio introduces some operational challenges. And yes, there are ways of overcoming them, but let's "think different". (Sorry, Apple!)
Is there another way to achieve the same result? One with fewer or simply different "notes"? One that has fewer Windows dependencies or requirements? As long as we end up with a network address of a service that can be used with Lightning Connect to expose the "Supplies" table in a compatible fashion...
And all you .NET fans - No hate mail, please! No slams here against Microsoft or .NET - just tackling a problem from a different angle.
So back to Sara's tutorial - assuming you've completed #1.
The Alternative Approach
2+. Create a data source at DataDirect Cloud for the SQL Azure database previously created. In additional to all the necessary address details and your Azure SQL Server user credentials, make sure to add "encryptionMethod=SSL" in the "Extended Options" field under the "Advanced" tab.
3+. Expose the "Supplies" table as an OData source by clicking on the OData tab, clicking on the "Configure Schema" button, and then selecting the "Supplies" table. (Add "Supplies" to both the "Singular" and "Plural" fields, or else you will end up with "Supplieses" when you synchronize your metadata!)
Now simply copy the service address from the OData panel for use in the Lightning Connect External Data Source definition.
Since the data source was named "SQLAzure", there is now a service endpoint or URI named:
https://service.datadirectcloud.com/api/odata/SQLAzure
Mission accomplished!
DataDirect Cloud is an alternate way of exposing SQL Server Azure (and many other data sources) as a Lightning-Connect-compatible endpoint by simply clicking a bunch of buttons and filling out some fields. Without requiring Visual Studio or writing any C# code or creating any WCF data services! (Before someone asks - I don't know exactly how many clicks it actually took me to connect.)
Now you can continue on through the rest of Sara's excellent tutorial, substituting the URI from DataDirect Cloud for the URI she created with the service she created and ran using Visual Studio 2013.
Two different approaches to the same problem! Try it out for yourself - free trials at DataDirect Cloud.
I look forward to your questions and comments.
Counting down to Dreamforce - come see us at either the Cloud Expo at booths W837 and W839 or in the Dev Zone!