Make It Easier for the DBA: Give SQL Connections the Application’s Name!

Imagine you’re a database administrator tracing query activity on a server. You’d want all the clues you can get to help you figure out what’s going on. One clue is the name of the client program associated with each database session. This name, passed from client to Microsoft SQL Server when a connection is established, can be used by the DBA to differentiate between multiple applications running on a single client and to focus on (or filter out) activity from the same application across multiple clients.

Does your application pass a meaningful program name to the database server?

Out of the box, SqlConnection defaults this name to “.Net SqlClient Data Provider” on .Net Framework and “Core .Net SqlClient Data Provider” on .Net Core. These default values aren’t particularly helpful to the DBA, as they don’t reveal the identity of the program using the session. True, these defaults indicate the technology that that program was built using—but the DBA can find this information elsewhere, as his management views separately report the client interface library used by the session.

In the sys.dm_exec_sessions management view output below, notice how much more informative the last row is because it contains a meaningful program name.

Why not make database administrators’ lives easier by setting the program name property to something meaningful—something that specifically identifies your application?

Setting the Application Name

To give a connection a program name, simply set the connection string’s Application Name property (or its alias, App):

Server=SomeWhere\SomeInstance;Integrated Security=SSPI;Initial Catalog=AppDb;Application Name=Super Cool App

If your application uses a hard-coded connection string, this is easy to implement: simply edit the connection string. However, if the connection string is user-configurable, a little more effort is involved.

Append It (or not)

At first glance, it might seem sufficient to append the Application Name setting onto the user-provided connection string:

var connectionString = $"{userConnectionString};Application Name=Super Cool App";

However, this approach doesn’t take into account the possibility that the user’s connection string could already contain the Application Name keyword. Appending Application Name onto the string could result in that keyword being used twice in the connection string—and that could produce unexpected behavior.

The Builder is Better

This possibility can be accommodated by using SqlConnectionStringBuilider.

var defaultApplicationName = new SqlConnectionStringBuilder().ApplicationName;

var builder = new SqlConnectionStringBuilder(userConnetionString);
if (builder.ApplicationName == defaultApplicationName) {
  builder.ApplicationName = "Super Cool App";
}

var connectionString = builder.ConnectionString;

The code is a little more complex but much more robust. It only sets the connection string’s Application Name if that property (including its alias—the builder understands and respects aliases) is either not set or set to the default value.* Also, since SqlConnectionStringBuilder handles escaping, you’re saved from needing to know the connection string’s syntax rules for escaping any special characters that might be used in the application name.


*Unfortunately, SqlConnectionStringBuilder does not provide a way to differentiate between “not set” and “set to the default.” If the connection string does not contain an application name, the builder sets that name to the default (instead of to null or to an empty string)—which is exactly the same value that property would have if it the user manually set that property to the default in the connection string.

One thought on “Make It Easier for the DBA: Give SQL Connections the Application’s Name!

  1. Jeff Taylor

    This is good advice. I’ve worked with many companies and required them to add this to their applications to help trouble shoot database issues.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *