Last week Microsoft released a “Community Technology Preview” of SQL Azure, a hosted version of Microsoft SQL Server 2008. In addition to the SQL Azure CTP, Microsoft also has a CTP for Windows Azure, where you can deploy web and worker roles – similar to how we deploy ASP.NET web sites and Windows Services (*nix daemons).
These are exciting news for developers and systems architects who have tried to deploy scalable web sites or services. The initial capital costs can easily go over $50,000 when you need a web farm of load balanced servers on a rack at your local hosting company.
With Windows Azure, there are zero capital costs (Microsoft takes care of the infrastructure), and the operational costs are very competitive. In addition, we can use the tools we use today (such as Microsoft Visual Studio or SQL Server Management Studio) to deploy Windows Azure projects. Here’s more info on the SQL Azure CTP:
The announcement that SQL Azure CTP is available
http://blogs.msdn.com/ssds/archive/2009/08/18/9874133.aspxPage where you can register for the CTP
http://msdn.microsoft.com/en-us/sqlserver/dataservices/default.aspxWindows Azure Platform Training Kit (includes a bunch of labs related to SQL Azure)
http://www.microsoft.com/downloads/details.aspx?FamilyID=413E88F8-5966-4A83-B309-53B7B77EDF78&displaylang=en
In the last few days I spent some using the SQL Azure training kit getting my hands dirty with both Windows Azure and SQL Azure.
After generating a script in SSMS, you
need to clean it up before running it SQL Azure
One of the issues you run into right away is that SQL Azure only accepts a subset of commands that are available in an on-premise SQL Server 2008 instance. If you generate a script in SSMS you need to clean it up before creating the same database on SQL Azure.
The Windows Azure Platform Training Kit includes a hands-on-lab called “Migrating Databases to SQL Azure” which takes the AdventureWorksLT2008 sample database and shows you in detail what needs to be cleaned up.
I followed the steps in this lab and took some notes. I thought it would be a good idea to publish a summary of these notes here – others might find it useful – you’ll find the notes below.
Cleaning up a SQL Script for SQL Azure
When generating a SQL Script on Windows Server 2008, clean it up before running it on SQL Azure. First, in SSMS > Generate Script, use the following options:
| Option | Value | Reason |
| Convert UDDTs to base types | True | SQL Azure does not support User Defined Data Types. You should use this option to convert any user defined types into their underlying base types. |
| Script extended properties | False | SQL Azure does not support extended properties. Therefore we do not need to script out these properties |
| Script USE DATABASE | False | SQL Azure does not support the USE DATABASE commend for changing database context |
Once the script is generated, you’ll need to delete some statements – here are the details.
Common Cleanups
SET ANSI_NULLS ON
SET ANSI_NULLS OFF
Replace with <blank>, the ANSI_NULLS ON option is not supported
SET ANSI_PADDING ON
SET ANSI_PADDING OFF
Replace with <blank>, the ANSI_PADDING option is not supported
ON [PRIMARY]
Replace with <blank>, not able to specify filegroups
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Replace with 'WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)'
PAD_INDEX, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS index options not supported
PAD_INDEX
SORT_IN_TEMPDB
ALLOW_ROW_LOCKS
ALLOW_PAGE_LOCKS
For example -
This: ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
Becomes: ')WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)'
Bookmark all 'PAD_INDEX', use CTRL-K + CTRL-N to move through bookmarks
Remove the following options within each index definition.
SORT_IN_TEMPDB,PAD_INDEX, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS
Ensure you keep the appropriate commas separating the remaining options.
PRIMARY KEY CLUSTERED
Add as needed, make sure every tabel in SQL Azure has a clustered index (before data is inserted into it).
Less used, but may need to be cleaned up
NT AUTHORITY\NETWORK SERVICE
Delete, no support for Windows Authentication
CREATE TYPE
Delete, no support for user defined type statements
NOT FOR REPLICATION
Replace with <blank>, column constraint not supported
CREATE XML SCHEMA COLLECTION
Creation of XML SCHEMA COLLECTION not supported
CREATE XML INDEX
Creation of XML INDEX not supported
CONTENT
[xml]
Replace '[CatalogDescription] [xml](CONTENT [SalesLT].[ProductDescriptionSchemaCollection]) NULL,'
With '[CatalogDescription] [xml] NULL,'
References to XML SCHEMA COLLECTIONs
ROWGUIDCOL
Replace with <blank>, the ROWGUIDCOL column property is not supported
If you get the error:
Msg 40031, Level 16, State 1, Line 1
The partition key column for table 'SalesLT.vProductAndDescription' is undefined.
This is the failure in the creation of an index on a view.
This is not supported in SQL Azure but for our purposes we can ignore it - it is a non fatal error.
It simply means this object will not be created.
Comments