October 24, 2008 at 6:43 am
I think that having some strategy for security, backups, etc. are the absolutes, not necessarily how they are accomplished. How often you do full vs. differential is open to discussion based on a number of factors but you have to have some method in place. Something is better than nothing, although something may only partially bail you out of trouble.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
October 24, 2008 at 7:05 am
Security is the first thing that comes to mind...
- Absolutely no use of the sa account, except for emergency purposes. I include myself in this restriction. Even if I had to use sa, I'd have to first open up an encrypted file (only myself and two others have access) which stores it's 100+ character randomly-generated obnoxious-to-use password.
- Applications should connect to SQL via Windows Authentication (each app has it's own login)
- No direct write access to production data. This is accomplished through a custom-built auditing tool.
October 24, 2008 at 7:08 am
How about get rid of NT AUTHORITY\SYSTEM on all production servers? That, along with picking the right form of authentication (mixed vs. SQL only) can help avoid aggravation in the future.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
October 24, 2008 at 7:28 am
I agree with indexes, however don't create an index just to have an index. Plan out your indexes and maintain them. I inherited a database that seems to have no thought to indexes. I've got tables with both a clustered and non-clustered index on the same column. While indexes are good, poorly planed indexes can also kill your performance.
October 24, 2008 at 7:33 am
Backups are required for me, after that everything is optional. If I could tell DBA's one thing, it's to understand what the business really wants from you, and if you don't agree - move! It's definitely worth explaining to those that decide why you want to implement a rule, but ultimately all decisions are business decisions.
As a manager/business owner, I need employees that help me understand the options, and then implement the ones I believe fit the circumstance. I can't tell you how many times I've watched people deliberately do what they believe was 'right' (and this wasn't about ethics) in clear contravention of what had been asked of them.
There has been way too much friction between developer and DBA, and I think a lot of it our fault. Take indexes for example, why should I expect a developer to send me the right indexes? Aren't I going to review them anyway? I do believe that developers should understand primary keys. Many developers work on a variety of data platforms during their career, we only work on one for the most part. We know the little bits of trivia, why should they? Though there is of course value in showing them what we do and why, because most are quite willing to learn if you can explain the logic.
October 24, 2008 at 7:33 am
Primary keys are a must. Also, do not create and leave a temporary object (especially a table or stored procedure) in my production database.
October 24, 2008 at 7:36 am
Your job is as secure as your last valid backup.
Security including getting DomAdmins out of SysAdmins Role
I enjoy the commentaries, though rarely respond. Keep up the good work! ~Clarie
October 24, 2008 at 7:38 am
Agree with the backup and index rules. However, the way I enforce those and more is via my number one rule: Nobody touches the database(s) structure but me! The developers can submit table design ideas, but I create, index, foreign key, write the stored procs and triggers, etc. etc. etc.
October 24, 2008 at 7:48 am
I'm a speed and accuracy person. I want the end-user's results to be accurate and to return as fast as possible. Those are always my first concerns.
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
October 24, 2008 at 7:50 am
1. Backups are a must... the more backups the better
2. 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.
October 24, 2008 at 7:50 am
Most of my support is for canned applications. I will fight vendors that insist on using the 'sa' account for everything. My stance is not to give that to anyone, if I am expected to support the system.
Brian
October 24, 2008 at 8:02 am
From a developer standpoint (we have NO DBA's, crazy I know but there's this budget... or not!)
BACKUPS! Appropriate to the database purpose (saved by them sooo many time)
Normalization
Primary Keys / Foreign Keys
Naming Conventions
That's my big 4, in order of priority. I'm still not knowledgable enough about performance to make it an absolute, but we're currently re-writing some views (converting to stored procedures) so we can pass parameters to limit the records - one view now pulls 21,000 records instead of 150,000 (with multiple joins).
I'm always a fan of documentation, whenever I can get it! 😎
Steph Brown
October 24, 2008 at 8:24 am
Absolute? Make backups. Verify Backups. Keep Backups safe.
Oh yeah, test Backups.
Student of SQL and Golf, Master of Neither
October 24, 2008 at 8:28 am
Stephanie J Brown (10/24/2008)
one view now pulls 21,000 records instead of 150,000 (with multiple joins).
Oh what I'd give for datasets that small!
One of our most commonly used views gave me 5,986,435 records today, and that rises every day!
It makes re-indexing a right pain!
October 24, 2008 at 8:32 am
I'd like requiring back-ups, indexes and security. BUt I'm absolutely flexible. If the customer doesn't want to take my recommendations, it just means more work and job security for me! 😛
Happy Friday; nice shirt Steve.
Viewing 15 posts - 31 through 45 (of 92 total)
You must be logged in to reply to this topic. Login to reply