As we were trying to customize our Omega.MSSQL product to manage SQL Azure database (http://www.cerebrata.com/Blog/post/Browser-based-SQL-Azure-Explorer.aspx), we wanted to test against a database hosted in SQL Azure.
At first we tried to upload AdventureWorks database however that didn't work because SQL Azure does not support Assemblies, User Defined Types, User Defined Data Types just yet.
Then I read on Roger Jennings blog (http://oakleafblog.blogspot.com) that he was able to port Northwind database (SQL Server 2000) into SQL Azure with some tweaks. We thought let us do the same as well.
After a number of iterations, we were finally able to do it. Here are the highlights of the whole process:
1. First you need to change references to "sysobjects" in the script to sys.all_objects and make changes accordingly e.g. sysobjects has an "id" column while sys.all_objects has "object_id" column.
2. Get rid of text, ntext, image data types as they are not supported in SQL Azure. In fact these data types are being deprecated (http://msdn.microsoft.com/en-us/library/ms187993.aspx). What we did was we replaced text and ntext data types with nvarchar (4000) and image datatypes with varbinary.
3. Get rid of any references to PartitionScheme as SQL Azure does not support that. That means removing all references to ON [Primary] in the SQL Script.
4. Commented any references to "USE" statement as SQL Azure does not support that as well.
and here is SQLCMD command
sqlcmd -U username@servername -P password -S servername.ctp.database.windows.net -d NorthWind
All in all it took us 3 - 4 iterations to create the database. It was still not perfect but sufficient for us. I am attaching the final modified SQL script with this blog so that if any body is interested in creating Northwind database in SQL Azure. I hope it helps. Feel free to modify it. If you find any issues with the script please send me with corrections.
Hope this helps
Thanks
Gaurav Mantri
instnwnd-sqlazure.sql (2.02 mb)