Migrating DataBase from SQL Server 2008 R2 Enterprise to SQL Azure

In this walkthrough we will migrate an existing Database from SQL Server 2008 R2 to SQL Azure.

Creating Database
 
The first step is to create database. We are going to use School database. Script of sample School Database copy from here.

Generate DatabaseScript for SQL Azure

Right click on School Database and select Tasks. From Tasks select Generate Script.



From Pop up select Set Scripting option.



Give the file name by selecting Save to file option.

Now the main thing to be noticed here is we need to so some change in advanced setting. For that click on Advanced options.



After that click next and Finish. You can see a SQL file is created and we will be using this script to migrate our in house school database to SQL Azure.

Create School Database in SQL Azure

Login SQL Azure portal with your live credential

https://sql.azure.com/

Click on SQL Azure tab. You will get the project , you have created for yourself.




Click on the project. In my case project name is debugmode. After clicking on project, you will get listed the entire database created in your SQL Azure account.



Here in my account there are two database already created. They are master and student database. Master database is default database created by SQL Azure for you.

Click on Create Database



Give the name of your database. Select the edition as Web and specify the max size of database.



You can select other option also for the edition as business.

After that click on Create you can see on Databases tab that Demo1 database has been created.



Run the Script in SQL Azure

Open SQL Server management studio



You will get Connect to server dialog box. Click cancel on that.



After cancelling the dialog box click on New Query from left top



On clicking New Query, you will get the connect to server dialog box again.



Now here you need to provide, Server name of SQL Azure and Login credential of SQL Azure.

To know what is database server name of SQL Azure portal, login to Windows Azure portal with your live credential and then click on SQL Azure tab



You will get the server name in form of

abc.database.windows.net

Where abc is name of your SQL Azure server. We need to provide this server name at local sql server management studio.



Make sure to select SQL Server Authentication and provide login user name and password of your SQL Azure database portal.

After that before clicking Connect click on Option



From Option select School database.



Run the Script

Now once you successfully got connected to School Database in SQL Azure. Copy the script and Run like below.




After successfully running of script , run the below command and all the tables name will get listed.



In this way you successfully migrated database to SQL AZURE.