Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database connections

Connection limits for databases and database servers

When you make a connection to a database, you connect via the database server. The maximum number of concurrent connections to your database depends on several factors.

These factors include:

  • Your license.

  • The type of database server that you choose to run.

  • The number of databases running on the database server.

  • The type of connections that the database and database server accept.

  • The database and database server options that you specify to reduce the maximum number of connections for your application.

Database server connection limits

The connection limit for the personal database server (dbeng17) is ten connections, and by default only seven of these connections can be HTTP connections. At minimum three connections are reserved for standard connections. A standard connection is any connection other than an HTTP or HTTPS connection

The connection limit for network database servers is equal to the maximum number of connections allowed by your per-seat license, or unlimited (32766) connections if you have a per-core license. (In practice, creating 32766 connections to a single database server might not be possible because of operating system restrictions. Also the database server makes internal temporary connections during operation, which also reduces the number of connections available for users.)

To reduce the maximum number of concurrent connections that a database server can accept, specify the -gm database server option.

If your database server accepts HTTP/HTTPS connections, then you can limit the maximum number of HTTP/HTTPS connections that can be made to a database server by specifying the MaxConnections protocol option. You can also reserve database connections for only standard connections by setting the reserved_connections database option.

Database connections

If your database server has numerous databases running on it, then limiting the number of connections to a specific database can be more effective than limiting connections to the database server. For example, a database server that allows 100 connections may have 90 connections to one database, leaving only ten connections available for all other databases.

To limit the number of connections that a database can accept, set the maximum_connections database option for that database.

If your database accepts HTTP/HTTPS connections and you must ensure that standard connections can be accepted at any time, then reserve connections for standard connections. Specify the reserved_connections database option to reserve standard connections.

You can also use a login policy to limit the number of connections to users who have the specific login policy. When creating the login policy, set the max_connections login policy option or the max_non_dba_connections login policy options. Alternatively, you could create a custom login procedure to limit the number of connections by using the login_procedure database option.

HTTP/HTTPS connection queuing and limiting connections

The database server queues HTTP/HTTPS connections. When the database server reaches its connection limit and a new HTTP/HTTPS connection is attempted, the connection attempt is queued. Connection attempts remain in the queue until an HTTP/HTTPS connection becomes available, or until the attempt times out after 150 seconds. The queue can hold a maximum of 1000 connection attempts.

Standard connections are not queued. When a database server reaches its connection limit, any new standard connection attempt fails.

While there are connection attempts in the HTTP/HTTPS queue, there is no opportunity for a standard connection to replace an HTTP/HTTPS connection. A user wanting to make a standard connection could wait indefinitely unless some of connections are reserved for standard connections. By reserving some of the database's connections for standard connections, you allow users to connect to the database to perform administrative tasks, such as dropping connections, performing backups, and so on.

Reserve standard connections by using the reserved_connections database option. The network database server does not reserve connections for standard connections. The personal database server reserves a minimum of 3 connections for standard connections.

You could also limit the number of HTTP/HTTPS connections that can be made to a database server by specifying the MaxConnections protocol option.

Connections above the connection limit

When the connection limit has been reached, a DBA user can make one additional standard connection to a database above the limit to forcibly drop connections as needed. This new connection is allowed only if all of the following conditions are met:

  • The user has the DROP CONNECTION or SERVER OPERATOR system privilege.

  • The total number of database server connections (excluding the new connection) is less than or equal to the MaxConnections database server property limit.

  • The total number of database connections (excluding the new connection) is less than or equal to the MaxConnections database property limit.

  • The connection is a standard connection.