And why was is it seen as such a turning point in the war?
tl;dr - Stalingrad and Operation Uranus showed that not only could the Soviets hold their ground but that they now had the capability of conducting large scale coordinated offensive operations.
From the Soviet point of view, in the winter of 1941 the situation is beyond dire. The German army is at the gates of Moscow, Leningrad was under siege, German troops were in the Crimea and the Ukraine was gone. Although the Soviet winter offensive pushes the Germans away from Moscow, in places it doesn't even push them back to the start line of their most recent attacks. It really just gives you some breathing room.
Then in the late spring and summer of 1942 it starts all over again. A Soviet offensive aimed at Kharkov isessentially annihilated, Sevastopol falls and the Germans go on to advance seemingly unopposed to Stalingrad and almost as far south as Grozny. All attempts to relieve Leningrad fail and the city is starving to death.
So pause for a moment in September 1942 and consider how this must have looked to those living and fighting. At no point in the past 15 months have you been able to hold ground in the face of a concerted enemy attack. Every city they've attacked has fallen. Meanwhile almost every attack you've made has failed with heavy casualties. Your sole success has had no apparent effect on the enemies offensive capabilities.
Then comes Stalingrad. At first it seems like this is just another heroic but doomed last stand. But Soviet soldiers continue to hold out in the city For two months. Then on November 19th the Red Army launches Operation Uranus and in the space of 4 days encircle a complete German Army. When the pincers meet On November 23rd you realize that the Germans are not invincible.
There's the key ... after Stalingrad, the soldiers and commanders of the Red Army now see that they have the ability to defeat the Germans. IIRC In the British TV series The World at War there's an interview with a Red Army soldier who was present when the encirclement was complete. He recalls breaking down in tears when he understands that the war can now have an end.
The Germans of course have completely the opposite reaction. Many commanders now understand they can be defeated. Some even realize that defeat is now inevitable. Of course they're also aware of the German defeat at El Alamein and the Torch landings. Goebbels uses the defeat to prepare the civilian population for a long struggle.
From a purely military point of view, looking back we can see that the Battle of Stalingrad, and in particular Operation Uranus, is the first time that the Red Army is able to successfully conduct a large breakthrough offensive. What we would call Blitzkrieg. Many of the concepts that underpin the operation had been floating around the army high command since the 1930's. However poor logistics, preparation and coordination has frustrated earlier attempts at similar operations. Most notably in Timoshenko's attack in May 1942. Finally it all came together for Operation Uranus and the Red Army achieved a success against the German Army that was un-paralleled at the time.
Certainly mistakes were made in this and other attacks as they continued to learn and refine their doctrine, but Stalingrad/Uranus gave them their first success to build on.
Reading :
Catherine Merridale, Ivan's War
Richard Overy, Russia's War