A lot of what follows in based on my own research into this, but not experience, so take this fwiw.
The general consensus is that all systems other than the Z06 Ti and the Corsas will drone. How bad, and whether that's objectionable, is very much a matter of the individual combination and the owner's tastes/sensitivity. Drone is considered a much more serious problem on automatic cars because you can't "shift around it".
I went from stock to the Z06 Ti, partly because they get great reviews, and partly because they are amazingly cheap - $450 or so used mint. Considering that the discount new price from GM is almost $2k, that's a deal.
They are a little louder than stock, and have a better tone, but you aren't going to notice the difference inside the car except when getting on it pretty hard - then there is a noticeable improvement.
I've come to the conclusion that I want louder. If those Harley guys can get away with it, so can I.
I rarely listen to music in the car, as I much prefer to listen to the engine. I have a set of American Racing headers on order, with cats and also an off-road pipe. I'll probably try it with cats first and then the off-road pipe (assuming I can find someone who will install it). A fair number of folks have told me that they thought the Tis with headers was the best overall sound. We'll see.
I asked this question on Corvette Forum: "Just how much louder are headers?" Responses varied a lot (go figure), with some saying it made a big difference, and some saying not much at all.
There are some straight-through and near-straight-through catback systems. If you just want a considerable increase in volume but aren't going for a hotrod engine build, these are perhaps a better way to go than headers. Candidates include B&B Bullets, Corsa Stingers, the Stainless Works chambered sets, and the Borla XR-1 and straight pipe systems. However, if you do plan to go to headers later, reports are that these systems can be very loud with headers.
It's quite common for guys to go through several sets of catbacks looking for the right tone. Yes, that can get expensive.
Thankfully, they are easy to sell used, and it's often the case that a guy with a quiet (not stock) set that wants to go louder can arrange a swap with a guy with a loud set who is tired of listening to it. It's also common that when you make a major change to your combination, like adding headers, a stroker, or a big cam, that it can change the sound enough that a catback change is in order.
There are various sites that have sound and video clips, but they are only so-so accurate. Two to try are ls1sounds.com and the video section at ls1tech.com. The manufacturers also often have sound clips.
Another thing to do is to attend Corvette shows and runs, where you can hear different sets in action. This can be a little time-consuming, but may be the best way to see what suits you without buyin' & tryin'.
Corsa will be at the upcoming UVP on the 18th. They often have pretty sweet deals at shows and may offer onsite installation (or may not - I haven't heard). Corsas do have the reputation of OEM quality and no drone - they are the only aftermarket exhaust that is sold and used by GM. Point is, they can be a good place to start as they are easy to sell later. They come in two sound flavors, Touring (about the same as stock), and Indy or Pace Car (louder). If you want a major step-up in volume, neither is likely to be enough.
Get out there and dig for videos. There aren't a ton of really good ones, but you can find some that are great and give a good feel for what an exhaust is like.
Good luck & have fun!