How ArcGIS Server manages connection pool in SQL Server

[gisarchitect]

Hello Everyone,

I am seeking insights on how ArcGIS Server manages connections to SQL Server when serving feature services, particularly in the context of SQL Server Standard Edition licensing constraints.

Scenario:

• I have a feature service that references an enterprise geodatabase hosted in SQL Server.
• This feature service is accessed by approximately 20 users via Portal for ArcGIS, with ArcGIS Server acting as the hosting server.
• The feature service is configured with a maximum of 20 instances.

Questions:

1. Connection Pooling and Management:

   • How does ArcGIS Server handle database connections in this scenario? Does it use connection pooling to manage and reuse connections efficiently?
   • If 20 users are accessing the feature service simultaneously, does this result in 20 individual connections to SQL Server, or are connections pooled and shared across instances?

2. Service Instance Configuration:

   • Given that the feature service has a maximum of 20 instances, does this configuration imply that up to 20 connections could be established with the SQL Server, assuming high load?
   • What best practices can be followed to configure the minimum and maximum number of service instances to optimize connection usage?
3. Registered connection files in ArcGIS Server:

According to Microsoft, every user or device accessing SQL Server software, whether virtual or physical, must be licensed with a SQL Client Access License (CAL). This requirement extends to users and devices accessing the SQL Server software indirectly through another application or hardware device. My question is: When multiple geodatabases are registered from the same SQL Server instance, does this count as multiple users even if they are connected using the same SQL Server user account?
In other words, should I license only the single SQL Server user account used for registering the geodatabases, or should I license based on the number of connections in the connection pool?

1. Licensing Implications:

   • Considering that SQL Server Standard Edition has licensing constraints based on the number of connections, how can I ensure compliance and avoid exceeding these limits?
   • Are there recommended strategies or configurations to manage and limit the number of concurrent connections to SQL Server in such a setup?

Thank you for your assistance.

[RedaAli]

I have the same enquires.

can you help regarding this?

[ArchitSrivastava · Accepted solution]

Hello @gisarchitect @RedaAli 

While I may not be able to address all of your concerns, I will do my best to answer as many as I can. Kindly find the details below (Hope it doesn't end up being a boring response 😁😞

Connection Pooling and Management:

• How does ArcGIS Server handle database connections in this scenario? Does it use connection pooling to manage and reuse connections efficiently?
  • ArcGIS Server uses connection pooling to manage and reuse database connections efficiently. Connection pooling minimizes the overhead of opening and closing database connections by keeping a pool of active connections that can be reused by different service instances.
  • Even if the ArcGIS Server is not utilizing the database, it will still have a "sleeping connection" to the database. You can validate this by querying the "sys.dm_exec_sessions" table in SQL Server with "s.login_name = 'RegisteredwithArcGISServer' AND s.status IN ('running', 'sleeping')".
• If 20 users are accessing the feature service simultaneously, does this result in 20 individual connections to SQL Server, or are connections pooled and shared across instances?
  • When 20 users access the feature service simultaneously, ArcGIS Server will not necessarily create 20 individual connections to SQL Server. Instead, it uses a pool of connections that are shared across service instances. The exact number of connections depends on the configuration of the service and the demand placed on the server, which controls the connections created by ArcGIS Server.

Service Instance Configuration:

• Given that the feature service has a maximum of 20 instances, does this configuration imply that up to 20 connections could be established with the SQL Server, assuming high load?
• Yes, up to 20 connections could potentially be established with SQL Server. This assumes that each instance requires a separate connection and that all instances are actively serving requests. However, if a connection completes a request and is free, it will be reused instead of creating a new connection.

What best practices can be followed to configure the minimum and maximum number of service instances to optimize connection usage?

This depends on several variables, with the major factor being "how fast the service can address the request from the client." The faster it addresses the request, the sooner that instance will be free to answer new requests. If the instance is reused, so is the DB connection (no new connection needed). These are controlled by a few properties on the service, including:

• Maximum time a client can use a service
• Maximum time a client will wait to get a service
• Maximum time an idle instance can be kept running
• Recycling

Documents that would help in extensively understanding these capabilities include:

• Tune and configure services
• Configure service instance settings
• Anticipate and accommodate users

Additionally, what helps me a lot is "monitoring and adjustment," i.e., regularly monitoring the usage of service instances and adjusting the configuration as needed. Tools like ArcGIS Monitor can help track service performance and database connection usage.

Registered connection files in ArcGIS Server

• When multiple geodatabases are registered from the same SQL Server instance, does this count as multiple users even if they are connected using the same SQL Server user account?

This depends on the architecture you want to apply.

• Database Authentication:
  • Essentially, ArcGIS Server will use the account you used to register the database connection, so all connections to SQL will show that account. In this case, you can create a separate account in SQL Server specifically for ArcGIS Server and use that for all connections (not recommended if you want editor tracking).
• Operating System Authentication:
  • In this case, use a domain account/local account with the same username and password on both the SQL and ArcGIS Server machines for the ArcGIS Server service. Add it as a login to the SQL Server instance and map it to a user you create in the database. ArcGIS Server will use this account to connect to the database. This would fall under the licensing process you are referring to.

• From what I could understand from different blogs, When multiple geodatabases are registered from the same SQL Server instance, each connection to the SQL Server counts as a separate user or device from a licensing perspective, even if they are connected using the same SQL Server user account. This is because SQL Server Client Access Licenses (CALs) are based on the number of distinct connections, not just the user account used for those connections.

I can say that I am no expert in SQL Server Licensing. Hence, I did some research on this and read a lot of blogs and in these cases YouTube videos help me a lot. I will try to summarize what I could understand briefly

• Licensing Obligations:
• Multiple Users or Devices:
  • Each connection to the SQL Server, whether coming from different geodatabases or multiple instances of the same geodatabase, counts towards your SQL Server CALs. Thus, you need to license based on the number of unique connections rather than the single user account.
• Connection Pool Consideration:
  • The connections in the pool represent the number of distinct sessions or instances that interact with SQL Server. Licensing should account for these connections as each connection could potentially be a different user or device.

• Licensing Based on Connection Pool:
• SQL Server CALs:
  • We need to consider and keep in mind to license SQL Server based on the total number of connections expected in the connection pool, not just the user account used to establish those connections. Each connection pool entry is treated as a distinct user or device for licensing purposes.

Key Points (From various sources)

• Multiple Geodatabases: If you have multiple geodatabases registered from the same SQL Server instance, each connection from these geodatabases counts as a separate user or device.
• Single User Account: Licensing could not be based solely on the single SQL Server user account but rather on the number of concurrent connections in the connection pool.
• Compliance: We can ensure compliance by licensing the maximum number of concurrent connections to SQL Server, reflecting the actual usage and number of connections that may be active at any given time.

Ensuring Compliance with SQL Server Standard Edition Licensing Constraints

Furthermore, again from reading different blogs and this time some YouTube Videos as well, SQL Server Standard Edition has licensing constraints based on the number of connections, here are some strategies and configurations to help ensure compliance and avoid exceeding these limits:

Strategies to Manage and Limit Concurrent Connections

Optimize Connection Pooling:

• Connection Timeout Settings: Configure shorter idle timeouts for connections to ensure that unused connections are promptly closed. This helps in keeping the number of active connections within the limits.
• Max Pool Size: Set the maximum pool size for database connections to a value that complies with your SQL Server licensing. This can be configured in the database connection string used by ArcGIS Server.

Configure Service Instances:

• Minimum and Maximum Instances: Set appropriate minimum and maximum instances for your ArcGIS Server services. By limiting the maximum number of instances, you can control the number of concurrent connections to SQL Server.
• Instance Recycling: Configure service instance recycling to periodically release and refresh instances, helping to manage the connection pool more effectively.

Monitor and Adjust:

• Monitoring Tools: Use tools like ArcGIS Monitor and SQL Server Management Studio to track the number of active connections, monitor usage patterns, and identify peak usage times.
• Alerts and Notifications: Set up alerts to notify you when the number of active connections approaches the licensing limit, allowing you to take preemptive action.

Implement Efficient Query Practices:

• Optimize Queries: Ensure that your queries are optimized to reduce the load on SQL Server, thus potentially reducing the number of connections required.
• Batch Processing: Where possible, use batch processing to handle multiple requests within a single connection.

Load Balancing:

Distribute Load: If feasible, distribute the load across multiple SQL Server instances. This can help in spreading out the connections and avoiding overloading a single instance.

User and Connection Management:

• Dedicated Accounts: Create dedicated SQL Server accounts for different services or applications to better track and manage connections.
• User Session Management: Implement policies to manage user sessions, such as limiting the duration of sessions or enforcing logouts after a period of inactivity.

Configuration Examples

Example of Connection String with Max Pool Size:

Sample query:

• Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;Max Pool Size=50;Connection Timeout=30;
• Setup Service Instance Configuration in ArcGIS Server in association accordingly

Example of Monitoring Setup:

• ArcGIS Monitor: Set up to track active connections, instance usage, and performance metrics.
• SQL Server Management Studio: As mentioned before you can monitor “sys.dm_exec_sessions” table and monitor that for “s.session_id = c.session_id” (could be more detailed as well).

Hopefully this doesn't cause more confusion. I understand that the response is a bit long but I tried to keep it as short as possible and not miss out on any key points. 

Hope it helps!

[gisarchitect]

To receive such a comprehensive and detailed response to a lengthy question with many points and topics is truly generous and deserving of immense gratitude. Thank you very much for your wonderful response, which makes me feel humbled to ask more questions, but I will take advantage of your generosity and ask one final question which is "The number of connections in the pool that ArcGIS use, is a property internally programmed or configured in ArcGIS Server which can be queried or known by someway? as I did not find this piece of information in any documentation or blog". Or it depends on the max number of connections configured in database server?

[ArchitSrivastava]

Hello @gisarchitect 

I am glad I could clarify the concerns. I would be happy to answer this or try to take a shot at it.

As per my understanding, ArcGIS Server uses connection pooling to manage database connections efficiently and would depend on the "Connection Pooling and Instances settings " for the service and would also take in consideration the database settings. 

For service instance settings:

• Dedicated : Each map or feature service can have a minimum and maximum number of instances, which should effectively translates into database connections. (but I think it does reuse the connections as well if too many requests hit the service one after other)
• Shared : The number of shared instances can be set to optimize resource use across multiple services. It is often recommended to have the number of shared instances align with the number of CPU cores available.

In addition to above, The actual maximum pool size can be influenced by your database configuration from SQL Server , where connection strings might define a maximum pool size limit.

Furthermore, I think it a difficult task to get this information by querying some where. I think it would require you to create a load testing scenario, which I can try to think of but would require tools like Jmeter etc.

I will try to come up with some scenarios and share that with you.

Apologies for the delay in response, was a bit occupied with things.

Hope it helps!

[gisarchitect]

Hello @ArchitSrivastava 

I do appreciate your reply. Thanks and I would be glad if you shared with me those scenarios.

Thank you.