Handling uncontrolled database development 8

[rosieb]

I’m in database hell!!

I work in a local government IT Dept where I have been given responsibility for Access databases as part of my job (the main part is data and process analysis and development).

We have c250 users, about 175 with full versions of Access, and I’ve traced about 450 separate Access databases on the network, I know there are others on local drives “because it’s safer than the network”. Many of these are obviously obsolete or copies, but I don’t know which!

Referring to an earlier post, thread655-606833 , I have Platypi, Chimerae, plus a whole lot of other types (including Snarks, Amobae and probably Krakens, Grendels and Cockatrices). Many are de-normalized and badly designed; all are undocumented. Worst of all, a significant number are “mission critical”. In many cases the originator has left the organization. Any problems are, of course, an IT problem (ie, MY problem). Any lack of an immediate solution is seen as just proving that IT is incompetent and unhelpful.

I’m trying to bring the situation under control. Ideally I’d take full Access away from all users and insist that all databases be developed by IT, but the world don’t work that way. I’m all for user empowerment, but in this reality – it’s dangerous.

I’m pursuing a number of avenues, but one thought is to develop some guidelines for anyone creating new databases (normalization, get the design checked by IT, split the database, only work on a development copy, documentation, etc.). A sort of kite-marking. Then, we only support databases which comply.

I want it to look reasonable, but hopefully bring home to some of our so-called experts (quote “I’m not going on a course, there’s nothing they could teach me”) that it’s not as easy as they think.

So, any ideas on what I should include? Any suggestions gratefully received.

Rosie

PS I’m told the budget doesn’t run to medieval instruments of torture, shame.

 

[lespaul]

Rosie,

As the owner of the original Platypus database, I can sympathize! (that turned into a very amusing thread!!)

As far as your problem goes, how much authority do you have over the databases, especially the mission critical ones? Are the other databases created and used by just one person for personal use or are they also distributed?

I think your idea of having general database guidelines is a good place to start. JeremyNYC has a great article on his website that deals with Relational Design that perhaps you can get your "so-called experts" to read.

http://www.alphabetcitydataworks.com/Developers/FundamentalsOfRelationalDatabaseDesign.doc

And I also agree that you should only maintain databases that are developed within the guidelines. If someone feels they can build a database, then they can maintain it.

But again, do you have the authority (or can you get approval) for the guidelines?

Luckily most of our users don't do any "development" of unauthorized programs (they don't know how!!).

Leslie

 

[fredericofonseca]

Hum.

First I agree with Leslie.

In my shop the databases that were considered "mission critical" were all removed from the users hands and placed on the network. (this is were backups are done is it not?)

First get numbers of how many, which ones are critical, which ones are redundant and so on.

Then prepare a nice report and speak with your manager. Try and agree on the steps to be taken with regards to the following.
1- Responsability for databases development and maintenance.

2- Localization of the same (network whenever possible, but special cases may need to be considered)

3- Licensing issues. Most users don't need the full Access license, only a runtime(Free). Development Licenses cost a LOT.

4- Data protection issues. Your company is responsible for all the information stored on your computers. If you are required to give this information you may find that you are not complying with the rules.

5- After deciding who can develop databases, decide on implementing development standards for those users.


I'm sure there are other issues that can be raised, but the above will give you enough to work already.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[edfair]

Access isn't in my line of work, but I can recognize a lose,lose situation, and this has the makings of one if left to fester.

I would fight the centralized battle later. And it really isn't your battle , but your boss's. You load the guns.

Like you say, mission critical, non-documented. I would prioritize the bombs from largest to smallest and get the documentation resolved and a line of authority on backup working from the most critical down. You be cooperative and easygoing and let your boss be the bad person.

And document the suggestions you make. Sounds like the little fiefdoms would try to wiggle out of any problems.

Ed Fair
Give the wrong symptoms, get the wrong solutions.

 

[bi]

Don't forget documentation as being part of the standards.

Regarding local copies: I can't imagine a mission critical database having to be on someone's own PC rather than a network, unless the company is keeping two sets of accounting books. You can protect network shares from prying eyes and if you can't trust your network folks from snooping, maybe you need to get new ones?

 

[rosieb]

lespaul

As you can probably guess, I loved the Platypus thread, it rang so many bells. (I think we need a full taxonomy of databases, tho’ that’s probably for a separate thread.)

In my environment “authority” is a difficult term, as I find the mission critical ones, I’m taking control – on the basis that if they need me to support it, I need control. [Currently studying up on HOW to setup security etc., I reckon I’m a good intermediate on Access, but I know my limitations. Some of my “expert” users are serious meddlers: “I just changed the colours; took some of the rubbish out of the report; and now it doesn’t work” type of thing....]

Most of these databases are distributed in one way or another (in one classic case, so far, 5 separate copies, and they don’t understand why they can’t see each others’ data!!!!).

Really nice link, I’m going to study it in detail tomorrow.
Thanks

fredericofonseca

Good points.

Working on 1 and 2, 3 is a problem – the various departments have a lot of autonomy on buying licenses, we’re trying to close it down, Data Protection is a really good point.

My manager is fully aware of the problem, he wants me to identify the targets, load the bullets, and point. Then, at the right time, he can fire. I’m gathering the ammo.

Your point 5 is my key area.

edfair

Absolutely right, it’s been festering too long – hence the problem.

Point taken about prioritization, one of my problems is that I don’t find out about a database ‘til it develops problems.

(I’m working on being a nice person but sometimes it’s hard going.)

bi
You’re right, any suggestions as to WHAT documentation I can (reasonably) ask?
The local copies issue doesn’t seem to be one of trust re snooping, either users seem to think that their local drive is “safer” than the network OR that it IS part of the network. The problem is educating the users (I’m also doing remedial training in how to copy & paste plus how to clean mouse rollers, amongst other things).

All
I really do appreciate this input, I feel I’ve got too close to this to approach it sanely.

Rosie

 

[bi]

sigh. It is too bad the ones who think their data is safer on an unbacked-up local drive didn't have my first boss. She would have a fit if she saw you working from your local drive because she lost a hard drive once. Then I lost my drive, but didn't lose anything really important because everything was on the network. I sympathize with you. [I also worked with someone who insisted on downloading things from the network (without telling anyone), making changes, then uploading back to network. Problem was, if you were making changes on the network copy, her copy would overwrite your changes.]

They might worry about the speed of a restore, so if you can assure them that won't be a problem, maybe that will help. And they may worry that someone else will delete it or alter it. You can assure them of that, too, by making sure the security level is set correctly and showing them.

On what to document: just some quick thoughts. I may add to them later and I'm sure some DBAs will have their ideas, too.

name of database; location ON THE NETWORK ; purpose of database; who it is aimed at; who authored it; who updates it; how often it's updated; reports that can be generated from the database, including name of report and maybe even a sample of each report; how to request changes to the database (such as adding a new report); maybe the sql statements that generate the reports; and a list of changes made to the database since it went into production (a sort of change management section. This will help the owner/author to remember what has been done to the database.)

I'll try to think of some more.

Good luck.

 

[rosieb]

Bi
Thanks (I know this is getting repetitive, I’ve run out of synonyms, but no less sincere)

Useful stuff on the content, it all goes on the list. If you do get any more good ideas….

As to the location on the network, it’s always the S: drive, or the P: drive or whatever – absolute mappings are a complete mystery – over a week, I spend hours tracking things down; and if for some reason people change mappings where they have linked databases, total chaos. I could ALMOST wish for a couple of lost hard drives, it might concentrate a few minds.

Frighteningly, they actually believe that the local discs are less prone to failure (the term back-up does not seem to compute). But then, one user whose plethora of databases I’d questioned regarding their purpose, suggested I’d deleted them – it turned out he’d lost his shortcuts – I had to spend an hour tracking them down on the network (multiple copies again) and re-creating the shortcuts. (He’s still far too busy to help me cull the obsolete versions.)


I know, I’m whinging again, but aaaaaagh.

Ta

Rosie

 

[DonQuichote]

I want to add a remark to this thread about how "mission critical" things are.

If the data is really mission critical (and no doubt some of it is), then it is not just your problem. It is the whole company's problem. Have a good talk with your boss about that. If the whole company does not realize that there is a problem, you alone will never be able to solve it. All the users together can create a far greater mess than you alone can clean up.
Be sure to point out problems of backing up data, out-of-sync databases, maintainablilty and data that is never checked. (You'd be surprised how many "mission critical" databases there are in this world that have never been checked or validated! Personally I'm even more flabbergasted that the builders think that is normal.)

But not everything will be mission critical. Obsolete databases should be removed. Have the courage to do so. Databases that contain "highly mission critical" data from someone who has left the company and that nobody understands do in fact have no data at all. Make a back-up if it feels good, but remove them.

Also, data isn't just data. It needs to be updated and cleaned up. There are all kinds of data and some people are responsible for it (or should be). Have a few people assigned to certain data: Some deparetment is responsible for the employees table, another department for the projects table, etc. These kind of tables should exist only once somewhere on the network. Other databases that need this info can link to the central databases. This will ensure that any mission data will be up-to-date.

One note about documentation: programs and datases get changed 5 times a day, documentation gets changed 5 time a year if you are lucky. Good, clear and maintainable design cannot be replaced by a document. Access has comment fields everywhere and I suggest that you use them. Make the databases their own documentation. Use clear table and field names and state the exact meaning of usage in the comment fields if necessary. Introduce a naming convention, for example the "hungarian notation" that Microsoft uses. Use the relations window to make a clear layout of the database scheme.

I am glad that you want to do this together with the users. That is the good attitude. Make the users aware of what there is to know about databases and what they already know. They will ask more questions and build better databases.

Good luck!

 

[jrbarnett]

Another thought:

If all these "mission critical" databases are such and such, once you have approved them, I would upload the data into an SQL Server or similar back end, and use Access as a front end to it, even if there is only one user. It might seem overkill at the start, but it will help you in the long run.
The users won't be able to tell the difference if it is done properly, and you have the data all in one place for centralised backup purposes. Doing this would also uncover duplicated data amongst all these different databases, which there is likely to be some.

John

 

[Stevehewitt]

Hey,

I would talk to the Network admins. Get those machines locked down. Users shouldn't be able to re-map drives or save to hard drives. I agree with John above - SQL sounds like a much better idea if you have multiple Access DB's - some of which are mission critical.

I would grab all the Access DB's secure the ones that are mission critical and ensure that they are on SQL server or BE is on a locked down network share. Then all of those that are not mission critical - Nick them! Send out a circular the second after you have done it saying that due to problems with local machines all Access DB's have been taken off line. Please see the DBA for assistance. If the DB is worth something then people will come up, you spend 10 mins telling them where you are going to store it and pass them a developers sheet. Enable access rights to the DB to the users that require it.

My two pence!

Steve.

Steve.

 

[fredericofonseca]

Rosieb,

I am not going to add to the thread as the most important things have been said.

Now regarding the mappings I have a question.

What type of File servers do you have?
Novell, NT or Unix/Linux?






Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[manarth]

I've not found a user yet who needed to alter the database - only the data.

I don't like spending money unnecessarily - my DBs run on a MySQL server operating on Linux. Most of my DBs run with a php web front end, so users don't even need a client program installed to make use of the database.

+ no client licencing required
+ no server licencing required
+ data is stored centrally, and easy to backup (automatically, using cron)
+ access control can be addressed centrally and monitored using a user list / logon mechanism
+ DPA requirements are easier to fulfil

- the data needs to be migrated to MYSQL and normalised
- you'll need to build a web front end.

<marc> i wonder what will happen if i press this...[ul][li]please give feedback on what works / what doesn't[/li][li]need some help? how to get a better answer: faq581-3339[/li][/ul]

 

[chiph]

Something to help persuade people that the databases need administrating is to do a survey on the cost should a couple of computers get damaged, stolen, or otherwise crash.

The stolen scenario is probably the one to focus on, as they probably don't want some of the data to be made public.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[bi]

rosieb,

Something else that needs to be &quot;documented&quot; is when the databases are going to be backed up and, presumably, unavailable to the users.

I like DonQuichote's remark about documenting the details in the database itself and setting naming conventions.

You/we are getting a lot of great ideas here. Don't bite off more than you can chew. One successful &quot;small&quot; project will go a lot further in convincing the others of the worth of what you are trying to do than a so-so result or a failure.

Good luck.

 

[dilettante]

I had the idea that MS Access was moving toward a &quot;Jet-less&quot; model, where only MSDE was available as an engine?

Since this won't work as a file-share database it would kill off everything but single-user desktop Access databases, wouldn't it? And you sure don't need users requesting extra machines to run as database servers to host shared MSDE databases.

But maybe that's in Office 12? We still run 9 (2000) for the most part.

 

[rosieb]

My cup runneth over, I cannot believe how much really useful stuff I’m getting.

DonQuichote
I’m very conscious of different levels of mission criticality, I work for a local council. One of the databases (actually the worst) manages the re-homing of homeless people – that one I see as really critical. Another controls the emptying of septic tanks, again potentially very nasty (though cause for a little toilet humour). The rest are probably less so.

Like me my boss is relatively new, but very keen to get a grip on the situation. Getting real understanding from other heads of departments is a problem, it’s very hard to make them appreciate the importance of data validity, I strongly suspect that some of the data coming out is inaccurate (there is no culture of testing or validation, just an assumption that if it comes out of a computer it must be right). I’m working on that one. Ditto on culling obsolete databases, I’ve already started some subtle zipping and hiding of old stuff (who needs authority when you have the permissions?). Back-up isn’t a problem, all servers are backed up each night and, yes, the back-ups are regularly tested.

Your point about documentation is a good one.

I’m working on standards for naming, and for structure of key data types, for example addresses, with the aim of ending up with a single authoritative source for such information.

jrbarnett and Stevehewitt
Spot on, that is part of the master plan. Actually for the really important stuff, we’re aiming to go for proper commercial applications rather that the current Noddy versions. More expensive but easier to administer.

fredericofonseca
We’re currently running NT but planning to upgrade to 2000 (I think) shortly.

manarth
If I could only convince them of that. The users feel the need to “improve” the databases (if they’d been properly spec’ed and designed…..) and currently I’m the only IT Access resource – and only half time on that.

Bi
You’re right about pacing it, it gets hard to see the wood for the trees and I’m conscious of trying to do too much at once.

dilettante
That, I’m afraid, went completely over my head. But it sounds really interesting, I’m going to have to do some research



I need to spend some time REALLY thinking about all of the above posts and adding the input into the mix. So much good stuff.

Rosie

 

[fredericofonseca]

Rosieb,

Maybe not your work directly but you may wish to point out to your boss the benefits of using a closed environment where users only have access to a limited set of applications on their workstations.
Something like ZENworks (and other products eventually) can enable you to do this.

Just to give an idea of this on my company most users only have access to Word/excel, email (Lotus notes), Terminal Emulation (connect to AS400) and IE (For access to local Intranet).
They don't have access to anything else on the machine except for their own local folder, and the network drives.

Access to their applications is done through NAL (Novell Application Launcher), they don't have access to the Desktop, and all drivers are remaped whenever they login.

Installation of new machines is done automatically, delivery of applications to the users the same. They just click on the corresponding NAL icon and the application is installed if needed. (IT guys heaven!! No time spend doing this!)






Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Kjonnnn]

QUESTION.

If these databases are only used by one person, how much does it increase network traffic for each of these people (let's 30 databases) to access their database across the network, that use to be on the computers?

 

[ChrisHunt]

Don't be surprised if people think their hard drives are more reliable than the network. It's a natural (though wrong) reaction to common experience. When's the last time you came to work and found your hard drive unreadable? When's the last time you found the network was down? Which is going to be perceived as more reliable? Furthermore, if the network is working at (or beyond) maximum capacity, users have another reason to want to avoid it. You may need to invest in hardware as well as better education.

Similarly you have to understand, and deal with, the causes of all those databases. Sure, user ignorance is one factor involved, but not the only one. People have work they need to do, and databases are an important tool for many of them. IT resources are limited, so there will inevitably be people who need a job done, but can't get to the front of the queue for IT to do it for them. Where do they go? What do they do? If you shut them out of Access, what will they use instead? A spreadsheet? A Word document? A card index?

Controlling everything in-house is no doubt appealing to us IT professionals, but it isn't always practical. I'd look more towards using the skills of your power users in other departments. How about looking into Certification schemes for them - offering them a genuine career advance for going on training schemes. You should certainly look at some Quality Assurance scheme for systems - if they don't make the grade, they don't get supported! Sometimes you need to be cruel to be kind.

-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk