[Azure] Migrez votre base SQL Server 2008 sur SQL Azure
{Mon post d'origine "Migrating your SQL Server 2008 Database to SQL Azure": http://memoprojects.blogspot.com/2012/02/azure-migrating-your-sql-server-2008.html}
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:
How-to?
- Let us assume you have VS 2010 and SQL Server 2008 R2, and you have an existing DB that you are happy with,
- 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),
- Modify your T-SQL accordingly to fix highlighted errors and generate a new database. Let call it DB_SQL_AZURE_COMPLIANT,
- 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),
- With VS 2010, create (or use an existing) a SQL Server Data-tier Application
- 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
- 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
- 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.
- To double check it is really compliant, we are going to use our VM,
- Mount your VM and configure your network card (so you could access it vis MSTSC)
- Make a Snap shot of your VM (in case you want to rollback)
- 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,
- 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”.
- 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) - Connect to your Windows Azure web portal, and go to “Database” (this is in Silverlight 4)
- Click “Manage” this will open the Web portal to manage SQL Azure
- The opens a new Web portal to manage SQL Azure (in Silverlight 5)
- 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 !!)
- 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.
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
