Microsoft Access on a network

Hello
Why not consider a solution like RDS (remote desktop solution)
The performances would then be better
See a remote app solution
David

Thanks David.  Not exactly what I was expecting but then, that's why I am on here!

I'm not really sure why that would enhance my network performance but perhaps you can point me to an article that explains this?

If it’s like most SBS servers, it’s probably overworked.  Have you done any performance checking?  With a Jet based data store,  processing is carried out on the client side. But still the server needs to serve up the file and handle locking,  so if it is overworked it could be slowing things down.  I would be checking CPU utilization, memory utilization, and how much network is getting chewed up.

Network attached storage would be faster,
as would simply getting a new server.   Do they have any plans to upgrade?  

 As far as RDP, unless your users are remote, there’s no reason to bother with it really.

Now  with all that said however, poor performance in most Jet based apps Is due to poor development.

Jim

In an effort to improve the data flow speed I'm wondering if moving the database to a new network attached storage device would be an improvement?

technically NAS will be faster in terms of data access and stability.

The maximum number of simultaneous data calls from different users is 7 although on most days 3 or 4 users would be normal.

you may consider to upgrade Access to a more reliable RDBMS such as MS SQL or MySQL for concurrent access, if that's part of the plan to go.

I presume that the app wasn't always slow.  the one Access-related thing you can do which might improve things is to compact the BE and then compact the FE and distribute new copies to each user.  The compact will reduce the size of the BE and reorganize all the indexes.   Compacting the FE will ensure that each query will have to recalculate an execution plan and that will force them to get new statistical data from the BE which reflects its current state.

Beyond that, the Access application did not degrade.  Therefore, there is something about the server or the LAN that is causing the problem.

hi,

does each user have their own copy of the FE (front-end) that is linked to shared tables in one or more BEs (back-ends)?

A FE contains queries, forms, report, macros, modules, and linked tables.  It may also have some resident tables for storing information such as user settings.

A BE contains tables that are shared.  It helps performance if the path to the BE is short.

have an awesome day,
crystal

It could also be that the tables were created without indexes. Such a thing is not noticeable during initial development, simply because the amount of data is insignificant.

It could be that adding indexes in the right place is the way to go. It could also be that the developer made heavy use of dlookups inside queries which CAN also slow down the database. Or using where clauses on calculated fields. Or....

So to sum it it, there can really be many many different reasons behind why your database is slow. So a new server might resolve nothing. If you want to attack the issue yourself, start by looking at the "slowest" form, then analyse its query. If you open the query directly, without using a form, is data retrieval instant or does it take a while before it shows up?
Also try to execute a single dlookup in code, directly against a table, using the primary key field in the criteria. Again is the result instantaneous or does it take a while? That tells you something about the data retrieval process. A single dlookup should return in less than 0.1 seconds. If that takes more, it could indicate a bogged down server.


An alternative is to invest in a professional developer, to spend a few hours to look over the issue.

From my experience when you use Access from workstations with different Oses the Oplocks is an issue ...to check Windows 10 machines should work faster than XP..
Also for the NAS...usually a NAS uses a Linux distro -->Samba which also has issues with lockings....
With such a small user base you shouldn't encounter any issues ...its just that Access over network is slower than local Access...but this boils down to design...tables normalization...queries optimizations and so on...

Thanks to all who responded.  I passed the database structure comments on to the database author.

Client has opted to put the database on a NAS device while it is migrated to SQL.  Once the NAS in installed, we can relieve some of the load factors on the SBS until going cloud based.

One other thing has come to light today.  Not only do the database users have workstations with different operating systems but they also have different versions of Access although all are 32-bit.

Would this cause any problems in database performance?  If so, I need to get this resolved before moving the database to the NAS solution.

Different versions of the OS are a problem and can lead to corruption.  Different versions of Access are less of a problem.  Depending on what Access versions you have, you may be forced to run .mdb's rather than .accdb's but Access versions do not cause known problems that i have seen reported.  The different OS's use different locking strategies and that is what can cause the corruption.

Make sure that EVERY user has his own personal copy of the FE and they all share the same BE.  That is the most important way to minimize tht opportunity for corruption.

Thanks Pat.  

Interesting comment about the operating systems.  In this network, some workstations are on Windows 7, for compatibility with a third party software package but the others, that do not use that software, are on Windows 10.

Looks like I'll need to get that standardized!

I'm not sure if Win 7 and Win 10 are the problems.  I think it is usually older versions of the OS but I haven't run into the problem myself since my clients keep a more standard environment.  So, the warning is third hand based on threads here and other Access sites.

Thanks again Pat.  I'll keep an eye on it when installing the NAS unit.  If anything noticeably improves with a common operating system, I'll let you know.

Actually it’s Windows 7 and 10 that had the issues. Older OSs like XP were fine.

 Most of that was related to SMB two and three protocol problems rather than anything to do with OPLOCKS And all of that has been cleaned up by now

OPLOCKS only come into play when there is a single user in the DB and the second one wants to come in.

 Most NAS’s  don’t have issues anymore either if they are recent vintage. Early on they did have problems with Access locking,   That was mainly due to the number of locks.

 With JET/ACE DB’s and corruption, the most critical thing is to have a solid network.

Jim

Hi Jim,

So you're suggesting that running the same database on a mixed group of Windows 7 and Windows 10 machines in the same domain is not a good idea?

No, I'm saying you won't have problems with it at this point.

 The issues that you hear about with mixed OS's were introduced when SMBv2 and SMBv3 were first released and it was mixed environments of XP and Win7/10 stations.  

  SMBv2 was released with Windows Vista SP1 and Windows Server 2008.   SMBv3 was released with Windows 8 and Windows Server 2012, so both have been in production now for a number of years.   As long as you as your are keeping up with patching, you should be fine in this regard.

  The only remaining issue you would have is if a device on the network only supports SMBv1.    Microsoft fully depreciated SMBv1 in June of 2013, and as of Windows 10 build 1709, has started to turn if off by default, leaving you with SMBv2 and SMBv3 active.

 So as long as XP is not in the environment, then you need to be looking else where for network problems and performance.   Ditto the problems with OPLOCKS, which was mainly related to SMBv2 when it first came out.  Many tried to disable them, but really their problems lied else where.

 On OPLOCKs, they do work as advertised and will speed up operations.    Where OPLOCKs fall flat is with clients that don't flush to the server as they should.  But since SMBv2.1, they don't even exist in the same way anymore.   A new mechainisum was introduced, which is why there are not the problems anymore that you once heard of.

Getting back to your original question,  I would say once again that almost every SBS server I've ever run across is overworked, and usually they are memory constrained.   16GB is the bare minimum you can get away with with SBS, and 32GB is just adequate.

What you'll find with SBS 2008 or 2011 is that once it hits about 90% memory utilization, it will appreciably slow for all operations, including file sharing.

 A quick "fix" for an SBS server is to hard cap all the SQL instances so that memory usage falls below 90% or add more memory.

You plan to move the back end to a NAS would work equally as well.

Jim.

Thanks Jim.  

I think we're on the right track with the NAS system going into place to provide time to migrate the Access database to SQL and update the server.

Fortunately, I've already moved the SBS Exchange system to a third party mail server so I can start removing the Exchange and Forefront components of SBS to recover disk space.  I'll see what I can do to reduce memory usage by moving the existing third party software store to a second NAS.  

Of course, when the server was built in early 2009, it was maxed out at 16GB.  How times change.

<<
Fortunately, I've already moved the SBS Exchange system to a third party mail server so I can start removing the Exchange and Forefront components of SBS to recover disk space.
>>

  That will help a lot.   Exchange takes up a good chunk of memory.  If you haven't already researched, Alex Fields has an excellent set of guides to help with SBS migrations:

https://www.itpromentor.com/migration-guides/

 The one in particular you would need is:

https://www.itpromentor.com/sbs-remove-exchange/

Jim.

Many thanks.  Looks like I'll have some quiet weekend reading to do.  :)

As a result of all of the comments made on this thread, I opted to move to two Synology NAS devices, one for the Access database, which during the data transfer revealed that it makes calls on 80GB of pdf files, and the other for the third party database.  Both are now completely separate, are running much faster on the network, and are backed up to Synology's "C2" Cloud based service.

The old SBS 2008 unit is now little more than a domain controller but I'll keep it running for another month as a safety precaution, just in case  the database authors find that some data did not fully relocate to the NAS units.  

However, one odd thing has occurred.  The Access database author requires that the Synology NAS that now contains his data is mapped from each user workstation using the same drive ID as before.  In this case, it's "drive X."  This works fine until the users log off from their workstations when the "drive X" map reverts to its' old folder located on the SBS server.  I assume that this is due to a group policy rule that somebody set up years ago but I just can't find it.  Any suggestions?

The problem will be overcome anyway, when the SBS server is decommissioned, but it would be nice to fix it sooner rather than later.

This brings me to a final question.  Should I move to a Cloud based domain controller?  Dropbox for Business offers a service which integrates with Active Directory.  Any alternative suggestions for this?

Meantime, many thanks for everyone's input.  It really did make a complex task a lot easier which, although not yet complete, is getting close.

<<I assume that this is due to a group policy rule that somebody set up years ago but I just can't find it.  Any suggestions? >>

  GPO, or possibly a login script file.

<<This brings me to a final question.  Should I move to a Cloud based domain controller?  Dropbox for Business offers a service which integrates with Active Directory.  Any alternative suggestions for this?>>

 Microsoft is also offering AD services in the cloud.    Haven't tried it myself, but that's the way their suggesting people go.

 Guess it depends on the situation and how stable of a internet connection you have.

Jim.

Thanks for the prompt reply Jim.  I can't find any reference to drive maps in the old SBS 2008 GPO.  Any suggestions on where I should be looking?

Yes, I did talk to the MS Azure people yesterday who are coming up with a domain controller proposal.  The client has 3 locations all with high speed cable connections so it does seem to be the way to go, especially since the 2 satellite locations will be able to communicate directly over the Internet with the Synology NAS units at the central location.

he Access database author requires that the Synology NAS that now contains his data is mapped from each user workstation using the same drive ID as before.  In this case, it's "drive X."

Then the developer doesn't understand that he can change the links to use UNC notation and doing that will mean that the drive doesn't even need to be mapped at all and probably shouldn't be unless there is some other reason to map the drive.

Tell the developer to open the linked tables manager and check the always prompt button.  Then rather than browsing for a drive, just type in the UNC path and database name -
\\servername\path\databasename.accdb

DO NOT put Access databases on cloud devices.  They CANNOT be shared there.

Thanks for your response Pat.  

The current change is to get the data off the old SBS 2008 unit and operating elsewhere while the developer converts his Access database to SQL.  At that point he intends to go Cloud based.

So, until we get there.......he says 5 to 9 months, then I need to map the workstation drives to the Synology NAS in the same way that they were mapped to the SBS data folder.  It's working well now but, as I mentioned earlier today, I have to re-map the stations each morning as once logged off, they lose their drive map to the NAS folder and revert to the SBS folder.

Any idea where I might find this GPO on the SBS?

@Pat,

Then the developer doesn't understand that he can change the links to use UNC notation and doing that will mean that the drive doesn't even need to be mapped at all and probably shouldn't be unless there is some other reason to map the drive.

 But linked tables may not be the only thing relying on the drive mapping.   There may be other parts of the app that rely on the "X" drive, or processes outside of the app that do.   If it were me, I would continue to use a mapped drive.

@DesertDawg,

I can't find any reference to drive maps in the old SBS 2008 GPO.  Any suggestions on where I should be looking?

  They could be in any number of GPO's.   Here's a PowerShell Script to search them:

https://gallery.technet.microsoft.com/scriptcenter/Search-all-GPOs-in-a-b155491c

 Don't forget to look at any login scripts.  For that, I would look at a user in AD and see if they have one listed.

eveloper converts his Access database to SQL.  At that point he intends to go Cloud based.

  Be careful with that.....most are not happy with performance.   It takes a different mind set to do true client / server with Access.   If your sticking with Access and moving to a SQL BE first, then to the cloud, you may not be happy with the result.

  If the plan is to have a backend in the cloud, you should develop right off the bat with that.

jim.

Thanks Jim.  I'll give that a try and pass your comments about Access / SQL on to the developer.