Porting the MVC Music Store to SQL Azure - SQL Azure Team Blog - Site Home

The MVC Music Store is an outstanding tutorial application built on ASP.NET MVC 2. It's a lightweight sample store which sells albums online, demonstrating ASP.NET MVC 2's productivity features and data access via Entity Framework 4. You can find it in CodePlex here. As a practice exercise we are going to port it from SQL Server to SQL Azure to show you how easy it is to get an existing application running on SQL Azure and Windows Azure.

The process for moving the database is fairly straightforward: you need create the database, schema, and data, then modify the connection string to point to SQL Azure. To move the application to Windows Azure you’ll need to create a Windows Azure Cloud project and attach the MVC Music Store project. A few tweaks to the MVC Music Store project and it is up and running.

Getting the Schema and Data on SQL Azure

The MVC Music Store that you can download from CodePlex runs on SQL Server, the download includes SQL Server .mdf and .ldf files to attach to your on-premises SQL Server database to quickly create the Music Store database. However, the CodePlex project also contains a Transact-SQL creation script for creating the database schema and data, one for SQL Azure and one for SQL Server. Here are the steps to get the SQL Azure database, schema and data created.

Download the MVC Music Store from CodePlex using the .zip file linked from here. The Transact-SQL file can be found here, download and copy it onto your hard drive.
Login into the SQL Azure Portal.
Create a SQL Azure project, or use an existing project to create a database named MvcMusicStore.
Using SQL Server Management Studio, open the SQLAzure-MvcMusicStore-Create.sql , and select your new created database for the connection information. If you’ve never done this before, there’s a blog post on how to do this: SQL Server Management Studio to SQL Azure. Server name, and your Administrative login and password can be found on the SQL Azure Portal.
Execute the script from within SQL Server Management Studio against the new database on SQL Azure; this will create the schema and all the data need.

Creating a Cloud Project

The next step is to take the MVC Music Store project that is designed to run locally on the Internet Information Server and enable it for Windows Azure. There is a detail explanation on how to do this with a generic project here. However, let’s walk through the steps for the MVC Music Store:

The first thing that we want to do is create a Windows Azure Cloud Server project using Visual Studio 2010. It is much easier to add an existing MVC project to a Cloud project, then trying to add the cloud aspects to the MVC project.

I am naming my cloud service MVCMusicStoreCloudService.
In the next step, I add no roles to the cloud service. I will be adding the MVC Music Store later as a web role.
Now that I am finished with the wizard my solution explorer looks like this:
Going back to the .zip file that I downloaded early, I am going to copy the MvcMusicStore project into the directory structure I just created as a sibling to the MVCMusicStoreCloudServer like this:
Going back to Visual Studio 2010, I right click on the solution in the Solution Explorer and add the MvcMusicStore project to my solution.
To add the MvcMusicStore project as a web role, I right click on the Roles in the MVCMusicStoreCloudService project and choose Add then Web Role Project in solution.
Choose the MvcMusicStore, press Add and your solution explorer should now look like this:
Now that the MvcMusicStore has been added as a project you need to right click on it and choose select Unload Project. This will allow us to modify the .csproj a file.
Right click on the unloaded MvcMusicStore and choose Edit MvcMusicStore.csproj
Modify the .csproj file to add a Web Role Type.
Save the MvcMusicStore.csproj and reload the project file into the solution.
Next thing to do is mark System.Web.Mvc assembly in the MvcMusicStore project as Local Copy true. You can do this by right clicking on the assembly, choosing Properties and changing the Local Copy setting.
One final thing to do is, in the MVCMusicStore project, add a reference to the Microsoft.WindowAzure.ServiceRuntime assembly.

Changing the Connection String

In order to access SQL Azure we need to change the connection string in the web.config of the MvcMusicStore project. The web.config that is downloaded points to a local database in App_Data. The easiest way to change the connection string is to return to the SQL Azure Portal and copy the connection string from the portal.

Mine looks like this:

Server=tcp:XXXXXXXX.database.windows.net;Database=MvcMusicStore;User ID=XXXXXX@XXXXXX;Password=myPassword;Trusted_Connection=False;Encrypt=True;

The downloaded connection string appears like this:

metadata=res://*/Models.StoreDB.csdl|res://*/Models.StoreDB.ssdl|res://*/Models.StoreDB.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MvcMusicStore.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True"

We need to change the connection string to point to SQL Azure, rather than SQL Express. Note the text in bold:

metadata=res://*/Models.StoreDB.csdl|res://*/Models.StoreDB.ssdl|res://*/Models.StoreDB.msl;provider=System.Data.SqlClient;provider connection string="Server=tcp:XXXXXXXX.database.windows.net;Database=MvcMusicStore;User ID=XXXXXX@XXXXXX;Password=myPassword;Trusted_Connection=False;Encrypt=True;"

Save it and you are done with all the changes you need to connect to SQL Azure. Now that you are done you can remove the MvcMusicStore.mdf from the App_Data directory by deleting it. This will prevent it from being packaged up and deployed to Windows Azure. The less files you have the faster the upload time to Windows Azure.

Just a Few More Tweaks

The default membership and roles providers that are used in MVC Music Store aren’t completely available in the Windows Azure Platform. For simplicity of the blog post, we are going remove this functionality from the MVC Music Store. However if you are interested in keep it you can use implementations that are in included in the SDK samples that use Cloud Storage. See this post for more information on how to set those up.

To remove them that I am going to remove these lines from the web.config:

<add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />

And these lines:

<authentication mode="Forms">
  <forms loginUrl="~/Account/LogOn" timeout="2880" />
</authentication>
<membership>
  <providers>
    <clear />
    <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
  </providers>
</membership>
<profile>
  <providers>
    <clear />
    <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/" />
  </providers>
</profile>
<roleManager enabled="true">
  <providers>
    <clear />
    <add connectionStringName="ApplicationServices" applicationName="/"
      name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" />
    <add applicationName="/" name="AspNetWindowsTokenRoleProvider"
      type="System.Web.Security.WindowsTokenRoleProvider" />
  </providers>
</roleManager>

Since we aren’t implementing membership and roles, we also need to remove the AccountController and Account Views from the project files. Finally, the admin link in the Site.Master needs to be removed, it looks like this:

<li><a href="/StoreManager/">Admin</a></li>

You can also remove the ASPNETDB.MDF database from App_Data by deleting it, again sliming the project file.

Ready to Deploy

That is all the changes you need to deploy the MVC Music Store to Windows Azure and SQL Azure. Test it in the Windows Azure DevFabric and then deploy away to Windows Azure.

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.