Well for one thing, Roose Bolton wasn't second in command. He may have been a senior commander but he was not the heir to the North (the real prize). At the time, Bran Stark was King Robb's heir. The overlordship of the North belonged to House Stark, as it has been for thousands of years. Historically, House Bolton have frequently waged war on House Stark over dominion of the North, but they have always failed.
Several things prompted Bolton to ... ahem ... bolt:
The thousand year old ambition of House Bolton to take control of the North, which the Lannisters promised.
Roose Bolton suspected that the war was lost now that King Robb had lost the support of the Freys due to him breaking his marriage promise. So he decided to back the winning horse: House Lannister.
When did he strike a deal with the Lannisters?
How long had Walder Frey known about it?
We don't know any exact times. Although there is a scene in the first episode of season three (Valar Dohaeris) where we Tywin writing this letter. It's blurry, but you can see the words "tempt the wolves" and "revenge". It is speculated by fans that this is the letter that Tywin sent to the Boltons and the Freys to solicit their help.
How many of his men were in on it?
Again we don't have exact details. But certainly only a few knew trusted commanders would have ever known. Like all the armies in feudal Westeros, it wasn't a professional standing army, but an army of conscript peasants supported by a few mercenaries. They had no business knowing their commander's plans until absolutely necessary.