Tuesday, September 29, 2009

Bug in Windows Azure Tools for VS2008

I just submitted a bug to Microsoft about the mess being created when you want to have both .NET RIA Services Tools for VS 2008 and Windows Azure Tools for VS2008.
The description:
When you have both Windows Azure tools and .NET RIA Services tools for Visual Studio 2008, the former will conflict the last.
In general, if you have installed only .NET RIA services tools for VS, a new type of project is added (affected one) - .NET RIA Services Library. It creates two projects - one Silverlight Library and one .NET Class library.
However, if you also have Windows Azure tools for VS installed, and want to create .NET RIA Serivces Library, the second project type will be Windows Azure Worker Role

Connecting to SQL Azure from ASP.NET

Let’s talk a little bit about SQL Azure.
Generally when you subscribe for SQL Azure at https://sql.azure.com/ you will receive your activation token (although is bit too late already to go for it, but you may have a chance). Once activated the service, it will automatically create a new project for you named “SDS-only CTP Project” and you will have options to manage it.
Then you go and create your first SQL Azure data base. From now on you will be fighting to use the toolset you used to, when developing against SQL Server. However there are some niceties you need to learn in order to get working with it.
First of all is how to connect to SQL Azure using either sqlcmd or SSMS (SQL Server Management Studio). I wrote detailed post about it here: http://blogs.staykov.net/2009/08/how-to-use-sql-azure.html
Next you will want to simply run a GridView bound to the SQL Azure database. Well … do not expect that either Server Explorer, or SQL DataSource creation wizard will work with it. Just forget about these tools for now. You are on your own. Or not exactly.
Here: SqlAzureSample.zip I have uploaded two sample projects and simple SQL script. So after you have read how-to-connect-to-sql-azure, and after you have set up your first database and assigned a login to it, you can start with the projects:
  • create_insert.sql simply creates a table and inserts some records into it.
  • WebSiteToSqlAzure.zip contains simple ASP.NET web-site which is bound to SQL Azure
  • WebRoleToSqlAzure.zip contains a CloudService project with single web role, which is bound to SQL Azure
Both web sites that are attached are using one and the same ConnectionString:
"Server=tcp:[YOUR_SERVER].ctp.database.windows.net;Database=[YOUR_DB];User ID=[LOGIN_WITHOUT_@SERVER];Password=[PASSWORD];Trusted_Connection=False;"
There are some placeholders for you to replace:
[YOUR_SERVER] – the server your CTP of SQL Azure resides. Something like "ccmlt4qacx
[YOUR_DB] – the database name you have created
[LOGIN_WITHOUT_@SERVER] – the login you have associated with database
[PASSWORD] – the password you have created for the login
Hope that this will be a good starting point for you!
And hope to continue Azure series :)

Wednesday, September 2, 2009

SqlAzure Migration tool

I was wandering wether we have to wait too long to have some tool to easily transfer our DB Schema to the cloud.
The anwser came just one day ago and it is here: http://sqlazuremw.codeplex.com/
LIghtweight, simple, easy to use, this tool generates (DROP) Create t-sql statements compatible with SqlAzure. The Scripts generated are bit large (Well it depends on your schema) and it takes some time to execute all, but it works.

I am still searching for a way to easily (or not, but automated) TRUNCATE entire DB and recreate tables again. Everything I found over internet is kind-of useless with Azure since there are no stored procedures like "sp_MSForEachTable" or so. The closest match I found is executing some statement like this:

DECLARE @TableName NVARCHAR(2048)
DECLARE @ConstraintName NVARCHAR(2048)
DECLARE Constraints CURSOR FOR
SELECT TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
OPEN Constraints
FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER TABLE [' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']');
FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName;
END
CLOSE Constraints
DEALLOCATE Constraints

DECLARE Tables CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
OPEN Tables
FETCH NEXT FROM Tables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('DROP TABLE [' + @TableName + ']');
FETCH NEXT FROM Tables INTO @TableName;
END
CLOSE Tables
DEALLOCATE Tables

But it works only partially on Azure and if you do not have lots tables and constraints. For some reason Azure can't drop some of the constraints.