Should it be easy to delete a database?

re: Should it be easy to delete a database?

FrankR — Thu, 22 Nov 2007 18:33:57 GMT

Making the ability to vreate a database with a NODROP option (or something like it) would, to my opinion, be a great solutio. This gives the DBA the ability to decide on how secure he wants to be.

Furthermore I would consider making a server setting which can be set to put on to replace the DELETE IMMEDIATLY option by DEFFERED DELETE option. The DEFFERED DELETE (hope my grammar is correct) could be your solution (Take offline and delete in x days). The great thing about this deffered delete is that yu get the opportunity to discover your error ;-)

re: Should it be easy to delete a database?

BuckWoody — Wed, 07 Nov 2007 21:09:50 GMT

That's an interesting suggestion - and in fact, with the new DMF feature in SQL Server 2008 you can prevent things like that.

re: Should it be easy to delete a database?

jrea8830 — Wed, 07 Nov 2007 20:46:00 GMT

I like the backup as an option only; I don’t think it is a good way to resolve the current discussion though.

I like the idea of instituting a flag/option that can be set on the database to prevent deletion. An example would be: CREATE DATABASE mydb WITH NODROP;

Then if the user tries to DROP this database they would receive an error stating this flag exists.

The user could pass a command to remove the flag, such as: ALTER DATABASE mydb WITH ALLOWDROP;

I think this solution would cover both types of DBAs. Those that do development may not be as interested in more work to DROP a database (think if this was implemented with tables, would you want to have another confirmation?), and those that are in production have an ‘option’ to further secure their databases. How can this not be a good thing? Options are much better than having more work forced upon us.

Also, can you please move the Delete command in the context popup menu? I agree it is too close to ‘Rename’. Maybe put another line-separator in there to make it so that I don’t accidentally click ‘Delete’.

Thanks for listening to me!

re: Should it be easy to delete a database?

justinb486 — Wed, 07 Nov 2007 11:55:56 GMT

Morning (UK time). The offline/delete sounds great. To be able to set a flag on a DB so that the TSQL DROP DATABASE command warns but doesn't drop would be good, so people can't accidentally do it via script too.

That sounds like something that would be great in a service pack/addon for SQL2000/2005...

re: Should it be easy to delete a database?

BuckWoody — Wed, 07 Nov 2007 03:46:44 GMT

Great informsation! Here's what I'm thinking based on what I've heard: How about a dialog that shows you the last backup of the database before you drop it? And how about an option to "Take Offline and Delete in X days"?

re: Should it be easy to delete a database?

justinb486 — Tue, 06 Nov 2007 19:11:19 GMT

Re the last few comments, in my company (European support only) we have 3,328 SQL databases and growing, all of which follow our service model naming conventions i.e. they're all named pretty much the same which is very scary!

So something that steps out of the ANSI standard of drop database, to something with a bit more validation (optional to only enable on production/important) would be handy.

In some very secure environments I've worked in previously, to delete a key entry, let alone a DB required 2 authorised users to approve.

To stop a typo dropping a £50 billion SAP DB rather than it's n-th degree prod sized copy would be good...

re: Should it be easy to delete a database?

rkralston — Tue, 06 Nov 2007 17:23:48 GMT

I suppose having an "Are you sure" dialog would be OK. The two phase commit, get a bunch of passwords idea is right out. We're not launching nuclear warheads (or is that nuculer?).

I understand wanting to save people who don't know what they are doing, but the organization that turns such a person loose on their mission critical data will be having other problems as well.

Honestly, this seems like the last group that needs to have their hands held.

On the other hand, as you make SQL Server more like Access (is that more accessible?) you will need to consider this expanded user group in your admin designs.

re: Should it be easy to delete a database?

FBuchan — Tue, 06 Nov 2007 16:50:28 GMT

It's A-OK as it is, because it is secure, and simple, and that dialog box is pretty clear what's happening. But why not enhance the dialog some, getting the best of all worlds?

For example, leave the process as it is currently by default. In the confirmation dialog provide a checkmark that backs up the current database to a safe location before deleting it. If checked, spawn a backup and complete it appropriately, then return to the deletion. Inside the SSMS options, provide a checkbox that reads something like "backup any database to the following location by default when deleting," and a path navigator to set that locaation. (Maybe a folder called "Backups\Deleted" or some such, by default.) This way, for those who like the speed provided by the delete as it is, they have no gripes; and for others who want to be extra-safe, they have a simple new feature that will make their lives easier.

Most of us probably have protocols for deletion, but no one could really argue with that kind of automated helper. (And I'm being selfish, because basically, that's what I tend to do, just on the off chance I decide later I wanted that database back, and such a feature would reduce my clicks significantly.)

While you're at it, though, you could add some colour to certain confirmation dialogs, or maybe a big icon with a warning context. The only complaint I have these days about SSMS, now that I've gotten used to its oddities, is that every dialog looks the same for the most part. Any dialog that commits an action of serious nature, like a deletion or detaching, should visually remind the user they are about to do something serious. Colour isn't a crime when used sparingly, and again no one would argue such a change because at hour 10 of a long day, a few obvious visual clues you're about to do something heinous would be nice.

Fantastic discussion, though, and I really look forward to seeing future value is the SSMS interface.

re: Should it be easy to delete a database?

markh319 — Tue, 06 Nov 2007 16:28:08 GMT

I would like an easy way to kick everyone out and delete a db with a couple of keystrokes, instead of having to find my script to kick everyone out then drop the db.....

You could use a 'recycle bin' approach maybe with a 'destroy permanantly' checkbox?

Mark

re: Should it be easy to delete a database?

slamberson — Tue, 06 Nov 2007 15:50:19 GMT

Implementing a feature which requires the user wishing to delete a database to type the full name of the database they wish to delete **might** make the person consciously aware of what they are doing. (Make sure to disable copy/paste.)