The difficult thing is- is he the main breadwinner? If not, I'd be tempted to say- if he's going to make it in academia, he can do so in more than one place, there won't be one fellowship, and he can continue to look around in the Midlands. However, the risk there is that he picks up another temporary contract for a couple of years and doesn't really provide you with the security you need as a family, to get mortgage etc- but if you are the main earner, then this may not matter.
If he is the main earner, then going into a secure long-term contract or permanent position is a good idea, wherever it is. Academia is notoriously insecure, and having 5-10 years, which would be legally permanent would be a fantastic thing and make his career.
I really think you were unrealistic to think you would meet someone and they would get a job 'in the Midlands'- staying in the UK with a professional job is probably as close as you'll get to stability, and not just for academia, I don't know many professional couples who haven't had to move at least cross country for one or other of their jobs at one time or another. Of course being near family and friends is very important, but I wouldn't sacrifice a permanent job to do so nor the job of the main earner- it's just not possible to do so.
I am an academic, have lived apart from my husband for some years on occasions, although we do prefer living together of course, and would do again if the perfect job came up and the other one didn't want to move, we like the world of weekend dates, work in the week, and I know lots of other academic couples who are both academics at different institutions who don't live together all the time (called LATS, Living Apart Together). I have children and know lots with children that do this too!
That said, there must be possibilities for him in the Midlands-great unis there, lots of them, so perhaps he's just seduced by this offer and needs to get on jobs.ac.uk and filter by Midlands and start looking far more seriously around at what's in your area rather than just fixing on this Glasgow option. Glasgow is a good city for academics and their families though, know friends there.