SQL Azure - Migrating On-Premises SQL Server Database to SQL Azure Cloud – Option 1

Introduction:

This is part 2 of the series of articles on Migrating an 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 a database to the cloud in the traditional process is quite easy and can be done quickly 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 use it for our development environment. Then we will move this database to the cloud using the scripting options available. Let us jump start to see the step by step process of moving the database to the cloud (option 1).

Steps:

Log in to the Azure portal using the following link. You will 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 to 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. Let's 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 authentication we can see the list of databases 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 the Next button to proceed further.

Now it will prompt to select the database as shown in the screen below. For our example we select the School database and click on the 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 procedures 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 the 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 a selection to script the database to a file as shown below then click on the FINISH button.

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

Click the finish button to start the scripting option as shown below:

Once all the options are scripted we can see the result Success as shown in the following 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 the OK button to navigate to the new window as shown in the screen below:

Enter the valid credentials and click the connect button. We will see a nice portal as shown in the screen below. If you get an error 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 an 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 window 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 will see the data as well migrated to the new server.

Conclusion:

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