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!

upgrade your server to SQL DB V12 which support heaps, and it is much closer to SQL stand alone compared to V11. — Sirisha Chamarthi
– Sirisha Chamarthi, Commented Sep 5, 2015 at 5:42

Shaun Luttin · Accepted Answer · 2015-09-04 19:06:29Z

5

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.

edited Sep 4, 2015 at 19:06

Shaun Luttin

143k93 gold badges443 silver badges495 bronze badges

answered Nov 22, 2013 at 15:20

AH.

3,1912 gold badges31 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Noah Heldman Over a year ago

This SQL Azure Migration Wizard made this so easy, and fixed all of the issues with aspnet_Membership seamlessly. Great stuff.

Shaun Luttin Over a year ago

This worked for me in Windows 10 by downloading the v5.15.6 Release.

Jonathan McIntire · Accepted Answer · 2012-04-13 17:21:04Z

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

answered Apr 13, 2012 at 17:21

Jonathan McIntire

2,67525 silver badges26 bronze badges

2 Comments

ckarst Over a year ago

SQL Azure now supports heaps on V12 servers, so this should no longer be an issue. msdn.microsoft.com/library/…

Andy Over a year ago

second link now broken but the blog can be found here azure.microsoft.com/en-us/blog/…

MUG4N · Accepted Answer · 2012-04-13 16:14:08Z

1

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.

answered Apr 13, 2012 at 16:14

MUG4N

19.8k11 gold badges58 silver badges86 bronze badges

3 Comments

Quibblesome Over a year ago

thanks, that really helped me where the chosen answer did not.

MUG4N Over a year ago

The Membership provider is deprecated therefore the script won't work any more. But there is a new Membership implementation. Take a look at this: aaron-hoffman.blogspot.de/2013_02_01_archive.html

crokusek Over a year ago

@MUG4N link dead also.

Collectives™ on Stack Overflow

Migrating ASP.NET Membership Database to SQL Azure

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related