Migrating to SQL Azure
So, I have myself a database in the cloud but it wasn't plain sailing migrating from an existing database.
I used SQL Server Management Studio to generate the whole schema as a script and attempted to run this on the cloud DB. As expected it didn't work.
Here's what I had to change:
- Removed all Database creation and settings
- Removed all Schema and Role creations
- Stripped out the following index and table options: PAD INDEX, ALLOW_ROW_LOCKS, SORT_IN_TEMPDB, ALLOW_PAGE_LOCKS, TEXTIMAGE_ON
- Removed all references to the file groups, ie [PRIMARY]
- Removed all instances of [dbo] referencing. This was actually to resolve an error with some aspnet db views containing three-part column names
- Added WITH (NOLOCK) to all table references that just had (NOLOCK), again aspnet db views.
- Populated the aspnet_schemaversions table as follows:
INSERT aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion)
VALUES ('common', 1, 1)
INSERT aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion)
VALUES ('health monitoring', 1, 1)
INSERT aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion)
VALUES ('membership', 1, 1)
INSERT aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion)
VALUES ('personalization', 1, 1)
INSERT aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion)
VALUES ('profile', 1, 1)
INSERT aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion)
VALUES ('role manager', 1, 1)
