I’m against child free weddings in principle, and would turn down child free invites from wider family. But I would try and accommodate close family I liked and loved.
Think about how you would best like to attend given the limitations caused by children not being welcome at the wedding or accommodation. There’s still a fair amount of choice. You could hire a nanny for the duration who keeps your children at home while you and DH attend (this seems to be your family’s expectation?) but that’s not something many mothers in the UK would be really comfortable with, for a 6 month old especially. You could rent child friendly accommodation near by for the nanny, or your DH to stay at looking after the children. Your DH could stay home and you could attend. You could drive up just for the wedding and wedding breakfast, maybe an hour or so of the reception and drive home again, either just you or you and DH while, say, your PiL look after the kids.
There’s a whole range of options, limited by breastfeeding considerations, money and babysitters you trust as well as how comfortable you are being away from your 6 month old. But there’s nothing inherently harmful in you leaving them for a few hours, or even a few days, if there is a loving, familiar caregiver available (DH or a grandparent, say). So you can build up that relationship for your DC to make it easier if that’s the way you want to go.
It sounds like you really want to be there and the important thing is to try and keep up friendliness with your DSis without jeopardizing your own family. In a few years time, if your sister has kids she might realise how badly she’s treating you, but at the moment she has no idea and she’s focused on her big day (which is probably feeling pretty overwhelming to her right now). Unless there is form for you always playing second fiddle, one instance of her being a Diva is not something to make a stand over, especially when it really is a very big occasion for her.
Personally I would decide what I was comfortable with that let me attend as much of the wedding as possible. My ideal would be to rent child friendly accommodation nearby for the duration and head up with DH, kids and a willing friend or in-law I trusted to babysit so DH and I could attend as much as possible. But the chances of being able to afford that and have a willing friend or in-law seems pretty low!
In any case I’d let my sister know what I cold manage. I wouldn’t offer it as a negotiation tactic, I would just say that it’s what works for me that doesn’t intrude into what she wants to do for her wedding and you’re sorry that you aren’t able to be more flexible now you have young children. If my plans didn’t involve me being there the whole time, I would offer to step down as MoH if she wanted but try to stay as supportive as possible.