The death star was the most feared asset of the empire but despite this, the rebel alliance was able to destroy it. In both legends and canon, Palpatine reacted with both worry and anger; he clearly did not expect the rebels to be victorious. However, this was not without warning from two of his most trusted associates, Grand Admiral Thrawn and Darth Vader. So why did Thrawn and Vader believe the Death Star to be a foolish idea and why did Palpatine fail to realise they were right. Thrawn was likely the most opposed to the death star and for a different reason than Vader was. Despite not ever being told about the existence of the death star, Thrawn was able to deduce the projects existence through analysing the distribution of metals for the imperial navy. Thrawn was predominantly a military man and thought in terms of resources, logistics and tactics. He was alarmed by his realisation of the death stars existence, believing it to be an inferior replacement to a larger navy of more durable TIE fighters and Star Destroyers.For Thrawn this was nonsensical. The death star could only be in one place at one time while the rebels operated as guerrilla fighters across many worlds. In Thrawn's opinion, a war against the rebels could only be won by superior military equipment, planning and military tactics.
The death star could not defend against rebel attacks on imperial mines, factories or training facilities. For Thrawn, the death star was simply too clumsy of a weapon to guard against the rebel threat. It would simply serve as a massive target for the rebels.
Vader also believed the Death Star to be unnecessary. Like Thrawn he also believed it to be a waste of resources. However, he also despised it due to its very nature. Vader had given himself truely to the dark side, believing it to be the greatest power in the galaxy with himself one of its greatest vessels. Vader feared that the death star would make him obsolete; replacing the power of the dark side with a technological terror. Whatsmore, Vader knew that the bid to control the death star would only lead to more infighting between the various imperial moffs and grand moffs, only weakening the integrity of the empire and reducing order in the galaxy. However, Palpatine dismissed the concerns of his trusted associates and to understand why, we must take a look at his reasonings for the death stars construction. Palpatine had three main reasons to favour the death star over an improved imperial military. First, Palpatine was in many ways tired of ruling the galaxy. He despised the day to day workings of ruling and delegated most of it to the grand moffs. Instead Palpatine wished to unlock the secrets of the force and was obsessed with finding the secret to unlimited power. The power politics of the senate as well as the countless rebel skirmishes distracted Palpatine from these true goals.
The death star was meant to mitigate these distractions, subduing rebellion through fear of complete annihilation of planets. Second, the death star centralised power within the hands of a few loyal moffs. Although this may have increased infighting and reduced order within the high ranks of the Empire, this was of little concern to Palpatine.
By centralising power, Palpatine exerted even more personal control over the galaxy. An enlarged navy would have spread the empires rule over many hundreds of imperial officers, increasing the likelyhood that some of them would threaten to breakaway from the empire or worse, attempt to overthrow him. The third reason contains a little bit of my own personal speculation but I believe it has good grounding within the lore. Palpatine was well aware of his machinations and domination within the force. Essentially what he was doing was going against the current and balance of the force itself. To maintain balance, the force would always be trying to push against Palpatine's actions. The death star symbolised the end of hope in the galaxy and the the introduction of pure terror into the galaxy. This fear would have shifted the balance of the force in the galaxy further towards the dark side, strengthening Palpatine's power while diminishing the strength of the will of the force. And it was for these reasons that Palpatine was so keen to construct not just one but two death stars. If successful, he would have been truly unstoppable and would likely have reigned over the galaxy for many millennia. However, Palpatine had been blinded by power. Just as he had used the Jedis arrogance and complacency to overthrow them, Palpatine's years as undisputed Emperor blinded him to the fact that the death star could be destroyed.