Migrating SQL Server On Premise DB to SQL Azure Using SQL Server Migration Wizard
I see that the customers have trouble in migrating SQL Server On Premise DB to SQL Azure.
There are lot of ways to migrate an on Premise DB to SQL Azure.
Here I writing about migration using SQL Server migration wizard.
The tool can be downloaded from here https://sqlazuremw.codeplex.com/
The page has links to three tools as follows
SQLAzureMW v3x and tools requires .NET Framework 3.5 and SQL Server 2008 R2 SP1 bits to run.
SQLAzureMW v4x and tools requires .NET Framework 4.5 and SQL Server 2012 bits to run.
SQLAzureMW v5x and tools requires .NET Framework 4.5 and SQL Server 2014 bits to run.
Please note this tool is open source and is community supported and CSS does not support the tool.
STEP BY STEP MIGRATION PROCESS:
Here I am using an example of SQL Server 2012 DB and will be using the tool SQLAzureMW v4.15.5 Release Binary for SQL Server 2012 and will be working on AdventureWorks2012 DB.
- Select the executable from the extracted folder.
2. Select the target Server.
Target server is the Source where the DB to be migrated resides.
SQL Server Migration Wizard can be used to migrate a DB from an On - Premise environment to another on - Permise Environment , On - Permise to SQL Azure , SQL Azure to on Permise and SQL Azure to SQL Azure.
Here we will look in to the SQL Server On - Permise Environment to SQL Azure.
3. Select the name of the Instance\Server where the DB is hosted.
Here I am connecting to my default instance in the localbox.
we can either specify the database name or select from the list of database existing in the instance.
Here i have selected to option of choosing the list of databases.
I am selecting the AdventureWorks2012 DB.
While migrating we choose to select all the DB objects or we choose any particular object of choice.
Note not all objects are supported in azure.
Details of limitations of Azure can be found from here https://msdn.microsoft.com/en-us/library/azure/ee336245.aspx
For the example I am selecting all database objects.
4. Select the objects to be migrated.
While Migrating we can choose to migrate the schema only, Schema plus data and data only.
To select the items to be migrate click on the advanced button.
The wizard automatically checks for Azure Incompatiable objects and tries to correct them in the script it genarates.
The wizard uses BCP to export and import the data.
5. Selecting the SQL Azure DB
- Once the script Genration is completed we need to connect to the Azure server that has been created.
- we get an option to import the scripts to an existing database or we can create new database.
- While creating database we get options to select the SLO.
We get three Performance level\Tiers options in SQL Azure DB.
The lowest performance level is Basic while the highest is Premium, Standard level is in between the both.
The details of the performance levels can be found from here https://msdn.microsoft.com/en-us/library/azure/dn741336.aspx
While creating the Database we can also specify the collation of the database.
Here for the Demo I am creating a database with the basic Performance level with the default collation.
once the database is created click on the next button and it will confirm the script execution of the destination.
Once confirmed the script execution will progress.
Once the script execution is complete the import is succesfully complete.
We can review and click exit.