Why You Should Love Your Connection Strings
Who is responsible for configuring and maintaining connection strings within your organization? Developers, operations, the database team? For me, the correct answer should be everyone - each team has an important part to play in configuring optimal connection strings. Unfortunately in most organizations, once they're configured that's it - but there's so much to gain from giving them a bit of love.
Connection strings are essential to every application - without them we can't connect and nothing works. So it's pretty obvious when they are wrong, but what about when they're just not quite right? Optimizing your connection strings can improve the performance of your application and databases. Using the correct options will make them more secure, faster, require fewer resources, simplify maintenance and reduce the time it takes to diagnose issues.
We've create some connection string generators for .NET and JDBC, which you may find useful.
SQL Connection String Generator for .NET and JDBC
Basic Settings
There's an evergrowing list of options available to tune connection strings to suit your application, but it's difficult to know what does what. This is made worse by most settings having 2 or 3 synonyms, just to keep you on your toes! To help with this, we created an online Connection String Generator which reduces some of the complexity and builds your connection string for you.
You don't need much to get a connection string working. Most settings have a default if you don't explicitly set them. Therefore, the minimal amount you need is simply the server name and how you wish to authenticate.
Data Source=database_server;Integrated Security=false;User ID=sql_user;Password=password123
Another setting that I'd like to class as a basic one is Application Name. It won't improve the performance of your application but you'll be glad you set it when you're diagnosing server issues. Application name simply populates the program_name field in sys.dm_exec_sessions. So the next time your application is overloaded or having issues, you'll know exactly what program on the host is causing it.
Application Name=Steves Test App;
Network and Security
Most people connect to SQL Server using SQL authentication purely through habit. Instead, try running your applications under service accounts, then connecting to SQL Server using integrated security. Here's why.
Connecting via integrated security removes the requirement of storing passwords in plain text web.config files. Although these can be encrypted, you don't have the same level of control over these. For example, web.config files may also be stored in source control or emailed around in zipped up folders of the application, which enables more people to gain control to your production systems.
Using Windows authentication over SQL authentication also removes the requirement of synchronizing security ids (SID) between availability group nodes. If you're not aware of this issue, here's a blog on synchronizing AG nodes, but if your SIDs are not synchronized, you won't be able to authenticate after a failover.
Integrated security is simple to implement and much easier after the first time, so please have a good stab at it. Most applications can be updated using the same method when altering the service account for SQL Server and SQL Server Agent. You could go one step further by adding the service accounts that require similar access into AD groups, which will make maintenance and administration easier.
Data Source=database_server;Integrated Security=sspi
Network settings are an area you can get lost in and cause some interesting application problems, but there are few that should be considered when building connection strings.
Setting connection timeout is one. You may not want to wait for the default 15 seconds for new connections if your application tries to log thousands of requests a minute. On the other hand, you may want to allow 60 seconds to connect to a server on the other side of the world.
Connection Timeout=60;
Once the connection is established, subsequent queries will be quicker as the application will reuse the connection. This is where connection pooling comes in.
Connection pooling is a blog in itself but there are three main settings you should consider. Min pool size and max pool size control the size of the connection pool, so how many connections you want to keep open and how many connections you're likely to use. The main gotcha here is that max pool size has a default of 100, so if you don't set it, this is what you get. If you're happy with 100 then actively set that, so you can see what it is rather than relying on secret knowledge.
Creating connections to a database can be expensive. Imagine that person on the phone in videos of the stock exchange. Instead of keeping their colleague on the phone and relaying commands, they dial the phone number of the other person every time, say hello, give them the command to sell (or order pizza? I'm not actually sure what they do) and then say goodbye.
So, we want to keep them open, but there's a balance. Too few connections will starve your application, but too many can consume precious resources - from using memory in SQL Server, to playing havoc with your load balancer by exhausting the number of concurrent connections allowed and causing issues with load distribution (covered next).
Min Pool Size=10;Max Pool Size=100;
The third option I think you should consider is Connection Lifetime. Its synonym, Load Balance Timeout, gives us a pretty good clue to why you should set it. With pooling, we create a connection and leave it open so it can be reused. This is great until we need to add a new server in a distribute the load. Let's say we have a hundred connections to servers 1, 2 and 3 but none to 4 as it's just been restarted. Once this server comes back online it won't get a new connection until the application needs to create one, and if we've set our pools correctly, it won't.
Setting the connection lifetime tells the application to kill a connection after a certain length of time, giving the load balancer the opportunity to evenly distribute the database connections and therefore load.
Connection Lifetime=300
Complete Connection String
There are so many options that can be set and I haven't gone into enough detail (more blogs to come) but if we use the Connection String Generator and set the options we've discussed in this thread, we'll end up with the following:
Data Source=database_server;Application Name=Steves Test App;Connection Timeout=60;Integrated Security=sspi;Min Pool Size=10;Max Pool Size=100;Load Balance Timeout=300