Minimalistic Database Administration

Question

I am a developer. An architect on good days. Somehow I find myself also being the DBA for my small company. My background is fair in the DB arts but I have never been a full fledged DBA. My question is what do I have to do to ensure a realiable and reasonably functional database environment with as little actual effort as possible?

I am sure that I need to make sure that backups are being performed and that is being done. That is an easy one. What else should I be doing on a consistant basis?

Answer 1

I would suggest:

• A script to quickly restore the latest backup of a database, in case it gets corrupted
• What kind of backups are you doing? Full backups each day, or incremental every hour, etc?
• Some scripts to create new users and grant them basic access.

However, the number one suggestion is to limit as much as possible the power other users have, this will greatly reduce the chance of stuff getting badly messed up. Servers that have everyone as an sa tend to get screwed up quicker than servers that are locked down.

Answer 2

I've been there. I used to have a job where I wrote code, did all the infrastructure stuff, wore the DBA hat, did user support, fixed the electric stapler when it jammed, and whatever else came up that might be remotely associated with IT. It was great! I learned a little about everything.

As far as the care and feeding of your database box, I'd recommend that you do the following:

Perform regular full backups.

Perform regular transaction log backups.

Monitor your backup jobs. There's a bunch of utilities out on the market that are relatively cheap that can automate this for you. In a small shop you're often too busy to remember to check on them daily.

Test your backups. Do a drill. Restore an old copy of your most important databases. Prove to yourself that your backups are working and that you know how to restore them properly. You'd be suprised how many people only think about this during their first real disaster.

Store backups off-site. With all the online backup providers out there today, there's not much excuse for not having an offsite backup.

Limit sa access to your boxes.

If your database platform supports it, use only role based security. Resist the temptation to have one-off user specific security.

The basic idea here is that if you restrict who has access to the box, you'll have fewer problems. Secondly, if your backups are solid, there are few things that come up that you won't be able to deal with effectively.

Answer 3

Who else is involved in the database? Are you the only person making schema changes (creating new objects, releasing new stored procedures, permissioning new users)?

• Make sure that the number of users doing anything that could impact performance is reduced to as close to zero as possible, ideally including you.
• Make sure that you're testing your backups - ideally run a DEV box that is recreating the production environment periodically, 1. a DEV box is a good idea, 2. a backup is only useful if you can restore from it.
• Create groups for the various apps that connect to your database, so when a new user comes along you don't guess what permissions they need, just add them to the group, meanwhile permission the database objects to only the groups that need them
• Use indices, primary keys, foreign keys, constraints, stats and whatever other tools your database supports. Normalise.
• Optimise the most common code against your box - bad stored procedures/data access code will kill you.