We were given a "classic" web architecture: an ASP.net MVC 3 & 4 web portal, backed up by a MS SQL database with Entity Framework as a ORM.
This architecture had to be prepared for scaling - ported to a system that would allow for a high number of requests to be served.
After analyzing multiple upgrade options (the full reasoning can be read in the previous article) we decided on SQL Azure and Windows Azure as a migration platform.
In this article we will go, step by step, over the database migration procedure, what tooling we did use and what problems appeared or could appear.
The database migration
The database was accessed through Entity Framework - this made our job considerably easier as one of the roles of an ORM is transparency regarding the chosen database.
The first thing we have to do is to check if the SQL Azure and SQL server databases are compatible - what can be migrated "as it is", what has to be changed and what cannot be migrated.
A good place to start documenting ourselves is the Windows Azure General Guidelines and Limitations. If you are dealing with an enterprise database then you have to go over the whole article, otherwise it is enough if you know that the following are not supported by Windows Azure:
Cross database stored procedures - if you are dealing with such a scenario then you will have to move the join logic to a superior architectural level (e.g. LINQ)
Automatic transactions on operations across multiple databases - same as above, if you find yourself having to support this, then you will have to move all the synchronization an rollback mechanisms to a higher abstraction level.
SQL Server agent or SQL server jobs - SQL Azure provides no support for SQL Agent so all your internal jobs have to be moved to an external service that will be periodically executed (for example you can move it to a thread on a Azure WebRole). Other alternative solutions can be found here
Tables without clustered indexes - the good thing is what on every table it is relatively easy to add a clustered index. If you find yourself asking why this limitation exists read this article
If you like a little less theory and a more practical approach, you can attempt a migration with one of the migration tools and, in case incompatibilities are detected, all the tools will provide you a detailed report.
Migration tools
Ok, what can we use to migrate our SQL database to SQL Azure?
SQL Management Studio 2012 (SSMS) - the 2012 version (or newer) is a must due to the fact that in this version a new migration to Azure task has been introduced which will make our life a lot easier. If you don"t own a SSMS license you can use the Express version.
RedGate SQL Toolkit - complexity wise is on the same level with SSMS. You will require a license for the SQL Toolkit - depending on preferences and requirements, your development shop will most likely have either a SQL Toolkit license or a SSMS license.
SQL Azure Migration Wizard - available on codeplex, is the free & open source (but not less powerful) migration tool alternative.
We used SQL Management Studio 2012 - as Bizspark member we had free access to it.
The migration procedure, step by step:
We select from Management Studio the database we wish to migrate to azure and select Tasks -> Deploy database to Azure
We input the name of the database we wish to create and specify the Azure SQL server instance that we wish to use - the SQL Azure server has to be created before from the windows azure management dashboard ) Note: if you already created a SQL Azure database the wizard has the bad habit of setting the already created database name in the Connection Properties, "Connect to database" setting. We are trying to create a new database so we have to make sure that the "Connect to database" setting points to "default". If we don"t do this we will get an error during the migration procedure.
We start the migration procedure and, once it ends, we go over the migration Log and see what incompatibilities were detected. In our case we got off easy: the only detected problem was the fact that not all tables had clustered indexes: we had to manually add a clustered index to each table (this was easily done from SSMS).
After all incompatibilities were solved we repeat step 1, until the migration succeeds.
Migration validation
After the migration is done, to check that everything works correctly we change the connection string in our web project to point to the SQL Azure database. We start up the project and either run some database unit tests or manually go over some basic scenarios. Due to the fact that we have Entity Framework, everything should work as expected, no other change being required.
Final thoughts
That"s about it regarding the database migration. If you encounter problems during your migration or have questions you can contact me directly at dragos(at)txtfeedback.net
In the next article I will go over how we moved our website & virtual directories structure to Azure.
