Migration to Windows Azure SQL Database: Step 1

Server Closet?

Last time I introduced you to Windows Azure and some of the key features: pay as you go, scalability, and ease of use.  These are the three key points I make when I start talking to business owners about why I like Azure, and how it can help their businesses.  But before we try and sell them on Azure, we have to ask a very key question.

Would the system work in Azure?

We have to ask some pretty basic questions to figure out if the business system would work in the cloud.  The most basic of these is “Does every part of the system have internet connectivity?”  It goes without saying that if you’re looking to move to the cloud, you’re going to need a connection to the cloud!

In the presentation we are dealing with a company that uses sensors mounted on vehicle to collect data about those vehicles and the number of passengers traveling on those vehicles.  These sensors use a cellular data connection to reach the internet.  They were already using that internet connection to “dial home” and hand this data off to a service that would then record the data into the database, and then acknowledge that packet.  Once the acknowledgement is received, the sensor deletes the data, and proceeds to begin collecting more data. I’d like to point out, these sensors will hold on to these packets for up to a year without dialing home.  This helps address possible connectivity loss between these sensors and the central service.  The fact that this fail-safe was built in, saved a lot of time and effort in the migration.

We didn’t have to build anything extra to deal with the fact that you cannot assume your connection to the cloud will always be there.  Connections are transient. If you build your solutions assuming the connection isn’t always there, and build in a retry policy, you’ll always be in better shape in the long run!

On the other end, users were already connection to the system using a windows based application to connect to the database over the internet, and retrieve data for reporting.  So from end-to-end, the system already had internet connectivity, so we were good to go!

Are you dealing with secret data?

Officially you shouldn’t be putting “top secret” data into Windows Azure.  But you can deploy sensitive data, as long as you’re taking the proper precautions in your system design.  In fact, Microsoft currently holds ISO/IEC 27001:2005 certification, SSAE16/ISAE 3402 attestation, HIPAA Business Associate Agreement,  (and goes through an annual review process).  You can find the current references to these certifications here. While the big one that’s missing is PCI, you should be able to obtain a PCI certificate with Azure, as long as you show you’re meeting everything on the PCI DSS checklist.  Showing the controlled access to the data centers would be a breeze, since you never actually get physical access.

Whenever we’re asked to deal with secured data, we always bring in an outside consultant to help us build our solutions in a way that would ensure the client get’s the certification they’re going for.

Of course, I personally like to avoid PCI all-together by using a tokenization solution from a existing credit card processor.  It makes this process so much easier!

In the presentation, we’re only dealing with GPS data, and counts.  Nothing that really needed to be secured more than by user account.  So for the case study, we’re off to the next question.

What kind of authentication are you using?

In our case study we found the client was only using SQL Security…just one account.  After discussing some of the limitations of using a single account, and developed a plan for improving that security, we were good to go!

In Azure, when it comes to the database layer, you’ve got two options SQL in a virtual machine, or Windows Azure SQL Database.  If your application is already using Windows authentication, and you can’t move to SQL authentication, then you’re going to be locked into using a virtual machine (as of the publishing of this post). If you’re already using SQL Authentication, or could move to it, then you can use either virtual machines or SQL Database.

The great thing about Windows Azure is it’s constantly improving!  When I started giving this presentation, the only option was SQL authentication.  Almost three months ago, Windows Authentication and Active Directory became valid options!

Final thoughts for this step

You’ve got to do some research up front to figure out if the system you’re looking at could move to the cloud.  Chances are it could.  But there are some times where it doesn’t make sense.  you’ve got to be ready for those.  We’ve been asked to look at cloud based options for manufacturing plants.  There was just so much electromagnetic interference, a solid internet connection just wasn’t going to be possible. Once you’ve done this first bit of research.  You’ve got your first round of documentation on who is using the system, what are they doing when they use the system, etc.  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.

As always, if you have any questions about your systems and if they could move to the cloud, let me know!  If you just have questions about Azure, SQL, or anything else, let me know, 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 *