SQL Azure - Migrating On-Premises SQL Server Database to SQL Azure Cloud

Introduction:

In this article we are going to see the second option for migrating an on-premises SQL Server 2005 database to SQL Azure using the SQL Server Migration Wizard. This tool is an open-source free tool from Codeplex.com and we will see the documentation and the usage of this tool from the link. Since this is from the community Microsoft is not providing any support for this tool.

Overview:

In our earlier article we have seen the first option for migrating the database from a local SQL Server 2005 On-Premises database to the cloud using the traditional approach. But in this article we are going to see the second option of doing the migration using the open source tool available from Codeplex.com. Now let's see the steps to do this migration process.

Steps:

First we need to download this tool from SQL Server Migration Wizard. Go to this link and we will see the Download option on the right side menu as shown in the screen below:

We will get the download ready (Includes the Binary files); save it in a particular location and extract the files. We will see the list of files available as shown in the screen below:

Now double-click the file and we will see a window opened as shown in the screen below. This window is the start-up screen for this tool.

The Tool can use to migrate the data between the following:

SQL Server to SQL Azure
SQL Azure to SQL Server
SQL Azure to SQL Azure

So our next step is to analyze our database to detrertmine if it can be migrated to the cloud. Follow the following steps to that. Select SQL Database from the Analyze only option as shown in the screen below:

Clicking on Next will Popup a window as shown in the screen below:

Now we select our Server Name and the credentials to connect to the database and click on Connect. Now we will see the list of databases ready to migrate as shown in the screen below.

Now select the School database and click the Next button as shown in the screen below. We will see options of database and stored procedures listed as shown below (Since this database has only Tables and Stored procedures it is compatible).

Now click on the Next button; we will see the Summary list as shown in the screen below. Before that we will see an option Advanced; clicking on that will open a window for user selection as shown in the screen below:

Select the respective option from the list as per our requirement and click on OK button. Now we will see the summary as shown in the screen below:

Click on the Next button. A tool will be ready for preparing the script and you will see the progress as shown in the screen below:

After completing 100% we will see the result windows as shown in the screen below:

We have an option for saving the Scripts and also we have an option to see the SQL Scripts by clicking on the SQL Script tab as shown in the screen below:

We can use this script to run it to the server destination as we do with the Option 1 which we saw in our earlier article on migration. Now we have an option to do that using this tool itself. Let's see the steps to do that using this tool. Click on Exit and open the new tool and follow the following step by step process.

Now select the Analyze and Migrate option and click on the Next button as shown in the screen below:

Now we will get the same options we did at the top of this article by connecting to the server, selecting the object and getting the summary and now we will see the option with the migration using the BCP as shown in the screen below:
Note – Follow the steps from the top

Now once the script is completed we will see the result as shown in the screen below:

Now click on the Next button to proceed further. We will get a popup as shown in the screen below:

This window is for connecting to the SQL Azure database, we will see the Server Name sample shown in the window itself. Now go to the SQL Azure database and fetch the server information as shown in the screen below:

Now go back to the tool and copy the server name and the login details as shown in the screen below:

Now click on the Connect button, and we will see an error as shown in the screen below:

With this error we will see the description that this IP address is not added to the Firewall Rule (Check my article on how to add firewall rules). Now add the IP to the firewall and click on Connect once again. After successful authentication we will see the List of DB's from the SQL Azure DB as shown in the screen below:

Now Select the School database and Select Next. Here we have option to Create or Delete an existing database.

After clicking the Next button, it will prompt with a window for selection of a script to execute against the destination server as shown in the screen below:

Click on yes; a window will popup and we will see the progress bar and at the bottom we will see the result progressing as shown in the screen below:

If there are any problem with the scripts we will see the process become frozen as shown in the screen below:

Now we will have an option to either edit the process or to Skip or Retry the process as shown in the screen below:

Clicking on Edit will open the window editable as shown in the screen below:

If we want to skip the process we can use it by clicking on the SKIP button at the bottom of the screen. We will see the list of objects failed in Red color as shown in the screen below:

Now we will skip all the process and Now once the process is completed we will see the complete scripts executed and the progress bar shows completed as shown in the screen below:

Now we are done with the Migration process and we will see the output in the SQL Azure window as shown in the following screen window:

To proceed further and test it, go to the Manage database and we will see the screen with the latest tables and stored procedures as shown in the screen below:

Conclusion:

So in this article we have seen how to migrate the SQL Server 2005 database from a Local server to the SQL Azure database using the SQL Azure Migration Wizard.