[SOLVED] Computer to Computer without Server?

[Tomkoel]

I have an SQL database on one computer and would like 2-5 other computers in the same room to be able to access that database and run routines on its records simultaneously. Maintaining speed and reliability are crucial. The individual routines could take days or weeks to run.
The SQL database PC, while having a constant internet connection and downloading and importing data will not have any other internet traffic, no hosting or anything like that.
Does this SQL PC have to be a full-blown server for this to work? Or can it be a Windows 10 system? My very limited experience is that back in the old days, while windows systems in the same office or home network could be pointed to each other, the connections were not very reliable. And I would hate to have the system(s) built only to find that the connections were failing or that the speed was crippled.
Any advice would be a big help. Thank you.

 

[Eximo]

I mean if I was going for a straight up data collector type database with MSSQL I would probably use Windows Server Core and put it on server class hardware with ECC memory and some sort of backup scheme. At a minimum.

Since you are talking about high availability and performance, you might want to consider a cluster.

As for the connections from the other computer, I haven't looked into the limitations in a while, but Home certainly has a fixed limit on remote connections (it might be one) Pro and Enterprise support higher numbers, but that is for connecting to the host directly. As far as I know the limits for connecting to a database instance are ~32000, that might have changed on newer editions of SQL. I'm still running 2012 until my software supports the newer versions.

 

[Tomkoel]

Thank you for your input. I really appreciate it!
The computers will be in the same room. How would I connect them directly? All at the same time? Is there a card or something that would allow me to direct wire the others to the one with the DB? And would that mean the others would have to give up their otherwise internet connection? I'm sure this sound completely amatuerish, but whatever insight you have would be helpful. Thanks again.

 

[Eximo]

Doesn't sound like you should be in charge of setting up something like this.

You are acting like a database is hardware. It is software. You would 'directly' connect them over a normal network. That traffic would use some of the available bandwidth.

 

[hang-the-9]

Thank you for your input. I really appreciate it!
The computers will be in the same room. How would I connect them directly? All at the same time? Is there a card or something that would allow me to direct wire the others to the one with the DB? And would that mean the others would have to give up their otherwise internet connection? I'm sure this sound completely amatuerish, but whatever insight you have would be helpful. Thanks again.

You don't need anything special to connect them, pretty much every office in the world with more than one computer has done this. Get a network switch, the router in the house/office will provide the IP addresses. But sounds like you need more than just knowing how to connect them together, just because they are all on the same network does not mean you need to do nothing else to connect to the database.

 

[Eximo]

Certainly depends on the software in question. Connecting through SQL Server Management Studio for doing things to the database and setting up permissions, either from the host or remote, is expected. You can use Access or Excel to run simple reports or data pulls. Microsoft Power BI isn't bad for making fancy dashboards. Or Visual Studio and Crystal Reports for making printable reports or what have you (or entire programs that use the data). Just depends what the actual goal is.

And that is just mainstream stuff, which is mostly what I deal with, plenty more custom solutions out there. But all of that costs money.

 

[Tomkoel]

Eximo is exactly right. I'm certainly unequiped. That said, what you've both said is very helpful. Thank you both! I'll be running MS Access on the PCs, hoping to access the SQL database, and hope to keep the computing load on those other PCs respectively - so the PC with the DB isn't doing much besides providing the connection. I'm hoping that will keep the speed high with regard to executing the routines. Does that sound like it makes sense? Would you recommend setting up fixed IP addresses for each of these computers? Would that help with security or speed or reliability? Or is that a lot of work for not much gain?

 

[Eximo]

No the host of the SQL database will be the one doing all the processing. The clients will just be asking for and receiving the data.

Fixed IPs is an interesting question, it won't make a difference performance wise. As for security, probably worse? If an attacker ever gets in they could get a permanent map of your network that way. And maintaining a static scheme can be fairly tedious.

 

[Tomkoel]

So, if MS Access is running on the 'outside' PC - which sees and has access to the SQL DB on the 'main' PC, you're saying all the work the Access routines will be doing will be done using the 'main' PC's cores and memory and not on its own? Thanks again for the help!!

 

[USAFRet]

So, if MS Access is running on the 'outside' PC - which sees and has access to the SQL DB on the 'main' PC, you're saying all the work the Access routines will be doing will be done using the 'main' PC's cores and memory and not on its own? Thanks again for the help!!

That depends a LOT on how things are written.

You can have all the stored procedures on the SQL system, and Access just reads those tables or views.
Or, Access can do a lot of the brain work on the client system.
Or some of both.

 

[Tomkoel]

All the procedures will be on the client (outside) system... So would that mean that the client PC would carry the bulk of the processor and memory load?

 

[USAFRet]

All the procedures will be on the client (outside) system... So would that mean that the client PC would carry the bulk of the processor and memory load?

If the SQL just holds the raw tables, and all of the queries, joins, logic is in the Access db's, then yes.

Whether this is a significant performance hit depends on other factors as well.

 

[Tomkoel]

Thank you everybody!!!