Introduction:

In this article we are going to see the second option of migrating 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 can 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 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 lets 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 can 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 can see the list of files available as shown in the screen below

Now double click the file and we can see a window opened as shown in the screen below. This windows 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 now our step is to Analyze if our database to see if its possible for migrating to the cloud. Follow the below steps to do the same. 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 can see the list of databases ready to migrate as shown in the screen below.

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

Now click on the Next button, we can see the Summary list as shown in the screen below. Before that we can 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 can see the summary as shown in the screen below

Click on Next button, now tool will be ready to prepare the script and you can see the progress as shown in the screen below

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

We have option to save 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. Lets see the steps to do that using this tool. Click on Exit and open the tool newly and follow the below step by step process.

Now select the option to Analyze and Migrate option and Click on the Next button as shown in the screen below

Now we will get the same options which we did at the top of this article by connecting to the server, selecting the object and getting the summary and now we can 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 can 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 can 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 Connect button, and we can see an error as shown in the screen below

With this error we can see the description that this IP address is not added to the Firewall Rule (Check my article on how to add fire wall rules). Now add the IP to the firewall and Click on Connect once again. After successful authentication we can 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 options. 

After clicking on Next button, will prompt a window to select to execute the script against the destination server as shown in the screen below

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

If there are any problem with the scripts we can see the process gets freezed and we can see the process as shown in the screen below

Now we can have an option to 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 can 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 can 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 can see the output in the SQL Azure window as shown in the screen window

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

Conclusion:

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