Going to chime in here with the others.
No, the ride is not scary at all.
Yes, lift off is quite intense and feels about as accurate as you'd imagine it to feel.
So does the "slingshot" portion and the period of weightlessness.
It's quite amazing actually.
I'm prone to motion sickness and wouldn't dare step on the Tea Cups for example.
MS is quite amazing the first time though - I would consider it something that should be experienced.
Pay attention to the instructions though, head against the headrest - eyes straight ahead.