September 11, 2009 by Mike Hillwig
A lot of application vendors try to do things that will make managing their application easier for customers who lack an IT staff. I seriously doubt they make it that much easier. Unfortunately, there is something called unintended consequences, and they frequently make their applications harder for those of us with an IT staff (especially a DBA) in-house.
Here are some things I wish these vendors would understand.
- You should use a CREATE DATABASE statement instead of just plunking down your own MDB and LDB files. I have my model database set the way I want for a reason. You’re circumventing things I already have in place.
- IF you’re going to plunk down your own MDB and LDB files and your database is set to use full recovery, please include a facility for backing up transaction logs.
- Please stop assuming it’s okay to use SA as the user to run your application. My auditors don’t like that and neither do I. Please create a different user that will run the application. I’d be thrilled if that user were an active directory account.
- Don’t think for a moment it’s okay for your application to run as SA with no password. That kind of thing can get me fired, and rightfully so.
- When your application is running, it should only have the minimum privileges needed to run the application. I doubt the account needs to be an administrator on the server or have the sysadmin role on the SQL server. You may have elevated privileges during install and upgrades.
- Please don’t set AUTOSHRINK on databases. That’s a very bad thing.
- Setting autogrowth on database files is okay with me, but please use larger increments like 1GB at a time instead of 1MB. You’re causing heavy fragmentation on my disks that way.
- Referential integrity isn’t an option. It’s a requirement.
- You may think people won’t be looking at your database back-end. You would be wrong.
- If your databases have no indexes, performance is going to be dreadful and force me to get upset with your support people.
- If I have to use ltrim() on your primary key column when writing a query, you’re doing something very wrong.
- Don’t assume your database will the only one on my database server. It probably won’t be.
- Many of your customers are bound by Sarbanes-Oxley and have requirements under that law. Your application should make it easier for us to comply.
- We have to keep auditors happy. Your application should help us.
- Eventually, people will want to use reporting tools to do things you hadn’t considered. Please provide good methods for getting to the data.
- We have to take care of things like high availability and disaster recovery. These aren’t foreign concepts in technology. We’re perfectly sane when asking you for best practices with your application in these things.
- Don’t be afraid of providing documentation that goes over the head of my business users. I’ll probably be able to understand it.
- If you really love your customers, you’ll provide an ERD so we don’t have to go spelunking through the database.