Step One: Create a SQL Server Azure Database (Post 1 of a 4 part series)

At this years annual Dreamforce conference, I had the honor of speaking about SQL Server Azure Database MeSpeaking2relationships using Lightning Connect. The talk was based on an article I wrote for DeveloperForce earlier this year titled, “Accessing a SQL Server Azure Database using Lightning Connect“. The original DeveloperForce article detailed the steps for creating a WCF Data service that exposed a single table SQL Server Azure database as OData.

This post will be the beginning of a 4 part series in which I will step you through what is necessary to expose and access a multi-table SQL Server Azure database with built-in relationships. These steps were covered at a high-level in my talk , but in this series, I will go through each step in greater detail. The steps will consist of the following:

1.) Create a SQL Server Azure Database (covered in this post)

2.) Create ASP.NET application to expose data as OData

3.) Publish Web Application to Windows Azure

4.) Create External Data Source and Define Relationships in Salesforce

TIP: Click here to access the code from this post, along with the Powerpoint slides from my talk at Dreamforce.

Step One: Create a SQL Server Azure Database

You will need a Microsoft Azure account to create a SQL Server Azure database, but you can sign-up for a free one-month trial through the following link. You will also need a way to build the database. If you do not have access to Microsoft SQL Server Management Studio, then you can use the built-in SQL Server tools that come with the free Community version of Visual Studio.

But the first step is just to create the database and the simplest way to do that is to go to the SQL Databases tab in Windows Azure Portal and click the big plus sign next the word New in the bottom right-hand corner of the Portal. Click Custom Create and enter a Database name along with a secure login name and password.

After the database server has been allocated, you can click here to download the latest version of Visual Studio, 2015. Keep in mind that it will take quite a long time to download and install. If you don’t want to wait, you can use an earlier version of Visual Studio, such as Visual Studio 2012 or 2013 (with latest update), but just NOT Visual Studio 2010.

Once installed, you can access it from the Windows Azure Portal by clicking the Open in Visual Studio link at the bottom of the database quick start page (see image below).

PetSuppliesPortal

When you click this link, you should be prompted to add your local IP address to the Windows Azure firewall rules. Go ahead and click “Yes”.

One thing to be aware of here is that unless you have a static IP address, you will need to do this every time the address changes (which for some DHCP environments could be daily). This is done by returning to the quick start page and clicking the Set up Windows Azure firewall rules for this IP address link under Design your SQL Database.

TIP: If you know the allowable range of IP addresses you might be assigned, you can configure a range of IP addresses in the Azure Management Portal by going to SQL Databases and selecting the link for the server that your database resides on. From there, select the Configure tab to enter a new rule with starting and ending IP address.

If you are using a browser such as Chrome, you may also be prompted with an external protocol request asking whether you want to launch the application. Go ahead and click Launch Application. Doing so will launch the Visual Studio application and open up SQL Server Object Explorer. The first time you connect, you should be prompted to enter the login name and password you specified when creating the database in Windows Azure Portal.

Once connected, you can use the SQL Server Object Explorer to add tables and data to your new SQL Azure database. If you prefer the point and click approach, you could create a new table for your database by expanding the Databases node, followed by your specific database and then right-clicking Tables and clicking Add New Table (see image below).

SQLObjectExplorerNewTable

If you are more comfortable working with SQL script, you could right-click on your database node and select New Query to bring up a window where you can type or paste in SQL script directly. Once entered, the script can be executed against your SQL Server Azure instance by clicking SQL | Execute.

Click here to see the second post in this four part series.

Come see me talk about SQL Server and Lightning Connect at Dreamforce 2015

LightningI am happy to say that I will be presenting again at this years annual Dreamforce, Wednesday September 16, 2015 at 9:15am. The session is titled, “Exploring SQL Server Azure Database Relationships Using Lightning Connect, and I should be in the Mobile Theatre in Moscone West.

Lightning Connect is what I consider to be a game changer in the world of Integration. It allows you to easily (and the key word here is easily) setup a real-time connection between your external data and Salesforce.

After setting it up, you can query external data from within Salesforce just like you would any other sObject. No exhausting setup of some overly complicated publishing/subscriber model (which is something I myself have unfortunately been involved with). No, you just publish your data as oData, setup an external data source in Salesforce, define some external relationships, and off you go.

Now, as with any new technology like this, there are a few gotchas to be considered. I will be covering some of these, as they pertain to setting up a connection to communicate with a SQL Server Relational database hosted on Windows Azure. The key takeaways I hope to communicate during this session will be:

  • What you need to know to work with Visual Studio and SQL Server Azure
  • How quick and easy it is to setup a connection and query data one-way in real time
  • What you need to know to define relationships in Salesforce

Hope to see you there!

Gotchas encountered when working with Lightning Connect and SQL Server Azure Databases

ILightning 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.