How to Set Connection String in Production Application for best Performance
Connection String in Production Application for best Performance
After watching a few of application in productions, today I thought to write something about connection string. We generally care about server address and login details but we do not think about other things that will help our application to run with the best performance. Though it's very simple if we just add few attributes with it.
What we generally do is like
"Data Source=ServerAddress;Initial Catalog=DatabaseName; Integrated Security=true"
Or We do like
"Data Source=ServerAddress;Initial Catalog=DatabaseName;Integrated Security=False;Persist Security Info=False;User ID=UserName;Password=UserPassword;"
In the above both connection string is fine it works, but if we add few things with it then our application can perform better. So before using it we first know what are the attributes are available for the connection string.
Attributes are Like,
Connect Timeout or Connection Timeout or Timeout
- A default Value is 15
- Valid values are greater than or equal to 0 and less than or equal to 2147483647
Connection Lifetime or Load Balance Timeout
- The default value is 0
- A value of zero (0) causes pooled connections to have the maximum connection timeout
ConnectRetryCount
- Default Value is 1
- Controls the number of reconnection attempts after the client identifies an idle connection failure. Valid values are 0 to 255. The default is 1. 0 means do not attempt to reconnect (disable connection resiliency).
ConnectRetryInterval
- The default value is 10
- Specifies the time between each connection retry attempt (ConnectRetryCount). Valid values are 1 to 60 seconds (default=10), applied after the first reconnection attempt. When a broken connection is detected, the client immediately attempts to reconnect; this is the first reconnection attempt and only occurs if ConnectRetryCount is greater than 0. If the first reconnection attempt fails and ConnectRetryCount is greater than 1, the client waits for ConnectRetryInterval to try the second and subsequent reconnection attempts.
Encrypt
- Default value 'false'
- Please understand it well before using it.
- When true, SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed. Recognized values are true, false, yes, and no for more details visit https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax?view=netframework-4.7.2
Enlist
- The default value is 'true'
- true indicates that the SQL Server connection pooler automatically enlists the connection in the creation thread's current transaction context.
Max Pool Size
- The default value is 100
- The maximum number of connections that are allowed in the pool.
Min Pool Size
- The default value is 0
- The minimum number of connections that are allowed in the pool.
Packet Size
- The default value is 8000
- Size in bytes of the network packets used to communicate with an instance of SQL Server.
- The packet size can be greater than or equal to 512 and less than or equal to 32768.
- Note:- Before changing default value analyze your infrastructure and application need else it can result in bad performance.
Persist Security Info or PersistSecurityInfo
- The default value is 'false'
- When set to false or no (strongly recommended),
- Security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state. Resetting the connection string resets all connection string values including the password. Recognized values are true, false, yes, and no.
Pooling
- The default value is 'true'
- When the value of this key is set to true, any newly created connection will be added to the pool when closed by the application. In a next attempt to open the same connection, that connection will be drawn from the pool.
- Connections are considered the same if they have the same connection string.
- Different connections have different connection strings.
- The value of this key can be "true", "false", "yes", or "no".
In fact there many more attributes with connection string, but I hope for small-medium application the above will works, for more please refer to System.Data.SqlClient class for full insight.
And if you interested to use Connection String Builders then in ADO.NET 2.0 we have a special class to for the same, to know more on you can visit Connection String Builders
Why these above points are valuable to understand before going into productions as we all know that our application does work with a database with SQL Server Connection Pooling (ADO.NET). So if you do not know how Connection Pooling works it's strictly recommended to understand it.
Note if you working with the latest version of .NET Core then please check once Asynchronous Programming to add some async part as well.
So what is SQL Server Connection Pooling?
"During application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling."
Connection pooling minimizes the number of times new connections must be opened. The pooler maintains ownership of the physical connection. Connection Pooler manages connections by keeping alive a set of active connections for each given connection configuration.
Why do we care about Connection string so much in production it will not only allow our application to run with the best performance, but it will allow us to guard our application with Connection String Injection Attacks.
What is Connection String Injection Attacks?
A connection string injection attack occurs when dynamic string concatenation is used to build connection strings that are based on user input data. If the string is not validated and malicious text or characters not escaped, an attacker can potentially access sensitive data or other resources on the server. If you want to read more about then visit https://blogs.oracle.com/security/a-new-threat-to-web-applications:-connection-string-parameter-pollution-cspp
And
https://blogs.msdn.microsoft.com/codejunkie/2009/07/20/connection-string-injection-attack/
Hope the above attributes of connection string will help us for moving our applications in productions.