Tuesday, September 11, 2012

Managing database connections in Azure Web Sites

This topic is not specific to git publishing, but it’s particularly useful in that scenario

In its first release, the Azure portal had a couple of limitations relating to database connections:

  • The database name had to match the connection string, which was quirky
  • You could not add external connection strings

The good news is that both limitations have now been removed, as you’ll see here.

 

Using a SQL Azure database associated with the site

Let’s say you’d like to publish the awesome NuGet Gallery to an Azure Web Site. When you create the site in Azure, choose the ‘Create with database’ option:

image

You will see a dialog that looks like this:

image

Let’s assume that you don’t have a database yet, and you’d like one to be created for you. All you have to do here is give Azure your connection string name (highlighted above).

So where does this ‘NuGetGallery’ string come from? It’s simply the name of the connection string from the app’s web.config:

image

This way, you don’t need to change your sources to point to the SQL Azure database. You instead rely on Azure to use the right connection string at runtime.

After the following step, you can complete the wizard by either creating a new DB Server or use an existing one. Note that the database itself can be named anything you like (or keep the random name), since it is now decoupled from the connection string name.

At this point, you can just ‘git push azure master’ the NuGet Gallery sources, and your site is up and running with no further configuration!

Now if you go into the Configure tab for your site, you’ll see your associated connection string:

image

Note that it’s hidden by default, but you can choose to display it if you need it (e.g. if you want to connect via SQL management studio). You can even edit it if you want to tweak it!

 

Working with external connection strings

In the scenario above, we were using a database that Azure created for us along with the site. In some cases, you will instead need to work with an existing database, which may or may not be hosted on Azure.

In that scenario, you’d create your site without any databases. Instead, you can manually add the connection string in the Configure tab, e.g.

image

Note: don’t forget to click the Save button at the bottom of the page when you’re done!

Note that as before, we’re naming the connection string after the one in web.config. The only difference is that the value now comes from you instead of coming from Azure.

12 comments:

  1. Will the connection strings you configure here persist even if a web.config is published with a different one? Or will the web.config value override the one configured using the azure portal? For instance, if the web.config in git has a development database connection string will azure keep the one configured so that when it publishes from git the development database gets overridden by a live database?

    If published web.config overrides portal configured connection string, is there a way to get git publishing to apply web.config transforms so that we can just remove connection string in web.release.config?

    ReplyDelete
  2. @Alex: The connection string configured in Azure always overrides what's in web.config. Note that this is done via some runtime magic, and not by physically modifying web.config. So if you look at your web.config (e.g. via FTP), you won't see any Azure modifications.

    ReplyDelete
  3. Great :). Another question real quick if you don't mind. I moved a private repository to github and made it public so I removed the connection string from web.config intending to add it in the azure portal as you suggest. The connection string looked like this:



    That connection string works fine when published straight to azure with that in the web.config, however when I published to azure with git and tried to set the string in the portal I still get an error as if it couldn't be found. You can see it here:

    http://codetunnel.azurewebsites.net

    I made sure the string was named CTContext just like the web.config one. I copied the value from connectionString into the connection string field. First I tried it exactly as it is in web.config and that didn't work. Next I tried changing the " to actual " in the field No change. I even tried removing the EF metadata stuff and only including the stuff between the ". That still didn't change anything. What am I doing wrong?

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. @Alex, would you mind posting this to the forum (http://social.msdn.microsoft.com/Forums/en-US/azuregit/threads) instead? It may take some back and forth to investigate, and blog post comments don't work too well for that. Thanks!

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Sorry about that.

    Here it is: http://social.msdn.microsoft.com/Forums/en-US/azuregit/thread/3e881238-909e-4546-b03a-d3a04ac0485e

    ReplyDelete
  8. Hey David, on the topic of SQL within Azure I have a question. Does it support SQL Server aliases? Can I create an alias with the IP of our server and use it within connection string?

    Let me explain how we have it now. We have an intranet based ERP SQL server that is opened for queries to our public web hosting (just to this server, IP based security done on firewall). The website makes seamless connections to our intranet based SQL server. Is this kind of scenario possible?

    If aliases are not an option is it possible to just use an IP?

    If any of the options was possible, I could see myself migrating one of the sites to Azure Web Sites.

    ReplyDelete
  9. @wh1l3tru3: sorry, I'm not a SQL Azure expert. Here is the best place for these questions: http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/threads. Thanks!

    ReplyDelete
  10. Sorry for posting this question here but I couldn't find a way of contacting you anywhere else in your blog.

    Help on ASP.Net Dynamic Data Contextual Help

    Would you mind giving me a little advise in ASP.Net Dynamic Data, please?

    I'd like to add some kind of help in my application, let's say that I'd see that help put in the upper part of the GridView but I'd like to have the help being shown depending on which entity the application might be currently working with.

    For instance, if I were working with Northwind's Customers so I'd like to give some kind of orientation about how that data entry form should work from the point of view of the end user so he/she could knew how to do the data CRUDing.

    Same applies to all other entities BUT as you know, dynamic Data works based on Page Templates so there is only one template for all entities in my database so I wouldn't expect to have as many pages as entities I might have in my model just to have a different help for every page

    Does that makes sense at all ... ?

    Carlos N. Porras
    (El Salvador)

    ReplyDelete
  11. @carlos: the right place for all DD questions is http://forums.asp.net/1145.aspx. Note that this blog post does not relate to DD, it's about Azure Web Sites. Thanks.

    ReplyDelete
    Replies
    1. thank you and I apologize. I already did in forum 1145 but it seems that there is no much feedback there ... so I dare asking you directly because you were an expert in that field.

      Is there any other place in which I could post a question directly to you regarding Dynamic Data?


      Carlos N. Porras
      (El Salvador)

      Delete