Now that you’ve completed analyzing your current database, and worked through that list of items that won’t work with Windows Azure SQL Database (WASD), it’s time to begin testing your new solution.  Testing begins with setting up your test instance of WASD, and loading your test data, then you point your application to that new instance and then run through your application end-to-end.  Let’s walk through those steps in more detail, and cover some of the issues you might discover.

Push a copy of your data to the cloud

After you’ve set up your instance via manage.windowsazure.com, getting your data pushed is pretty simple.  You could either use the Migration Wizard, or you could use SSIS.  I prefer the migration wizard, since it has better throughput.  All you’ve got to do is pick your source database and tables, then target your new instance and database, and click go!  It takes care of the rest.

If you choose to use SSIS, remember you’ll have to use ADO.NET connections for your destinations.  Also, be sure to set the connection string so that your package knows it’s sending this data to SQL Azure.  I haven’t had a connection disruption in the middle of a data flow task, but I have had it fail between data flow tasks.

Once all your test data is in Azure, and you’ve verified it.  You’re ready to move on to testing your application.

Test your application

It’s a good idea to invite a small number of your system’s power users to join in on the testing.  They’re the ones who generally push your system the hardest currently, so you want them to bring that same focus to testing.  Systematically test everything your application does with the database.  You want to go through everything now, before you turn this over to the end users.  There are some automatic tools for this, I would direct you to my application architect for more information on those automated test suites.

You want to make sure you go through every feature your application has.  When you find an issue you want to record as much detail as you can around what you did to create the issue.  Once you’ve gone through the application once, you’ll bring those issues back to the programmers and you’ll start breaking down what has to be changed to fix the issues.

This is where you’ll discover things like your connection pooling isn’t set appropriately for SQL Azure.  You might discover your retry policy is too aggressive, or you’re sending atomic updates rather than batching them together.  This is when you’ll discover if you’ve been following some programming best practices all along.  If you haven’t, no time like the present to improve those skills, right?

Fixing those issues

As you discover issues you’ll have to prioritize them and decide how you will address them.  You can choose to fix them now, later, or drop the feature all together.  These decisions will come down to a question of time and money.  I can’t give you a definitive answer on which kinds of issues are which.

As you work through these issues, you’ll return to the testing phase several times, each time verifying the issues you address truly are fixed.  Don’t forget you are also re-testing all those items that passed the first time too.  You want every test to pass every  time.  The great news is, once you pass all your tests, you’re ready for the final step.

Go Live!

Going live is very similar to the testing process.  It starts with a database push to make sure all the data you need for production is in Azure.  Once you have that, you make sure all the applications are wired to hit the new Azure instance.  You run through the test suite one more time.  If all your lights “go green”, then you announce to your users the migration is complete, and invite the full user base to start using the new system.

And then you wait.

During our first migration this wait was a little longer than usual.  We heard no feedback from the end users that first day.  All the diagnostics showed the system was performing well.  No unusual SQL errors, the application response time was reporting well below the response time threshold.  Reports were running as quickly as they had during testing.  Why weren’t we hearing anything from the users?

Finally we got a call on the second day.  I was manning the phone.  One of our most demanding users who wasn’t in the test group called was on the line.

“So I’m going through this new system…” she began.  She let that pause hang for far too long. “And it’s just so fast!”

I was completely relieved.  We spent the next 30 minutes discussing the efforts we had put in performance tuning the reports and working with the test users to tweak how we would pull back the data from the data import processes.  The call ended with me feeling the biggest smile cross my face.  It was a great day.

From start to finish this process took a year.  There was plenty of work poured in to the project, and it shows.  The customer now has a system far beyond the one he began with.  A year after go live, he’s doubled the number of clients using his system.  During that year I got to learn a ton about what Azure is, and what it’s capable of.  I also see the potential for it to become something so much more.  I’ve also found that there is a data architecture that many SQL professionals across the country aren’t familiar with: sharding.  It’s the real key to scaling out SQL Server.

And that’s the topic I’m tackling next.  Are you ready to see what the OLTP version of Parallel Data Warehouse looks like?  Stay tuned!

In the mean time, if you have any questions, send them in.  I’m here to help!