Floating FB popout byF5debug

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

 

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.

[more]

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/

clip_image002

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

clip_image004

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

clip_image006

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

clip_image008

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)

clip_image009

clip_image010

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

clip_image011

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

clip_image012

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

clip_image013

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

clip_image014

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

clip_image015

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.

clip_image016

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.

clip_image017

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.

clip_image018

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.

clip_image019

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.

clip_image020

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

clip_image021

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

clip_image022

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

clip_image023clip_image023[1]

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

clip_image024

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

clip_image025

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

clip_image026

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.

clip_image027

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.

clip_image028

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

clip_image029

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

clip_image030

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.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter