Trust error messages. Error messages arising from some software or human error are extensively well documented and always tell you the underlying problem. Take this message for instance:
"Server object instance creation failed on all SOC machines. The base table definition string is invalid. Bad User."
Obviously, the problem is the existing cache on the server with an identical name. You should manually delete the folder on that machine. All of the errors messages you get will be similarily helpful and to-the-point.
User experience is for end users, the software is supposed to be hard
A lot of tools exist to automate common database operations - many exist as simple command line scripts (often paired with SQL) and provide a good way of managing large datasets and effect complex model changes with proper testing and without human error. A lot of web frameworks now support a migration pattern, where schema changes can be made and reversed in a maintainable, testable, and reliable fashion (see Rails Migrations, Django South, and very soon EFCodeFirst from Microsoft).
But that junk is for scrubs. Far better for you to push some buttons in a wizard than have a clean config file paired with a command one-liner that can be easily integrated into maintenance scripts. Database migration and automation tools are for chumps who don't want the continued experience of pushing "next" to recreate the network. Otherwise what excuse would you have to bring the server down?
It's totally necessary to have some middleware between you and your database
Modern relations database management systems are really unreliable/untested and none of them really support spatial types. Better to abstract away that nonsense with some DCOM goodness.
It's especially useful to have a multiple ways to use the middleware to effect some database change - like deleting a domain - that silently fails if it isn't in the right program or uses/doesn't use a toolbox tool.
Eliminating complexity is for people who don't want job security
Use every possible complex ArcSDE feature, that is what they are there for! If you don't start using, say, a versioned geometric network feature class dataset, some DBA without any GIS training might get it in his/her fool head that they can do what you do. Nothing is more dangerous. ArcSDE helps you avoid this cruel fate by making a simple insert statement into a tremendous pain.
Fear your database tables
If you've just installed ArcSDE, you will notice it has convienently created a number of tables with obscure names. This is just it's gentle way of letting you know that you no longer really own this data or have much control over it. It's important to avoid the inclination to start using that database in the same manner you might other databases - creating convienent views or reoccuring jobs that better insure data accuracy and validity. If you want to do anything like that, go open up ArcMap or Visual Studio with an ArcObjects template.
Jump into the new version immediately
Don't let those 987,345 service packs on the previous version fool you. You can immediately deploy that hot new version with those nifty new features. Yes, version 10 has three service packs already, but 10.1 is going to come out production-ready, so don't wait.