Solved: SQL Server: Increase the Concurrent Connections

fbermeoq · 09-19-2022 01:23 PM

We have a client which require to migrate from SQL Server v2016 in On Premises to a services managed as Cloud SQL with the same engine SQL Server with version 2017.

Our client has performance and connections problems in some peak times during during the month. So in addition they want to increase the maximum concurrent connections supported by SQL Server engine. In the official documentation for SQL Server v2017 or higher, the maximum value is 32767 concurrent connections, doesn't vary.

https://learn.microsoft.com/en-us/sql/t-sql/functions/max-connections-transact-sql?view=sql-server-v...

https://learn.microsoft.com/en-us/sql/t-sql/functions/max-connections-transact-sql?view=sql-server-2...

¿Is it possible to implement a infrastructure using Cloud SQL (SQL Server) that allows to increase more than 32767 concurrent connections?

Thanks in advance for your guidance.

Andre_Fiesco

According to this documentation:

The actual number of user connections allowed depends on the version of SQL Server that you are using, and the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections.

The limit for the concurrent connections is the same as SQL Server v2017, but there is a way that increases the number of connections. This limit is for a primary instance, but you can create a duplicate called read replica. You can create up to 8 read replicas, and each replica will have 32,767 user connections. The read replica is an exact copy of the primary instance. Data and other changes on the primary instance are updated in almost real time on the read replica. You will have 32,767 extra connections, but there are a few limitations to the read replica; here a few that need to be considered:

SQL Server read replicas can have more CPUs and memory than the primary instance, but they cannot have fewer CPUs or less memory.

Read replicas do not provide failover capability. To provide failover capability for an instance, see Configuring an instance for high availability.
Read replicas are read-only; you cannot write to them. The read replica processes queries, read requests, and analytics traffic, thus reducing the load on the primary instance. You can have up to 8 read replicas per primary instance.

The read replicas are used to alleviate the connection load; the primary instance will manage reads and writes, while the read replica will only manage reads. You use a read replica to offload work from a Cloud SQL instance.

View solution in original post

Andre_Fiesco

According to this documentation:

The actual number of user connections allowed depends on the version of SQL Server that you are using, and the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections.

The limit for the concurrent connections is the same as SQL Server v2017, but there is a way that increases the number of connections. This limit is for a primary instance, but you can create a duplicate called read replica. You can create up to 8 read replicas, and each replica will have 32,767 user connections. The read replica is an exact copy of the primary instance. Data and other changes on the primary instance are updated in almost real time on the read replica. You will have 32,767 extra connections, but there are a few limitations to the read replica; here a few that need to be considered:

SQL Server read replicas can have more CPUs and memory than the primary instance, but they cannot have fewer CPUs or less memory.

Read replicas do not provide failover capability. To provide failover capability for an instance, see Configuring an instance for high availability.
Read replicas are read-only; you cannot write to them. The read replica processes queries, read requests, and analytics traffic, thus reducing the load on the primary instance. You can have up to 8 read replicas per primary instance.

The read replicas are used to alleviate the connection load; the primary instance will manage reads and writes, while the read replica will only manage reads. You use a read replica to offload work from a Cloud SQL instance.