sitereactor

There has been a lot of buzz around the Azure Accelerator for Umbraco by Microsoft, but it doesn't seem like many have tried it out yet. It might be because it is a little complicated to get up and running. But with Windows Azure Pass (gives you 30 day free access to Windows Azure and SQL Azure) and this blog series there shouldn't really be any excuse.

First off I need to give some credit to Daniel Bardi for wrting this 23 step guide on the our.umbraco.org wiki "Installing Umbraco to SQL Azure", and to Microsoft for creating the Azure Accelerator project (developed by Slalom Consulting). The guides that are available helped me succesfully deploy an Umbraco database to SQL Azure and an instance to the Windows Azure Hosted Services. So why am I writing this post if guides already exist? Well, I found a couple of gotchas along the way that I think others can benefit from. And if you have got limited to no experience with Azure or if you find the existing guides too techie or complicated this post is for you.

On a side note: On the Azure Accelerator project page on codeplex you will find two extensive guides to deploying Umbraco to Azure, but nothing about the database and i'm not sure if this is simply because they have used an embedded database, but it confused me the first time around.

The focus of this first post is SQL Azure, because I found it the best approach to get the db up and running first. Best see the database working before we start deploying the Umbraco solution, right. (If you were using an embedded database this wouldn't be necessary, but since SQLCE is still in beta and Vistadb is out of the picture SQL Azure is the best option in my opinion).

Prerequisites:

SQL Server 2008 - Only needed if you don't already have an SQL Server or SQL Server Express available. SQL Management Studio 2008 R2 Express - If you already have an SQL Server installed then you just need to download the SQL Server Management Studio Express (second column), which is needed to connect to SQL Azure among a couple of other things. Local Internet Information Server (IIS 7.5). Umbraco v.4.5.2 ASP.NET 3.5. Windows Azure account with access to Hosted Services and Storage (will be used in next post), and of course access to SQL Azure.

1.) First thing you want to do is to setup the database server and database instance on SQL Azure. I'm using the new layout on the Windows Azure Platform and I recommend you do the same. When you login you should see a left column similar to the screenshot below:

The interesting thing here is the Database, so click it and you should see your subscriptions for SQL Azure. From the top menu click Create Server to setup your Database Server instance. You will need to select a Region for server, and an administrative user and password. Select a region that is close to you (i.e. I have selected North Europe). The name of the server will be generated for you and the DNS to the server will be servername.database.windows.net. When the server is up and running you need to configure Firewall Rules, otherwise you will not be able to connect to it from your local machine. So add an IP range that includes your local IP (see example below).

With database server and firewall setup you can now create a new database, but instead of doing this through the portal we will create a .dacpac and an sql script to create or rather deploy the Umbraco database to SQL Azure. The next steps will take you through the process of creating these two scripts, and finally deploying them to SQL Azure.

2.) Now that you got SQL Azure setup, you need to make a local installation of Umbraco. This is just a regular installation of Umbraco, so just do what you normally do to setup a site in your IIS. One very important thing is to do a "clean" installation, which means to let Umbraco run its install script to setup the db, but don't install runway, cws or any other starterkit. Keeping the database clean will make it easier to deploy.

A side note for installing Umbraco with regards to the upcoming post is to install it to IIS' default site. If you have the possibility to do this it will save you from editing a couple of settings when deploying Umbraco, as the Azure Accelerator is set to the default IIS site (but it can of course be changed).

3.) Third step is to make the local database deployable - this is also step 3 in the 23-step guide on our.umbraco.org. So open up management studio R2 and navigate to the database for your Umbraco install - I have called mine UmbracoAzure. Expand the database, then Tables and find the table called "umbracoUserLogins", right click and select Design from the menu. In the design view you select the two rows called contextID and userID, right click and select Set Primary Key. Now save the changes to the table and you should be ready for the next step.

4.) This step will cover step 4-6 from the 23-step guide. The files that are generated in this step and the next is available for download at the end of this post. Close the design view, and select your database, right click and navigate to Tasks -> Extra Data-tier Application (if you don't have this option you probably don't have Management Studio R2 Express installed).

A new dialog will appear, which will guide you through the creation of a .dacpac file and an sql script. Click next and verify Application name (same as database), and make a note of the location in "Save to DAC package file" as we will need this file to deploy the local database to SQL Azure. Click next a couple of times to finish generating the file.

5.) This step will cover step 7-13 from the 23-step guide. Once again go back and select the database, right click and select Tasks -> Generate Scripts.

A new dialog will appear, which will guide you through the creation of an sql script with inserts for the database on SQL Azure. Click next and change the default radio button selection to "Select specific database objects" and check the Tables checkbox, as you only want to generate a script for the tables. This is an easy step as a default Umbraco database only contains tables. On the next screen click the Advanced-button and find the row with "Types of data to script" in the new dialog, and change it to Data only. Click OK, note the location of where the file is saved and click next, next and finally finish.

6.) Now we have the two files needed to deploy the database to SQL Azure, but you need to move a single line the sql script before deploying anything. This is the first gotcha! Open up your sql file with the data inserts and look at line 29 where the inserts for the umbracoNode table begins. If you look at the values you will notice that the id is ascending -92, -90, -89 etc. but all of them have a parentID, which is -1. The node with id -1 is the umbraco master root, which you need to move up so its the first insert in the umbracoNode table. If you don't you will get various insert errors while deploying, you will be able to login to Umbraco, but if you navigate to the Developer section and expand the DataType folder you will notice that something is missing. The edited script is available for download at the end of this post (login is: admin and password: b).

7.) Disconnect from your local database server and connect to SQL Azure using the connection info that you got while setting up the database server in step 1 and 2.

When you are connected you can simply expand the Databases folder and see your databases if you have created any through the WindowsAzurePlatform portal. The cool thing about the R2 version of SQL Management Studio is that is allows you to connect to SQL Azure as it was just another SQL Server. Microsoft has also created an online management tool that you can use to do a lot of the same stuff as in management studio. You can access this online tool from the WindowsAzurePlatform portal by clicking Manage from the top menu (in the Database section), but I prefer to use management studio. You will need management studio to deploy the .dacpac file, which creates the database with Tables and contraints.

When you are logged into your SQL Azure database server, right click on the server and select "Deploy Data-tier Application" from the menu. This will open up a dialog where you simply select the .dacpac file, which was generated in a previous step, click next a couple of times and when you are done you have a new database with all the tables of a normal Umbraco install. Next step is to insert the default data.

8.) From the menu in SQL Management Studio R2 Express click File -> Open -> File and locate the sql file that was previously generated and re-organized. Make sure the newly created database is selected or that the script starts with "Use [DATABASENAME]" and click Execute in the menu (might be an idea to click Parse first to verify there are no errors in the script). Execute should run without any errors, so if you encounter any errors you best revise the previous steps, delete the database and try again.

9.) With the database setup you can now verify that it is in fact working. Go back to your local install of Umbraco and change the connection string in web.config, which should be changed to something like this:

<add key="umbracoDbDSN" value="Server=tcp:DATABASESERVERNAME.database.windows.net;Database=UmbracoAzure;User ID=USERNAME;Password=PASSWORD;Trusted_Connection=False;Encrypt=True;" />

Note: If you can't access the database it might be that you need to review your firewall settings for the SQL Azure database server.

And there you go, now you have deployed a standard Umbraco database to SQL Azure. Next post will go through the deployment of the Umbraco solution.

Package with the two scripts needed to deploy to SQL Azure: Zip contains both the .dacpac and .sql files.