Using a Windows 10/11 client computer as a 'quasi server' for other client computers. (1 Viewer)

[AHeyne]

I am writing this question to clarify for myself whether what I assumed so far is really (still?) true.

Required services of the 'quasi server' would be file services (SMB) and Microsoft SQL Server (ODBC).

The clients would use the services of this 'quasi server' from a Microsoft Access application.

So far I have assumed that this scenario should be strictly avoided, as Windows client operating systems such as 10/11, unlike Windows server operating systems, allow a maximum of 20 simultaneous incoming connections (across the board) and even one Microsoft Access client can already hold several database connections simultaneously, which could lead to unpredictable behaviours.

Now I have read here (https://learn.microsoft.com/en-us/answers/questions/1008380/maximum-number-of-concurrent-connections) and here (https://learn.microsoft.com/en-us/a...ical-limitations-when-using-windows-10-as-the) that you have to differentiate:

You may allow up to 20 other devices to access the software installed on the licensed device for the purpose of using the following software features: file services, print services, Internet information services, and Internet connection sharing and telephony services on the licensed device. You may allow any number of devices to access the software on the licensed device to synchronize data between devices.
This section does not mean, however, that you have the right to install the software, or use the primary function of the software (other than the features listed in this section), on any of these other devices.

and

The limits on file & printer connections has nothing to do with any other process on the server, including SQL Server.

So it seems that this limit of 20 simultaneous incoming connections applies to file services (SMB), for example, but not to SQL Server services (ODBC).

- Is this really the case? Is there really no limit to the number of simultaneous incoming database connections?
- Or does this possibly depend on whether they are made with TCP/IP / NETBIOS / Named Pipes?
- What is the legal situation? Can does not always mean may...?

What is your opinion/experience on this?

 

[CJ_London]

Not clear if this is a hypothetical question or one with a specific requirement in mind. If the latter would be helpful if you explain the requirement as there is often more than one solution.

I can't really answer your question, but another factor to be taken into account is how 'wide' your quasi server connection to the network will be. That can have a significant limiting affect on performance for larger numbers of users.

Edit: there is this current thread which has a setup of 4pc's with Ssql Server Express on one of them - might be of interest

Cannot connect to SQL Server on a network

I am trying unsuccessfully to connect my O365 Access FE to the SQL Server on a network. On a standalone pc there is no problem. The SQL Server properties and Connect string are shown below: ODBC;DRIVER=ODBC Driver 17 for SQL...

www.access-programmers.co.uk

 

[AHeyne]

@CJ_London : Thank you, I came across the topic again through the mentioned thread and thus came to my question.

If it should help to answer my question, I will of course be happy to provide some context:

The aim is to use a Microsoft Access application, which requires file and SQL server services, from a maximum of 5 workstations.
For this purpose, we always rely on a server in the LAN with a Microsoft Server operating system in order to use it as a file and Microsoft SQL server.
If, however, contrary to my previous assumption, it is actually possible to use one of the clients as a file and SQL server without any problems, then this could actually be an alternative in environments where no dedicated Microsoft operating system server is available, although definitely not a favored one.

I would like to ignore hurdles such as protocols, firewalls etc. so that the SQL server can be reached at all.
My primary concern is whether there might not be a technical (and possibly also legal) bottleneck, especially with the ODBC connections.
It must be ensured that the Microsoft Access application can always establish and use all required connections to the SQL server.
So exactly the same relyability as if the SQL server is used on a Microsoft server operating system.

 

[CJ_London]

There is no problem with using an .accdb back end on one of the users machines and linked to by others on the network - subject to network bottle necks, the user not rebooting, etc.

There is also not a problem (in principle) with use sql server express (or mySQL) in the same way. I cannot speak for a full version of sql server which is what you are implying. With regards legality, you would need to inspect the license documentation.

With regards reliability, if a user is using the same machine on which sql server is based then almost by definition, it will not be as reliable as a dedicated server because a dedicated server is only doing one thing, whilst a pc in use by a user is doing many things, any of which has the potential to error or lock causing the machine to freeze or require rebooting or consume more resources preventing sql server to maintian performance.

So in answer to this

it is actually possible to use one of the clients as a file and SQL server without any problems,

I would say no

 

[AHeyne]

There is no problem with using an .accdb back end on one of the users machines and linked to by others on the network - subject to network bottle necks, the user not rebooting, etc.

Interesting. Even with more than 20 simultaneous connections? This would mean that the limit of 20 connections (which also seems to apply to file services) is no longer valid. Or maybe is only of a legal nature...?

There is also not a problem (in principle) with use sql server express (or mySQL) in the same way. I cannot speak for a full version of sql server which is what you are implying. With regards legality, you would need to inspect the license documentation.

Express would be sufficient too for me, I wasn't pointing out a higher version explicitly.

With regards reliability, if a user is using the same machine on which sql server is based then almost by definition, it will not be as reliable as a dedicated server because a dedicated server is only doing one thing, whilst a pc in use by a user is doing many things, any of which has the potential to error or lock causing the machine to freeze or require rebooting or consume more resources preventing sql server to maintian performance.

I would only be interested in a possible deliberate restriction of the connections.
Of course, there are other factors as well.

 

[CJ_London]

If you have 5 users, you only have 5 connections unless users are using more than one front end or they are using ado to create new connections

 

[AHeyne]

I'm not sure about that, for example. I have in mind that Access establishes several, if not many, simultaneous database connections. For example, when several forms are open. I think I have also read in the past that each ComboBox, for example, establishes its own connection.

 

[The_Doc_Man]

You are correct that Access will try to establish multiple connections. However, there is the tactic of opening a connection via some hidden form or perhaps something on a persistent form - e.g. a "switchboard" or "dispatcher" - to keep a connection open even if apparently idle. As it happens, Access will take advantage of the network connection and "ride" that persistent connection rather than try to open another connection.

 

[AHeyne]

Thanks Doc.

Ok, the current state of knowledge seems to be that Microsoft Access opens/holds several database connections at the same time, but it is not possible to say/calculate exactly how many.

And with regard to my initial question as to whether a Windows client operating system limits the number of simultaneous, incoming database connections (possibly to 20), it is also not really possible to determine precisely.

It's a pity that there are no official statements on this.

 

[Shimon]

Hi,
This is what I found.

If you install an SQL Server Express somewhere in your network, you can share all hosted databases with all users in your network... there is no limitation based on the number of users, only in size and compute power.

Sincerely,
Shimon
P.S. I couldn't add the link, but a google search with my answer should bring you straight to the page.

 

[AHeyne]

@Shimon : Thank you for your contribution. Of course, the SQL server does not limit this, as the article you mentioned too states.
But the client operating system maybe does. That is what I would like to clarify.