Migrating and Deploying a Simple Cloud App: Part 3 - Executing the Scripts on the SQL Azure Database

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

Introduction
Part 1: Setting Up a SQL Azure Server and Database
Part 2: Scripting the On-Premise Database for SQL Azure

In this part, we’ll connect to our SQL Azure database and execute some scripts required by the application.

Connect to the SQL Azure Server

1. In the Windows Azure Management Portal, click on the server you previously provisioned. The Properties pane on the right-hand side of the screen will refresh with the server information.

   If for some reason you can’t see it, hover your mouse on the right-hand side of the screen until you see the cursor change to a two arrow cursor.  Drag it towards the left and the Properties pane will appear.

   

2. In order to connect to the database, we’re going to need the fully qualified DNS name of the server. Highlight the value in the Fully Qualified DNS Name and press CTRL+C.

   Remember, the Windows Azure Management Portal is a Silverlight application.  Don’t bother trying to right-mouse click to copy because you’re just going to get the Silverlight menu.

3. Open SQL Server Management Studio and open a new connection.

4. Paste the server name in the Server name field.

5. Change the authentication type to SQL Server Authentication.

6. Enter the administrator login and password we setup earlier in the Login and Password fields respectively.

   If you don’t remember the login, just flip back to the Windows Azure Management Portal and it will be in the Properties pane.

   

7. Before we click on Connect, click on Options.

8. We want to make sure that the connection is secure.  To do that, click on Encrypt connection.

9. Click on Connect. Give it a few seconds and you’ll be connected.

    

   Notice the icon next to the server name? The icon is blue, indicating that the connection is to a SQL Azure server.  Expand the Databases node to see our NerdDinner database.

10. Open the first of three script files required for the ASP.NET membership and profile providers required by the app. From the File menu, click on Open, and then select File.  Select InstallCommon.sql.

    If you haven't already done so, download the required scripts from here.

11. Change the database to NerdDinner and click Execute.

     
    It should run relatively quickly and executed with no problems.

12. Repeat steps 11 and 12 with InstallMembership.sql, InstallProfile.sql, and NerdDinner.sql (the file we scripted in part 2).

    Don't forget to ensure that the NerdDinner database is selected before running each script.

13. Let’s check that the tables and stored procedures were created.  Expand the NerdDinner database.  You should now see tables and stored procedures.

     

So that was relatively painless, right? Not too different from running scripts against your on-premise SQL Server, right? Right! Congratulations! You have now successfully deployed your SQL Azure database.

Online Database Manager

Let’s pause for a moment and look at a scenario where you may not have SQL Server Management Studio available to you. No problem. You can deploy the scripts from the online management console.

1. Go back to the Windows Azure Management Portal.

2. Click on the NerdDinner database, and then click on Manage in the toolbar. A new tab or window will open.It’s going to open up a new tab.

   If you have a pop-up blocker, it may prevent the window from opening.   Allow the window to pop-up in your pop-up blocker, and click on Manage again.

3. All the login information is pre-populated because you previously selected the database. Just enter your password and click Connect.

You can do pretty much everything from here. New query, tables, etc. For example, to deploy scripts:

1. Click on Open Query.
2. Select a script file and click Open.
3. Click on Execute.

There you have it – deployments from anywhere!

SQL Azure Deployment Completed

With that, we’re done the SQL Azure portion of our deployment. Let’s review what we did:

• We created a SQL Azure Server.
• We created a SQL Azure Database.
• We took our on-premise database, scripted it for SQL Azure.
• We connected to the SQL Azure database, and executed the scripts against it.

Next up – creating the Cloud solution in Visual Studio 2010.