Until ArcSDE 10.0 you need to install ArcSDE on the server. You have two possibilies to connect to the Server: Direct Connect or Application Server.
If you configured the DB for application server connections you had to create an sde service. In that case you have to do the following steps to delete the SDE-DB in SQL Server:
- kill all connections to the SDE DB (using sdemon)
- delete the ArcSDE service (sdeservice -o delete)
- remove a keyword from the Windows registry (sdeservice -o unregister -r ADMIN_DATABASE -d SQLSERVER -v SDE) -v is the name of your DB and have not to be SDE
- remove the entry in the sde and windows service files (%windir%\system32\drivers\etc\services and %SDEHOME%\etc\services.sde)
- drop the database in SQL Server (e.g. right clicking on the database in SQL Server Management Studio)
If you configured the DB for direct connect you don't need the sde service. And if you have no service created, you just need to drop the database in SQL Server.
As Brad Nesom mentioned, with ArcSDE 10.1 you don't have to install ArcSDE on the Server if you use direct connect. You don't even need to install the ArcSDE command line tools on the server, since you have the most necessary management tools in ArcCatalog and ArcToolbox 10.1. There is a good post to this topic by Russell Brennan (from ESRI): http://forums.arcgis.com/threads/57375-Why-would-I-install-ArcSDE-with-10.1
It is much cleaner and faster to start fresh with a new database and to load the data from the old database into the new database, than to rename the database in SQL Server.