Migrating and Deploying a Simple Cloud App: Part 2 - Scripting the On-Premise Database For SQL Azure

If you’ve started reading from this post, you’ll need to go through the previous parts before starting this one:

Introduction
Part 1: Setting Up a SQL Azure Server and Database

In this part, we’ll take our application’s on-premise database and script it for SQL Azure.

Attach the SQL Server Database

1. Unzip the Nerd Dinner zip file you downloaded. 

   If you haven’t yet downloaded Nerd Dinner, download it from here.

2. Locate the App_Data directory.  You’ll notice there are 4 files.  Two .mdf files and two .ldf files.

   

   We’re only going to need NerdDinner.mdf and NerdDinner_log.ldf.

3. Copy NerdDinner.mdf and NerdDinner_log.ldf to your SQL Server’s data directory.  If you don’t have access to a SQL Server but you have SQL Express installed, that will do as well.

4. Open SQL Server Management Studio and open a connection to you SQL Server.

   You can download SQL Server 2008 Management Studio Express from here if you don’t already have the full SQL Server 2008 Management Studio.

   If you’re using SQL Express, the server name will most likely be localhost.

5. Right-mouse click on the Database node and select Attach.   The Attach Database screen will be displayed.

   
    

6. Click on Add.

7. Locate NerdDinner.mdf in the list and select it. 

8. Click OK.

9. Click OK once more to attach the database to your SQL Server.

10. Right-mouse click on the Databases node and select Refresh.  Look for C:\USERS\JON\DESKTOP\NERDDINNER_2.0\NERDDINNER\APP_DATA\NERDDINNER.MDF.

    Unfortunately, the name that was originally given to the database is preserved.  That’s why the name is so long and doesn’t just display as NerdDinner.

    

Producing the Script

1. Now that you’ve found the NerdDinner database, right-mouse click on it, select Tasks, and then select Generate Scripts.

2. Click Next on the first step of the Generate and Publish Scripts wizard.

3. Before we produce the actual script file, we need to tell SQL Server Management Studio that we will be deploying the script to SQL Azure.  Click on Advanced.

4. Scroll down and locate Script for database engine type.  Change the type to SQL Azure Database.

    

5. Click OK.

6. Now the script has to be saved. Click on … located to the right of File name.

7. Find the directory where you’d like to save the file, give it a file name, say NerdDinner.sql, and click Save.

   Save the file in an easily accessible location such as your desktop. You should delete the script file after you deploy it in part 3.

8. Click Next and Next again to begin scripting.

9. When all the tasks have completed, click Finish.

Detach the Database

The on-premise version of the NerdDinner database will no longer be needed. We can now detach it from our SQL Server.

1. Locate the NerdDinner database.

   Remember, it has a long name and is all in capital letters.

2. Right-mouse click on the database, select Tasks, and the select Detach.

3. Place a check mark under Drop Connections.

4. Click OK to detach the database.

That’s all it takes to convert a SQL Server on-premise database to a SQL Azure cloud database! Depending on the complexity of your future applications, you may have to do some more tweaking once the script is created. There are some features of SQL Server that are not supported by SQL Azure, and as such, you’ll have to remove them from the produced script. The database for this walkthrough is intentionally simple, so no further tweaks have to be made.

Next up – executing scripts on the SQL Azure database.