Unfortunately it didn't really took off, but it's a shame because it's a good, polished and complete implementation that allows for some advanced scale-out topologies (it can also be used as a foundation for data dependent routing and map/reduce scenarios).

<rant>I guess it's not much used because people like to reinvent the wheel every time (by manually implementing queues using tables with all the traditional concurrency problems) instead of learning something a bit more complex.</rant>

Anyway, it's not going to be thrown away anytime soon as it's used in other parts of the engine (e.g. SMTP mail integration, Server Events and Query Notifications).