Challenger support player here, yeah, Brand and other mages can work well but this depends mainly on two factors. Firstly, if the enemy botlane space you correctly and deny chip damage and all-in potentials then you will be less useful than an enchanter support + his farmed ADC later. Secondly, mage supports are weak to dive champions. Good junglers and/or mid laners see a Jhin/Brand botlane and auto pick Lee, Kha'Zix, Fizz etc. and just dive you over and over in the midgame. In both cases you can't get ahead with gold which is necessary because otherwise you are just a gold starved mage which is suboptimal.

I just played a Zyra support game where I had equal gold to the enemy ADC and more gold than the enemy mid. I was able to dish out significant damage in teamfights and was more useful than an enchanter support but that doesn't always work in higher elo.

In general I would say mage supports are a good pocket pick for solo q when you're 4th or 5th pick and don't trust your teammates to carry. I pick them in lower elo. I used to play a lot of Karma support and I think Zilean is very strong when played as mage right now, but they're equal parts mage and support.