Today I’d like to talk about the wonderful and magic things that are Connection Strings. If you’re not familiar with connection strings, they are the way that a developer informs ADO.NET which server to connect to and connection options to use.
A Simple Rule
The problem with connection strings (actually, there are quite a few problems, but I’ll stick to the point of this post) is that there are far too many options to choose from, but let me simplify everything for you:
If you don’t need to change an option, or don’t know what it does, then don’t specify it.
The default values for connection string options are there for a reason, so unless your application has some unusual requirements, you should stick to the default values. However, you shouldn’t re-specify the default values in your connection string either, unless you heavily rely on the behavior provided by that default value. The reason for this is that we may change the default at a later date if we make code changes (which make a different value more optimal), or we introduce a new value that is better for most developers. The most common case I see here is setting the "Max Pool Size" value – for the vast majority of applications the default size of 100 is reasonable, however you shouldn’t specify 100 in case we (or SQL Server) make modifications to our network code and so are able to increase the maximum, or perhaps we’d have different values for client and server applications*. Either way, you’d want to be able to get this benefit for ‘free’ by not specifying a value, rather than having to modify all of your deployed config files with the new values (because, of course, you are using config files, and don’t have the connection string hard-coded in your application).
Alternatively. the thought "I don’t know what it does, but I might use it later" may also lead to included unneeded connection string options. If there was some beneficial feature that didn’t have any negative side effects, then we would enabled that option be default. The fact that a connection string options is disabled by default should indicate that there is some other side effect of turning it on (typically a performance hit, but possibly other things). If you don’t know what an option does, then you probably aren’t taking advantage of it, and if you aren’t taking advantage of it, then you don’t need it. A good example of this is "Multiple Active Result Sets" (aka MARS). This is a feature introduced in SQL Server 2005 that permits multiple commands to be executed on a single connection simultaneously**. This may sound great, but most applications don’t really have a need for it, they can simply open another connection. However, if you turn it on because you "may need it", then you will be taking a performance hit and possibly hiding errors in your code (since having MARS off ensures that you dispose a SqlDataReader before trying to open a new one on the same connection).
Before you go ahead and start removing options from your connection strings, there is one thing you should be aware of: since you had these options specified, there may be parts of your code that rely on the non-standard behavior. For instance, if you turn off MARS, any part of your code that created multiple readers on the same connection will start throwing exceptions, or reducing the Max Pool Size may reveal a connection leak that was previously hidden (resulting in more exceptions in your code). So be very careful when changing connection string options and ensure that you run all of your tests (which, of course, you have) and have a rollback strategy to deploy the old connection string if something goes wrong.
*These are just examples and are not necessarily in our current plans. But if you like these ideas, or have some of your own, feel free to post the on Connect
**Technically speaking, we don’t support multithreaded access to the same connection, even with MARS turned on (unless you are cancelling a Command). So, you would still need to synchronize each of the Command\Reader executions\reads on the same connection.