Bird Stream

Entrepreneurship, development and (sometimes) cycling, often simultaneously, mainly in Nottingham

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)

Comments