Wake-On-LAN requires either
1) An Airport Extreme Base Station or Time Capsule (with current firmware)
or
2) Something to send the "Magic Packet"
If you have option 1, the Mac that goes to sleep will "hand off" its "identity" to the router, and when someone tries to access something on that Mac, the router with wake up the sleeping Mac, and hand back the "identity" so the requested Mac can accommodate the request.
If you don't have option 1, you have to go with option 2, which requires a specially formatted packet sent via ethernet (or wifi, if supported) to the sleeping Mac to wake it up. I've done this before, but its by no means automatic like option 1.
I haven't had an issue waking a computer up immediately after putting it to sleep manually to test this feature.
The services you want to use must be broadcast via Bonjour for option 1 to work. I do not know if the router will recognize ALL Bonjour services, or just the Apple-branded ones. They do note in the KB article "
Works on any service Because Wake on Demand uses Bonjour, it can handle any service that registers with Bonjour regardless of the underlying protocol" so any Bonjour service *should* work, but I haven't tried.
More info here:
Wake on Demand lets Snow Leopard sleep with one eye open | Mac OS X | MacUser | Macworld and
Mac OS X v10.6: About Wake on Demand