Like your sister, I love Manchester. Honestly, it's great. It's got everything you really want in a city (nightlife, shopping, culture), the cost of living is low (more money to spend on other things!) and the public transport fantastic.
It's not too busy for my liking - I grew up in a smaller city, and I find London too big and busy personally, but I'm fine with Manchester.
Pretty much anything that you want to find club-wise in Manchester you can find - there's a LOT of nightlife on offer.
In terms of societies, see
Sports societies
http://www.sport.manchester.ac.uk/athletic-union Non-sport societies
http://manchesterstudentsunion.com/groups#club-society for full lists
The Union is quite good at what it does, but it should be borne in mind that it's not the social hub of the university. This is because, unlike some universities, Manchester has a lot of other options for nights out, so the SU can concentrate on doing other things. However, the SU does include the Academy, which is one of Manchester's biggest live music venues.
What normally happens in lectures is that there are a lot of students in the lecture theatre (hundreds, sometimes). But lectures aren't the place to form a relationship with your lecturer. You should do that in the tutorial / workshop / labs, which typically only have about 15 students there. So, it's entirely possible to build up a relationship with teaching staff regardless of the size of lectures. However, I think you can safely ignore the bit about Brian Cox. He's a celebrity who teaches one 10 credit module in first year (bearing in mind that you do 120 credits per year, this is pretty insignificant). The cynic in me says that he's kept on the payroll primarily for student recruitment purposes. There are much more interesting staff at Manchester e.g. Jeff Forshaw, who wrote several books with Brian Cox, and the two Nobel prize winners.
However, Manchester is a very good uni for physics, so don't let Brian Cox (or the lack thereof) put you off. For most people, the foundation year is a chance to get into a uni that they would otherwise never have got the chance to go to, and friends who did physics foundation say they'd do it again in a heartbeat.
Have you stayed with your sister at all in Manchester? That might help to make your mind up. Ultimately though, if I had my time over again, I'd choose Manchester - I've never regretted coming here at all, and I will be rather sad to graduate.