[Azure] Migrating your SQL Server 2008 Database to SQL Azure

They are many ways you could do to migrate your SQL Server 2008 database (SSIS bulk copy, Data Sync, dedicated tools, …). Here is one of them, that was quite painless, and free of charge (at opposed of using commercial tools).
Because the Windows Azure world is moving really fast, this method might be already out of date as you are reading !

Pre-requisites:

• Get a Windows Azure account (currently you have 90 days free http://www.microsoft.com/france/windows-azure/Offres/Essai-90-jours.aspx)
• Get “Free Download Manger” application http://www.freedownloadmanager.org/ (if you have a proxy at work, you could use a direct link from sourceforge: http://sourceforge.net/projects/freedownload/files/Free%20Download%20Manager/FDM%203.8.1173/fdminst.exe/download)
• Download the TFS11 and VS11 (i.e. what will probably be VS2012 and TFS2012): http://blogs.msdn.com/b/briankel/archive/2011/09/16/visual-studio-11-application-lifecycle-management-virtual-machine-and-hands-on-labs-demo-scripts.aspx
  from Brian Keller’s blog, using Free Download Manger
• Install the SQL Azure Migration Wizard: http://sqlazuremw.codeplex.com/

How-to?

1. Let us assume you have VS 2010 and SQL Server 2008 R2, and you have an existing DB that you are happy with,
2. Install and use “SQL Azure Migration Wizard” tool to connect to your existing database. It will generate you as T-SQL Script will errors that would occur in SQL Azure (you could find all the infringement rules alongside this CodePlex project),
   
3. Modify your T-SQL accordingly to fix highlighted errors and generate a new database. Let call it DB_SQL_AZURE_COMPLIANT,
   
4. Migration DONE !! (either let the tool correct for you or do it your self). This tool could even push your database (without its data) directly into SQL Azure.
   The steps after are to enable you to work in a secure manner and ensure your project is still compatible with SQL Azure, and provide a link to show how to also push your data (using BACPAC),
   
   
5. With VS 2010, create (or use an existing) a SQL Server Data-tier Application
6. Reverse Engineer you SQL database into Visual Studio Database DAC project using "Import Data-tier Application...", and connect to your new DB_SQL_AZURE_COMPLIANT database
   
7. You will have a VS 2010 DB project that is able to generate DACPAC v2.0 deployment file (basically, it is a ZIP file that contains schema of you database, without any data. As opposed to BACPAC that is a extension of DACPAC, where a BACPAC also contains you data as JSON format). More info on DACPAC format: http://msdn.microsoft.com/en-us/library/ff719373.aspx
   
8. Now you have a DB project that is most likely be able to be deployed into SQL Azure (you can be certain only once you have migrated everything). You could verify that by running again the “SQL Azure Migration Wizard” tool and deploy into SQL Azure. You now should have no errors.
   
9. To double check it is really compliant, we are going to use our VM,
10. Mount your VM and configure your network card (so you could access it vis MSTSC)
11. Make a Snap shot of your VM (in case you want to rollback)
12. Copy/Paste you DAC project (created with VS2010) into the VM and open it with VS11, and confirm the you want your project to be migrated into a VS11 DB project,
13. Because you’ve done a great job correcting the T-SQL Errors, and you have great Stored procedures, views, … and no errors, you could activate ALL rules CodeAnalysis (below) with confidence !!
    Also, ensure you have a Data-tier Application (.dacpac file) checkbox is selected, and that the “Target platform” is “SQL Azure”.
    
14. Install Silverlight 5 add on the VM. This is because the existing one on the VM is old and will not support new SQL Azure web portal
    (generally, unless you know what you do, do not install SL5 on your DEV machine if you are working on SL4, else you would spend hours uninstalling. Indeed, it will break your SL4 dev environment)
15. Connect to your Windows Azure web portal, and go to “Database” (this is in Silverlight 4)
    
16. Click “Manage” this will open the Web portal to manage SQL Azure
    
17. The opens a new Web portal to manage SQL Azure (in Silverlight 5)
18. Now you could play with your newly database migrated into SQL Azure (type in plain T-SQL and run it). This web portal contains MANY MANY MANY great functionalities that you should discover ONLY progressively (because it looks nice, but a bit messy !!)
    
19. The final step is to populate your database with data (simply use a T-SQL script as a quick hack). Indeed, again DACPAC only deals with schema and for data, this is done using BACPAC: http://www.4tecture.ch/blog/sql-azure-import-export-service-is-now-in-production
    But this is another story, based on other Windows Azure platform components and further CodePlex projects ! 

By the way, with a large database, I never managed to make the Microsoft “SQL Azure Compatibility Assessment” tool working. http://www.microsoft.com/en-us/sqlazurelabs/labs/sqlassessment.aspx.It seams really promising, but it always tells me that my DACPAC is of an incorrect format, and that I should use a SQL Server Data Tools (CTP4) to generate it.
But how come ?? unless my version of SQL Server Data Tools installed in my VS11 is out of date ! In which case, I must again run after the latest tool and latest news concerning Windows Azure.