Migrating ASP.NET Membership Database to SQL Azure

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!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

Method 2

If you can, try to use the ASP.NET Universal Providers For SqlExpress 1.1 (I wish they’d change the name as it is a bit of a misnomer). This is the officially supported method of enabling the SQL membership, role and session providers across both SQL Azure and standalone SQL Server.

Here are a couple of other links for reference:

Scott Hanselman – Introducing System.Web.Providers

SQL Azure Team Blog – Using SQL Azure for Session State

Method 3

SQL Azure is not the same as SQL Server because not all features are supported. There is a special SQL Azure Membership Script to create the database.

See here: http://support.microsoft.com/kb/2006191/de

I hope this helps.

Greetings.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x