October 24, 2008 at 8:51 am
Backups of some sort are are a must. Also a cold six pack on a Friday night!:)
October 24, 2008 at 8:52 am
Agree with many of these, how about another one not mentioned here -- consistent typing? We have had multiple issues of developers either being careless in data type matching or else using defaults (in LINQ, shudder) and having performance issues. In the latter case, we had a default LINQ setting be nvarchar while the actual field was varchar, which caused a table scan on a table with 400,000 rows and slow performance (and since it was LINQ, I could not do anything as a DBA, grr). I had the development team confirm their LINQ mappings for all fields to make the typing consistent and explicit.
October 24, 2008 at 8:55 am
Our latest project for a financial institution is scoped for an estimated 1 billion rows in the first year. I'm having fun explaining to the powers that be (through my boss) that 1TB of space over the next five years is not enough. They twitched when I mentioned file groups.
Obviously speed will be key, especially on some of the larger transactional tables. We gauge around 10 million rows per table per month once the system is up to speed (no pun intended), and the queries need to be as fast as they are now with 500k rows.
So my list of absolutes are (in no particular order):
1. Naming convention, and consistency therein
2. Backups
3. Normalisation
4. Proper indexes
5. Security
6. Documentation
If I can avoid it, I won't use Views. One of my colleagues here has been fighting with query times on a series of 7 cascading views on a small (50MB) database, and resolved the problem with a combination of temp tables and functions (from 3m+ response times to <2s).
Also, I don't necessarily have a problem with forward read-only cursors, but I go for non-RBAR where possible.
Flame away 🙂
October 24, 2008 at 8:57 am
Ooh! A LINQ cotcha! I'll be watching for that in the current project being developed
'...I consider the database just to be a big bucket to put stuff in...'
from one of senior devs - the guy is code brilliant.
Hiding under a desk from SSIS Implemenation Work :crazy:
October 24, 2008 at 9:05 am
Grant Fritchey (10/24/2008)
Backups, absolutely.And, this one does make people crazy, but I've been insisting on it for over a decade now... a tested script to deploy changes to production. No winging it, no developing against production. You put me in charge of protecting the corporate data and I'm going to do it gol-darnit.
Over the years experience, and blood, has worn away insistence on primary keys, clustered indexes,naming conventions... but I'm not giving up backups or a scripted production rollout!
Hear, hear! I wholeheartedly agree.
----------------------------------------------------------------------------
"No question is so difficult to answer as that to which the answer is obvious." - George Bernard Shaw
October 24, 2008 at 9:43 am
mtassin (10/24/2008)
1. Backups are a must... the more backups the better2. Document your flipping database. With that, consistent naming of database columns and consistent use of the same data types for that column in every table that it is used is a must.
3. Security - Minimum required permissions. Always use Integrated Windows security, always setup groups and give them permissions on the SQL server.
4. Foreign Keys - if you're using the values from a table in another table, declare the foreign key constraint. Later you will be glad you did.
Good summary - I totally agree. A good Entity-Relationship Diagram is part of the documentation for me.
October 24, 2008 at 10:05 am
Interesting comments. It's funny to see how many people look at different things.
And I think it's fine to not have any absolutes. I try to have few, mostly guidelines though some are stronger than others.
October 24, 2008 at 10:29 am
There are only 2 absolutes:
1. Vodka
2. There are no other absolutes
Mark
October 24, 2008 at 10:45 am
One that I will not give in on: all interaction with the database and application / mid-tier is done via stored procedures. NO DYNAMIC SQL IN APP CODE!
October 24, 2008 at 10:50 am
Vic Kirkpatrick (10/24/2008)
One that I will not give in on: all interaction with the database and application / mid-tier is done via stored procedures. NO DYNAMIC SQL IN APP CODE!
Would you quit a job over it? Engineering tried to get the developers hired to build our app to do that and not use LINQ, they replied that it would be faster and cheaper for them to use LINQ, and the decision-makers listened to them. So either we support LINQ or else find a new job.
October 24, 2008 at 11:07 am
Backups (including system databases), DBCCs, and log review. Most of my systems back up locally and then again to a SAN, so I automatically have the backup in two places. I run my DBCC results through Find so I only see if there are errors, I don't have to flip through miles of results. And I wrote a Perl program to gather all of my logs for the previous 24 hours (it runs at 23:59 every night) and filter out all of the successful logins, so I see if I had any backup problems, server reboots that I didn't know about, or people repeatedly having login failures.
I also like to have all of my databases in Full recovery model, but I'll settle for Simple for read-only databases.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 24, 2008 at 11:34 am
Jeff Mason (10/24/2008)
Vic Kirkpatrick (10/24/2008)
One that I will not give in on: all interaction with the database and application / mid-tier is done via stored procedures. NO DYNAMIC SQL IN APP CODE!Would you quit a job over it? Engineering tried to get the developers hired to build our app to do that and not use LINQ, they replied that it would be faster and cheaper for them to use LINQ, and the decision-makers listened to them. So either we support LINQ or else find a new job.
There was enough support for 'No absolutes', that I wan't going to bother to comment. Then I saw Vic's comment. That's about as close as I could come to declaring an absolute.
Jeff. I wouldn't quit my job over it if I were in your position. but I'm a developer, and I have trouble believing that your developers really know what they are doing. Sorry for your bad luck!
October 24, 2008 at 11:43 am
OMG do I have opinions on this topic! As the manager of a small development team that has no DBA, it is absolutely critical that we have absolutes and we have a gagillion of them. All tables have absolutely identical required fields, all tables are indexed on the same identity field (not clustered in case we find that we need to cluster on a different field), all tables have one and only one key, all tables contain a standard GUID for alternate, non-key access, etc. etc. ad nauseam.
I recognize that this inflexibility prevents us from taking advantage of certain sophisticated techniques and features, but I don’t consider anyone on my team (including myself) to have the expertise necessary to implement them. This standardization also extremely simplifies data access, since so much of it can be standardized.
I do not know if this is the best way to organize a dev team, but it seems to have worked for us, and more to the point, I find our software to be just a dream to work on. Data access is so smooth and simple I hardly have to even think about it while I am coding.
At least, this is the view of a non-DBA developer. I know that our DB schema could be improved by a DBA and I intend to find a contractor someday when we have the resources.
October 24, 2008 at 12:01 pm
Many interesting comments have been posted, and even several opposing ones.
My addition to this is not limited to SQL server, but many tried and other would-be RDBMS(s), as some offer features others do not, and some have issues in some places that others do not.
NO rule is a deal breaker, that would cause me to walk if I did not get my way. Constant vigilance, study, teaching, and willingness to serve but strongly guide is really my only rule. When people stop joining that conversation, then for me is the time to move apparent. Know your stuff, and expect others to bring stuff to the table.
For me, in no particular order, the things I strive very hard for:
1. Naming conventions - these are not just for me but for every other person that may have to pick it up. Verbosity may seem cumbersome to dev, but easily self-teaches descriptively any who may question purpose and content.
2. Primary keys on every table... preferable a numeric that has no bearing on the data. It is simply a row key for use by the database engine, not for end user data. May seem to add overhead, but always pays off big dividends in upscale, normalization, integrity, and integration. Also with this item, a data-driven unique key for the table. For instance, the primary numeric key should not be the customer account number in a customer table. Primary Keys are strictly for the use of the database. Take for example a personnel table: SSN may be the data-driven key, but should never be the primary key... what if it was fat-fingered (entered wrong) and then later corrected, and the relational integrity never was established or has failed (it happens). Now you have orphans you have to manually RBAR to resolve. Also, another example is when data is ported (integration) between systems, and one has a different key that is already populated with potential conflicts (especially with fat-fingering). As a RULE there are always errors in the user data entry, so user data should not be the primary key.
3. Relational Integrity. Maintain it. Audit it. Re-check it regularly.
4. Appropriate and regular Backups scheduled at a frequency no more then the maximum amount of data the business can stand to manually recover if active system(s) fail catastrophically. Enough said about this already, but I'd also like to mention, redundancy, fail-over, logs, and business process to shore up and limit if not prevent data-loss.
5. Communication, Study, and Documentation. Enterprise relationship validation. Nothing is perfect, and there is always bigger and better ways. Some are not worth going for. Stay abreast, stay fresh, and know your stuff, and remember you are in a responsible SERVICE position.
Even if you do keep your mullet and flip-flops, if you are the source and approachable, people will work with you (remember the breath mints).
October 24, 2008 at 12:06 pm
While we are talking primary keys -- I have to post my primary key pet peeve. It's OK if you have a reason to cluster on primary key fields to do so -- but for me, please do it as an extra index. Yes, it takes up more space, but in two years, if I find that the clustered index is better served on another field and you made the actual constraint the clustered constraint? I have to drop and re-create the primary key itself, and if that is a foreign key elsewhere, those have to be dropped and recreated too. Whereas if I have a separate clustered index on the same column, I can move it off-hours and no one is the wiser and I don't have to worry about the risk to data integrity for time without constraints. Not an absolute, to be sure, but still a personal pet peeve.
Viewing 15 posts - 46 through 60 (of 92 total)
You must be logged in to reply to this topic. Login to reply