I
recently published my first article for DeveloperForce about Accessing a SQL Server Azure Database with Lightning Connect. I had a lot of fun creating the database and application, but I did encounter several gotchas as I was working with this fairly new technology. This posting lists some of the issues I uncovered while working with SQL Server Azure and what you can do to avoid the problems I encountered.
- Adding firewall rules
- DSL will likely change your IP address, so you will have to set this everyday most likely (unless you are fortunate enough to have a static IP address). You do this through the Set up Windows Azure firewall rules for this IP address link on the Quick Start page.
- Connection may get lost occasionally, even though your IP address has not changed. If you start getting weird problems trying to publish, you can go back to Azure portal and click the Setup Windows Azure Firewall link again. Even though it is already added, doing this somehow refreshes the connection and will resolve your problems.
- Connection String user needs to use an unrestricted DB user that has access to the Master DB. Refer to this article for more information http://www.odata.org/blog/got-sql-azure-then-youve-got-odata/. Real-world applications exposing their sensitive data via oData will need to take this into consideration and try to use a more restricted user.
- If you try to use the latest version of the Entity Framework with web services, you will have to install some additional NuGet packages as outlined in the article. See article for more about specifics on that.
- Depending on your internet connection, the initial publish may take a long time to complete and if you try to access the hosted website before then, you will get errors (probably that the resource cannot be found). Make sure the output in Visual Studio shows that the publish was complete and with no errors (such as a timeout error, which happened to me several times) before attempting to see the results online. If successful, a browser window should be spawned and you will be directed to the home page of your new hosted website.
- Unfortunatley, Lightning Connect does not yet (and notice the hopeful word yet) automatically create relationships when you create your external data source. You will have to manually create these yourselves. It gets even more complicated in that you MUST edit the existing synced fields to create these relationships and DO NOT create new fields instead. If you do create new fields then you will encounter errors such as the ones I got and reported about here. The error had to do with how I defined the external lookup relationship. I went to the external object definition page and clicked New to create a new field and then define the relationship. In order to avoid the error, you will need to instead edit one of the existing SYNCED fields and specifically Change the Field Type so that it is of the External Lookup Relationship type and that it points to the correct external table object. Then, you can perform your SOQL query and get no errors.
Let me know about your experiences with Lightning Connect. Despite all the above issues, I think it is a FANTASTIC new development for integration and I am excited to see it develop even further.
saramorgan.net 