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

Moving to SQL Azure

Moving to SQL Azure Last week I finally got the long awaited invitation for the SQL Azure CTP. That is a real SQL server (with tables and stored procedures as we know them) in the cloud. The featureset is limited, but This is a real SQL sever and not the SDS ACE model, with SOAP and REST interfaces, that was announced as last PDC and that is now being phased out. See SQL Data Services RDBMS Model for details. With pricing starting at $9.99, no initial HW/data center cost and an option to get “auto high availability” it can be a very attractive offering for some scenarios.

Code near vs. code far There are two models that you can work with – “code near” og ”code far”. In the former model, you have you application an the database in the same datacenter in the cloud, i.e. both IIS and SQL as Azure services from MS. Without a relational SQL Azure and ”code near” i think it would be hard to move most existing applications to Azure. With ”code far” you can run you application”on premise” and use SQL Azure in the cloud. That’s very easy (just update the connection string) and very slow (since its usually quite some latency between the on premise application and the cloud based database). “Code far” makes a lot of sense for”remote tools” (SQLCMD, Management studio on your PC working against a SQL instance in the cloud). However I’m still to be convinced that running an on premise application against a clod based database is a good idea.

Tools and scripts RedGate’s otherwise so brilliant tools doesn’t work yet and only a very limited set of functions from SQL Management Studio works, so best bet is probably SQLCMD.

[Updated 28-01-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.

Since the feature set in SQL Azure is limited there are many things that can’t be used (ROWGUID, Spatial data, index padding, ANSI NULL, ntext og image data type etc.). This means that a SQL script genererated from Management Studio needs to be “cleaned” from non-supported SQL.

Sunday, August 30, 2009 2:31:16 PM (GMT Standard Time, UTC+00:00)      Comments [0]  Azure | S+S | SaaS