Make Your Application’s Name Heard

Odds are, you’ve got more than one application or script accessing your database or SQL Server instance at any given time. You’re probably stacking them on a small number of servers in an attempt to cut down on licensing costs and resource usage. All those Powershell scripts running on the central job server are running under a single service account, and you’ve got a lazy vendor who set up both the website and back-end application server to run under the same account, maybe even on a single app/web server.

Then the call comes in. “The database is slow.” Before firing up your monitoring suite, you take a quick pass with sp_who2 or sp_whoisactive and you’re greeted with a dozen sessions, all sporting a program_name of .Net SqlClient Data Provider. Terrific! Which session is coming from which application and causing all the trouble? Not so easy to figure out.

Fortunately, the .NET SqlClient (and other ODBC drivers as well) has a built-in solution. Your application’s connection string has quite a few parameters available to provide configuration and information, and one that seems to get overlooked is Application Name. This one does exactly what it says on the tin – it lets you specify a name that will be displayed to anyone looking for it in SQL Server, including sp_whoisactive. Anyplace you have the ability to write a connection string, you can use this. It costs you nothing!

  • If you’re using System.Data.SqlClient.ConnectionStringBuilder, it’s just another item in the properties collection.
  • If you’re constructing your connection string as a regular string, just add Application Name=Andy's Awesome App; to the end of your current connection string.

$DBConnection = New-Object System.Data.SqlClient.SqlConnection;
$DBCSBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder;
$DBCSBuilder['Data Source'] = ".\sql2016";
$DBCSBuilder['Initial Catalog'] = "WideWorldImporters";
$DBCSBuilder['Application Name'] = "Andy's Awesome Application";
$DBCSBuilder['Integrated Security'] = "true";
$DBConnection.ConnectionString = $DBCSBuilder.ToString();

#alternative method:
#$DBConnection.ConnectionString = "Data Source=.\sql2016;Initial Catalog=WideWorldImporters;Integrated Security=true;Application name=Andy's Awesome Application;"

$DBConnection.Open();
$QueryCmd = $DBConnection.CreateCommand();
$QueryCmd.CommandText = "select c.CityName from application.cities c join application.stateprovinces s on c.StateProvinceID = s.stateprovinceid join application.countries ct on ct.CountryID = s.CountryID where ct.CountryName = 'United States' and s.StateProvinceName = 'New York'; waitfor delay '00:00:15';";
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$QueryCmd.Connection = $DBConnection;
$SqlAdapter.SelectCommand = $QueryCmd;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet);
$DataSet.Tables[0];


import-module sqlserver;
Invoke-Sqlcmd -ServerInstance .\sql2016 -Database wideworldimporters -Query "select c.CityName from application.cities c join application.stateprovinces s on c.StateProvinceID = s.stateprovinceid join application.countries ct on ct.CountryID = s.CountryID where ct.CountryName = 'United States' and s.StateProvinceName = 'New York'; waitfor delay '00:00:15';"
remove-module sqlserver;

Here’s the difference between using SqlClient and Invoke-SQLCmd in Powershell when seen from the SQL Server side:

invoke-sqlcmd-app-name

While the .NET Framework lets you specify this fairly easily, as we move up in abstraction layers these options become buried or entirely unavailable. This is the case with the Powershell Invoke-SqlCmd cmdlet. One of the many benefits of using Invoke-SqlCmd2 is that it does construct its own connection string (if you don’t pass in a SqlConnection object) but it doesn’t expose Application Name as an available parameter for it.

In an upcoming post, I’ll show how I added Application Name support to Invoke-SqlCmd2.

Advertisements

My First Windows Update Gone Bad

I don’t think I’ve ever had a Windows Update go bad – until this week.

I recently upgraded to Office 2013 and late Monday afternoon, decided to check in with Windows Update prior to our company’s normal monthly patching to see how bad the damage would be. Nearly 1 GB of updates, thanks to my fresh Office install. But there were also a couple optional updates, including a .NET Framework update. So I figured I may as well go ahead and do everything while I was at it. This way I could control the reboot instead of being forced into one in the middle of important tasks.

Tuesday morning, I got a call asking if we were having any issues with one of our key systems. I fired up my trusty SQL Sentry client to check things out. And failed. I couldn’t connect to the server to start monitoring. Never a good sign. Then I tried SSMS 2012. Again, couldn’t connect to any servers. I got the following error:

Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

That sounds pretty ominous. Try a few more times, no luck. Reboot, no better. Uninstall SQL Sentry Client and attempt to reinstall – still nothing. Things were going from bad to worse in a hurry.

I bounced the error messages off Kendal Van Dyke (b | t) and he suggested that it might be an issue with the SQL Native Client. So I reinstalled that from the SQL Server 2012 Feature Pack. And still, I couldn’t connect. I even tried PowerShell (v3), both the SQLPS module and SqlServerCmdletSnapin100 Snap-In and got the same errors SSMS 2012 threw out.

Taking a deep breath and stepping back, I started reviewing the situation. What do all of these have in common? They’re all using the latest (or at least a fairly recent version) .NET Framework. Let’s take a step back and try something older. SSMS 2008 R2 – works fine. Start up PowerShell v2 – that works too. Now we’re onto something!

The Framework update I installed was KB2858725, bringing it to version 4.5.1. My first thought was that maybe the installation was botched somehow, so I downloaded the installer and tried again. But to no avail. So I uninstalled it entirely and reinstalled 4.5 completely. This finally did the trick.

Due to other commitments, I haven’t had a chance yet to re-try the 4.5.1 update, but I’m also in no rush. While it won’t take me 4+ hours to troubleshoot and fix if it breaks things the next time around, I need my system stable for the next few weeks so I won’t be trying again until 2014.