February 19, 2015 at 1:38 am
Hi folks,
Hopefully i'm in the right part of the forum to ask this question...
We are using HTML and javascript(?) for multiple users to update an Access database at once.
Our old storage seemed to handle all these users perfectly, but now we have moved onto a cluster(?) and it has all fell to pieces.
Other terms our IT team have used are...
Virtual Servers
Hyper-V
DFS
Clusters
Our databases have gone through various issues including; corrupt database, duplicate errors, to simply ignoring some of the entries.
Thanks, hope someone can help or point me in the right direction.
February 19, 2015 at 4:20 pm
Access databases tend to be fairly fragile in an environment where peoples files are moving around, and it only takes one small glitch for the data to be corrupted, or an index to be modified incorrectly, etc. We need more information about the environment your "cluster" exists in. I understand Virtual Servers, which Hyper-V is one kind of, and DFS (which I assume means Distributed File System), but the word "Cluster" can have several meanings. I'm surprised the word "Cloud" hasn't been mentioned either.
Is this all within a private network, or do you have geographically distant users entering and/or editing data across the Internet? Also, how many users do you have? And what version of Access is being used? The use of javascript for working with an Access database is rather unusual - is it possible that it is actually Visual Basic, or VB Script? With answers to these questions, we may be able to provide some suggestions, but I suspect you may need to redesign your application, and possibly change to a different database engine such as SQL Azure.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 23, 2015 at 8:19 am
Hi, thanks for replying. Our infrastructure chap is back from holiday and kindly provided a little more information...
We are now running:
Failover file server,
Shared storage,
Using Distributed File System,
NOT load balancing,
Running Windows 2012 Server (Not R2),
We were on Windows 2008 R2
Finally, the provider used to show Microsoft.Jet.OLEDB.4.0, now its Microsoft.ACE.OLEDB.12.0
When opened Access shows as (2000 file format), my local machine runs Access 2010.
All users opening the HTML web form are located in the same office. I expect only 20 users max to be entering data at once.
As far as we can tell the issues are with the database lock file crashing when too many users input at once.
We originally thought the server was managing copies of files in different locations which in turn was causing sync issues. After our discussion with infrastructure this seems unlikely.
February 23, 2015 at 8:59 am
Smit-101 (2/23/2015)
Hi, thanks for replying. Our infrastructure chap is back from holiday and kindly provided a little more information...We are now running:
Failover file server,
Shared storage,
Using Distributed File System,
NOT load balancing,
Running Windows 2012 Server (Not R2),
We were on Windows 2008 R2
Finally, the provider used to show Microsoft.Jet.OLEDB.4.0, now its Microsoft.ACE.OLEDB.12.0
When opened Access shows as (2000 file format), my local machine runs Access 2010.
All users opening the HTML web form are located in the same office. I expect only 20 users max to be entering data at once.
As far as we can tell the issues are with the database lock file crashing when too many users input at once.
We originally thought the server was managing copies of files in different locations which in turn was causing sync issues. After our discussion with infrastructure this seems unlikely.
You're well outside of the recommended concurrency level for an Access DB in my experience. Anything much over 10 users tends to lead to a LOT of crashes and corrupted DB's. This also goes up dramatically if you have the Access DB in a folder being scanned by some real-time AV provess.
You should definitely look at moving the data into SQL Server rather than Access. I've used Access as the "UI" to a SQL server database for years in small environements, but the locking and consistency issues on the data required moving the data out (and spending some time optimizing data access). In its default state - Accesss tends to be very "write intensive" and will tend to ovewhelm disk substructures if it's in charge of persistence.
You should be able to run this in a SQL Server "express" copy, although I'd consider putting it into a Stadnard edition (which will make it much easier to use some of the automated tasks, etc...) Either way though - you will likely need a good amount of help from said infrastructure specialist AND someone versed in wiring up Access to external data sources AND a SQL SErver DBA: this is not trivial by any means.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply