4

We have a database of ASP.net sqlMembershipProvider based users on an Azure SQL database. It has become apparent that the out of the box sqlMembershipProvider 4.0 does not have the required retry logic for Azure SQL connections which can drop out due to throttling or can expire.

It is possible to implement our own membership provider that has this functionality but it would have to be exactly the same database interactions as the standard sqlMembershipProvider 4.0 in order to work with the existing users in our database. However for that it would require looking into the source of the original sqlMembershipProvider 4.0, this code has not been released into the open since version 2.0 so my question is:

What is the lowest effort way to get retry logic into the sqlMembershipProvider? And or would that be reflecting the code of sqlMembershipProvider 4.0 in System.web.security.sqlMembershipProvider and creating a custom membershipProvider that has the same functionality as the sqlMembershipProvider but uses retry logic such as that of Microsofts TransientFaultHandling ReliableSqlConnection? Would it be legal to do such a thing (reflecting and creating similar code but with added functionality) given Microsoft's license for asp.net libraries eg. System.Web?

Finn Krewer
  • 63
  • 5

1 Answers1

3

I think the best solution today would be to inherit from the providers available in System.Web.Providers (the ASP.NET Universal Providers assembly) and simply inject the retry policy for each public method:

public class MyDefaultProfileProvider : System.Web.Providers.DefaultProfileProvider
{
    private RetryPolicy retryPolicy;

    public MyDefaultProfileProvider()
    {
        var retryStrategy = new Incremental(5, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(2));
        this.retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(retryStrategy);
        retryPolicy.Retrying += retryPolicy_Retrying;
    }

    private void retryPolicy_Retrying(object sender, RetryingEventArgs e)
    {
        // Log, whatever...
    }

    public override System.Web.Profile.ProfileInfoCollection GetAllProfiles(System.Web.Profile.ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords)
    {
        int tempTotalRecords = 0;
        var profiles = retryPolicy.ExecuteAction(() =>
        {
            return base.GetAllProfiles(authenticationOption, pageIndex, pageSize, out tempTotalRecords);
        });
        totalRecords = tempTotalRecords;
        return profiles;
    }

    ...
}

To know if it would be legal to reuse the code, you should look at the license.

Sandrino Di Mattia
  • 24,739
  • 2
  • 60
  • 65
  • That is a great idea, one thing, is there any reason this should not work with the older sqlProfileProvider or say sqlMembershipProvider from .net 4.0 ? A different SO question [link](http://stackoverflow.com/questions/6940732/what-do-the-asp-net-universal-providers-enable-that-the-default-sql-providers-do) mentioned the schema is different and that means overhead to switch existing membership data over. – Finn Krewer Aug 19 '12 at 15:25
  • This KB explains more or less why the normal provider is not compatible with SQL Azure: http://support.microsoft.com/kb/2006191. Besides that you should look at Troy's blog, he also talks about new security features and a simplified schema in the new provider: http://www.troyhunt.com/2012/07/stronger-password-hashing-in-net-with.html. Since the hashing of password is different, I wonder if it's even possible to migrate your existing data to the new providers. – Sandrino Di Mattia Aug 19 '12 at 17:33
  • PS: You might want to look at the "Updated ASP.NET scripts for use with SQL Azure" (http://archive.msdn.microsoft.com/KB2006191). This SHOULD work correctly with the classc SqlMembershipProvider. – Sandrino Di Mattia Aug 19 '12 at 17:35
  • I definitely see the advantage of the new providers, also I will attempt to switch over to them. but I had azure sql full up with users in the 4.0 version and apart from retry it worked (I did it last year using the scripts you pointed to). So I went ahead and implemented your method on the old Profile, Membership and Role Provider and it worked great, tested and tested under a good load. Thank you! – Finn Krewer Aug 19 '12 at 18:49