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.