Migration to Windows Azure SQL Database: Step 2

Let’s continue our discussion on what it takes to migrate a database from SQL Server classic to Windows Azure SQL Database (WASD).  At this point in the migration we’ve determined that all the access points in our system do have internet connectivity, so they’ll be able to reach our WASD instance.  We’re not going to store any highly sensitive data (credit card data in plain text or government secrets), and our current system was already using SQL Authentication to connect to the database and control who had access to what.

In our next step, we’re really going to dig into the code.  We’re really going to find out what would slow down the migration, and what might prevent the migration all together.

Research and Documentation

I’ve worked with both the small and medium sized businesses, and Fortune 500s.  There’s one thing they both have in common.  Business systems are never fully documented.  Some are just more documented than others.  The next step in the migration process requires you find all the documentation that exists on the system as if functions today.  With that in hand you’re going to see what things may no longer work with SQL Azure (WASD).  I like to break my research down into 5 basic questions.

Who uses the system?

Knowing who your users are will help you when you have questions later.  When you read through the documentation and discover that one step where the writer assumes you just know how to do something.  Knowing who normally performs that function will save you a lot of time and energy.  You can go straight to the source and find out, how do you perform that function.

Knowing who your power users are will help too.  You’ll want a few of them in your testing team.  After all, these are the people who are pushing your current system.  You’ll want them to push your new system too.  It’s better to push it before going live to see where it’s going to fall short.  Also, if you get some of these power users on your side, they’ll help sell these changes to others in the company.  And since resistance to change is one of the hardest challenges to overcome, you need to recruit as many to that cause as you can.

You can’t just change a system without considering the users.  Get to know them before you begin the process.  Listen to their concerns, if you can address them in the migration, it will make the whole project more successful in the long run.  Besides, you’re making this change to improve their experience…right?

What, what what?

There are many whats I like to answer.  What are the users doing in the system?  What kinds of questions are they trying to answer?  What does the system do?  What is the system supposed to do?

By answering these questions you’ll get to know the system from the inside out.  You’ll start to spot opportunities for improvement.  When you start answering the question “what questions are you trying to answer?”  you’ll really start to see room for improvement in your reporting solution.

As you answer these what questions, you’ll also start to see areas where the system is actually doing things differently than the users expect.  This is another area where you can make improvements to the system.  While not strictly a part of the migration, this question can help guide your team to make constant little improvements that create a strong relationship between your team and your customers (or co-workers).

These what questions will also help you spot troubles in the migration.  If the system regularly pulls down 300,000 rows to build a report, that’s not going to work well in SQL Azure.  I’d also argue it isn’t working well onPremise either!  But these what questions start pointing out problems that you’ll have to dig further into the code to determine if you can continue as is, or if you’ll have to make changes to the system in order to proceed.

Where are your users located, and when do they use the system?

These two questions help you manage your maintenance windows.  What’s really frustrating for many smaller shops is not having a clearly defined maintenance window.  I’ve actually worked in many shops where, there was no communication between IT and the end users.  Maintenance windows were just taken whenever we didn’t see users on the system.

That doesn’t work when you’re dealing with users all over the planet.  Even less when your business is online, and open to the public.  By talking to your users, either through actual discussions, questionnaires, or logging, you can get actual insight into where your users are located and when they are using your system.

You can then start scheduling your regular maintenance windows, establishing service level agreements, and also planning for those longer maintenance windows where you’re going to be migrating a ton of data from one data center (onPremise) to the cloud.

Trust me, you’re going to want to communicate that with your end users.  Be up front with them, good faith is hard to create with your users.  And communication is one of the few free ways to do that.

Why do your users use this system?

This last question may sound like a marketing question.  And it is in a way.  If you can understand why users are using the system, it will help you make certain decisions in the future.  If people use your system because it’s the most accurate, then that needs to be important to you while you conduct the migration.  If cost is the top priority, then keep that in mind.  Getting into the minds of your users help you stay on the same page.  It will also help you address problems with the right tools.

Why the users want to see how many people get on at a certain location and off at another could help fleet managers plan for putting more or less buses in service.  In turn that will help riders wait less, and also help the fleet save money.  But if you never understand that, you’ll never understand why the user keeps asking for several reports.  Once you take the time to understand why they are using the system, you can work with them to design the report that actually answers both questions, and helps them accomplish both goals in one report.  Then you no longer have to design multiple reports to answer the same questions over and over!


Get to know the system you’re migration, as well as the users on that system.  It makes the process go more smoothly.  It also helps you identify opportunities for improvements.  If you skip this step, it is possible to complete a migration, but you’ll be limited to a lift and shift migration.  Take the time here, and you open up a ton of possibilities.  And you’ll grow a relationship with your customers at the same time.

Next time, we’re going to dive into the technical details around whether or not you can migrate your database from SQL Server OnPremise to Windows Azure SQL Database.  We’re going to discuss the Migration Wizard that we used for our case study project, as well as how we would do it today with SQL Server Data tools built into Visual Studio 2012.

As always, if you have any questions about a migration, or anything SQL Server related: feel free to comment below or email me.  I’m here to help!


By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *