Migrating a SQL Server Database to SQL Azure

I have had a number of interesting conversations with both clients and colleagues about the feasibility of Windows and SQL Azure and the whole “cloud” concept. Every always asks whether or not it is appropriate to use for our clients. I don’t believe there is a straight answer and it depends on your clients requirements around things such as where the data is hosted, how can I get support, backups, redundancy, SLA’s, cost etc.

Yesterday I had a similar conversation with another one of my colleagues who is concerned about the additional latency between Australia and the USA or Singapore, so we decided to initiate an experiment of migrating an existing application that already runs in a small local Australian public cloud to Windows and SQL Azure.

The aim of this experiment is not to prove that there is additional latency between Australia and the USA or Singapore (Southeast Asia), because there is, and it can easily be proven by tracing the network route to the destination, or performing a simple ping test. What we are trying to determine is whether or not the average user of a web based business application will actually notice the additional latency or not, and if the additional latency causes any issues with their day to day use of the application.

There are a number of simple code enhancements that can be made such as the use of Content Delivery Networks and combining CSS and JS, which have not been completed for this test and the file uploading will be ignored for this test as Windows Azure does not allow you to write to disk by default.

In part one if this three part series, we are going to be migrating the SQL Server 2008 R2 Database schema and data to SQL Azure.

We are going to be deploying to both the USA and Singapore to determine whether or not the are any noticeable differences between these two geographically dispersed locations using different internet service providers, and whether or not we can use these locations for production and/or redundancy.

Generally the latency between Australia and Singapore is lower if you are with a quality ISP such as Internode, however If you are with a budget ISP, your internet traffic will more than likely reach Singapore via the USA because it is both easier and more cost effective for the ISP.

We are going to be using SQL Azure Migration Wizard v3.5.2 (available from CodePlex) to migrate the SQL Server database from a local machine to SQL Azure.

image

Fig 1. Microsoft Azure Data Centre Locations

Getting Started with the SQL Server Database Migration

The steps that I followed to migrate the database are detailed below:

Confirm that you have access to SQL Azure and know the username and password – this is required to create a new SQL Azure database and migrate the content.

If you do not already have a SQL Azure Instance available, create a new instance and add your source IP address as an exception in the firewall rules, otherwise you will be blocked by the Azure firewalls and not be able to connect to SQL Azure.

Run the SQL Azure Migration Wizard to Analyse and Migrate the database

image

Fig 2. Select the option to “Analyze and Migrate” your SQL Database.

image

Fig 3. Connect to the local SQL Server Database that you would like to migrate to SQL Azure.

image

Fig 4. Confirm that you have selected the correct source database and click “Next”.

image

Fig 5. Select the database objects that you would like to migrate to SQL Azure. In this case I am migrating everything, so I have selected “Script all database object”. This option will script all database objects that are compatible with SQL Azure.

image

Fig 6. Confirming the database objects that are going to be scripted.

image

Fig 7. Confirm that you are ready to generate the SQL Script and BCP files that will be used to create your SQL Azure database.

image

Fig 8. The SQL Azure Migration Wizard has finished and generated two types of output files:

  1. A script file to create the SQL Azure database and
  2. BCP Files containing the data to be migrated – see the images below

 

image

Fig 9. The output BCP files in the file system

image

Fig 10. The generated SQL script that will be executed against the SQL Azure database.

image

Fig 11. Connect to SQL Azure to create the database and begin the data migration.

image

Fig 12. Select to “Create Database” option to create a new SQL Azure database. The script files above will be used to generate the new database.

image

Fig 13. Enter the name for the new database.

image

Fig 14. Confirm the creation of the SQL Azure database – this will initiate the database migration and data migration.

image

Fig 15. The SQL Azure Migration Wizard begins uploading BCP files and executing the scripts against SQL Azure.

image

Fig 16. The wizard has completed, but the “Next” button is not available – this appears to be the expected behaviour. This is because all the script files have already been executed and there is no further action required.

As a precautionary measure, you should scroll through the output window in Fig 16 to make sure that there is no red text. Red text indicated there was an issue and the script may have failed. If you find some red text in the output window, you should manually check status of the highlighted items and make sure that they have been migrated successfully.

You can use SQL Server 2008 R2’s management studio to connect to SQL Azure and confirm the database status.

In the next post we are going to look at what is involved in migrating the ASP.NET MVC 2 based web tier to Windows Azure.