Not all SQL features are supported: http://searchsqlserver.techtarget.com/feature/Why-you-should-think-twice-about-Windows-Azure-SQL-Database
Prerequisites
You need SMSS for SQL 2012. This can be downloaded here as just the tool: http://www.microsoft.com/en-us/download/details.aspx?id=35579 (select SQLManagementStudio_x64_ENU.exe or x32 as appropriate)
This is probably easiest done from an Azure VM where you have temporarily mounted the database (meaning you hast have to push backup to Azure)
Getting your database into an Azure database
This is not as easy as it seems – especially for a big database due to bandwidth issues.
I found (working with limited bandwidth) the best solution was to place the database on a temporary Azure SQL VM (via a normal backup) from where i could then move it into Azure DB
There are (at least) 3 ways to do the migration.
Using the Deploy Database to SQL Azure Wizard
This needs SSMS 2012 – and is the easiest for a simple database that passes all Azures limitations
You need an Azure database account and this simply creates another database within that server and copies the database there.
To use this wizard, open SQL Server Management Studio (SSMS) and connect to the server. Right click the database you want to move, click Tasks, Deploy Database to SQL Azure...
NOTE: This export will fail if you have any unsupported features or errors in your schema. This includes:
Alternatively export your existing database to bacpac file
Right mouse click on your database. Choose Tasks | Export Data-tier Application. You will be given the opportunity to name both the bacpac file and the container name. Recall that the Bacpac file is a blob file in Windows Azure Storage.
This is similar to the previous method but gives you slightly more control
http://blogs.msdn.com/b/brunoterkaly/archive/2013/09/26/how-to-export-an-on-premises-sql-server-database-to-windows-azure-storage.aspx
Use the codeplex export tool
http://sqlazuremw.codeplex.com/releases/view/32334
This tool will script out the structure and the database. The main advantage is that it will try and fix up any unsupported Azure features your database is using and is by far the most flexible.
Possible issues
There are a number of these
Collations
When you create database – make sure you use the same collation value – but note that this doesn’t actually set the database collation itself as this is fixed. This can be a serious limitation.
The default collation for character data in SQL Azure databases is SQL_Latin1_General_CP1_CI_AS. This collation is also used across the SQL Azure infrastructure to sort and compare metadata that defines database objects. The server and database level collations are not configurable in SQL Azure. However, you can use a collation of your choice at the column and expression level
http://blogs.msdn.com/b/sqlazure/archive/2010/06/11/10023650.aspx
Clustered Indexes
All tables must have a clustered index – so “SELECT INTO …” will fail. This is likely to be an issue for much use of temporary tables and needs to be changed to a CREATE TABLE and then INSERT INTO - http://blogs.msdn.com/b/sqlazure/archive/2010/05/04/10007212.aspx
Others
Manual fix ups required for my example database
- Built-in function 'suser_name' is not supported in this version of SQL Server
- Collations all needed swapping to match database SQL_Latin1_General_CP1_CI_AS (knock on effects??) – did global replace in script to get it to upload
Azure connection string.
Username must have server name appended to it:
e.g. username@myAzureServerName
For odbc (e.g. mapserver) its fussy about the driver and I had to specify
CONNECTION "Driver={SQL Server Native Client 11.0};Server=tcp:abc.database.windows.net,1433;Database=MyDB;Uid=Me@abc;Pwd={MyPassword};Encrypt=yes;Connection Timeout=30;"