未加星标

Make Your Application’s Name Heard

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

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/webserver.

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 sportinga 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 fewparameters 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:


Make Your Application’s Name Heard

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 my next post, I’ll show how I added Application Name support to Invoke-SqlCmd2 .

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQL.NetSQL Server
分页:12
转载请注明
本文标题:Make Your Application’s Name Heard
本站链接:http://www.codesec.net/view/520048.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(59)