Introduction:

This is part 2 of the series of article on Migrating On-Premises database to the cloud. In this article we are going to see the first option out of the 3 options available to migrate the database to the cloud

Overview:

Migrating the database to cloud in the traditional process is quite easier and fast to proceed for a normal lite database. Let us take a sample database Microsoft School database(check the attached database scripts downloaded from Microsoft Site) we will run this script in our local database and make it as a development environment. Then we will move this database to the cloud using the scripting options available.

Let us jumps tart to see the step by step process on moving the database to the cloud(option 1)

Steps:

Log in to the Azure portal using the below link. You can see the screen look similar to below

http://www.microsoft.com/windowsazure/

Login to the portal using your Microsoft Windows Live credentials with Azure credentials to the management portal and you will see the screen as shown in the screen below

Now we can see the Database Menu at the bottom left, Click on that will go to the Database Subscription window as shown in the screen below

Clicking on the subscription name will provide the complete details of the server as shown in the screen below

Now we will create a new database SCHOOL as shown in the screen below(Check my previous article on how to create a new database step by step)

Now we are ready with the destination database on the cloud, lets setup the migration required objects from the Source database using the SQL Server Management Studio. Open SMO and connect to the local database as shown in the screen below

After giving successful login credentials and authenticated we can see the list of database available. For our example we are going to use the School database selected as shown in the screen below

Now Right click on the database name and select Task and then select Generate Scripts as shown in the screen below

Now we can see a Popup as shown in the screen below, Click on Next button to proceed further.

Now it will prompt to select the database as shown in the screen below. For our example we select School database and click on Next button as shown in the screen below

Now we can see the list of scripting options available for the user selection as shown in the screen below and click on the Next button. Since we are using SQL Server 2005 we don’t see an option for scripting the data in the list, if we are using SQL Server 2008 then we can see an option to script the insert data query.

Now we can see the list of options to select(tables, stored procedures, views etc. ) as shown in the screen below. Since we are migrating to the Cloud we don’t have support for the Assemblies and click on the next button.

Now we can see the list of stored procedure to select, based on the requirement we can select the stored procedures for migration as shown in the screen below. Click on the Next button to proceed further.

Now we can see the list of Tables available to give the user option to select the required tables as shown In the screen below and click on the next button to proceed further.

Now we can see the option to script the database to the new window or to a file as shown in the screen below. We make selection to script the database to a file as shown below and click on FINISH button.

We can see the summary of the options selected as shown in the screen below

Click on finish button will start the scripting option as shown below

Once all the options are scripted we can see the result Success as shown in the below screen

Now we are ready with the Scripted document of the database. Now go to the SQL Azure database window and connect to the newly created School database as shown in the screen below

Clicking on the Manage button will open a new browser as shown in the screen below

Now accept the agreement and click on OK button will navigate to the new window as shown in the screen below

Enter the valid credentials and click on connect button. we will see a nice portal as shown in the screen below(If you get an errors please check my earlier article on How to use the Manage database using Azure portal.

Now select the OPEN QUERY option from the top menu as shown in the screen below. We get option to open the script file, select the file which we scripted in the top of the article.

We can see the scripts opened in a new windows as shown in the screen below

Now click on the Execute button and then refresh the server to see the changes and the new tables and stored procedures created as shown in the screen below

If we have used SQL Server 2008 with the option of creating the insert scripts for the data we could see the data as well migrated to the new server.

Conclusion:

So in this article we have seen on the first option to migrate the SQL server On premises database to the SQL Server cloud using the traditional approach.