There are only two hero deaths that have ever stuck in my memory.
The first was the death of Rhidhana Dubhaine during the final battles in Krimml that led to the end of Fontan. That character was almost a physical embodiment of the ideals of Fontan and she was absolutely loyal to the realm. The death of the character exactly at the time of the death of the realm is the most fitting and poignant death of a hero I've seen.
The second is the death of Armstrong Ironsides in Oligarch when Sirion took the city. Again, it was an entirely fitting death for the character. Armstrong had become world-weary and wanted a meaningful death. To that end, he created the single-region realm of Fane in Oligarch knowing that Sirion would never accept it. Then he remained in the city fighting in every battle and hoping one would lead to his death. I can't actually remember whether it was a battle that finally killed him or whether it was a death duel. Either way, it was an heroic death that also led to a RP funeral in his home region that really was attended by friends and enemies alike.