I haven't had any issues with cooling, although I bumped up the allowed hard drive temperature in HD Sentinel a bit after my first settings proved too conservative. I was getting far too many emails about the temperature when it wasn't really a problem. Check the specs of your hard drives carefully and set a realistic temperature for alerts if you do use HD Sentinel.
I just use the standard
Lian Li PC-C60 cooling arrangement, which is two 14cm input fans on the right with cross flow to the left, as per this image (front of the PC is at the top in this image, so my right and left are reversed):
I believe in using positive pressure in a PC case to avoid sucking dust in through all the little gaps and holes that are unavoidable. The two input fans are filtered. Rather than drilling more holes, I tend to close them off, if they don't suit my cooling design. But using positive pressure means I mostly just leave what holes are there, as I have with this HTPC case.
In addition to the two input fans on the right I have a Noctua NH-C14 CPU cooler that pulls air in from the top through a 14cm fan hole, which is also filtered. The Noctua itself has two quiet 14cm fans.
All four of the 14cm fans are set to run quietly, and are speed controlled by the motherboard.
The hard drives sit at the front left, where there is another 14cm fan hole, so most of the air blows over the hard drives and out of the case.
Finally, the
Seasonic SS-660XP2 power supply has a 12cm fan in it that stays off most of the time, but will turn on if the power supply warms up, using its "Patented Hybrid Silent Fan Control". It draws warm air out of the case and through the power supply when it runs. Even when the power supply's fan is off, the positive pressure created by the other fans ensures a certain amount of air flows out through the power supply. That is partly why the power supply fan rarely turns on.
I still need to replace the standard Lian Li fans with quieter versions at some time, as they can be heard close up. I'll probably put Noctua fans in there, as they seem to be the quietest and best at the moment. I don't have a discrete GPU in the case at the moment, since I am using the Intel iGPU of the i5-3570K CPU. When I do add a discrete GPU the cooling requirements and flow will change a bit, but I think what I have will be enough.
I assume your water cooling has a fan that blows air out of the case, and probably runs at a reasonable speed. In that case, if your two 12cm input fans are running at low speed for quiet operation they may not be providing much positive pressure in the case, if any. So any additional holes may result in air being sucked into the PC case, bringing dust with it, rather than blowing air out the holes. I wouldn't be adding any extra holes unless you know that your two 12cm fans are pushing in substantially more air than the water cooling fan is pushing out of the case. I don't think I have ever seen anyone put holes in the case behind the motherboard, although I have seen it discussed, as people were worried about heat build-up. However convection takes care of that, as long as there is sufficient flow into and out of the case, the hot air will be taken away.