We just went on July 11 and I was apprehensive about DD4, but she ended up loving it. Of course, all kids are different, but my little one hated Soarin', Test Track, and even Goofy's Barnstormer but wanted to see Fantasmic again.
I did prepare her before that there were scary parts, so we had a deal she could cover her eyes and hide in my arms if she got scared. She did have to do that a few times but only briefly.
We sat about halfway back, so we weren't too close, and if you're worried about someone getting scared, I'd suggest you avoid the very front. Also, if you sit near the front, you could get wet by water spray; if you're back beyond the first few rows, you should be okay.
It does get loud, and there are fireworks, small explosions, and lasers. There are also frightening appearances by villains and a huge dragon.
If you do try to go, you could sit near the end and make a hasty retreat if your DS gets scared during the show.