0

I'm working on a Python application with an SQL Server database using pyodbc, and I need to open multiple connections from the application's side to the database.

I learnt that the max number of connections allowed on an instance of the SQL Server database is 32,767. My understanding is this is the max that the DB instance "can handle", i.e. all simultaneous users combined.

Is there a limit on how many connections one client can open towards the same database instance, is it also 32,767? If yes, where / how is this limit configured?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
UdonN00dle
  • 723
  • 6
  • 28
  • 1
    May I ask why is this a concern? The number you mention is mostly a theoretical limit as the id for the connection is probably represented by a 16 bit integer. I doubt many use cases reach a number of connections close to that. Specially when you consider that most client side connection will use some form of connection pooling. – Fernando Sibaja Feb 23 '21 at 18:01
  • Are you considering opening thousands of connections from a single client? You will likely run out of ephemeral TCP ports on the client. – Dan Guzman Feb 23 '21 at 18:07
  • @FernandoSibaja Thank you Fernando for the question and comment! I'm working on a PoC and there weren't many design thinking put into it, I was trying to use the same db connection & same cursor with threading to insert multiple records, but I was getting some 'connection is busy' errors. I think this is resolved by adding `MARS_Connection=yes` in the connection string. Great idea to look at connection pooling! Thank you. – UdonN00dle Feb 24 '21 at 01:53
  • @DanGuzman Thank you for the reply Dan! Excellent point about the TCP ports, I was planning to open multiple connections with threading inside loops, it wouldn't have reached 10K, I wanted to know for sure and couldn't find anything online. After the replies here I think my idea was gonna become a hot mess, thank you for guiding me away from it! I was getting a "connection is busy" error with only one connection & one cursor, this is now resolved enabling MARS. – UdonN00dle Feb 24 '21 at 01:59

1 Answers1

0

Taking an educated guess here that there is no connection count limit on the client side towards the same DB instance, there is a limit of 32,767 on the server side, but the client would be more likely to run out of other resources way before it gets close to this figure.

I was using one connection, one cursor, and threading to insert multiple records, but kept getting a "connection is busy" error, this is resolved by adding "MARS_Connection=yes" in the pyodbc database connection string, thanks to this MS documentation.

Related:

  1. How costly is opening and closing of a DB connection?
  2. Can I use multiple cursors on one connection with pyodbc and MS SQL Server?
UdonN00dle
  • 723
  • 6
  • 28