I am through all of this on the other side so I will give you my take. My youngest child is now in year 12 so just finishing first year of sixth form. I became a sahm when Ds1 was 2 having previously been part time. My disability became worse so it made sense to give my body a break, it was meant to be for a couple of years.
It is absolutely the best thing we ever did, it completely worked for us, Dh was able to concentrate on his career enabling me to stay home and have a lifestyle that doesn't involve scrimping and scraping in fact far from it. We didn't have any family around to provide any childcare for us and I had friends with older children who I watched pour over calendars with their husbands working out who was taking time off when, when they could get a 2 week overlap over summer to go on holiday, working out summer club schedules etc. School holidays are 13 weeks of the year, unless you work term time only no one has that much leave from their paid job.
I was able to clean, cook, shop, do all the school runs, all sick children pick ups or have them at home without worrying about a boss being pissed off. Dh made it to every sports day, was a completely involved hands on Dad who relished his parenting role. He has a very strong loving bond with both our sons. He literally finished work and came home to a meal prepared for him. All the housework was done in the day by me when the children were older, none on weekends leaving us both free to spend time together as a family, one on one with each child and time on our own too with no resentment. But he also did bath times, bed times, came home and rolled his sleeves up and got straight into any parenting duties, it wasn't all left to me, when he was here he was available.
Dh's Mum had been a sahm, mine had worked full time, we used to get ourselves ready for school and walk there ourselves. I was 8 with a sibling to take with me as my Dad worked away a lot like your Dh does. Our relationship has worked, we are incredibly happy but there has to be respect from your Dh for the job you do (seems appreciated if you pay someone to look after your child but if a parent does it somehow it is lazy or easy) you need to have the same thoughts on spending money and you need to pay into a pension for yourself.
It can also be mind-numbing and dull, repetitive, the drudgery of housework is endless (thank god for Podcasts/streaming radio you can rewind if you miss something) and incredibly lonely. Only myself and one other person have stayed home with children in secondary school and beyond. We both believe it was the best decision we made. I am very happy in my own company.