This is the first post in transitioning a SaaS application that Red Jungle hopes to offer as a Product from its privately hosted Virtual Machine on Myhosting.com to run instead on Windows Azure as an Azure Website running against SQL Azure.

Currently the application is an ASP.NET MVC 3 application running on .Net Framework 4.5 using the Entity Frameworks.  The data is maintained on a Sql Server 2008 R2 Database.

This post is about transitioning the Database layer from it’s hosted SQL Instance on the VPN server to SQL Azure.  This walkthrough depends on your having Visual Studio 2012, all of the Latest Azure SDKs and SSMS 2012 installed on your development machine.  Trying to do this with SSMS 2008 is just too much work.  Please note this article was correct as of the 9th of March 2013, but be aware that the Azure Platform is changing and improving daily (thanks @ScottGu) so these instructions may have been superseded, it may be even easier than it seems here.

The Steps necessary to pull this off are:

1. Log into and Backup the Database on the Virtual Machine, and bring that backup down to your development machine.
   
2. Restore the database on your local Sql Server instance
   
3. Open the VS2012 solution that contains your SaaS project (or you can start a new solution just for the database if you want).
   
4. Add a new Sql Server Database Project
• 
• Name the project what ever you want the eventual database to be named in SQL Azure
  
5. Right mouse click on the Database project Select Import From , then Select Database…
• 
  
6. Import the database using the Import Database Dialog
• 
• Personally I prefer to have the folder structure set up by Object Type, since especially with Azure we find everything ends up being DBO
• Don’t bother with importing the Referenced logins or the permissions, or the database settings, cause they are all going to be changed for Azure
  
7. Once that is done you will have Database structure in a solution that can not be added to source control, modified, deployed, etc
•  
• We won’t be using the Security on SQL Azure so we delete those
• And Extended properties are not supported on SQL Azure so if you have any delete those as well
  
8. Double click on the Properties tab of the project and in the Project Settings | Target platform: change to Windows Azure SQL Database
   
• 

Here is where things begin to become different, some of the stuff I’m about to do is not specific to SQL Azure, It can apply to any Sql Database running on any SQL 2012 instance.  Basically Microsoft is changing the way we think of databases, and has introduced some new terminology.  Recognizing that Versioning is a continuing problem with databases, they are asking us to think of a database as a “Data-Tier Application” that runs on a SQL Hosting machine.  It’s still a Database, but they want to support some development scenarios that just don’t translate smoothly to how we as developers have been thinking about database, so they are hoping some new terminology will assist us in making that transition.

So from here on out don’t think of you data storage as a database, but rather as a Data-Tier Application that runs on a SQL engine.  So why Data-Tier Application? Well to be honest a SQL “database” really isn’t a database. We have stored procedures, triggers, functions, etc, which encapsulate usually business rules and logic, which to be totally honest isn’t data, its process.  Microsoft is just trying to get us developer to recognise this and realise that what we call a Database is more then just a place we store some bytes, it’s a place where actual coding, logic, and versioning is needed.  Hence why they are calling it now a Data-Tier Application.  So along those lines from here on out, I won’t be calling the item we are working on a Database I will be referring to it as a Data-Tier.

So now, looking at that Project Settings page, click on the Properties… button and a new dialog opens where we get to enter our Data-tier Application Properties

And finally here you can see where they have begun to introduce the concept of a Version Number in the Data-Tier.

The other interesting thing to note about the Project Settings Tab (in the same place where we clicked the Properties… button

They specifically have noted that the Output type is Data-tier Application which they have given a .dacpac file extension.  You do have the option to instead of using the .dacpac having the output created as a .sql file (the old database way), but if you read Microsoft’s documentation on SSMS 2012 and SQL Azure, the writing is pretty large on the wall, they REALLY want us to start using .dacpac.

The .dacpac can be thought of as the definition of the Data-tier without the data.  To read more of the concepts and way to think of a Data-tier application and how it relates to a .dacpac file, check out Data-tier Applications documentation on the Microsoft MSDN website. I’ll just be giving you the specific highlights you need to know for this particular part of the operation.

Also of note is that this new type of Database project can be “built” like any other project type, if you go to the Build section of the Properties for the project

you will see a build output path.  The data-tier (remember data-tier === to database, just a new more inclusive name) is dropped then recreated according to the scripts and specifications that have been saved in the project.  This uses the new localdb/v10 functionality of Sql (Microsoft is also doing away with SQL Express for on the fly attached files (single instance) database, and having us move to the localdb approach).  This data-tier once built can then be put thru various SQL and code analysis tools and warn you of problems with the Data-tier.

For example, imagine you have created a stored procedure that references the “User” table, but in a future version you decide to rename the table to “Person”.  Before you wouldn’t get an error until some piece of code in your business layer tried to access that stored procedure.  With this project type and being a Data-Tier, VS2012 will give you an immediate Error in the output window on a Build.  You find out about the error before you even get close to deploying the mistake. Think of it as Precompiling for Databases.

Now once that is all done, and you can successfully build and cleared all your errors (it will use the Target Platform we set earlier to make sure all features specified in the data schema and stored procedures, etc are compatible) we can publish the .dacpac to SQL Azure

On the Build Menu, there is a Publish <DatabaseName>… option, you can also find this by right mouse clicking on the project and finding “Publish…” there.

Now we end up with a dialog that was obviously designed by a developer and a UI consultant didn’t get a chance to review it.  Once you understand all its options and in what order to access them, it’s pretty easy, but let’s just say the UI is not intuitive.  Also the dialog can’t quite make up its mind whether or not it is going to embrace the new terminalogy and instead is a mishmash that just confuses the new conceptual approach.

First place to start, is at the bottom left with the Create Profile button (unless of course you have already created a profile previously in the project in which case you start with the Load Profile… button).

This will now Create a new publish profile and add it to the Project (for next time you need to use it)

Next we go up to the top left of the dialog and click the Edit button and you will get the dialog we have all seen for creating a connection to a Database, here we will put in the name of the SQL Azure Server url and the Sql Authentication credentials from the SQL Azure website.  http://manage.windowsazure.com

Once that is done, we have two options on how to proceed.  Staying with the “let’s jump around on the dialog” theme the developer who put this together has started with, we need to next look at the middle of the dialog at the “Register as a Data-tier Application” checkbox on the right, and the currently enabled “Advanced…” button to the left of that.

Let’s start by first clicking on the “Register as a Data-tier Application”.  You may notice that the “Advanced…” button has been disabled.  The Advanced button would allow you to set all sorts of Scripting publishing rights and defaults.  A Data-tier has those rules “built-in” to it’s schema so we don’t need to worry about them for this operation.

The other thing you will notice is the “Block publish…” checkbox has become active.  This will prevent a publish of the changes to the database if the target database connection already has a database published, and the version number is the same as was set back in the Data-tier application dialog, but the structure has changed.  This means you need to go back and modify the Version Number before it will allow you to publish the changes.  Of course if working on your local development machine you may not care, but on a production machine, if the database has changed at all we really do need to make sure we have modified the Data-tier application version number.

If all the settings are right, you should now go back to the bottom left of the dialog and “Save Profile”.  With that done, finally you can go to the bottom right and click “Publish”, and the database and structure will be published to SQL Azure and the database will automatically be set up to keep track of its “versions” as a Data-tier Application

Here you can see that the Data-tier functionality has added a __RefactorLog where it will keep track of Versioning and changes for you as part of being a Data-tier application.  This is a good thing.

Last thing to do is go into Visual Studio and Check in your Data-Tier Application database project to TFS (or Git-Hub, or SVN, or what ever source control system you use, just please use source control!)

Now to move the data

Ok, so I have actually been moving a database from our VPN to Azure as I have been making up this walk-thru (this is more for my co-workers at Red Jungle, but I thought I might as well share it with you as well).  So up to this point we have

• Moved and versioned the database from the original hosting Virtual Machine onto my Development machine
• Converted the Database structure to a SQL Database Project
• Targeted the SQL Azure platform
• “Converted” the database to be a Data-Tier Application
• Set Versioning up.

So that’s great, I now have the database that our SaaS product uses deployed to SQL Azure, I could now in theory just change the connection string in our SaaS product and it should start using the SQL Azure database (and in fact it does…). Only one small problem, there is no data in that database, Red Jungle’s clients won’t like having to re-enter all their data…

So I guess I should have just restored the Backup I took of our SaaS database to SQL Azure.  Well there is a problem there as well, you see, SQL Azure doesn’t allow for Backups and Restores as you and I have come to know them in SSMS.  In order for SQL Azure to be massively parallel and scalable and replicable, Microsoft had to make things work a little bit different. 

This is Where we now learn about the .bacpac file format and Exporting a Data-tier Application.  Again you can follow that link to read in depth about it, but I’ll give you the highlights here.

First let’s start by going back to that database backup I downloaded and restored to my development machine.  Now first things first, if any changes had to be made to the Data-tier application project in VS2012, I’ll need to add a Schema Compare to my project,

and run it comparing my Project to my target database on my machine, and update the database on my machine so it is the correct schema.

Next if the database I grabbed off the Virtual Machine was not deployed as a Data-tier application, (which in this case it wasn’t) I will need to “publish” the project again, but this time against the local version of the database.  This will add the versioning information to the database making it a true Data-tier application as well.

Once that is done, next I right mouse click on the database in my local SQL server and under task look at the Data-tier Application options

Hang on a moment what’s that “Deploy Database to SQL Azure…” option and why didn’t we use it before? Well 3 reasons

1. The Database to be moved to Azure STILL needs to be converted to a Data-tier application, so we still would have had to do 80% of the work we already did
2. When deploying a database to SQL Azure you can only deploy a NEW database; that is the <funkystring>.database.windows.net,1433 SQL Azure server you have set up thru the Azure Portal won’t let you overwrite the existing database, you will have to add a new one, which as long as you plan ahead could work for you, but still you do need to learn about how to backup and restore in this new context of Data-tier Application, so this is as good a time to learn and practice as any.
3. Because when I started this tutorial I had never yet done a right mouse click on the latest edition of SQL Data Tools since the update was installed on my machine and I didn’t know they had added this feature until I got to this point in the article… and I don’t feel like re-writing it from scratch. ;-p

You might also notice the “Extract Data-tier Application…” option, the difference between Extract and Export, is Extract will just pull out the Data-tier schema, basically giving you an empty database of the current version, whereas the Export option pulls out the data AS WELL as the schema, it is akin to the old .bak file we are used to.

So back to the “correct” way, Now that we have everything set up correctly, select the “Export Data-tier Application…” option.

In this case we want to save it to Windows Azure (if you haven’t yet, go set up a Storage Account and a Container in Azure).

Click the Connect… button

Now retrieve your values for Windows Azure from online, you can find the account key by clicking on the Manage Keys on the Storage Home Page

Once you have those values fill out the settings dialog appropriately.

Click Next and you are taken to the “Summary” page, finally click Finish.

This basically makes what we have know as a .bak file, compresses it all up and uploads it to the Windows Azure Storage Account in a Blob.

The rest now all happens from the Azure Management portal.

1. Open the Azure Management Portal and login
   
2. Click to enter the DB section
• 
  
3. at the bottom of the page in the ribbon you should see “import”
• 
  
4. Click the Import then follow the dialog to connect to the storage container where you dropped the .bacpac file, and fill in the settings.  You will need to import this database using a new name, for example in the dialog below I just tacked on the word “Backup” to the original database name
• 
  
5. Making sure the Configure Advanced database settings are set, click to go to the next page, set the edition and database size and click Go.
6. Then go get yourself a cup of coffee, this is going to take a while!

Then once the database with the data has been properly imported you rename it, using the old Silverlight Manage Console, and issue T-SQL commands into a sql command prompt window. 

ALTER DATABASE Database1 
MODIFY NAME = Database1_OLD
GO
WAITFOR DELAY '00:00:30'
GO
ALTER DATABASE Database1_copy_02_01_2012
MODIFY NAME = Database1
GO

Okay, that’s the “official” way to do it, but if I have to fall back to the Silverlight management console, and do esoteric T-Sql commands well I’d rather do Powershell (and if you know me you know that means I don’t want to do that!).

So instead the easiest thing to do is just delete the original database, and use that “Deploy Database to SQL Azure…”  command that I didn’t know about! I just specified the name for the Database to be the same as the one I had just deleted, and called it a day.

Which is exactly what I did!

Well that gets the database off the Virtual Machine and onto SQL Azure, the last step was to go onto the virtual machine and change the connection string to connect the new SQL Azure database.

Next up Loading Certificates for your Azure Website from a Blob.

Ok, fifth time I’ve had to Google this (I keep forgetting the exact steps), and unfortunately not enough people have actually written about this error message for the first 2 to 3 pages in the Google search to be of any use. So let me make this one short and sweet.

You get the following error dialog

The Web project ‘YourProjectNameHere’ requires SQL Server Express, which is not installed on this computer. The recommended database engine for Visual Studio 2012 is SQL Server LocalDB Express.

To upgrade the project database to use SQL Server LocalDB Express, double click the database file and follow the instructions. Note: After this upgrade, the project database cant be modified using earlier versions of Visual Studio. To continue using SQL Server Express for this project, install it from the Microsoft Download Center.

Ok, this sure seems like it is a straight forward thing to fix, but it isn’t (doh).  So why isn’t it an easy thing to fix? because with EF and Code First and Design First more and more people are pushing up examples that don’t actually have a database in them.  They depend on EF to JIT build the database for the project (and for you) the first time you run their sample.

That of course means you can’t double click on the project database as instructed by the helpful Microsoft dialog, because there IS no project database to double click on. But wait a sec, if there is no project database how is Visual Studio 2012 determining that you aren’t using the latest greatest from Microsoft.  Well because despite what the dialog says, VS 2012 is actually NOT looking at the database, it is looking in the project’s active .config file and the connection strings therein.

So for the example of the project above in the web.config here is what comes from the author for the Connection strings entries

  <connectionStrings>

    <add name="ApplicationServices" 

         connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" 

         providerName="System.Data.SqlClient" />

  </connectionStrings>

So the problem here is the section that says “data source=.\SQLEXPRESS”. VS2012 is reading that in the .config file and warning you.  Now if there was a database file in the project you could double click it and a wizard would run that would make the necessary changes for you.  BUT if the project author left out the database, then you have a chicken / egg problem, you can’t create the database to upgrade it until you have already upgraded the database.

So how do you get around this problem? Easy, just need to make a quick change in the .config file and you are ready to rock

What we need to do is change the .\SQLEXPRESS to indicate instead the LocalDB engine and then EF Code First / Model First will work just fine and your database will be created without requiring you to install SQLExpress.

Change

connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|DatabaseName.mdf;User Instance=true"

To

connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\DatabaseName.mdf;Integrated Security=True"

And you are done.

We are doing a lot of work these days with OAuth and at its core OAuth really relies on Certificates the public and private key setup to do the things it does.  So of course I need to create a Certificate for my new shiny OAuth Resource and Authorisation Servers. No problem you think, since everything these days is about Security, this should be a snap.

Well it’s not, go ahead and google it.  I tried about 7 different variations of “create new x509 Certificate”.  Lots of links how to do that in code, but I really don’t want to write my own UI.  There is the commandline MakeCert supplied by the Microsoft .Net SDK, and if you are powershell type of person (I most definitely am not, I like GUI) then just do a search on “MakeCert” and you’ll find lots of commandline examples.

But luckily Pluralsight.com’s CTO, Keith Brown, is my type of guy (A GUI Guy) and he has a made a nice (well programmer nice) UI app for creating your own certs that he details here, or you can just download his project code from here.

Anyways, happy Cert Making.

If you haven’t heard of or used ELMAH (Error Logging Modules and Handlers for ASP.NET) by Aziz Atif,  in your Asp.net application (any flavour, MVC, forms, Web.api) then you owe it to yourself to investigate and add it to your project Forthwith!  It will save you hours off troubleshooting.

But If you want to use it on Azure, you have few choices, Nuget has a Elmah for Azure Tables.  Now if you are like me, you are using Windows Azure to host your Asp.net Web API app (and MVC app as well), with the newest edition of Sql Azure as your database backend.  Now there is a Nuget package for Elmah using Sql Server, but the script that is provided with the package to provision the SQL database with the necessary tables and stored procedures is marked up for Sql 8 and SQL 8 compatibility… which doesn’t work with SQL Azure database functionality.

Here is the solution I came up with, the modified SQL script that can be used to provision a SQL Azure database for Elmah support.

CREATE TABLE [dbo].[ELMAH_Error]

(

    [ErrorId]     UNIQUEIDENTIFIER NOT NULL,

    [Application] NVARCHAR(60)  COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Host]        NVARCHAR(50)  COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Type]        NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Source]      NVARCHAR(60)  COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Message]     NVARCHAR(500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [User]        NVARCHAR(50)  COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [StatusCode]  INT NOT NULL,

    [TimeUtc]     DATETIME NOT NULL,

    [Sequence]    INT IDENTITY (1, 1) NOT NULL,

    [AllXml]      NTEXT COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 

) 

GO

ALTER TABLE [dbo].[ELMAH_Error] WITH NOCHECK ADD 

    CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY ([ErrorId])

GO

ALTER TABLE [dbo].[ELMAH_Error] ADD 

    CONSTRAINT [DF_ELMAH_Error_ErrorId] DEFAULT (NEWID()) FOR [ErrorId]

GO

CREATE NONCLUSTERED INDEX [IX_ELMAH_Error_App_Time_Seq] ON [dbo].[ELMAH_Error] 

(

    [Application]   ASC,

    [TimeUtc]       DESC,

    [Sequence]      DESC

) 

GO

/* ------------------------------------------------------------------------ 

        STORED PROCEDURES                                                      

   ------------------------------------------------------------------------ */

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO

CREATE PROCEDURE [dbo].[ELMAH_GetErrorXml]

(

    @Application NVARCHAR(60),

    @ErrorId UNIQUEIDENTIFIER

)

AS

    SET NOCOUNT ON

    SELECT 

        [AllXml]

    FROM 

        [ELMAH_Error]

    WHERE

        [ErrorId] = @ErrorId

    AND

        [Application] = @Application

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO

CREATE PROCEDURE [dbo].[ELMAH_GetErrorsXml]

(

    @Application NVARCHAR(60),

    @PageIndex INT = 0,

    @PageSize INT = 15,

    @TotalCount INT OUTPUT

)

AS 

    SET NOCOUNT ON

    DECLARE @FirstTimeUTC DATETIME

    DECLARE @FirstSequence INT

    DECLARE @StartRow INT

    DECLARE @StartRowIndex INT

    SELECT 

        @TotalCount = COUNT(1) 

    FROM 

        [ELMAH_Error]

    WHERE 

        [Application] = @Application

    -- Get the ID of the first error for the requested page

    SET @StartRowIndex = @PageIndex * @PageSize + 1

    IF @StartRowIndex <= @TotalCount

    BEGIN

        SET ROWCOUNT @StartRowIndex

        SELECT  

            @FirstTimeUTC = [TimeUtc],

            @FirstSequence = [Sequence]

        FROM 

            [ELMAH_Error]

        WHERE   

            [Application] = @Application

        ORDER BY 

            [TimeUtc] DESC, 

            [Sequence] DESC

    END

    ELSE

    BEGIN

        SET @PageSize = 0

    END

    -- Now set the row count to the requested page size and get

    -- all records below it for the pertaining application.

    SET ROWCOUNT @PageSize

    SELECT 

        errorId     = [ErrorId], 

        application = [Application],

        host        = [Host], 

        type        = [Type],

        source      = [Source],

        message     = [Message],

        [user]      = [User],

        statusCode  = [StatusCode], 

        time        = CONVERT(VARCHAR(50), [TimeUtc], 126) + 'Z'

    FROM 

        [ELMAH_Error] error

    WHERE

        [Application] = @Application

    AND

        [TimeUtc] <= @FirstTimeUTC

    AND 

        [Sequence] <= @FirstSequence

    ORDER BY

        [TimeUtc] DESC, 

        [Sequence] DESC

    FOR

        XML AUTO

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO

CREATE PROCEDURE [dbo].[ELMAH_LogError]

(

    @ErrorId UNIQUEIDENTIFIER,

    @Application NVARCHAR(60),

    @Host NVARCHAR(30),

    @Type NVARCHAR(100),

    @Source NVARCHAR(60),

    @Message NVARCHAR(500),

    @User NVARCHAR(50),

    @AllXml NTEXT,

    @StatusCode INT,

    @TimeUtc DATETIME

)

AS

    SET NOCOUNT ON

    INSERT

    INTO

        [ELMAH_Error]

        (

            [ErrorId],

            [Application],

            [Host],

            [Type],

            [Source],

            [Message],

            [User],

            [AllXml],

            [StatusCode],

            [TimeUtc]

        )

    VALUES

        (

            @ErrorId,

            @Application,

            @Host,

            @Type,

            @Source,

            @Message,

            @User,

            @AllXml,

            @StatusCode,

            @TimeUtc

        )

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

So this is a little helper for those of you out there who, with me, are attempting to using the Azure Framework and Infrastructure for the next GREAT project.  It’s tough being a pioneer, so let me spare you some arrows.

So here is the setup You have a SQL Azure Database, and you want to use it with an Azure Website that you will have configured to run MVC or maybe WebAPI.  Now let’s say you have a legacy database, before we all learned to Code First.  Or maybe you are like me and you actually don’t really like Code First; Database is Database, and should be designed as such; Business layer and Models are different, there is an impedance mismatch yes, but if you want your code to be scalable, robust and responsive, sometimes you just have to accept that you are going to need to write code…

So for what ever reason, you decided to use the new Entity Framework 5 and you want to do a Model First approach; as of 17th of December 2012, you will have a problem, because the Model First approach requires a “special” type of Database Connection String that as well as having information about the Database connection also has information about the Metadata used by the EF5 Model First.  I won’t get into the whole discussion of whether this was a good idea to put create a connection string that is really Metadata and has the “real” connection string hidden in the Metadata, that’s a thought for another day (but yes, I think this was a bad decision on the EF teams part).  So if you don’t know exactly what I mean let me show you.

 1:   <connectionStrings>

 2:       

 3:       <add name="Rincon" 

 4:             connectionString="data source=tcp:sqlazureUrl.database.windows.net,1433;

 5:  initial catalog=[dbname];User ID=[AzureLogin];Password=[AzurePassword];

 6:  Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" 

 7:             providerName="System.Data.Sqlclient" />

 8:       

 9:      <add name="RinconEntities" 

 10:             connectionString="metadata=res://*/Dal.RinconDb.csdl|res://*/Dal.RinconDb.ssdl|res://*/Dal.RinconDb.msl;

 11:  provider=System.Data.SqlClient;

 12:   provider connection string=

 13:   "

 14:  data source=tcp:sqlazureUrl.database.windows.net,1433;

 15:  initial catalog=[dbname];User ID=[AzureLogin];Password=[AzurePassword];

 16:  Trusted_Connection=False;Encrypt=True;Connection Timeout=30;

 17:  "" 

 18:       providerName="System.Data.EntityClient" />

 19:   

 20:   </connectionStrings>

Ok, so the first connection string is the one we have been using since ASP.NET 1.0 for our Data needs, but that won’t work with a EF Model First approach, it requires the second  more complicated string, where the “real” connection string is embedded within the Model First connection string.

So what’s the problem you ask? Well currently You can’t configure the connection strings in Azure Websites to be anything but the “normal” real ones, like the first example

So I can’t show you (without giving away the secret sauce), but I have entered two Connection Strings in the Configure section for my Azure Website, and the “Custom” is suppose allow you specify something other then the defaults

You’ll notice that the Model First requires a ProviderName of “System.Data.EntityClient” but as you can see that is not provided for in the drop down list, so you’re supposed to be able to select “Custom” and specify whatever type of connection string you want, but trust me as of the 17th of December, that second one if it is a Model First Connection string WILL NOT be propagated out to the Web.config for the web site, even though you are told it should work.

So I had to solve the problem.  Mind you I also needed to support developing locally, and running Unit Tests (This project is TDD) with full gated checkin and Unit Test run and continous integration from MS hosted TFS…. Yeah in for penny in for a pound!  Let’s really put all the new stuff to the test, My goal in life is to keep @ScottGu busy trying to keep up with the promises

Anyways, you probably have come to this page looking for the fix…. well turns out the fix wasn’t easy to figure out, but should be easy for you to implement, thanks to my work. In order to pull this off we need to change how we make the DBContext for the Model First connection, and what’s worse the only way to do that is to modify the T4 file, luckily the fix is easy, once you know what to do.

I’ve submitted it as a bug to the EF team (should probably also submit it to the Azure Team), with my suggested fix, and see about putting in a pull request to update the T4 template for Model First DBContext.  You can follow my submission here As currently configured SQL Azure will not work with Model First EF DBContext.

Down at the bottom you will find a comparison of the modifications you or you could just download my Context.tt, and replace the contents of your file with mine.

Then to use it in code you would start a new context using the following syntax

var dal = <EntitiesContainerName>.NewContext(
    "<StandardSqlClientConnectionName>", 
    "<QualifiedMetadataInformation>"))

the <QualifiedMetadataInformation> string comes from the first entries on the Entity Frameworks Model First connection string

metadata=res://*/Dal.RinconDb.csdl|res://*/Dal.RinconDb.ssdl|res://*/Dal.RinconDb.msl;

In this case we would use the “Dal.RinconDb” as the substitute for “<QualifiedMetadataInformation>”

Here is an example of actually using the new functionality, this will work on both the local machine and on an Azure Website

 1: public ActionResult Test()

 2: {

 3:     using ( var dal = RinconEntities.NewContext("Rincon", "Dal.RinconDb"))

 4:     {

 5:         //do something

 6:     }

 7: }

The first parameter is the “name” of the normal standard Database connection string in the .config file, and the second is just the Metadata information we need to pragmatically build up Model First MetaData data connection.

I sure hope this can help someone else out there.

and as promised here is the difference in the original and my modified Context.tt

So here we go, if you do a model first in EF 5, the <ModelName>.Context.tt will start like this:

 1: <#@ template language="C#" debug="false" hostspecific="true"#>

 2: <#@ include file="EF.Utility.CS.ttinclude"#><#@

 3:  output extension=".cs"#><#

 4:  

 5: const string inputFile = @"RinconDb.edmx";

 6: var textTransform = DynamicTextTransformation.Create(this);

 7: var code = new CodeGenerationTools(this);

 8: var ef = new MetadataTools(this);

 9: var typeMapper = new TypeMapper(code, ef, textTransform.Errors);

 10: var loader = new EdmMetadataLoader(textTransform.Host, textTransform.Errors);

 11: var itemCollection = loader.CreateEdmItemCollection(inputFile);

 12: var modelNamespace = loader.GetModelNamespace(inputFile);

 13: var codeStringGenerator = new CodeStringGenerator(code, typeMapper, ef);

 14:  

 15: var container = itemCollection.OfType<EntityContainer>().FirstOrDefault();

 16: if (container == null)

 17: {

 18:     return string.Empty;

 19: }

 20: #>

 21: //------------------------------------------------------------------------------

 22: // <auto-generated>

 23: // <#=GetResourceString("Template_GeneratedCodeCommentLine1")#>

 24: //

 25: // <#=GetResourceString("Template_GeneratedCodeCommentLine2")#>

 26: // <#=GetResourceString("Template_GeneratedCodeCommentLine3")#>

 27: // </auto-generated>

 28: //------------------------------------------------------------------------------

 29:  

 30: <#

 31:  

 32: var codeNamespace = code.VsNamespaceSuggestion();

 33: if (!String.IsNullOrEmpty(codeNamespace))

 34: {

 35: #>

 36: namespace <#=code.EscapeNamespace(codeNamespace)#>

 37: {

 38: <#

 39:     PushIndent(" ");

 40: }

 41:  

 42: #>

 43: using System;

 44: using System.Data.Entity;

 45: using System.Data.Entity.Infrastructure;

 46: <#

 47: if (container.FunctionImports.Any())

 48: {

 49: #>

 50: using System.Data.Objects;

 51: using System.Data.Objects.DataClasses;

 52: using System.Linq;

 53: <#

 54: }

 55: #>

 56:  

 57: <#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext

 58: {

 59:     public <#=code.Escape(container)#>()

 60:         : base("name=<#=container.Name#>")

 61:     {

 62: <#

 63: if (!loader.IsLazyLoadingEnabled(container))

 64: {

 65: #>

 66:         this.Configuration.LazyLoadingEnabled = false;

 67: <#

 68: }

 69: #>

 70:     }

 71:  

 72:     protected override void OnModelCreating(DbModelBuilder modelBuilder)

 73:     {

 74:         throw new UnintentionalCodeFirstException();

 75:     }

All you got to do is replace the start like this

 1: <#@ template language="C#" debug="false" hostspecific="true"#>

 2: <#@ include file="EF.Utility.CS.ttinclude"#><#@

 3:  output extension=".cs"#><#

 4:  

 5: const string inputFile = @"RinconDb.edmx";

 6: var textTransform = DynamicTextTransformation.Create(this);

 7: var code = new CodeGenerationTools(this);

 8: var ef = new MetadataTools(this);

 9: var typeMapper = new TypeMapper(code, ef, textTransform.Errors);

 10: var loader = new EdmMetadataLoader(textTransform.Host, textTransform.Errors);

 11: var itemCollection = loader.CreateEdmItemCollection(inputFile);

 12: var modelNamespace = loader.GetModelNamespace(inputFile);

 13: var codeStringGenerator = new CodeStringGenerator(code, typeMapper, ef);

 14:  

 15: var container = itemCollection.OfType<EntityContainer>().FirstOrDefault();

 16: if (container == null)

 17: {

 18:      return string.Empty;

 19: }

 20: #>

 21: //------------------------------------------------------------------------------

 22: // <auto-generated>

 23: // <#=GetResourceString("Template_GeneratedCodeCommentLine1")#>

 24: //

 25: // <#=GetResourceString("Template_GeneratedCodeCommentLine2")#>

 26: // <#=GetResourceString("Template_GeneratedCodeCommentLine3")#>

 27: // </auto-generated>

 28: //------------------------------------------------------------------------------

 29:  

 30: <#

 31:  

 32: var codeNamespace = code.VsNamespaceSuggestion();

 33: if (!String.IsNullOrEmpty(codeNamespace))

 34: {

 35: #>

 36: namespace <#=code.EscapeNamespace(codeNamespace)#>

 37: {

 38: <#

 39:      PushIndent(" ");

 40: }

 41:  

 42: #>

 43: using System;

 44: using System.Configuration;

 45: using System.Data.Entity;

 46: using System.Data.Entity.Infrastructure;

 47: using System.Data.EntityClient;

 48: using System.Data.SqlClient;

 49: <#

 50: if (container.FunctionImports.Any())

 51: {

 52: #>

 53: using System.Data.Objects;

 54: using System.Data.Objects.DataClasses;

 55: using System.Linq;

 56: <#

 57: }

 58: #>

 59:  

 60: <#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext

 61: {

 62:      

 63:     public static <#=code.Escape(container)#> NewContext(string sqlConnectionName, string metadataSpecification)

 64:     {

 65:         var sqlConnection = ConfigurationManager.ConnectionStrings[ sqlConnectionName ];

 66:         var entityBuilder = new EntityConnectionStringBuilder();

 67:  

 68:         entityBuilder.ProviderConnectionString = sqlConnection.ConnectionString;

 69:         entityBuilder.Provider = sqlConnection.ProviderName;

 70:  

 71:         entityBuilder.Metadata = string.Format( @"res://*/{0}.csdl|

 72:                                                                 res://*/{0}.ssdl|

 73:                                                                 res://*/{0}.msl" , metadataSpecification );

 74:      

 75:         return new <#=code.Escape(container)#>( entityBuilder.ConnectionString );

 76:     }

 77:  

 78:     public <#=code.Escape(container)#>()

 79:         : base("name=<#=container.Name#>")

 80:     {

 81: <#

 82: if (!loader.IsLazyLoadingEnabled(container))

 83: {

 84: #>

 85:         this.Configuration.LazyLoadingEnabled = false;

 86: <#

 87: }

 88: #>

 89:     }

 90:  

 91:     public <#=code.Escape(container)#>(string nameOrConnectionString)

 92:         : base(nameOrConnectionString)

 93:     {

 94: <#

 95: if (!loader.IsLazyLoadingEnabled(container))

 96: {

 97: #>

 98:           this.Configuration.LazyLoadingEnabled = false;

 99: <#

 100: }

 101: #>

 102:     }

 103:  

 104:  

If you weren’t able to attend TechEd New Zealand 2012 you can view the recordings of the sessions here
TechEd New Zealand 2012 (Channel 9)

Well it’s just been a little over a week since I got back from TechEd and I thought I would take a moment to reflect on my time at TechEd NZ 2012 as an attendant and as a Speaker.

First up, I would like to call out the session that I found the most inspirational, the one that reminded me why I find Programming to be such a great profession, and why I am lucky to be in this line of work.

Azure, the Platform for Internet of Things Applications Presented by Bryn Lewis

So what was so special about this presentation? Sadly the final demo step didn’t actually work (the demo gods were not kind to Bryn), but the idea that Bryn had, what he was trying to accomplish was at the heart of what technology should be about, making the lives of people better and safer while at the same time having fun doing it. His idea is to use off the shelf components for the .Net Micro Framework and Azure data bus such that it would allow for early earthquake shake approach warnings for people in their homes and businesses.  Open sourced and free is his goal, and I continue to applaud his effort and hope I can help him with it some day.

The second most interesting thing to me was that the Opening Keynote, while it had the requisite “marketing”, was mostly NOT marketing and dare I say, even Microsoft Free.  Jer Thorp gave a talk about data and the world and looking at his slides, his art, his work, I’m guessing he probably doesn’t use Microsoft software or even regular PC’s much in his daily affairs (I’d be willing to bet pretty big, his personal machine is a Apple). His talk was inspiring, thought provoking, and made me go “aaawwww” then on deeper reflection “ooohhhh”; As Jer himself hoped it would.

While I have been a speaker many time for lots of groups this was the first time that I was a presenter at a “real” conference, with big sponsors, big bucks and big audiences.  I have been to many conferences as well over the years, but attending a conference as a speaker is a completely different experience.  The first thing you have to know is it’s really COOL being able to go into the “private” areas.  The speakers lounge with hard wired networking, drinks and biscuits and fruit and food with no crowds, comfortable chairs and nice large tables is a definite Perk! A place for us important people to retreat from the rabble… LOL.

But it is the “rabble” that made the conference so much fun, all our inner geeks and nerds hanging out on full display.

Oh yes and since I was a speaker I guess I should give you a link to view or download my presentations.  Here then is Live Tiles, The Good, The Great and The Ugly

I actually did two presentations, but the first one was a dog's breakfast!  The attendees REALLY didn’t like it, and I can’t say I blame them in the slightest, it just didn’t live up to TechEd standards!  I would recommend that you skip it if you find it on Channel 9.

But the one I linked to above (Live Tiles, The Good, The Great and The Ugly), was very well received and I’m actually rather proud of it.  I certainly learned a lot in the process of putting it together and am really looking forward to doing Metro Development.  I enjoyed myself enough that I’m going to start working on my TechEd 2013 submissions NOW and hope to see you all there next year.

And that would be Me.  I submitted two talk submissions, and they picked them both up.

The first one is

Live Tiles; The Good, The Great and The Ugly

Track: Building Metro Style Apps: Windows 8 and Windows Phone

Session Type: Breakout Session

Live Tiles are all the new rage and talk of the world in WP7 and with Windows 8 release just around the corner they are going to become even more important to your software. Your users are going to come to expect your live tile to actually be alive and meaningful. To do a Live Tile correctly however takes planning and an understanding of the Metro Experience Live Tile update architecture and life cycle. I'm hoping to help you to make sure your Live Tile is Great and plays well with Metro. In this session you will discover what makes for a good Live Tile, a bad Live Tile, and a "just don't do that". We'll walk thru

• determining what should be on a Live Tile (and what shouldn't)
• How to make the tile engaging and informative without being distracting.
• Creating a Web Service that feeds the Live Tile in the way that best works with the Metro UI life cycle.
• Audience Participation will be greatly appreciated!

Many of the Live Tile examples that are currently out there that show you "how" to do Live Tiles are not Best Practice! We look thru some not so good examples and really understand WHY they are should be avoided. For Live Tiles to be useful to the End Users we must use our great power with great responsibility

And the next one is:

WP7 Silverlight DataBinding:

How exactly do you do that?

Track: Building Metro Style Apps: Windows 8 and Windows Phone

Session Type: Breakout Session

We have all seen how Databinding works in WPF and in Silverlight, you work in Expression Blend, you do some binding... but what REALLY is going on. How does this binding work, how are contexts set up?

I set out to pull back the curtain on the magic and help you to fully understand the significant amount of plumbing that is taken care of for you with {binding Name} an why sometimes it seems to work and other times...

We will be concentrating on just how the binding works / what the notation means. Attendants will need to be very familiar with Entity Frameworks, SQL CE 3.5 local storage, Linq and Lambda notation to follow the demos. A cursory understanding of MVVM pattern concepts is also recommended.

I hope to see you all there.

I’m really hoping to be able to present this year at TechEd (I’m a natural ham and love being on stage).  I’m really interested in Live Tiles, but I have a fear that as so often happens some people won’t truly understand what a Live Tile should and should not do, and will dilute or even ruin the Live Tile experience. I’ve been doing a lot of reading on the theory behind the LT concept, and I’m not sure that Microsoft is doing the education it really should on this subject.

I mean how many times have we seen a cool UI idea, that gets left by the way side because it gets abused.  I think we all need to do what we can to make sure that LT UX remains relevant for end users for years to come.

So anyways, I just put this Content Submission in for a session at TechEd 2012 in New Zealand.  What do you think, is this something you would like to see?  And if it was to be picked up, what sort of questions do you have on this subject, I’d love to hear your thoughts!

TechEd New Zealand 2012 Call for Content Submission Confirmation

Submitter Information

Matthew Hintzen

Topic Information

Track: Windows 8: Metro Style Applications

Session Type: Open Session

Session Title:

Live Tiles, The Good, The Great and The Ugly

Description
Live Tiles are all the new rage and talk of the world in WP7 and with Windows 8 release just around the corner they are going to become even more important to your software. Your users are going to come to expect your live tile to actually be alive and meaningful.

To do a Live Tile correctly however takes planning and an understanding of the Metro Experience Live Tile update architecture and life cycle. I'm hoping to help you to make sure your Live Tile is Great and plays well with Metro.

In this session you will discover what makes for a good Live Tile, a bad Live Tile, and a "just don't do that". We'll walk thru:

• Determining what should be on a Live Tile (and what shouldn't).
• How to make the tile engaging and informative without being distracting.
• Creating a Web Service that feeds the Live Tile in the way that best works with the Metro UI life cycle.

Audience Participation will be greatly appreciated! Many of the Live Tile examples that are currently out there that show you "how" to do Live Tiles are not Best Practice! We look thru some not so good examples and really understand WHY they are should be avoided. For Live Tiles to be useful to the End Users we must use our great power with great responsibility

Microsoft is known for making great developer tools, Hell that’s why I’m a self confessed Microsoft FanBoi, it’s just EASY AS to program using their tools… with one exception, Ajax calls.  The built in tools in Visual Studio / IE are great for a lot of things but Asynchronous Javascript calls is not one of those things.

This post is for @tobint and @HumanCompiler on twitter.  I’ve been trying to defend using IE as my primary browser to my fellow worker @redjungle (Phil Gale to his friends), but as we have done more and more advanced Browser based applications with lots of jQuery and javascript and Ajax calls I finally realised that ultimately he was right.  For that job the correct tool is Firefox.  I complained about it to @HumanCompiler (suggesting that MS buy up company that makes firebug and make it the tool for IE Ajax debugging) I was referred to @tobint, who admitted he wasn’t a fan of FireBug, but wanted to know why I like Firebug, how it is “better” then the built in IE tools.

Trust me @tobint as a microsoft fanboi, it takes a lot to make me use someone else’s tools, (hell I used TFS 2005… so that shows I have a high pain threshold), but I just can’t disagree anymore.  If a tools job is to get in, get the info I need, and get out (and I believe that is what a tool should do) than for Ajax Firebug wins.

Let me show you with screen shots.

I have a jQuery Modal that asks for some input information, the user clicks it and then clicks save and an ajax call is made. Now of course my code wouldn’t have ANY bugs in it, so it will all work, but let’s imagine that maybe someday I don’t code up my

[HttpPost]
public ActionResult

correctly (yeah, I know, like that would ever happen) like maybe I put in a throw new NotImplementedException( ); by accident

So here is what I have to do to see the problem in IE

I load up the page and get my model and all I see is this

And it just sits there doing nothing (except spinning), no problem let’s fire up the Dev Tools by pressing F12… Whoops for some reason the F12 isn’t working (maybe that modal is interfering, not sure, but that’s ok, I’ll just go get it from the tool tips at the top)

It’s just two clicks, and here is what I get

Ok, so without looking at the help files, where do I go?, gee I’m not sure, maybe I need to refresh my page, and it will show me something.

so I do that and … Nope still nothing “obviously” wrong… Now at this point I happen to know (cause @tobint told me) that I can use the network tab, so I’ll go there.

Hmmm, nothing, I have to click “Start Capturing” I guess, hang on a moment didn’t I have to click on the Open Developer tools, doesn’t it seem like if I am asking for development assistance with my web page, that it should start capturing by default, I mean isn’t that really the logical thing to have happen?  Oh well, enough whinging, click the button and refresh the page again.

Now I get

It actually took me putting in this screen shot before I visually located the problem line, can you find it in one glance?  It’s the one that has the Red 500 (not exactly obvious, is it?).  Ok so click on that row and you get… a highlighted row… Ok, so double click on it

Ok this looks a little more useful, so let’s go tot he Response header and see what the return was

Yeah… that’s not too helpful, Let’s go look at the Response Body tab, maybe that will have more information.

Well there is the exception detail, inside a bunch of HTML, now I can extract the information I need from this page, but it isn’t going to happen in a glance, I’m going to have to do a little visual parsing.

Keep in mind when you are doing Web 2.0 development this is the most common action you will have to debug

Now let’s see the steps necessary in Firefox using Firebug.

I open Firefox and follow exactly the same steps as I did in IE (no pre-prepping the browser) to get to the problem screen

I don’t know the keyboard shortcut for Firebug, so I will just go up and click on the icon for it (only one click)

And I have

I don’t know about you, but I’m pretty sure I KNOW EXACTLY where thing broke after just one click. Let’s make sure, let me click on that bit row that is RED, and after a single click I have.

Well that’s good, here is my response html that I can parse thru and find the error, but wait, what’s that “HTML” tab? let’s click on it and see…

That sure seems a lot easier to read / debug now wouldn’t you agree @tobint?

So with IE, even if I did everything as quickly as I could it would take 8 clicks to find the information I want, and even then I would have to parse the raw HTML error page.

Firebug in Firefox took 3 clicks all in one window.

Gentlemen of the Jury… I rest my case!

Please feel free to let me know what you think of my comparison at @matthewhintzen on twitter.

Update

the instructions for setting up P4 to work with TFS Merge are a little more difficult and best explained here, this works with 2010 as well

Using P4Merge with Visual Studio 2008 and TFS

So it took me only one day to see that I had been blind, and I switched over to Perforce’s merge tool as my default Diff tool in TFS on all my dev machines (as detailed here ).

However, diff wasn’t enough I wanted the Merge Capabilities of Perforce Merge as well, that’s a slightly different command.

and in looking up the right way to call Perforce Merge as a Merge tool, I saw right away that for merge capabilities it requires %2 to come before %1. 

Yesterday’s post detailing how to set up Compare had %1 then %2.  While for Diff purposes this would work fine, that means that you would have a difference between Diff and Merge in the presentation of your source and target files. So just to be OCD, I updated the compare operation to also put the Local version before the Server version in the perforce display.

Thanks to the following links for helping me to figure this out.

Installing and Using P4Merge in Git for Windows

diff/merge configuration in Team Foundation - common Command and Argument values