Server Specs for a Split DB

[EmanuelItz]

I use access 2007/2010 to run my mail order business. I have the DB split so that each of my employees have a local front end on their computer and the back end is on a single shared server that also holds all our other files. Lately things have been getting slow so I want to get a separate server just for Access. What specs would you recommend for the server? We have about 5 or 6 users at a time. I'm not sure if I need more processing power or memory. My back end file is only about 200 MB.

Thanks for the help and I'm sorry if this is a stupid question but I know very little about these things...

[Dal Jeanis]

Okay, not sure the server is the problem. Split db is a good thing - but it can put extra stress on the network, based upon design parameters. If you only have 5-6 users, I'd check db and query design first. Although - I may have misread your concern, and you may have already determined that it is the OTHER things on that server that are the problem, and you want to give Access its own safe playground.

What data is being sent across the network? What queries are being run against the database? What other stresses are there on the shared server? That's the kind of analysis I'd do before I determined to get a server just for Access.

[EmanuelItz]

Right now we have a single server that holds all our shared files including all of our pictures, artwork, pst files, .psd, etc. The server is 5 years old and filling up quickly so I will eventually need to upgrade that as well.

I'm not sure what is being sent across the network as I don't really know how a front end / back end system works. Lets say one of my users wants to search for an order, is that query done locally or is the request sent to the server and the server process it? I would guess its done locally as access is not open on the server but I don't know.

Our network is gigabit if that makes any difference.

Thanks for the help!

[Dal Jeanis]

In answer to your question, the order data is almost certainly on the backend, so a search for order data will go against the backend. And, yes, the Access file is open on the backend and that server is processing the request. The front end attaches to the back end and sends queries/requests to the backend for processing.

The question of what gets processed where is a matter of database design and interface design. Typically, all the "real" data is on the server, and the front-end only carries a few temporary tables that are used to track what the user is doing.

The question of bandwidth requirements comes up largely in the context of the need to transfer large amounts of data from the front end to the back end or vice versa. As long as it's only a query that goes from the front end to the back end, and the query results that go back to the front end, then the network isn't stressed, and the server is doing nearly all the work.

On the other hand, in a certain query situations, such as if you have a query/subquery where data from the front end is being married repeatedly to data on the backend, you can end up with stress on both the network and the back end server.

One place that I've seen that problem is in badly designed mail merges. It CAN be much more efficient to extract the recipient information to the front end, then do the mailmerge there. Another place that happens is (over-)complicated processing, where a programmer has created a routine that iterates through a result set, executing related queries over and over. There is often a better way, that will reduce the stress on the network and the backend.

That all being said, if your backend server is already 5 years old, it wouldn't hurt to move the backend to a new box.

NOW, in order to answer your question about processing power vs memory (and add my question about network bandwidth), you'd need someone to monitor the server and the network when you were experiencing the slowdown. That person would be able to see whether the CPU was pegged, or whether there was excessing swapping/caching, or whether the network was clogged, or all of the above. You should identify the pinch point before investing in an upgrade, since upgrading the wrong item can result in (effectively) slowing down the others.