Step-by-step: Migrating ASP.NET Application to Windows Azure (Part 2–Preparing SQL Azure Database)

This is the second post of Migrating ASP.NET Application to Windows Azure series. In the first post, I’ve show you in preparation of website to be migrated. Now, let’s prepare the database for that.

If you notice the App_Data of the personal website, you will see the Personal.mdf file which is the database of the application.

image_40B2A6CAimage_17C791C1

However, by noticing the web.config file, there is also another database called aspnetdb.mdf. This database will be automatically created when we open-up the ASP.NET Administration portal. To do that, click on ASP.NET Configuration button to open up the web site administration tool.

image_2FDAD64F

I believe most of you are familiar with ASP.NET administrator tool. As it, I won’t go into the details of how to create roles and users. But in this example, create 2 roles (Administrators and Guests) and 1 user (admin).

image_3906EBC3

When you are done, try to refresh the App_Data folder and you will see that the new aspnetdb.mdf file was created.
Since we are going to host our application on Windows Azure, it’s impossible to keep our database on our local storage. As it, we’ll need to export our database to the cloud database service,the SQL Azure.

1. The first step is to prepare the SQL Azure database. To do that,go to http://sql.azure.com. I assume you have got the SQL Azure account in this step. Click on the project name and it will bring you to your server administration as shown below.

image_3AA791CA

You will notice that some info above such as my server name, admin user, as well as server location which I’ve defined it earlier.

2. The next step is to create the database. To do that, click on the Create Database button and fill in the name (I name it PERSONAL). You may also want to specify the edition of your database (whether Web or Business) as well as the maximum size for your database. Click Create when you are ready.

Immediately, you will see the new created database.

3. Now the we’ll need to connect to the SQL Azure database from client. To do that, open up the SQL Server 2008 R2 Management Studio. (You’ll need to use the R2 version since the ability to connect to SQL Azure). Enter the server name as you could seen from your server administration, eg: [server name].database.windows.net. Subsequently enter your username and password as authentication. Click on option, and type the database name that you’ve been created just name (PERSONAL).

image_5FC4DC36 image_2EC1D931

If anything goes well, you should see as following screenshot.

image_772C1842

4. Now you are connected with the SQL Azure database, the Personal. The next step is to create tables and other objects from the on-premise database. Remembering that we have 2 database on local server, aspnetdb and personal. Now we are going to prepare the script of these two database. To do that, . Login to the your local database engine with your admin credential. I would recommend you to copy the mdf and ldf file of your database (in this sample aspnetdb and personal) to the SQL Server data folder in order to simplify the attach process.

Next, attached your database mdf file. Moreover, I rename the database to personal and aspnetdb from the long fullname. If everything goes well, you will see the following database in the explorer like mine.

image_5487D9C7

5. The next step is to generate the script of each database. To do that, right click on the personal database and select Task – Generate Script.

I’ll select the script entire database objects on the “Choose Object” step.

image_45142C1E

6. On the Set Scripting Options steps, click on Advanced button. Modify the following properties:

Types of data scripts : Schema and data.
Script use database: false

Click OK to close the “Advanced scripting options”

Subsequently, Select “Save to file” and locate the file to your preferred location. (in this example, I save it to my documentsscripts.sql

Click Next and Next, Finish to finished.

7. Do note that the script that has been generated is not SQL-Azure-ready! It means that we’ll need to modify it first so that could be successfully compatible with SQL Azure. Thanks to community folks who have been developing the SQL Azure Migration Wizard to simplify the task. Download and open up the Migration Wizard.

Select Analyze Only (as we’ll do the migration manually) and TSQL file as the input. Click on Next to proceed.

image_4DB484AA

8. You will notice that in the Result Summary, some of the sections are marked with these comments –~ CREATE DATABASE is not supported in current version of SQL Azure

image_7FCBAF27

From the Object Explorer, select our cloud database “Personal”. Click on New Query button. As the new query editor comes up. Just copy and paste all of the script from SQL Azure Migration Wizard to SQL Server Management Studio. For each block with those comment, delete it.

Run it immediately. If anything goes well, you will see the result as following.
image_3A16FF3C

9. Repeat step 4 to 8 with database aspnetdb. Do note that although the source comes from two database, we are only targeted to one destination on SQL Azure for simplicity purpose.
When you run the aspnetdb query, you might find the problem as below on the [aspnet_Membership_GetNumberOfUsersOnline] stored procedured. This is caused the SP uses nolock keyword which are deprecated features in SQL Server 2008 R2. To solve it, just delete the (NOLOCK) keyword.

image_717D106A

10. If everything goes well, when you refresh the cloud personal database, you will see the tables and stored procedure are all there.

image_4891FB61

It means that we’ve successfully export our on-premise database to the cloud.

11. Now, to test whether the database is successfully created, we can connect our personal starter kit website to use SQL Azure database. To do that, modify connection string section in the web.config file as following.

<add name="Personal" connectionString="server=[server_name].database.windows.net;database=personal;uid=[username];pwd=[password]" providerName="System.Data.SqlClient"/>

<add name="LocalSqlServer" connectionString="server=[server_name].database.windows.net;database=personal;uid=[username];pwd=[password]"/>

 

12. Run our ASP.NET website and see whether it is successfully connected.

image_66900955

 

In the next post, I’ll show you how to migrate our ASP.NET website to ASP.NET Web role.

Stay tuned.

This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Step-by-step: Migrating ASP.NET Application to Windows Azure (Part 2–Preparing SQL Azure Database)

  1. Pingback: Step-by-step: Migrating ASP.NET Application to Windows Azure (Part 3–Converting ASP.NET Web site to Web Role) | Wely's Cloud Journey...

  2. Pingback: Step-by-step: Migrating ASP.NET Application to Windows Azure (Part 1 – Preparing the ASP.NET application) | Wely's Cloud Journey...

Leave a Reply

Your email address will not be published. Required fields are marked *

*