Latest Contributions by ArchitSrivastava

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!   ... View more