Wade Wegner

I’ve spent the last few days refactoring a web application to leverage SQL Server via Entity Framework 4.0 (EF4) in preparation for migrating it to SQL Azure.  It’s a neat application, and a great example of how to fully encapsulate your data tier (the previous version had issues due to tight coupling between the data and web tier).  More on this soon.

So, when I deployed my database to SQL Azure (using the SQL Azure Migration Wizard) I was confounded by the following error:

I was caught off guard by this error, as I was pretty sure my connection string was valid – after all, I had copied it directly from the SQL Azure portal.  Then I realized that this was the first time I had attempted to use EF4 along with SQL Azure; my first thought was, “oh crap!”

After a little bit of frantic research I found the following question on the SQL Azure forums.  Raymond Li of Microsoft made the suggestion to set the ‘TrustServerCertificate’ property to True.  So, I updated my connection string from …

<add name="BidNowDataContext"

	connectionString="metadata=

		res://*/BidNowDataContext.csdl|res://*/BidNowDataContext.ssdl|

		res://*/BidNowDataContext.msl;

	provider=System.Data.SqlClient;

	provider connection string="

		Server=tcp:SERVERNAME.database.windows.net;Database=BidNow;

		User ID=USERNAME@SERVERNAME;Password=PASSWORD;

		Trusted_Connection=False;Encrypt=True;""

	providerName="System.Data.EntityClient" />

… to …

<add name="BidNowDataContext"

	connectionString="metadata=

		res://*/BidNowDataContext.csdl|res://*/BidNowDataContext.ssdl|

		res://*/BidNowDataContext.msl;

	provider=System.Data.SqlClient;

	provider connection string="

		Server=tcp:SERVERNAME.database.windows.net;Database=BidNow;

		User ID=USERNAME@SERVERNAME;Password=PASSWORD;

		Trusted_Connection=False;Encrypt=True;trustServerCertificate=true;

		""

	providerName="System.Data.EntityClient" />

… and voilà!  It worked!

Turns out that when Encryt=True and TrustServerCertificate=False, the driver will attempt to validate the SQL Server SSL certificate.  By setting the property TrustServerCertificate=True the driver will not validate the SQL Server SSL certificate.

Of course, once I learned tried this I came across an article on MSDN called How to: Connect to SQL Azure Using ADO.NET to says to set the TrustServerCertificate property to False and the Encrypt property to True to prevent any man-in-the-middle attacks, so I guess I should include the following disclaimer: Use at your own risk!

The SQL Azure team has announced SQL Azure Service Update 4, which includes database copy, an improved help system, and deployment of Microsoft Project Code-Named “Houston” to multiple data centers.  See the SQL Azure Team Blog for the formal announcement.

Customers have been asking for a way to backup databases with SQL Azure for a long time, and the new database copy capabilities will provide a lot of support.  From the SQL Azure Team Blog:

Support for database copy: Database copy allows you to make a real-time complete snapshot of your database into a different server in the data center. This new copy feature is the first step in backup support for SQL Azure, allowing you to get a complete backup of any SQL Azure database before making schema or database changes to the source database. The ability to snapshot a database easily is our top requested feature for SQL Azure, and goes above and beyond our database center replication to keep your data always available. The MSDN Documentation with more information is entitled: Copying Databases in SQL Azure.

Good stuff.  So, if you want to rename a database, you can do this …

// Rename a database

ALTER DATABASE database1 modify name=database2

… if you want to make a copy of a database, you can do this …

// Create a consistent copy of a database

CREATE DATABASE database2 AS COPY OF database1

… and finally, if you want to track the copy progress of a database, you can do this …

// Keep track of the copy progress

SELECT * FROM sys.dm_database_copies

There is also some great “How-To” documentation that’s been published to MSDN that you should take a look at:

Guidelines for Connecting to SQL Azure Database

How to: Connect to SQL Azure Using sqlcmd

How to: Connect to SQL Azure Using ADO.NET

How to: Connect to SQL Azure Through ASP.NET

How to: Connect to SQL Azure Through WCF (ADO.NET) Data Services

How to: Connect to SQL Azure Using PHP

How to: Connect to SQL Azure Using the ADO.NET Entity Framework

And don’t forget about “Houston”.  Microsoft project Microsoft Project Code-Named “Houston” (Houston) is a light weight web-based database management tool for SQL Azure. Houston, which runs on top of Windows Azure is now available in multiple datacenters reducing the latency between the application and your SQL Azure database.

Enjoy!

George Huey has done it again!  He has just published some significant updates to the SQL Azure Migration Wizard.

Previously, I’ve described the SQL Azure Migration Wizard as a tool that helps you migrate your SQL Server database into SQL Azure.  This is still true, but now, thanks to updates made by George Huey, you can also migrate from SQL Azure-to-SQL Server and SQL Azure-to-SQL Azure.  These are significant updates to the tool!

Please watch the following video for an updated explanation of the tool:

As I said, the updates made by George enable all the following scenarios for database migration …

• SQL Server-to-SQL Azure
• SQL Azure-to-SQL Server
• SQL Azure-to-SQL Azure

These last two updates are significant!  Take a look at this thread on the SQL Azure Migration Wizard codeplex site – the user had a scenario where they wanted to migrate a 1 GB database in SQL Azure into a 10 GB database in SQL Azure.

Please take a look at the SQL Azure Migration Wizard up on Codeplex, where you can download the source code and/or binaries.

Last week I presented at the Wisconsin Azure User Group for the second time, along with Clark Sell.  Our goal was to provide an overview of everything announced at the Professional Developers Conference (PDC) 2009.  We made a ton of announcements, and I recommend you check out the Microsoft PDC website for more information, including videos and decks from all the presentations.

Shameless plug: watch my session on migrating applications to the Windows Azure platform with Accenture, CCH, Dominos, and Original Digital – Lessons Learned: Migrating Applications to the Windows Azure Platform.

While I was supposed to only spend twenty minutes talking about updates to the Windows Azure platform, I ended up spending over an hour.  There was too much information to share; here’s a short outline:

• Rebranding of the .NET Services as the Windows Azure platform AppFabric.
• Microsoft Codename “Dallas”, a content brokerage and discovery platform available as a CTP at commercial launch.
• Windows Azure platform integration with Microsoft Pinpoint.
• Enhanced service architectures (i.e. inner role communication, worker roles exposed outside the datacenter, etc.).
• Enhanced diagnostics in Windows Azure.
• Ability to use existing NTFS APIs to store data in durable drives in Windows Azure (called Windows Azure XDrive).
• Administrator privileges in the Windows Azure virtual machines.
• Deployment of pre-configured virtual machine images while still benefiting from the Windows Azure service model.
• User-selectable geo-locations for replicas of Azure storage.
• Secondary indices on Windows Azure tables.
• Content Delivery Network.
• Remote terminal server access to virtual machines.
• Tool for data synchronization called SQL Azure Data Sync, built on the Microsoft Synch Framework and ADO.NET Sync Services.
• Claims-based access control for REST web services through the Access Control Service.
• Commercial launch in and paid usage in February, 2010.
• Datacenter options in USA, Europe, and Asia.

Thanks to everyone for their patience – especially Clark – as I went on, and on, and on …

Don’t forget that you can request and redeem tokens for the Commercial Technology Preview (CTP) at http://windowsazure.com/.  This provides you free usages, with quotas, through February 1, 2010.

Here’s the deck I presented:

Thanks to everyone that attended – I had a great time!

It’s hard to believe that the Professional Developers Conference (PDC) 2009 is less than two weeks away.  It doesn’t seem that long ago that I sat behind the stage at PDC 2008 providing support for the RedPrairie keynote with Bob Muglia and spoke in a breakout session with Jack Greenfield on Multi-Enterprise Business Applications.  I’ll be back again this year, and I’m giving another talk – this time on lessons learned when migrating applications to the Windows Azure platform.

Rather than present this session entirely on my own, I decided to invite some of my customers to come and talk about their own experiences.  I am extremely excited that the following three customers will join me at PDC:

CCH, a Wolters Kluwer Company – CCH is a Wolters Kluwer company, based in Riverwoods, Illinois, providing tax and business law information and software solutions. The company has approximately 700 products for the United States market.

Accenture – Launched originally as the business and technology consulting arm of Arthur Andersen, Accenture is now among the world’s largest consulting organizations. It employs more than 180,000 people in 52 countries.

Domino’s Pizza – See how Domino’s Pizza is running Java and Tomcat in Windows Azure to run their eCommerce application.  Windows Azure provides high scalability to handle Super Bowl load, interoperability with different platforms and technologies, and the ability to integrate to on-premises resources.

Our intent with this talk is to make it highly interactive – translation: ask us questions!  We have a lot of information to share with you, and while we like to present, we’d much rather engage in a meaningful conversation.  Thus, we plan to quickly provide the following information for each of the solutions migrated:

1. Background on the application (i.e. what does it do? who uses it? what technology stack?).
2. The previous architecture (before the migration to Windows Azure) and some of the challenges this architecture presented.
3. The new architecture leveraging the Windows Azure platform.
4. The migration process: what was easy, what was hard, what worked, and what didn’t.

After providing these details, we want to open the session up for Q&A.

We know that we won’t answer all of your questions in the time allotted to us, so we plan to stick around after the presentation so that we can talk to you 1-on-1 and answer your questions.

It’s going to be a great PDC – I hope you’re there!  If so, be sure and come to our presentation on Lessons Learned: Migrating Applications to the Windows Azure Platform.