Database Underground | Sean McCown » More on Access

August 13, 2007 | Comments: (0)

More on Access

TAGS: Microsoft

OK, well, the response to my last Access post was a lot more than expected. There are some pretty good comments in there. Some of you made some very good points, both for and against my post. And for the first time in a long time I actually got on and approved comments so everyone could see them.

What I think is great though, is that a lot of you came out against my post, and ended up proving my point for me. I love it when that happens. Rather than dwell on those things though, I thought I'd just kinda explain why I wrote that to begin with.

I'm a professional DBA. It's what I do. That means that I deal with data all day. I backup and restore DBs, I develop DR plans, I spec sizing and performance for new servers, I troubleshoot indexing and performance issues, I recover corrupt data (as best I can), and everything else that goes along with being a production/dev DBA. In my group we do it all. I've been doing this for over 10yrs. And while I realize that there are plenty of you out there who have been in IT longer than I, a lot of you haven't been doing high-end DBs as long as I have.

So I'm looking back at some issues I've had over the past, and it hits me... a LOT of the issues I've been called in on have been due to end users pretending to be DBAs. The issues have been everything from 'we didn't put PKs/FKs on our tables and now we've got thousands of ghosted records' to 'I accidently updated all the records in my table and now we're completely screwed cause I didn't back it up.'

The issues are so varied there's no way I could recount them here, but those of you who support Access know what I'm talking about.
There are simple design principles that end users just don't know. And there's no reason for them to know them either. They're not DBAs. So why then do they want to develop their own apps? I get that it's quicker to use a tool like Access and keep the IT guys out of it. Yeah, sometimes things get over-engineered, but if you leave it in the hands of your end users then things will get under-engineered with almost no thought put behind it.

There's a reason we have DBAs. They know what they're doing. Well, they're supposed to anyway. And for every user that tells me that his Access solution is good enough, I always end up asking them a series of questions that makes them think otherwise. I've always felt that any data worth storing is worth protecting. It's clearly data you want, so why wouldn't you do what it takes to protect it? InfoWorld Daily just last week had an article on the true cost of data breach. So what cost would it be to you to have your employees be able to not just steal your data, but also the entire DB and application?

A couple of the comments to the last post said that I was wrong because there are tons of fortune 500 companies using Access all over the place. Do you think that makes one bit of difference to me? Do you think I really care what those companies are doing? Big companies can take chances with their data the same as everyone else... it doesn't make it right.

Even MS agrees with me. Look at how they're starting to treat SQL Server. In the old days, SQL shipped fully open. You could do whatever you wanted right out of the box. Now they're starting to shut things down more. With Yukon, you can't just install the DB and have it operational. You have to specifically turn on external connections. You also can't use openrowset or cmdshell right out of the box anymore either. The theory is that if you specifically turn something on, you're aware of its presence and are more likely to secure it and manage it properly. That's a method that's worked with Oracle for years.

I've always said that the general population of Oracle DBAs knows far more about Oracle than SQL Server DBAs know about SQL Server. That's because SQL has always been so incredibly easy out of the box that pretty much anyone could get a working DB up in just a day or 2. That's just not true with Oracle. It's becoming more true as Oracle's trying to become more user-friendly though.

So anyway, why do you think MS is putting all this effort into locking down SQL and making you jump through more hoops to open up functionality in SQL? Do you think it's because they're idiots, or because they've gone through their support records and saw where the bulk of their issues were and decided to start protecting us from ourselves? After all, any data loss we suffer in their product instantly becomes part of the reputation of that product whether it's their fault or not. So why wouldn't they follow this same reasoning with Access? Why give users another avenue to create a data store that's ill-conceived and basically unmanaged? From my perspective it just doesn't make sense. Like I said earlier, there's no such thing as trivial data. You either need it or you don't.

Most of you guys seem to treasure Access for its GUI abilities. Almost all of you agree with me on its merits as an actual DB. So why not pressure MS into making it a front-end dev utility? Have its functionality added to Visual Studio or the like and be done with it? I'd probably have a lot fewer problems with Access if it were just a dev environment and didn't put your data at such risk. Or if people used it the way they should and just connected to server-side DBs instead of using it to store data. That's the problem, isn't it? People have been given a pistol without a safety, and they insist on looking down the barrel.

Remember, in DBs, just because you can doesn't mean you should. MS even took object-level restore away from us in SQL 7 because of integrity issues. They wanted to protect us from ourselves. A position they're finally reconsidering. MS isn't stupid. They've got a lot of smart people working for them. And they know how to manage data. So with the way they manage SQL and the way they manage Access, I can only conclude that they're saying anything in Access isn't data worth keeping.

Let's face it, as a company, there's just no reason to give your users the ability to walk off with your entire DB and application. Why even take the chance? And there's no reason to let amateurs create DBs and apps. You're just asking for trouble. Look at it this way, it goes back to a lot of my previous posts where I talk about the lack of respect for DBAs and data in general. You'd never find one of these companies letting some random guy in IT file their taxes with some off the shelf home or small business tax software, would you? Of course not. They take their taxes too seriously. And you'd never find a company letting some random secretary file their numbers with Wall Street, would you? Absolutely not. There's too much at stake. How about making sales deals? Could I just make a sales deal with someone for my company? I mean, why spin up a project for it, or go through the channels and get a sales guy involved? They'll just complicate things and a month later we'll still be waiting. I can make the deal in just a couple days. I'll even go through a day of sales training if it makes you feel better. No wait, here's a better idea. I'll go through a day of training, and then I'll go negotiate our health plan. Maybe I'll choose where our 401K will be invested instead.

All of those suggestions are ludicrous and no company on the planet would even consider any of them because every one of those things takes a highly skilled professional to do properly. Someone who knows that field inside and out and knows the pitfalls. But data... hell anyone can do that. Let's let the dept secretary run an Access DB on her workstation. She can build it and maintain it too. What's a foreign key? Oh, that's not important. What am I going to do if someone updates all the data a table and there are no log backups? Just never you mind. And how often should we back it up? I'm sure someone else will be in charge of that. What about data theft? Oh, that's just an old wives' tale. Is this data already being stored somewhere else? Who knows? What about integration with other systems? Will other people find this useful? Now you're just talking crazy. It's really like being your own lawyer. An end user who writes his own application in Access has a fool for a DBA. At least if you went with the client/server model you'd have to study up some more and you stand a little bit better chance at success.

Now, that's not to say that Access doesn't fulfill a purpose right now. From all the comments I've seen, almost every one of you values it as a front end tool. It's just that MS should make it a front end tool and leave it at that. They should take steps to phase out the DB portion of the product. But that's just my humble opinion. A lot of you were getting very upset at the mere mention of doing away with Access. I don't know why though. It's not like MS is going to do it just because I said it. I could see it now. The Access team sitting around going, oh man, now I've gotta find a new job. Sean said we should get rid of Access. Oh well, I liked the tool and this job, but what can I do... Sean said it has to go.

I'm gonna go off on one last diatribe and I promise to keep it short. Every enterprise data modeling class I've ever seen taught by a college has been taught with Access. Seriously guys, what the hell? While I've seen modeling classes taught in other DBs, they're usually a section in a line of courses for that specific product. But for general and enterprise modeling classes, they're always in Access. I'm not even going to touch that one.

Posted by Sean McCown on August 13, 2007 02:06 PM

RATE THIS ARTICLE: