Pau Lo
asked on
access BE location best practice
Are there any ideal locations/best practices on where to store the backend database behind access DB databases?
Do you put them on a dedicated server, or is that overkill (thinking cost vs benefit). Need to investigate the costs of a bare bones windows based VM with access installed.
We have inherited an access based app, of which the backend of the database is only approx. 100 MB in size, so very trivial really. However, the backend DB is just located on a file server alongside about 1 million other documents. Permissions are restricted to only users of the application, however, but its still on the same drive as the other teams data.
We don't have local admin rights over the server so troubleshooting file locks and the like requires another team to investigate who prioritise such a task accordingly in line with other ICT incidents and it can take hours to resolve something that would take approx. 30 seconds!
Are there any other risks / issues / concerns you can foresee in storing the backend DB on a general file server? I am trying to build a case for moving it, but don't have too much in terms of issues on why to do so, or where too.
Do you put them on a dedicated server, or is that overkill (thinking cost vs benefit). Need to investigate the costs of a bare bones windows based VM with access installed.
We have inherited an access based app, of which the backend of the database is only approx. 100 MB in size, so very trivial really. However, the backend DB is just located on a file server alongside about 1 million other documents. Permissions are restricted to only users of the application, however, but its still on the same drive as the other teams data.
We don't have local admin rights over the server so troubleshooting file locks and the like requires another team to investigate who prioritise such a task accordingly in line with other ICT incidents and it can take hours to resolve something that would take approx. 30 seconds!
Are there any other risks / issues / concerns you can foresee in storing the backend DB on a general file server? I am trying to build a case for moving it, but don't have too much in terms of issues on why to do so, or where too.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Gustav Brock
Keep it where it is. Indeed if you don't want the trouble of maintaining a separate machine, including backup etc.
Well, I guess you need to more research for requiring your requirements here.
There are multiple different aspects:
1) Some file servers and their infrastructure are optimized for delivering documents. An Access database is opened and read chunk by chunk when needed. Also such document traffic may have lesser network priority.
2) Recovery procedures in your company. How long does it take to do disaster recovery, when you need to use the fileserver associated procedures? Also your mentioned lock issues.
3) Compliance in general.
You need to assign that an overall cost (for down time etc.) and business use. Then you (your management) can decide the road to go:
- Stick with the current system.
- Get a separate file share on a different system with different IT processes.
- Use a SQL Server (Express is free) backend.
There are multiple different aspects:
1) Some file servers and their infrastructure are optimized for delivering documents. An Access database is opened and read chunk by chunk when needed. Also such document traffic may have lesser network priority.
2) Recovery procedures in your company. How long does it take to do disaster recovery, when you need to use the fileserver associated procedures? Also your mentioned lock issues.
3) Compliance in general.
You need to assign that an overall cost (for down time etc.) and business use. Then you (your management) can decide the road to go:
- Stick with the current system.
- Get a separate file share on a different system with different IT processes.
- Use a SQL Server (Express is free) backend.
There is no need for a separate server. Jet/ACE which are the database engines you are using if your BE is Access would be running on YOUR PC and not on the server so it is most important that the local PC have sufficient RAM and speed to handle the application.
Restricting permissions on the folder holding the BE is important to prevent people from accidently (or deliberately) messing with the BE data. Better still is to have the BE in a hidden subfolder so not even the authorized users can actually "see" the BE file on the server. This will prevent anyone from being able to copy the BE from the server to their PC or thumb drive so they can crack it at will.
Because of the way Access works, users must have create/read/update/delete permission for the folder holding the BE file so if they were to navigate to the folder, they could conceivably delete the BE. So hiding the folder minimizes this risk and you can also change the permissions on the BE file to prevent delete but this will mess up your compact procedure so I don't normally do it.
Restricting permissions on the folder holding the BE is important to prevent people from accidently (or deliberately) messing with the BE data. Better still is to have the BE in a hidden subfolder so not even the authorized users can actually "see" the BE file on the server. This will prevent anyone from being able to copy the BE from the server to their PC or thumb drive so they can crack it at will.
Because of the way Access works, users must have create/read/update/delete permission for the folder holding the BE file so if they were to navigate to the folder, they could conceivably delete the BE. So hiding the folder minimizes this risk and you can also change the permissions on the BE file to prevent delete but this will mess up your compact procedure so I don't normally do it.