Database Connection Strings and Diagnostics

published: 2015-09-10 10:34

RockSolid SQL Tips and Tricks

G’day,

Many database professionals have worn a variety of different hats throughout their careers, from database developers to database administrators to database architects and other more varied roles. All of these roles have a significant degree of overlap and being familiar with one is often extremely useful – though not necessarily a pre-requisite – when moving from one to another. Collaboration between different database roles and is often essential and can often save a great deal of time when troubleshooting.

One quite simple – yet often overlooked – example of this is the willingness of database developers to build diagnostics into their applications. These diagnostics can take many forms and are often helpful later in the application lifecycle to help database administrators find opportunities for further improvements – such as improving query performance.

When a database administrator only has a few active databases on a server, these opportunities can be relatively easy to spot. But, organisations often wish to utilise their hardware and licensing to the maximum extent possible, which is a good thing and helps keep costs at a manageable level. However, when the number of databases on a server starts to increase, so does the complexity of spotting which application is the potential bottle-neck.

However, there is one small step that application developers can take to help their database administration colleagues out. All applications that connect to databases have a connection string somewhere and normally that connection string would reside in an easily reachable and easily maintainable place, such as a web.config file.

If the connection string contains an attribute known as the “Application Name”, this can be immensely helpful to database administrators in identifying applications quickly.

An example is shown below:

Connection Strings

While this change can be made after an application is released to production it is often easier to make it upfront at the development stage. After an application is in production it can potentially take some paper work, such as a change control request, to get even small changes like this made – however, even at this point it is probably still worth making.

A call to action would be to add this attribute to any applications where it is missing and to make it one of the first things that’s added to a new application. Your database administration team will be grateful for this small change that allows them to filter connections more easily and help identify performance improvement opportunities all the more efficiently.

SQL Profiler no Application NameSQL Profiler with Application Name

We’ll visit details of the diagnostic tools that can take advantage of the Application Name in later posts.

Martin Catherall
Martin is a Senior SQL Consultant with RockSolid SQL and has over 10 years’ experience as a TSQL developer and database administrator. He is a current recipient of the Microsoft Most Valuable Professional (MVP) award in SQL Server and is a regional mentor for the Professional Association of SQL Server (PASS) in the Asia Pacific area.

Related Categories


DBASQL Pro SQL Server

RockSolid Automation Platform