Windows Azure SQL Database Marketplace
[This article was contributed by the SQL Azure team.]
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.
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.
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:
I am naming my cloud service MVCMusicStoreCloudService.
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:
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.
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:
<forms loginUrl="~/Account/LogOn" timeout="2880" />
<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="/" />
<add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/" />
<add connectionStringName="ApplicationServices" applicationName="/"
name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" />
<add applicationName="/" name="AspNetWindowsTokenRoleProvider"
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:
You can also remove the ASPNETDB.MDF database from App_Data by deleting it, again sliming the project file.
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.
Do you have questions, concerns, comments? Post them below and we will try to address them.