When I was first working through getting DotNetNuke to run on Azure, I found a number of issues with the core SQL Scripts that made it impossible to run on SQL Azure. These generally were divided into 4 categories of problems:
- Table hints
- String Literal Column Aliases
- Unsupported features
- Indexes
Table Hints
DotNetNuke scripts contained a number of places where table hints were used without a WITH clause. This often looked like:
SELECT * FROM sometable (nolock) WHERE somecondition = true
These instances were changed to add the WITH clause:
SELECT * FROM sometable WITH (nolock) WHERE somecondition = true
String Literal Aliases
There were a number of areas where column aliases used string literals which are not supported in SQL Azure:
SELECT 'ColumnAlias' = column FROM sometable WHERE somecondition = true
Instead, these scripts should define column aliases using the AS keyword:
SELECT column AS 'ColumnAlias' FROM sometable WHERE somecondition = true
Unsupported Features
There are a number of features which are not supported in SQL Azure. Often this is because you should not be worrying about the specifics of how your database is stored in the cloud and because you don't have permissions to many system tables or stored procedures. In the core framework this meant removing any reference to the PRIMARY filegroup and to replication hints (e.g. constraints marked as "NOT FOR REPLICATION").
I did not run into any errors when migrating columns since we don't use ROWGUIDCOL and instead inject a GUID generated by the data layer of the application or by using a DEFAULT CONSTRAINT that sets the value to newid() (see this forum post on MSDN).
Indexes
As you rightly pointed out, all tables must have a clustered index. This is easy to fix by ensuring that either the primary key or another table index is marked as CLUSTERED.
If you want to correct issues with a specific module, I recommend creating a single roll-up script for the module and then modifying that script to be SQL Azure compatible. This will prevent the need to modify several scripts per module. Following this approach you should be able to quickly test any script by executing it directly against a SQL Azure sandbox or by using the SQL Azure Migration Wizard to test the script.