SQL Anywhere 17 - A New max_connections Database Option

[JasonHinsperger]

Historically, the number of connections that an individual user may make to a server is limited by the server license.  For a chip license, the limit is set by default to 32766, but can be changed using the server -gm option.  You can use the server property() function to get the MaxConnections property, which will return the maximum number of connections allowed to the server.  SQL Anywhere 17 now supports limiting the total number of connections to an individual database by setting the public database option “max_connections”.  This will cause SQL Anywhere to limit the total database connections to the specified number.  The max_connections option can be set as a temporary option to allow the limit to be set without persisting it in the database.

Note that the database option cannot be used to override the server setting for the maximum number of connections. Therefore, if the max_connections database option is set to a number larger than the server property setting for ‘MaxConnections’, then it has no effect.  The total number of connections that will be allowed to a database is determined by the following rules:

• The total number of server connections (SELECT property('MaxConnections') FROM DUMMY) cannot be exceeded (except by 1 user with DROP ANY CONNECTION privilege)
  • The error SQLERR_TOO_MANY_CONNECTIONS will be returned
• The total number of database connections (SELECT db_property('MaxConnections') FROM DUMMY) cannot be exceeded (except by 1 user with DROP ANY CONNECTION privilege)
  • The error SQLERR_TOO_MANY_DB_CONNECTIONS will be returned
• The total number of user connections limit cannot be exceeded

There is also a new database-level property ‘MaxConnections’ that reflects the maximum number of connections that can be made to the database, as defined above.

Only “licensed” connections count toward the database connection limit.  Internal connections for events, mirroring, monitoring, etc... are not counted. HTTP connections are, however, included.

For example, with a personal server (or one whose license limits it to 10 connections):

If we set max_connections to 20:

Because the maximum number of connections to the personal server is 10, increasing the max_connections option beyond this has no effect.

However, if we set the max_connections value to 5, we see the following:

The current database is limited to 5 connections.  The rest of the connections (5) are available to other databases running on the server.

Note: There is a provision built into the server that allows for a single user that has the DROP ANY CONNECTION privilege to connect to the database even when the connection limit has been reached.  This allows an administrator to connect and remove one or more connections if required (eg. if all other connections are deadlocked, if a connection is overusing resources, etc…)

Existing databases (version 16 and older) will not make use of this feature.  If the max_connections database option is set in these databases, it will be ignored and these databases will continue to work as they did previously. In addition, if you try to upgrade a database using ALTER DATABASE UPGRADE and the max_connections option has been set, the upgrade will fail with the error SQLERR_UPGRADE_CONFLICTING_OPTION.  The max_connections option would have to be removed before the upgrade would be able to continue.