Want to setup a network for access db. Do I need client server or peer to peer?

[alexandervj]

We have a small company of about 10 people, and only about 6 or 7 would need to be connected to the network. I'm not sure if I should go for a client-server network, or peer to peer. Does Access run on Windows Server OS? Can I just setup a peer to peer and have one peer act as a dedicated server running windows 7 which has Access and the database on it? Will it have speed and reliability issues? Thanks

[alexandervj]

I want to setup a network for our small business (about 7 users) to run an Access database on. I'm not sure if I need a client-server network or peer to peer. A client-server would be nice but I dont have any experience with Windows Server and I'm not even sure if Access will work with Windows Server. Eventually I will probably want to get SQL Server and probably have it manage a database and use Access as a UI. Do I need anything else to make Access work with Windows Server?

[ItsMe]

All you need is to have your server configured as "File Server" no special software needed. Clients can access the file via conventional AD settings

[pbaldy]

I merged your similar threads. Either type of network would be fine, but Access would have to be installed on each user's PC, not a single installation on the server (unless it was a terminal server, but that doesn't sound like the direction you're going). There is a free runtime version of Access available from Microsoft, so you'd only have to pay for it on the PC of the person developing the db.

[alexandervj]

Perfect! Thanks!

[ssanfu]

At home I have 6 computers on a LAN (peer to peer). I have the BE's on one computer and The FEs on other computers. Works very well.

At the Office we have a server/client setup. We have an outside company administer our server because of security concerns. This is expensive, but necessary.

Everyone has access on their computer (that needs Access), with the BE on the server. Access does not have to be installed on the server. So we are using the server as a file server, just like the peer to peer setup.

You can/could run SQL Server Express on the peer to peer network. But once you move to SQL Server, you should think about buying server hardware and having someone manage Windows Server/SQL Server.

[alexandervj]

Thanks Steve,

What does the outside company do to administer your server/how does it improve security? Could a certified DBA with Windows Server/SQL Server work as an administrator of the server? I think as a temporary solution while we are in R&D what I'll do is setup a peer-to-peer network, buy a micro server which will serve as the BE and also my FE while the engineers have their own computers with Access on it as their FE. I'm the only one that would need to modify the database structure, everyone else would just need to input/edit data, etc. Then once we go into production I'll buy a dedicated server, keep it in the server room, install Windows Server and SQL Server on it and just use my micro server that I'm buying as the temp solution as the computer to Access the DB for myself. Does that sound like a viable solution? Thanks for all your advice. Also how much does SQL Server cost? I've seen a lot of different prices ranging anywhere from $600 - $3000 for SQL Server 2012 Standard.

[ssanfu]

The outside company is in charge of keeping the security up to date (AV and firewall), administering user accounts, backups, server configuration, network configuration... pretty much the health of the system. Configuring Windows Server correctly is not a trivial task.

buy a micro server which will serve as the BE and also my FE

If the micro server is running Windows Server software, you might want to rethink this. I bought a couple of used servers to try and learn how to configure the server software. My bad ... When I find time, I want to take a course on server configuration.
I had plans to do the same thing as what you propose, but quickly found out it was a bad idea.

A server is a server and a workstation (client) is a workstation (client). I would recommend NOT using the server as a workstation. If, in the future you want to use the micro server as your workstation, wipe the server software from the HD and install Windows XP, 7 or 8 or whatever version you can get.

I've seen a lot of different prices ranging anywhere from $600 - $3000 for SQL Server 2012 Standard.

When you buy a server, usually Windows Server is already installed and the cost of the software is included in the package cost.


When you do decide to buy a server, pack it full of RAM - max it out if possible. More RAM is more efficient. Server CPU utilization should be below 20% (one recommendation I recieved).

[pbaldy]

When you buy a server, usually Windows Server is already installed and the cost of the software is included in the package cost.

Presuming you mean SQL Server is included, I think that's only true for the SBS version. Regular server versions do not include SQL Server. Just found this:

http://en.wikipedia.org/wiki/Windows_Server

which says in part:

"Microsoft has also produced Windows Server Essentials (formerly Windows Small Business Server) and Windows Essential Business Server (discontinued), software bundles which includes a Windows Server operating system and some other Microsoft Servers products."

which would imply that is no longer available. That said, I'm certainly no expert, but our servers have either Server 2003 or 2008 and none had SQL Server installed by default. It was a separate install on one of them, at additional cost.

[ssanfu]

@Paul,
You are correct. I got stuck in an endless loop on the Windows Server software.

SBS has been discontinued. We have SBS are struggling with what to do now. Looking at new hardware now, using Windows Server 2008.
Still deciding about which version of SQL Server to buy. Probably the standard version.
(I was just reading what you wrote on another post about SSQL Express ) (and thanks for keeping me straightened out)

@alexandervj
You might start out with SQL Server Express (free!) on a workstation and go to SQL Server Standard on Windows Server when necessary.
If you use SQL Server Express on a dedicated workstation, again, pack it with RAM and don't use it for any other purpose.


Sorry for any confusion I caused.

[pbaldy]

SBS was a pretty good deal, which is probably why they discontinued it.

[alexandervj]

Thanks for all the advice!
Another question - since SQL Server is limited and you have to pay for a license once you reach the limitations of the express version, what are the benefits of SQL Server as opposed to MySQL? Can you use Access as a UI with MySQL just like you can with SQL Server?

[alexandervj]

And another question in addition to my question just above - does anyone know some training/certification courses I can take for either SQL Server and/or MySQL? I found some from learning tree for SQL Server for about $5000-6000. Does anyone know of anything like this that might be better? Thanks

[ssanfu]

since SQL Server is limited

In what way? do you mean the 10GB file size?

what are the benefits of SQL Server as opposed to MySQL?

That is something you will have to evaluate for yourself.
Two advantages of MySQL are (1) Free and (2) you have the option to run it on a Linux box on your LAN. This results in lower hardware costs.
Ultimately, it all depends on your requirements.

A long time ago, MySQL didn't have as robust security as MS SQL Server. But MySQL has come a long way and I understand that MySQL security is comparable to MS SQL Server

Can you use Access as a UI with MySQL just like you can with SQL Server?

AFAIK, yes, Access can be used as a FE to MySQL.

[alexandervj]

Thanks Steve,

Yes, just the 10GB file size, otherwise its my understanding that SQL Server is one of the best RDBMS out there if not the best. Trying to convince my company to go with something we have to pay for over something free is always the hurdle. Once we get to that 10G db size which might happen pretty soon once we go into production I think the cost will be pretty much negligible though. Just wanted to get a feel for what other peoples personal preferences are here and why they might go with SQL Server instead of MySQL. I have seen more training options available with SQL Server, ie. LearningTree.com, than MySQL. Does anyone know of any other training resources comparable that will get you certified or give you the knowledge you would need to be certified?