I DO actually have a fort like this!
The main 'fortress' consists of six stories, three plumbing and three filled with living quarters, workshops, etc. It's... actually not as impressive as it sounds, mostly just a large 'C' shape ringed with structures. A 1-tile wide grated aqueduct runs through the center of the main tunnel in every work floor.
The aqueducts are fed from a large reservoir, something like 10x15x30 tiles (with a few unused for supports for the walkways above). A maintenance stairway runs down one side, covered by a locked hatch (to prevent maintenance from flooding), and a grated drain at the bottom allows the reservoir to be drained... slowly. The drain uses the old trick of digging to the edge of the map, smoothing the edge, and then carving fortifications to allow water to drain off into nothing. I'd like to imagine I'm creating an aquifer somewhere.
The reservoir has two methods of feeding the aqueducts that run through the fortress: first are sets of three pumps... which work remarkably fast and because I designed them wrong, will flood the fort if I ever turn them on again. Make sure they're pumping water UP onto the aqueduct level so there won't be pressure issues, or you'll flood your fortress. It also helps to plan ahead and use lots of doors in the main hall until you're absolutely certain that you aren't going to accidentally flood your fort when you fill the aqueducts.
The aqueduct drain system, which also functions as a slower fill, is a series of diagonal openings with floodgates installed to allow water to either slowly trickle in or out. Each floor of aqueducts acts separately, meaning it's possible to drain one floor without draining the others. Since they all hook directly into the reservoir instead of having their own separate drains, though, draining one of the aqueduct levels essentially means draining the reservoir.
As for filling the reservoir, it's just a pretty simple task of finding a nice river or ocean and re-routing it to serve your purposes. If you want the water to be drinkable, remember that salt water needs to be passed through a pump to filter it and then becomes re-contaminated if it touches natural stone.
It's definitely a big project, but it's also REALLY fun to try and pull off, and once you get it running it looks really impressive.