25 February 2011

aspnet_regsql.exe doesn't work with SQL Azure

If you want to create the database structure for using Forms Authentication in your Windows Azure hosted ASP.Net application, you'll probably have noticed that running aspnet_regsql.exe on your Azure database doesn't work.
I have tried merging a local database using Red Gate SQL Compare 9 (you can of course also use the SQL Azure Migration Wizard), but that also gave me some errors regarding some elements in the resulting update statement that were not supported in SQL Azure.
I have looked into these errors and found that it was relatively easy to fix these. If you take a look at the Stored Procedure called aspnet_Membership_GetNumberOfUsersOnline in a "regular" SQL server, you'll see that it contains a couple of (NOLOCK) statements in it's SELECT statement.