Migrating ASP.NET Membership Database to SQL Azure

Question

I am trying to migrate a database from SQL Server 2008 to SQL Azure. The database that I am attempting to migrate includes the ASP.NET Membership database (http://www.asp.net/web-forms/tutorials/moving-to-aspnet-20/membership). This database works fine when I run it in my SQL Server 2008 database. However, when I try to authenticate a user against SQL Azure, I receive an error that says:

"Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again."

However, I'm not sure what to do. When I try to update the index on the aspnet_Applications table, I receive a foreign key problem. I am using the following in an attempt to migrate my non-clustered index to a clustered index:

ALTER TABLE aspnet_Applications
DROP CONSTRAINT PK__aspnet_A__SOMEID

ALTER TABLE aspnet_Applications
ADD CONSTRAINT PK__aspnet_A__SOMEID PRIMARY KEY CLUSTERED(ApplicationId)

Can someone please help me overcome this issue? Thank you!

Answer 1

I had problems with the ASP.NET membership provider tables (non clustered indexes) and missing the "WITH (NOLOCK)" statement on the stored procedure aspnet_Membership_GetNumberOfUsersOnline etc.

The migration tool found here fixed the problems and exported the database to Azure.

http://sqlazuremw.codeplex.com/

It is a nice wizard that connects to your existing SQL Server and migrate the selected database to Azure.