If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
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.
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.
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!
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:
Background on the application (i.e. what does it do? who uses it? what technology stack?).
The previous architecture (before the migration to Windows Azure) and some of the challenges this architecture presented.
The new architecture leveraging the Windows Azure platform.
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.
Note: This is a guest post from George Huey, Architect Evangelist in the Developer and Platform Evangelism group.
When you upload your data into SQL Azure, SQL Azure replicates your data to three different locations in order to provide triple redundancy. Therefore, it needs a little more time to get the data in the proper places.
One of the things that we found out during a series of Windows Azure Platform Migration Labs held in the Chicago MTC is that you cannot upload hundreds of thousands of records without giving SQL Azure time to catch up. Consequently, you have to chunk your data and give SQL Azure time to process each chunk before uploading the next chunk of data.
The tool that we used for migrating our customer databases to SQL Azure was the SQL Azure Migration Wizard. The migration wizard uses BCP to download data from an on premise SQL Server database and then uses BCP to upload the data to SQL Azure. BCP allows you to specify the first row (-F), the last row (-L), and the batch size (-b). These options will allow you to chunk the data beginning uploaded to SQL Azure. For example:
BCP MyDb.dbo.Transactions out Transactions.dat -E -q -n –T
The above command extracts data from table Transactions in the database MyDb. At the end of the BCP output, you will find the number of records copied to file (for example: 2,524,520 rows copied).
In order to upload in chunks, you would do something like this:
Note, you will have to put some kind of delay between BCP commands to give SQL Azure time to store the data (say 15 seconds). You will probably find that sometimes the 15 seconds is not enough time and that, during the upload of one of your BCP chunks, SQL Azure might shut it down. If that happens you will see something like this happen:
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Warning: Fatal error 40501 occurred at Oct 30 2009 4:15PM. Note the error andtime, and contact your system administrator.
From the above BCP output, you will see that a total of 155,000 rows were sent before SQL Azure closed the connection. Thus you would have to adjust your next BCP command to start at your –F value + 155000.
While this process works reasonably well, it can make the process of uploading data a little tedious if you have a large number of tables with a large number of records per table. In order to simplify the process, we have modified the SQL Azure Migration Wizard to do all of this work for you. It allows you to specify the chunk size, the batch size, and the time to wait between BCP chunks in SQLAzureMW.exe.Config. It also catch BCP errors and adjust for records processed and then retry.
Try it out, review the source code, and be sure to provide some good feedback!
Today I presented on the Windows Azure Platform at the Day of Cloud conference, in Chicago, IL. This conference was hosted by Tech in the Middle, and gave speakers the opportunity to present on SalesForce.com, the Windows Azure Platform (yours truly), Amazon Web Services, and Google App Engine. A great concept, and a lot of fun.
Gabe and Scott from Tech in the Middle recorded and posted each of these talks. You can find them here:
