SQL Azure Provider

Oct 13, 2011 at 6:46 AM

What would it take to use SQL rather than TableStorage?  I'm suprised this was done, considering Membership was SQL to start with, lots of work to use TableStorage instead. Working with SQL Azure is just so easy.

Why SQL?  Because TableStorage costs per transaction, whereas SQL transactions don't.  It is cheaper to run your site off SQL Azure.  Accessing membership info could potentially happen with every call to the website or services in an app in order to verify actions.  This will take a beating, lots of transactions, no?

Tables are better for binary data, large amounts of data that is scalable, and low transactions (cached, or not accessed as much), and cost per transaction even within the data center.  SQL Azure is better for smaller, high traffic non-binary data (such as user info), costs only in size, and does not cost per transaction within the data center.

See References:

http://msdn.microsoft.com/en-us/magazine/gg309178.aspx

http://www.paraleap.com/blog/post/Five-tips-for-creating-cost-effective-Windows-Azure-applications.aspx

Coordinator
Oct 13, 2011 at 4:05 PM
Edited Oct 13, 2011 at 4:15 PM

You could use SQL Azure if you like to, there is a guide and source code here if you want to do this http://msdn.microsoft.com/en-us/library/ms178587.aspx . If you want a less expensive session provider choise you should look into using the Azure AppFabric session state provider, see how here http://msdn.microsoft.com/en-us/library/windowsazure/gg185668.aspx

I made he session-state provider to work with Table Storage since

  • it is much more easily scalable than Azure SQL,
  • all other providers in the project uses the Table Storage and I did not want to add another back-end,
  • you only pay $1 USD per 1 million transaction, which in my mind is not that expensive, how much will it not cost you adding the complexity of another back-end storage and the time/cost of paying a developer to make the provider? The TCO may prove to you that it is not worth in in the end.

On the other hand, there are no issues using a SQL session-state provider or a AppFabric session-state provider with the rest of the providers in the AzureProviders solution.

Kind regards,

Inge.

Oct 13, 2011 at 8:58 PM

This is great info, Inge.

  • ATS is more scalable, but even with 40 million users, I'm not sure it would be too much for SQL Azure for membership data, well within its limits. No?
  • I totally get the logic on using ATS because its already in place for other requirements of AzureProviders.  :)
  • $1 per million could add up with an active app, bearing in mind that with each action of a user, it will not just be a session transaction, but also IIS/Page calls, or WCF service calls, queues, ATS requests, blobs, all in a single "action" by the user, this multiplies out.  I'm not sure dev time would be a big deal since Membership already exists as a SQL DB, but I have not yet evaluated your project to see what it would take to wire up.  Do you think it would be involved?

 App Fabric is a great suggestion for sessions.  I'll have to evaluate this vs. SQL and vs. ATS.  Good tip.

Bottom line:  the AzureProviders project is one of the most complete, well thought out, well documented projects I've seen on codeplex in a long time.  And it actually works without issue, porting, tweaking, without hassel.  Nice work! Congrats!

 

Coordinator
Oct 14, 2011 at 7:20 AM

Azure SQL uses a "light" version of T-SQL, so you would probably need to rewrite a session-state provider that was developed with Microsoft SQL Server in mind. There are also other limitations like indexes on Azure SQL that you need to take into consideration when converting a MS SQL session-state provider to Azure SQL. This project may help you find such migration issues http://sqlazuremw.codeplex.com/ .

Thank you,

Inge.

Oct 23, 2011 at 12:39 AM
Edited Oct 23, 2011 at 2:32 AM

A quick update for anyone following this:

I have AzureProviders Membership provider working against SQL Express on my dev enviro, very easy with just a change the web.config:

replace this: name="AzureMembershipProvider" type="WebPages.Bll.AzureMembershipProvider"

with this:      name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices"

Next, replace the custom profile and role providers with the default ASP.NET providers (see web.config of a new ASP.NET project that uses Membership). Be sure also to add these properties inside the <profile> section since AzureProviders uses these: 

      <properties>
        <add name="Gender" type="System.Int32" />
        <add name="PortraitBlobAddressUri" />
      </properties>

Now make sure a connection string to the memberhsip DB is of course added and correct, as follows:

  ...
  </appSettings>
  <connectionStrings>
    <add name="ApplicationServices" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=ASPNETMembership;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
  </connectionStrings>

I am looking at using SQL Azure JUST for Membership, but use ATS for everything else (sessions, etc) in AzureProviders.  This would be useful because I have other software that I use to administer the membership data that points to the same SQL membership data store. 

Note that on Azure, you must use the updated scripts in order for ASP.NET Membership to work on SQL Azure (see here or here).