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!
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.
I had problems with the ASP.NET membership provider tables (non clustered indexes) and missing the
"WITH (NOLOCK)" statement on the stored procedure
The migration tool found here fixed the problems and exported the database to Azure.
It is a nice wizard that connects to your existing SQL Server and migrate the selected database to Azure.
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:
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.
I hope this helps.