SQL Azure Migration Wizard - first impression

When generating a script from an on-premise source database to be executed against a SQL Azure in the cloud, there are numerous issues that have to be changed either in the source database or in the generated script. Most of these issues are mentioned in the discussions group

René had the kindness to point me to a tool on CodePlex called the “SQL Azure Migration Wizard”. It’s very early alpha, but when commercial tools like RedGate SQL Compare not supporting SQL Azure (yet?), this migration wizard is a decent second best. You can find the tool here: http://sqlazuremw.codeplex.com/

[Updated 18-02-2010] RedGate has released a private build of SQL Compare that I'm currently testing out. Wanna get you hands on the bits? Apply for it here.

I tried to move a small database (50 tables, 400 stored procedures and some ASP.NET membership tables/sprocs/views). My own database migrated very smoothely over to SQL Azure, with the exception of non-us collations.

In my create table scripts I have command like this “…nvarchar(x) NOT NULL COLLATE Danish_Norwegian_CI_AS”. When SQL Azure is officially released, it’s expected to support non-us collations, but not at the column level. For now trying to set non-us collations will result in the following error when using string functions in stored procedures: “Cannot resolve collation conflict between 'Latin1_General_CS_AS' and ' Danish_Norwegian_CI_AS ' in equal to operation”. For now, until SQL Azure is released, I solved this by adding the following to the TableStatement element in the “NotSupportedByAzureFile.Config” file: NotSupported Text="\sCOLLATE Danish_Norwegian_CI_AS\s" ReplaceWith=" " SeverityLevel="0" ReplaceString="true" DisplayWarning="true" WarningMessage="Removed COLLATE Danish_Norwegian_CI_AS to use database default collation"
This text replacement is executed on the generated script before the script is executed against SQL Azure.

Here are some other issues I ran into:
• Tables worked fine to a new empty database, but to an existing database I got several errors because a column default allready existed.
• Table hints with the WITH keyword is no longer supported. In the ASP.NET membership stored procedures there a statemets like this: “…FROM dbo.aspnet_Users u(NOLOCK)” and this must be changed to “…FROM dbo.aspnet_Users u WITH (NOLOCK)”. Sometimes the source database has the WITH keyword and sometimes its missing, the table alias varies and there’s 15 or so different table hints to look for. Since I’m not the sharpest RegEx head, I fixed this in the source database instead of trying to invent a fancy regex to fix the generated script.
• Data types ‘image’ and ‘ntext’ was replaced for tables, but not for stored procedures and since there’s no specific config section for stored procedures, I use generic TSQL section the following to the TSQL section in the “NotSupportedByAzureFile.Config” file:
NotSupported Text="\sntext" ReplaceWith=" nvarchar(max)" SeverityLevel="0" ReplaceString="true" DisplayWarning="true" WarningMessage="Replaced column type [ntext] with [nvarchar](max)"
NotSupported Text="\simage" ReplaceWith=" varbinary(max)" SeverityLevel="0" ReplaceString="true" DisplayWarning="true" WarningMessage="Replaced column type [image] with [varbinary](max)"

Wednesday, September 02, 2009 7:16:17 PM (GMT Standard Time, UTC+00:00)      Comments [0]  Azure | S+S | SaaS