How to further troubleshoot an MS Access database corruption in a Citrix Environment
I have a Citrix app which runs a MSAccess frontend, .accde in each users Temp folder on one of 4 citrix servers, each running MSAccess 2013. The Backend is an mdb database on another server close by from the Citrix servers, with a gig speed between them. There are 10-12 simultaneous users. The frontend is 110mb and the backend is 550mb. (About 250 tables on the backend)
I continue to have issues which lead to corruption and the need to compact/repair almost on a daily basis. I have two other sites which do not use citrix, but have their own local server with 10-25 simultaneous users at each site. Those two sites are using the same frontend as the Citrix app, with none of these issues. (Errors which would lead to corruption, ie Invalid bookmark, Unrecognized Database format, etc.
I know the memo field can cause issues. I also deleted any records that had over 1000 characters. Still having issues.
At first I noticed that several users were going around Citrix and accessing the application as they did prior to Citrix, like the other two facilities still do. This was causing two different versions of MSAccess to be connecting to the database, ie 2013 and Office 365. The literature seems to indicate this can cause issues, so I ensured that everyone is using the Citrix app. This did not correct the issues.
SO, I have imported the backend tables into a new database. This helped for maybe a day before having similar problems.
I have deleted all relationships and copied relationships from a previous snapshot 6 months earlier. Still without success for more than a day.
In checking a table that has a memo field, I noticed there being many with 1000-5000 characters. I deleted all records which were over a 1000.(Many were old records and did not need to be saved anyway)
I did notice today that several records had a memo field with #Error displayed. When clicking on this, the Unrecognized Database format error would display. I later ran a compact and deleted these records and later added them back from a clean backup. Still ends up with issues.
I seem to be down to one of three causes:
1. The Citrix Server(s)
2. The Server the backend is located on. (It appears to be a cloud server with a 1000 gigabytes of disk space, so it seems to be part of a shared virtual servers setup)
3. The backend has some issues I cannot seem to correct with Compact/Repair or moving everything over into a new database. (I will be testing this database with Access Fix software again to see if it can identify anything. I ran it once before and it seemed to have no problems)
Any suggestions at this point would be very welcome!!
This is a bad idea. Access cannot run over any type of a WAN. In fact what I'd do is put a check in the app for the station name, and if it's anything other than a citrix server, I would refuse to start the app.
It would only take one user connecting this way to corrupt the DB.
<<2. I could move the backend to a different dedicated server which would be only used by these users. If no problems arise, then clearly it was the other server. >>
This would be good to do for sure. The most stable setup you can have with Access is the users being on the same machine hosting the BE database. There's no networking involved and it's very stable that way.
<<3. If 1 and 2 fail I am back to further diagnostics with the backend database
Ideally, we should have a dedicated Citrix server which by itself can handle 20 users and have the backend database on it as well. Avoids network issues since each session is just connecting to the database on the same server. But getting the IOT Citrix department to do this will be difficult. And of course it increases the cost >>
Yes, that would be the best setup, but might be difficult to achieve. Before going down that route, see if you can get them to turn off leasing on the server that is hosting the BE, which is covered here:
https://support.microsoft.com/en-us/office/access-reports-that-databases-are-in-an-inconsistent-state-7ec975da-f7a9-4414-a306-d3a7c422dc1d
The other thing I would do is get IT to exclude .accdb/.mdb from any kind of virus scanning or behavior monitoring software, especially if it is AI based.
Beyond doing those, some more details would be helpful:
1. When the DB becomes corrupt, what do you see? Error message from Access, bad data in records, etc.
2. How many users are there in the DB concurrently?
3. How old is the app?
4. Is this a recent problem?
5. Any recent changes in the environment?
6. Is it a virtualized environment?
7. If so, VMware or Hyper-V?
8. When the DB corrupts, is there any kind of pattern to it? Time of day, users that are on, functions being performed, etc.
The other thing to at least consider as a way out of this would be a move to SQL server for the BE. How big is your current BE?
Jim.
ASKER
Thank you for all your input!
The bottom line is we are in the process of moving the BE to a SQL server. But things move very slow and in the mean time, just trying to keep this viable for another month or two.
I agree with your #1 assessment regarding using this over the WAN. It was not our choice when the server was moved nor were we given any warning. Surprisingly it did work, though slow, for 6 months, with maybe two incidents of corruption.
#2, maybe I was unclear, we would still be using citrix, but I would move the BE to a different server. They would still access it thru the Citrix app, but there would be no other traffic on that server.
- I will check about the virus scanning
You asked if it was a virtualized environment..I think the answer is yes. The citrix servers adjust resources as needed. I'm sure they are virtual servers. I will need to check about VMware or Hyper-V. What issues would this present and how can they be corrected?
It seems to me that the problems arise in conjunction with the user adding a log with a note field which is a memo field. There does not seem a pattern as to when.
Just curious that the two other facilities do not have this problem.
I have considered changing this particular field to a text field. But of course it limits their ability to elaborate.
I may do so just to test the theory.
VMware had at one time problems with their network drivers, which would corrupt Access DB's. I don't know if that was ever solved.
For right now, I would:
1. Check the virus scanning/behavior software
2. Turn off leasing.
3. Start looking for network problems.
But that's just some basics to check. Without answers to the questions I asked, it is hard to offer anything else.
Jim.
ASKER
Regarding these questions:
1. When the DB becomes corrupt, what do you see? Error message from Access, bad data in records, etc.
Invalid Bookmark, Unrecognized Database.., The Microsoft Office Access database engine stopped the process because you and another user are attempting to change the same data at the same time ..
After a Compact/Repair have seen records in the tblLog table corrupted. Most recently, about a dozen were showing an #Error in the note/memo field as described above. Out of the 250 tables, there have been only 3 tables that had any issue. Two of them only once. More often than not, it was the tblLog table.
2. How many users are there in the DB concurrently?
10-12
3. How old is the app?
Has been under continual development since 1988 and in Microsoft Access since 2001 thru this year.
4. Is this a recent problem?
Began to be an issue with the Citrix environment not long after starting with Citrix. Initially could have been due to some still accessing the database using a different version of MS Access. But in the past week the users have only been using the Citrix and its installed 2013 MS Access
5. Any recent changes in the environment?
Just the Citrix setup
6. Is it a virtualized environment?
Yes
7. If so, VMware or Hyper-V?
I will have to find out with IOT
8. When the DB corrupts, is there any kind of pattern to it? Time of day, users that are on, functions being performed, etc.
As mentioned above, it seems to be centralized more around the addition of a user log with a note field which is a memo field.
Thanks Jim for the time you have put into this question. Really appreciate it. Hopefully I can come up with a resolution. I did have AccessFix check the database and restored. In the process it recommended the 2002-2003 format for the final restoration, which I accepted. I will be using this tomorrow. My next step if the issue continues is to change the memo field to a text field. (Will import all the tables except the tblLogs table into a new database; import only the structure of tbllogs, change the memo field to a text field, and then run a query to add all the tblLog records back in, which should of course bring in no more than 255 characters from that memo field.)
The vmxnet3 has advanced features, was a bit buggy, and I don't know if it's ever been fixed. I ran into this quite some time ago (years) and I'll have to dig for the settings that needed to be changed if you are using that.
In the case I had, it showed up with WireShark; lots of TCP disconnects and retransmits on the network. Took me three months to track it down. Changing some settings eliminated them and took care of the problem with Access. It wasn't just Access in my case either. Other software would see occasional ODBC errors talking to a BE RDBMS server. Access though was bothered the most by it because of its design.
Since this is only a month or two effort, and it's probably going to be difficult to get IT to change anything, the other other option would be to get all the users onto a single server with the BE so networking doesn't come into play.
In my case, that's what we did to quickly avoid the Access problem. I then had the time to track it down. But if you are moving to SQL, I would not change anything and just move the users.
Jim.
ASKER
If the network issues are not corrected, would SQL as the backend, also experience problems if accessed thru a citrix frontend app?
ASKER
It is possible. As I said, in the case I had, other apps would throw an occasional ODBC error, but it was so infrequent that it was basically being ignored. Client didn't even realize that had a network problem. It was blamed on Access for just being "Access" by the services company.
Access, like other client side ISAM based DB's, is pretty chatty network wise, so it notices network problems far sooner than most other products. But those products are not immune to network problems either.
SQL's not going to corrupt the DB through on you like it does with Access. With Access, all the db Engine instances run client side. There's nothing sitting on the server running. SQL Is the opposite, so there is always a process that can rollback the database when the network is interrupted.
Jim.
ASKER
Also, I learned the "fileserver" that the backend is on, is a file within an Isilon massive array of assignable space.
You would need to determine if they are using the VMXNET3 virtual nic.
<<Also, I learned the "fileserver" that the backend is on, is a file within an Isilon massive array of assignable space. >>
Um....that might be part of the problem to. Technically, Access requires a Windows OS as a host. Long ago, some of the storage controllers would cause problems, but I haven't heard of that in quite some time.
But one thing is for certain; you are not using VMware then with the BE, so that can't be the problem.
I would move the DB to an actual Windows OS Server and see if anything changes.
Jim.
- 1
- 2
- 3
ASKER
In regards to pinging from the FE machine, these are on citrix servers and are locked down pretty well. Not sure how to even get to the command prompt to ping.
But I believe there is gig speed between them.
I will have to check with IOT about the nic cards.
With IOT, don't believe physical server is an option.
ASKER
2. I could move the backend to a different dedicated server which would be only used by these users. If no problems arise, then clearly it was the other server.
3. If 1 and 2 fail I am back to further diagnostics with the backend database
Ideally, we should have a dedicated Citrix server which by itself can handle 20 users and have the backend database on it as well. Avoids network issues since each session is just connecting to the database on the same server. But getting the IOT Citrix department to do this will be difficult. And of course it increases the cost!