I'm a rainbow and a brownie leader.
This is generalising a bit, but in my experience, 6yr olds who have done sleepovers with friends or cousins / grandparents etc usually do fine. Those that haven't, tend to struggle a bit (usually about 11.30pm when they haven't been able to get to sleep, or have slept for a bit and then woken up disorientated). Is it just a one night thing? Rainbows aren't allowed to do more than one night away so don't know if it's different with beavers, but for brownies, we find doing a one night away sleepover first, really helps them to prepare for a bigger camp / holiday etc.
As pp have said, generally the threshold for calling parents is pretty low when they are little. And you don't have to send him for overnight - they may be day attendance opportunities. There will be lots of other overnight opportunities if you want to sit this one out.
(Edit as I missed a para I wanted to write) But if you think he is ready and will enjoy it, then do consider letting him attend. They are great events and the kids get so much out of them.
On a side note, if you have any time, please do consider volunteering to help with the groups. You don't have to attend meetings or become a leader. In the past, I've had a parent help by doing my accounts for me and one help run a social media account for us. Every little helps to take pressure off the volunteer leaders.