How is he killed? His army is decimated by Bolton's men, but he manages to survive . . . until Brienne finds him. She informs him that she was a member of Renly Baratheon's Kingsguard and that she knows Stannis's face was behind the shadow that kills Renly. She then raises her sword and brings it down.
Is he definitely dead? It's very likely. We don't actually see Brienne's sword slash Stannis, but it's heavily implied, and the producers talk about Stannis being ready to die in the "After the Episode" featurette.
Level of devastation: Five. If Stannis hadn't green-lit the murder of his young daughter in the previous episode, his death would be a lot more upsetting. At this point, Stannis might actually be happier in the afterlife, but it's still sad that we won't get to see him anymore.