Peer to Peer Networking

Topic

New Reply

Hi there!

I have been asked to look at a network of 2 pc’s where the client in question has been having problems trying to run their Office Access application simultaneously on both pc’s. The app resides on pc1 which runs ok but they had the scenario whereby if they ran the app up on pc1, it wouldn’t run on pc2 and vice versa. I somehow got round this by mapping a drive to the folder on pc1 where the app exists from pc2 and have been able to run the app on both pc’s simultaneously, but with the following limitations:-

1) If the app has been run up from pc1, then it won’t start directly from pc2, I have to start Access and then open the app from pc2.

2) If the app is not running on pc1, then it will start directly from pc2 and pc1 will also start.

3) If the app is running on both pc’s simultaneously, it is very slow on both pc’s.

This setup of using a pc as a server does not seem to be very clever and it also seems to me that installing a server may well be the right way to go. However, there could be an alternative of installing a NAS box and placing the back end of the app onto this and then accessing it from front ends on the pc’s. (I’m looking to split it into front and back end anyway).

Has anybody got any experience of running Access on a peer to peer network or using a NAS box? Most setups I have come across are either single user or with a network and server already in place.

Any advice would be greatly appreciated.

Cheers

Niven

Reply | Quote

Replies

We have run small databases on peer-to-peer networks without difficulty, so it should be possible to make it work in a two PC arrangement. However, I would be sure to split it so that each PC has the front-end, and only the back-end is shared across both PCs. As to the issue of start up, check and make sure that one isn’t trying to open the database exclusively. On the performance issues, it may be that the memory in the PC1 is inadequate to support both PCs running the app. It may also be that the network has problems that compound the performance issues. A server would of course be nice – I would take that approach over the NAS as it could be a relatively modest server that could provide many other functions – you might even want to consider moving the database back-end to SQL Server Express.

Reply | Quote

Wendell,

Many thanks for your reply.

I will look at the startup settings for both PC’s as well as the memory on PC1. I know this could depend on the spec of the PC’s, but what size memory in your view should we be looking at?

Cheers,

Niven

Reply | Quote

… but what size memory in your view should we be looking at?

I would insist on 512 MBytes, but if you can get 1 GByte even better (on XP). Should they decide to upgrade to Win7 or Vista, a minimum of 1 GByte, and preferabl2 to 4 GBytes

Reply | Quote

Wendell,

Many thanks again, I believe the PC’s may already have 512Mb, but one thing I am going to change for them is the way their network is put together.

They are using a 4 port internet router at present to connect their pc’s, so am going to put in a Switch and have the the pc’s, router and printers all effectively hanging off this.

As for the server, it depends on their budget. Is a NAS box an option at all? It had been suggested to me as a cheaper alternative, but having never seen this type of setup am bit wary as to investing in something that may not actually work.

Cheers again,

Niven

Reply | Quote

Many thanks again, I believe the PC’s may already have 512Mb, but one thing I am going to change for them is the way their network is put together.

They are using a 4 port internet router at present to connect their pc’s, so am going to put in a Switch and have the the pc’s, router and printers all effectively hanging off this.

As for the server, it depends on their budget. Is a NAS box an option at all? It had been suggested to me as a cheaper alternative, but having never seen this type of setup am bit wary as to investing in something that may not actually work.

I’ve used Access many times in peer-to-peer situations, so I know it can run just fine (especially for only 2 users). A stand-alone server is not required.

As Wendell suggested, make sure each PC has a copy of the frontend with the backend residing on the “server” PC. And I’d make sure that PC has at least 2GB of memory (and 1GB of memory on the other PC would be helpful). Is anything else running on the “server” that might be using memory? If so, maybe more than 2GB is required. Also, check the amount of disk space and disk fragmentation on the “server”.

Reply | Quote

I’ve used Access many times in peer-to-peer situations, so I know it can run just fine (especially for only 2 users). A stand-alone server is not required.

As Wendell suggested, make sure each PC has a copy of the frontend with the backend residing on the “server” PC. And I’d make sure that PC has at least 2GB of memory (and 1GB of memory on the other PC would be helpful). Is anything else running on the “server” that might be using memory? If so, maybe more than 2GB is required. Also, check the amount of disk space and disk fragmentation on the “server”.

Mark,

Many thanks your reply.

How do you go about connecting to the server PC? Do you map a drive or do you use another method? Sorry, I’ve never messed about with peer to peer networking, I’ve always had the luxury of networks already setup for me. So far with this installation I just mapped a drive, ‘X’, in this instance to the folder where the back end is. I’m having to reinstall Office 2003 on the 2nd pc as it has corrupted somehow, throws out all sorts of errors when loading any Office app and won’t let me link the back end in.

Cheers,

Niven

Reply | Quote

How do you go about connecting to the server PC? Do you map a drive or do you use another method? Sorry, I’ve never messed about with peer to peer networking, I’ve always had the luxury of networks already setup for me. So far with this installation I just mapped a drive, ‘X’, in this instance to the folder where the back end is. I’m having to reinstall Office 2003 on the 2nd pc as it has corrupted somehow, throws out all sorts of errors when loading any Office app and won’t let me link the back end in.

You can use a mapped drive, but I don’t like to. I often work with situations that have more workstations, and I don’t want to rely on each one having the same drive mapping.

I prefer to link using the UNC. So when you go to link the frontend to the backend, locate the backend by using Network Neighborhood (or Network Places, or whatever they are calling it know). Locate the Server, then work down to the drive, folder, and database. The resulting connection string might look something like: \Serverdatasomething.mdb

The convenient thing about linking this way is that once you have linked one workstation, you can just copy the frontend to the other workstation and it is ready to go; no re-linking required.

Reply | Quote

You can use a mapped drive, but I don’t like to. I often work with situations that have more workstations, and I don’t want to rely on each one having the same drive mapping.

I prefer to link using the UNC. So when you go to link the frontend to the backend, locate the backend by using Network Neighborhood (or Network Places, or whatever they are calling it know). Locate the Server, then work down to the drive, folder, and database. The resulting connection string might look something like: \Serverdatasomething.mdb

The convenient thing about linking this way is that once you have linked one workstation, you can just copy the frontend to the other workstation and it is ready to go; no re-linking required.

Good stuff,

Thank you Mark

Reply | Quote

I often work with situations that have more workstations, and I don’t want to rely on each one having the same drive mapping.

Mark,

One more question for you. How many workstations do you think you can have in an Access peer to peer environment before you should think about putting in a Server?

Cheers,

Niven

Reply | Quote

One more question for you. How many workstations do you think you can have in an Access peer to peer environment before you should think about putting in a Server?

That is a very tough question to answer, as it depends on alot of different factors. Basically it comes down to the power of the workstation that contains the backend database, and what kind of workload it has.

That is, assuming this “server” workstation seemingly has sufficient memory and speed, do other tasks running on it could tax memory and disk access to the detriment of the other users trying to use the database?

And then the database itself is a factor. A 100+MB backend will put alot more requirements on the server-workstation than a 10MB one. Also a factor is the design of databases (frontend and backend); there is good design and bad design, and the performance hit can be significant.

Anyway, under “normal” circumstances (if there is such a thing), I’d say that you should start looking at a dedicated server as you get to 5 users.

Reply | Quote

That is a very tough question to answer, as it depends on alot of different factors. Basically it comes down to the power of the workstation that contains the backend database, and what kind of workload it has.

That is, assuming this “server” workstation seemingly has sufficient memory and speed, do other tasks running on it could tax memory and disk access to the detriment of the other users trying to use the database?

And then the database itself is a factor. A 100+MB backend will put alot more requirements on the server-workstation than a 10MB one. Also a factor is the design of databases (frontend and backend); there is good design and bad design, and the performance hit can be significant.

Anyway, under “normal” circumstances (if there is such a thing), I’d say that you should start looking at a dedicated server as you get to 5 users.

Many thanks again Mark for your reply. Plenty of food for thought!

Reply | Quote

That is a very tough question to answer, as it depends on alot of different factors. Basically it comes down to the power of the workstation that contains the backend database, and what kind of workload it has.

That is, assuming this “server” workstation seemingly has sufficient memory and speed, do other tasks running on it could tax memory and disk access to the detriment of the other users trying to use the database?

And then the database itself is a factor. A 100+MB backend will put alot more requirements on the server-workstation than a 10MB one. Also a factor is the design of databases (frontend and backend); there is good design and bad design, and the performance hit can be significant.

Anyway, under “normal” circumstances (if there is such a thing), I’d say that you should start looking at a dedicated server as you get to 5 users.

I’m not sure if I should repost this, but I upgraded both PC’s to 2Gb of Ram and I’m still getting performance issues with the PC that remotely accesses the backend of the DB. I ran up the performance monitor and watched as the FrontEnd ran up. It flat lined at 100% whilst loading the switchboard and took ages to run simple reports. I’m going to try a dummy front end and run some simple reports from there to see if it sheds any light on the general DB design. The applications and processes monitor doesn’t show anything else going on that might interfere with the running of the DB.

Any thoughts or suggestions would be greatly appreciated as the client is asking whether a server should be installed and as things stand I’m not sure if that will solve the current problem.

Cheers,

Niven

Reply | Quote

Might I inquire whether you have checked (simplistically, perhaps, via Task Manager -> Networking, perhaps adding in the columns “Bytes Received per Interval” and “Bytes Sent per Interval”) the amount of data being transmitted between the two PCs?

I say this because we have an Access database on a server which, for some functions, has to send a crippling amount of data across a 100 Mbps LAN to the workstation for what seem to be trivial requests…

BATcher

Plethora means a lot to me.

Reply | Quote

Might I inquire whether you have checked (simplistically, perhaps, via Task Manager -> Networking, perhaps adding in the columns “Bytes Received per Interval” and “Bytes Sent per Interval”) the amount of data being transmitted between the two PCs?

I say this because we have an Access database on a server which, for some functions, has to send a crippling amount of data across a 100 Mbps LAN to the workstation for what seem to be trivial requests…

Many thanks your reply.

I hadn’t checked the Networking aspect. I will have a look at this the next time I’m on site even though I’m keeping my fingers crossed that the sudden improvement in performance isn’t a flash in the pan (see response to Wendell’s post).

Cheers,

Niven

Reply | Quote

…
Any thoughts or suggestions would be greatly appreciated as the client is asking whether a server should be installed and as things stand I’m not sure if that will solve the current problem.

Based on your description of the situation, I would not look at putting in a server until you identify why it takes so long to start up the remote PC. Putting in a server will only make both PCs with the front-end be slow.

The fact that the Processor Occupancy goes to 100% on the front-end only PC isn’t surprising. Access essentially goes into a loop waiting for the data it requests, and takes virtually total control of the PC until it gets the data. Are you loading a form with data from a table with lots of records, or many records with lots of fields, so a great deal of data has to be passed to the front-end PC? And I would look at network activity – I have seen networks that were misconfigured, or had hardware that was misbehaving (including bad cabling) that can cause those kind of problems. One test you can do is to copy a large file from PC A to PC B and see how long it takes. On a 100 MB LAN, it shouldn’t take much longer than simply copying the same file to another folder on the originating PC.

Reply | Quote

Based on your description of the situation, I would not look at putting in a server until you identify why it takes so long to start up the remote PC. Putting in a server will only make both PCs with the front-end be slow.

The fact that the Processor Occupancy goes to 100% on the front-end only PC isn’t surprising. Access essentially goes into a loop waiting for the data it requests, and takes virtually total control of the PC until it gets the data. Are you loading a form with data from a table with lots of records, or many records with lots of fields, so a great deal of data has to be passed to the front-end PC? And I would look at network activity – I have seen networks that were misconfigured, or had hardware that was misbehaving (including bad cabling) that can cause those kind of problems. One test you can do is to copy a large file from PC A to PC B and see how long it takes. On a 100 MB LAN, it shouldn’t take much longer than simply copying the same file to another folder on the originating PC.

Many thanks your reply.

The latest is I was on site Sunday morning and PC B had lost its network connection. I re-booted the PC and logged onto the DB as normal and had a very pleasant surprise in that everything I ran, from customer lookups to weekly reports, ran with the same speed as the Back End host PC A.

I now can only think something has been going on in the background, such as users not logged off from their sessions and leaving something like an Internet connection running. What puzzles me though is I have looked for this sort of thing and checked the Processes and Applications in Task manager and haven’t see anything other than Access running. They use Norton anti-virus, but even that didn’t seem to be running.

Any futher thoughts on this would be greatly appreciated. I’m not going to hold my breath and hope the situation has resolved itself.

Cheers,

Niven

Reply | Quote

A NAS could be used to store the database back-end, but my view is they are more appropriate for backup. Typically they aren’t optimized for fast retrieval of information such as you might have in a database. And with Access being a file type of database, if you need a table for a query, it will pull the entire table over if the query involves more than two tables. That’s the beauty of a server database such as SQL Server. It runs the query for you and then passes the result – assuming it has been optimized. And the Express version will run on very modest PCs. We have a situation where we have a web app that hits a single processor system with 1GB of memory. It is used by some 800 persons, and we get adequate performance during all the busiest time periods. We plan to upgrade it shortly to improve that by going multi-processor and 4GB of memory.

Reply | Quote

A NAS could be used to store the database back-end, but my view is they are more appropriate for backup. Typically they aren’t optimized for fast retrieval of information such as you might have in a database. And with Access being a file type of database, if you need a table for a query, it will pull the entire table over if the query involves more than two tables. That’s the beauty of a server database such as SQL Server. It runs the query for you and then passes the result – assuming it has been optimized. And the Express version will run on very modest PCs. We have a situation where we have a web app that hits a single processor system with 1GB of memory. It is used by some 800 persons, and we get adequate performance during all the busiest time periods. We plan to upgrade it shortly to improve that by going multi-processor and 4GB of memory.

Wendell,

I don’t know if I did something wrong, but I downloaded SQL Server Express and it has caused my PC to crash each time I’ve logged on.

I’ve retrieved a last good login and am logging in ok. However, I tried to uninstall SQL SERVER Express and lo and behold my PC crashes instantly.

Have you any experience of this?

Cheers

Nive

Reply | Quote

Wendell,

I don’t know if I did something wrong, but I downloaded SQL Server Express and it has caused my PC to crash each time I’ve logged on.

I’ve retrieved a last good login and am logging in ok. However, I tried to uninstall SQL SERVER Express and lo and behold my PC crashes instantly.

Have you any experience of this?

Cheers

Nive

No, I’ve not seen that behavior, either in running SQL Server Express, or in uninstalling it. I presume you did more that just download it, and that you installed it. Is it possible you are running short on disk space somewhere, or that there was some other problem that prevented the install from working? Also, what OS are you running on your PC?

Reply | Quote

No, I’ve not seen that behavior, either in running SQL Server Express, or in uninstalling it. I presume you did more that just download it, and that you installed it. Is it possible you are running short on disk space somewhere, or that there was some other problem that prevented the install from working? Also, what OS are you running on your PC?

Wendell,

Disk space is fine and my OS is XP. I may try downloading and reinstalling, but am a bit worried about getting the blue screen again and not being able to recover.

Reply | Quote

Wendell,

Many thanks again for the info. Most appreciated.

Cheers,

Niven

Reply | Quote

Wendall, a very interesting reply; can I follow up with a supplementary question regarding the database splitter? I have 2 pc’s – pc1 and pc2. I want to split the client database so that there is a front end on pc1. Having done that do I, just, copy the front end files from pc1 to a (same/similar) location on pc2? Do the references in the queries and forms change to reflect this movement please?

Reply | Quote

It doesn’t really matter where the frontend is, as long as the backend remains in the same location.

Reply | Quote