Bad practices
-
Hi,
I was thinking (and yes it hurt) that as this is a fairly big forum which has a lot of consolidated experience working with sql that there must be some weird things that people have seen over the years. I was wondering what are the worst/bizarre database design/practices that you have ever seen.
(also it's a subtle way of me learning not to do them
)Laters
*I didn't do anything it just got complicated*
"I didn't do anything it just got complicated" - M Edwards -
Hi Ritch,
quote:
I was thinking (and yes it hurt) that as this is a fairly big forum which has a lot of consolidated experience working with sql that there must be some weird things that people have seen over the years. I was wondering what are the worst/bizarre database design/practices that you have ever seen.delete BUILTIN\Administrators without knowing the sa password
Should become a huge thread
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Abend Frank,
quote:
delete BUILTIN\Administrators without knowing the sa passwordThats a classic!!
Später
*I didn't do anything it just got complicated*
"I didn't do anything it just got complicated" - M Edwards -
Hi Ritch,
quote:
Thats a classic!!yes, now I can laugh over this!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'm assuming blind panic took over for awhile
How did you correct it?
*I didn't do anything it just got complicated*
"I didn't do anything it just got complicated" - M Edwards -
quote:
I'm assuming blind panic took over for awhileDon't ask!
quote:
How did you correct it?Newinstallation, restore.
quote:
*I didn't do anything it just got complicated*Is this your own, or is it taken from somewhere else?
Here's another one fot the NOT to do list.
- first thing after installation on target machine, keep a close look at who is allowed on this box. Took me also a while to figure out why a developer had access to SQL Server without having individual permission. Some two weeks later, and with help from Brian Kelley (Thanks, again!) I figured out, this guy was left for convenience in some domain account which also had a login on my box.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Newinstallation, restore.Ouch!
quote:
quote:--------------------------------------------------------------------------------
*I didn't do anything it just got complicated*
--------------------------------------------------------------------------------
Is this your own, or is it taken from somewhere else?
Unfortunately not, a friend who is the senior developer at somewhere I used to work used it, but it suits me more
. I shall relinquish it if he ever turns up here tho!!Cheers Frank!
Ritch
*I didn't do anything it just got complicated*
"I didn't do anything it just got complicated" - M Edwards -
Just a few that come to mind:
* leaving the sa password null or some ridiculously guessable value
* not having primary keys, or at least unique keys
* not having clustered indexes on volatile tables
* wrong selection of clustered index columns - eg. frequently updated columns or overly large (sets of) columns
* use of unicode when there's no need for it
* use of text columns when varchar will suffice
* use of simple recovery mode on critical databases
* setting db to full recovery and not configuring any tlog backups (I once came across a db of 25.1GB, where 25GB was the transaction log)
* useless or almost-identical indexes
* not testing database recovery in a "dead server" scenario
* not updating stats regularly
* not doing a checkdb regularly
* not having some sort of benchmarking capability (eg. even a simple capture of SQL's @@ stats regularly - even if it's not utilised initially)
* setting productions databases to unlimited growth (personal opinion only - so don't flame me!)
* audit level on production servers set to none
* leaving the error log unexamined
* developers testing their stuff as a db_datareader/writer, or even worse db_owner, or yes, yes... sa. AND then expecting to deploy to production under the same circumstances.
* developers who abuse their privileges on production databases that your management has allowed them (hmmm, I can use use EM to insert a column between these other two in this 3 million row table)
(again, don't flame me... I have nothing against developers - they provide me with a job. It's just that I've seen these things happen)
Cheers,
- Mark -
Thanks Mark,
*I didn't do anything it just got complicated*
"I didn't do anything it just got complicated" - M Edwards -
Granting permissions on public.
-
OK, how about running scripts that build new tables in your master database. I used the SQL Analyzer to import metadata for a project and forgot to change change to the desired database.
This brings up a question. Does anyone know how have a database, other than the master database, be the default as SQL Analyzer is loaded?
-
hi ryno,
quote:
This brings up a question. Does anyone know how have a database, other than the master database, be the default as SQL Analyzer is loaded?äh, do you mean how to change default one's default db when QA starts?
If so, sp_defaultdb [ @loginame = ] 'login' , [ @defdb = ] 'database' should help
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Something I've learnt is when doing upgrades is never rush them!! This is a dark chapter in my life as a wannabe dba.
I had to upgrade a lot of servers in a short space of time. My procedure was to backup the db and restore as dbold, run the upgrade scripts against the original db, this was modifying table structures etc. I then had to run a script to copy the correct info back from dbold into the updated db.
Unfortunately I had run the backup on another instance by mistake as I was doing several at the same time (no excuse but it makes me feel better!). Resulting in a loss of all stock and orders for the store. And to top it off backups were the customers realm of responsibility (something I am so against!!).
Laters
*I didn't do anything it just got complicated*
Edited by - Ritch on 07/22/2003 06:19:48 AM
"I didn't do anything it just got complicated" - M Edwards -
quote:
Granting permissions on public.I'd certainly dispute this one as a worst practice. Carefully done (as I have outlined in a previous thread) this is very useful. It prevents some very basic permissions from having to be stated on every group.
But how about these as true worst practices:
*Adding fields so you can create your own locking mechanism rather than let SQL Server's locking mechanism work for you.
*Not using relational joins, but instead using code to enforce all your relations.
*Not using stored procedures.
*Using views indiscriminantly rather than stored procedures.
-
I'll follow mccork, our developer dbas always start with unicode, until I push back.
Of course they also love Varchar(1) !!!!
Worse they start at NVarChar(1).
KlK, MCSE
KlK
. I shall relinquish it if he ever turns up here tho!!
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply