Thanks for the reminder, yes, that's what I was talking about. Correct, we've found with that particular project that even though we used SQL Session, we still had issues with users jumping from one server to another in the middle of a request. Sticky session was the solution at the load balancer level.
For your implementation, I'm not familiar with Azure Traffic Manager. We have several web farm implementations and none of them use that, we are simply using the built-in features Azure has for scaling out and directing traffic. We have one particular site with 3 servers in the farm and averaging 1.1 million hits per month and all works without issue.