A dark night of the soul as defined by Holly (and obviously I think usefully) is a moment when the protagonist doubts everything she has done before and the goal itself. This should happen somewhere midway through the book, possibly 60-65% of the way in, though I wouldn't want to say exactly where and make writers formulaic about it. It could also happen right near the ending. The important thing is that it can't happen too early on or there is no impact. There has to be a real commitment to the goal or it doesn't hurt when you feel like you have to give it up. There has to be several try/fail cycles so we can see that the protagonist isn't just giving up easily. This isn't about it being too hard. This is about it being impossible literally.
In addition, the reader has to feel the dark night of the soul every bit as much as the protagonist does. It has to be absolutely real, no faking allowed here. No smiling, just kidding, or oh, that was all a misunderstanding (one of the reasons I have problems with romance novels). This has to be a realization of the fruitlessness of the effort or possibly a moment of betrayal. It can be seeing the real flaws in the person you are following or are in love with. It can be, as in Pride and Prejudice, the moment that you realize that the one person you might have loved is also the person you can now never have because your sister has now married the one person he could never be related to in marriage, and your whole family is disgraced. And it is all your fault because you could have stopped it if you had only not been so flawed. Or in Emma, where you believe for a very long chapter that the person you love is in love with someone else, and you have to wonder if you are willing to try to break them up in order to be happy yourself. Are you that kind of person?
In romance, it may feel like the stakes are smaller than in genre fiction, but really, they are the same. They are accepting that everything you have done before has no meaning, that you are useless, that you have no control over your life. It means accepting that you will not have what you want, that the world as you know it will end. (If this feels overly dramatic, think of Pride and Prejudice, when Lydia has run off, and how the whole family will partake in this ruin. They will not be able to be part of any society ever again. It really is the end of their world.)
In a fantasy novel like The Lion, the Witch and the Wardrobe, the dark night of the soul is when Aslan is found dead. Edmund going over to the White Witch is pretty bad, but it's not the dark night of the soul because Aslan can still keep the good side going. Edmund may be lost, but the cause is not. When Aslan is dead, the world is over. The White Witch will win. Her armies will kill everyone else. Narnia itself will be in winter forever.
In a movie like The Fugitive, the dark night of the soul is the moment when Harrison Ford has found the one-armed man but he is surrounded by the police and the Marshalls and it seems that after all his work, he is still going to be shot and the bad guy is going to go free. In a detective novel, the dark night of the soul can be anything from realizing that no one is ever going to give you the information you need to solve the murder to being caught by the murder and threatened with death. In The Princess Bride, the dark night of the soul is when Wesley is being tortured on the machine and the princess is being forced to marry a man she hates.
How you get out of the dark night of the soul is up to you as a writer. There are as many ways to get out as there are ways to get in. You can use a deus ex machina (which gets a bad rap, but is still used all the time. It can work if done well). You can have the hero decide to go forward anyway with the plan, even if there is no hope of winning, and win anyway (a la Return of the Jedi). You can turn things around so that it turns out that the goal wasn't the right one anyway and that the dark night of the soul was a necessary reevaluation of the truth (in spy movies this happens when alliances shift). You can use magic (as with Aslan), and though I tend to prefer magic that has previously been hinted at, Lewis is playing with Christian tropes, so the deeper magic works because we expect it on some level. You can have an important reveal or do one more try. Or I suppose as in a literary novel, you can simply give up and have an unhappy ending (I don't recommend this).
The point is that the happy ending will have a lot more power if you have entertained the possibility of an unhappy ending, and really shown how the hero would deal with it if that was what happened. Courage is facing fear, not never feeling fear, as they say. So a protagonist who accepts failure as a possibility is more courageous, even more heroic. And the worse the failure, the greater the triumph. As readers, we really do want to see what a character does when at the lowest. Do they throw a tantrum? (Again, this can work). Do they swear and curse? Do they give up? Do they yell at all their friends and demand to be left alone?
I think in the end we want to see this because we want to feel that when we are in our dark nights of the soul (and everyone goes through many of these), there is hope that we can come out of it. When we are having a tantrum or swearing and yelling at friends, we can remember that we are showing who we are, too, and that doesn't mean we have to be perfect all the time. We read because we want to practice what we are reading about, I believe, and surprisingly, readers want to go through hard stuff in practice even if they don't want to in real life. Don't cheat your readers. Make your dark night of the soul truly dark, in the way that would really make your character hurt. You aren't a writer to make your character's life happy and bright. You're a writer to make your reader fell.