Prefacing this with, "I like happy endings." I'm not a fan of tragedy and generally avoid it. When people talk about a work and one of the first things they mention is, "It made me cry!" I want nothing to do with it. xD; (I still haven't seen Up, even though I'm pretty sure by now I already know what the sad bits are.) I don't really like "heroic deaths" because I always feel like the happiest ending doesn't involve a living sacrifice. (Noble, perhaps. Necessary, perhaps. But happy? No.)
There are certain heart strings of mine that are easily tugged. Even obvious/poorly done set-ups can "get" me. (Usually, this results in a sort of deep resentment. I hate crying and feeling manipulated by something done poorly or in something it feels inappropriate in just pisses me off.)
I think that ultimately, for an MC's death, feeling like "you could have saved them" has no real impact other than, "Let's try that again." (Or - at most affecting - "Let's let that never happen again.") For an NPC's death, the feeling that I could
have saved them makes me feel guilty (especially if I liked them). When I play Fire Emblem, it's "no man left behind", even if I don't actually like the character. If I figure out that a character can't
be saved and it felt like I could have or should have been able to, I get frustrated. (Aerith? Why did Phoenix Down not work? -- /noting I haven't played, but everyone always makes that joke, so why can't I?
Until Dawn is a very good piece to study. I don't have the system, so I just watched Let's Plays (lots of them - the reactions were really interesting and not in the "haha, jumpscare!" way). If you can, I'd say it'd probably be better to play it yourself. Preferably with other people in the room with you since that seems to increase the experience (based on the LPs I've seen - solo players didn't get into it as much as those who were playing with others). I most recommend Scary Game Squad's LP, though I really enjoyed the one played by presshearttocontinue and her fiance thestrippin (full playthrough is on both of their channels, so it's just up to you which you watch it on.)
Knowing that any of the cast can die and not wanting to be powerless to stop it (again) makes the players paranoid of losing them - even when by and large every person I've seen or heard play the game thinks the entire cast is awful to one degree or another. Some characters end up growing on players, though - even some of the worst have their fans. When a player loses a character, there's always dismay. Some of that is clearly in the gamer mindset of, "I am not performing optimally - crap!" But those who are particularly drawn into the story are also upset on at least a human level of "a life lost". If they really liked the character, or a character they liked was close to the character, they feel
it. You can hear it in a LPer's voice when they lose someone they had a connection for. And there were some who apologized to the characters who mourned the lost - and seemed to mean it beyond the playing to the viewing audience.
A great game to study for how to get that reaction out of people! Even if it has the complication of basically every character being both an "MC" and an "NPC" - the interesting part is how the deaths can affect people even if people by and large find them awful.
So... we'll say now that it's clear you can't save them.
For Fire Emblem: Awakening, that's taken care of with cut scenes. Trying not to spoiler it (though it does happen early in the game), but we'll just say that with the first NPC death (maybe the only one? I honestly haven't finished, so I don't know for sure there are any others, I just assume there are), I actually stopped playing the game to look up online which choice was "better" because it seemed pretty clear that there wasn't a way to save them and I at least wanted to pick the one that would have the least upsetting results. It moved me because I felt their actions were noble and, moreover, the depth of pain shown by those who cared for them moved me. I really wanted to go after the badguys. And I think part of it was I did
have that choice. It in some way made me a part of what happened in a way a cut scene without any sort of interactivity wouldn't.
Mild spoilers for the very beginning of Until Dawn: There's nothing you can do for either of the sisters at the beginning of the game. You don't usually realize that the first time you play, though. (So I guess I'm cheating a little on my, "It's clear you can't save them" bit - but some people do catch on.) You're given choices - so gamers tend to assume that you can. That probably activates the "guilt", especially since everything you've seen up until that point makes you feel sympathy for them. When they die, it's "your" fault for not saving these girls who didn't deserve what happened to them, right? Oddly, finding out later that there's no way to save them doesn't make me feel frustration. (I'm guessing some of it has to do with me only watching LPs of it, so I didn't feel quite as complicit in their deaths. But I think most of it is just recognizing it was "the tutorial" and the horror movie trope of "first blood".) What the deaths do is make most gamers dead-set on "never again".
So - what has to be necessary to give that impact...
I think I've made pretty clear with the two examples that feeling "involved" or even "complicit" in an NPC's death is very affecting. I definitely prefer seeing that I can't actually do anything or it being made clear shortly after it happens that there was nothing I could have done, just because it saves me from going, "I'll restart from the previous save and try again. D:" or feeling frustrated the entire rest of the play-through, thinking that the reason it happened was because I did something wrong (and "whatever it was must have been before my last save").
Empathy/sympathy should be established. If not through the NPC itself, then the MC(s) it affects. I oddly feel uncomfortable with giving examples of what "gets" me, so I'll just leave it at that. I'm sure the next bit gives hints anyway.
You can look to how the opposite is achieved too. Why don't you usually feel sympathy for "some random" being murdered? For instance - the death of a security guard. If they're a bad guy: they're on the "wrong side", often being rendered faceless (mask/shadows/drawn that way in a game), sometimes explicitly given off-putting characterization like saying or doing something unpleasant, and often just the fact that they're wearing a uniform (and thus "one of many") - all of that can make it less impactful when they die. In fact - you see many of the same techniques used if they're not
a "bad guy". You get a generic looking person and put them in a uniform and that alone is enough for most people to feel nothing when the monster crawls out from the ducts and eats them or the ruthless spy shoots them. Especially if they don't get a name or any indication of personality.
All I really warn about is foreshadowing too heavily. Some foreshadowing is probably good, but if you go too heavy on it, people generally have already gone, "Ah - expendable character trying to manipulate me into feeling something. Nah."
I feel like I have more to say, but I've already made a text wall most people probably won't read. xD So I'll stop.