Are there any real world scenarios where we cannot do without Visual Studio workflow? That is where default SharePoint workflows or Sharepoint Designer won't suffice?
This is a pretty open ended question and the answer would be VERY subjective.
You can get pretty creative with SharePoint Designer's "Declarative Workflows" and you can do some pretty complicated things by creating custom actions that do just about anything.
What I love about it as an option is that it does a good job with simple automations that cannot be done with calculated fields that would otherwise have been done with event receivers (heavy dev effort). It also offers the promise of enabling the information workers to design, create, and maintain workflows without ITs assistance. That last part isn't seen as an advantage to some.
The big limits that I've hit more than once are:
- The need to treat it like an application that can be deployed to multiple environments. Things are a little better in 2010, but in 2007 a workflow is bound to a specific list or library on a specific site. There can be severe challenges in moving to different sites or different environments making updates difficult at best.
- The need to support iterations or any advanced control logic. Since it can only move forward, never back and never repeat, you end up having to run through multiple workflow instances instead of having all of the detail together in one workflow record (instance).
- In many scenarios you will end up needing custom approval or business forms instead of the basic forms associated with tasks in a SPD Declarative workflow.
If you want to do more advanced stuff with SPD workflows, check out Muhimbi's Workflow Power Pack.
This product basically allows you to inject c# code into SPD workflows.
There are many cases that can only be handled with a custom WF workflow, but here's a simple example of something that would effectively be impossible to replicate with SharePoint Designer workflows. Let's say I want a new content type called "Legal Document". Any time a Legal Document is created in any document library in any site, it should be routed to a legal team for approval. There is no way to enforce this with SPD/declarative workflows because they are always tied to a specific list. However, custom workflows can be attached to a content type and thus be executed anywhere that content type is used.
Another pretty common scenario is a state-machine style workflow. While you can try to "emulate" a state machine with the sequential workflows allowed in SharePoint Designer, it gets extremely messy, if not downright humanly impossible, to try to keep track of it once you have, say, two different states each of which can end up in two or three other states. I've managed to model a simple 3 "state" workflow in SPD, but even that was pretty ugly.