Sorry, but I just don't think he's thought this through at all, or at least not thought about how it affects anyone but himself (and even then it's a bit patchy and rose-tinted).
Your children may well see their life as being in London, with their friends and student life. They may feel a bit unsettled by the 'family home' whisking a couple of hundred miles away, and at a time when stability would give them a better base to try out their new adulthood (poorly expressed, but I'm hoping you get what I mean). Especially your DD, in 6th form - does she really need this distraction when she's on the final stretch of her A levels? Travelling expenses (in time as well as money) between London and the North could subtly dissuade them from frequent visits.
You would have to give up your job, a job that you enjoy and have been doing for 9 years, so is a big part of your life; and move to a part of the country where you may have in-laws but few (no?) personal friends. You would have to build up a new network from pretty much scratch. And you may not see your children as often as you would had you not moved from London.
Your DH will need to spend "about 1/2 his time in London". Presuming this will be something along the lines of weekly trips rather than e.g. two months at a time, he will be wasting spending a lot of time travelling. Would you be accompanying him every time (tiring), or would you be left all by yourself hundreds of miles from where your '"life" has been here for more than 30 years.' ?
"DH has the view that he wants a real, proper garden" . Well, what's stopping him having one in London? If it were really that important to him, he'd have done something about it in the last 30 years. No, that sounds like a bit of a pipedream - he sees himself sitting in a garden having a beer/cuppa, in a 'master of all he surveys' kind of way. It's not even a pipedream, just an image of how he thinks he should be, and would be, if only he were 'back home'. I think you are quite right that you will be the one wielding the spade.
And he has "his workaholic tendencies" . I wonder if this might translate into, despite supposedly splitting time between London and the North, him still gravitating more to London, as workaholism seems to always entail the notion that the office will fll apart without them actually being there? Leaving you simmering resentfully and alone in the North?
Like I said, I think he has a very rose-tinted view of 'moving back'. My parents lived abroad for over a decade, really missing 'home'. Well, when they did come back to live, they really missed abroad. It completely threw them that life had moved on while they were away; that their parents/siblings/children/friends had not been wrapped in cotton wool and stored asleep in a box while they were away, that buildings had been built/demolished, new shops opened/old shops closed. That the 'home' of their memories existed only in their memories. Is your DH sure that this is not also the case with him?
(BTW, I have some sympathy with your DH, as a Scot whose life has been in England for over 20 years; but I know the Scotland of my teens and twenties is history and have made my life here.)