It depends on your priorities to a great extent.
Edinburgh – nice city to live in but expensive. Ok local stuff – GT / Innerleithan less than an hours drive away – real mountains not much more.
Sheffield – good access onto the hills with plenty of good riding – Not a lot of trail centre type stuff. Nice place to live as well.
Tweed Valley – good access to the trail centres and other good local riding – somewhat provincial and uninspiring towns.
Wales I don't know and nothing further south bar perhaps the south west is worth considering from an MTB point of view.
One of the magazines tried to work this out a year or two ago and Edinburgh was their answer.