SQL Server Performance Forum – Threads Archive

SQL Success Audits

we have built an application which maintains Qs in the database. We constantly
create ADO connections using a connection string. and we close connections
when done with the operation. so this is constantly creating login/logout success audits in SQL server. Some customer has these audits setup and is upset that they see
thousands of these audits in the eventlog every day. Have we designed the app improperly ? or the customer just being a pain ?

You can use connection pooling instead login/logout for each time… Customer always right<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> when you are selling something<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> http://msdn2.microsoft.com/en-us/library/ms810829.aspx</a> http://www.sql-server-performance.com/sk_connection_pooling_myths.asp</a> http://www.dotnetspider.com/kb/Article1290.aspx</a> MohammedU. Moderator SQL-Server-Performance.com

What kind of events you are auditing, if it is set to ALL then yes it is a normal behaviour to see such ‘more’ events in a day. You have to consider what kind of events you would like to monitor or take a thorough check of process at that client. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

I always thought connection pooling is by default enabled if the connectionstring does not change. no ? how do i go about enabling it using ADO ? can i use the connectionstring for that ? The customer is seeing successful login/logout events in security log.

http://www.sql-server-performance.com/bl_asp_ado.asp Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

im already closing the connections properly and performance is pretty good. Is there anything special that i need to do to enable connection pooling ? I did go thru’ the link that you sent me and im closing the connection and also setting it to null.

im using SQL Oledb provider for SQL Server.

quote:For instance, currently the Microsoft OLEDB provider for ODBC uses ODBC 3.0 to connect to data sources. This means that OLEDB can take advantage of ODBC 3.0 connection pooling. ADO uses this OLEDB provider to connect to Datasources. Both ADO and this OLEDB provider can take advantage of ODBC 3.0’s connection pooling. Currently the only OLEDB provider using ODBC is Microsoft’s OLEDB provider, it can take advantage of ODBC connection pooling. Because ADO uses OLEDB, ADO can also take advantage of ODBC connection pooling. Active Server pages can also use connection pooling if they use ADO as their database interface

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Thanks for the response. The customer is breathing down my neck <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> But do i need to do anything special to enable this Connection Pooling with the Microsoft SQL Oledb driver ?

Read the following articles in my previous post… Check the following download for samples… http://www.microsoft.com/downloads/…50-3e29-45cf-8c02-5597c014a707&displaylang=en
MohammedU.
Moderator
SQL-Server-Performance.com

When i run this SELECT spid, dbid,uid=rtrim(loginame), Program_name=rtrim(Program_name), dbname=db_name(dbid), status=rtrim(status) FROM master.dbo.sysprocesses I see the connection showing up and in the next second it is gone. The connectionstring i have is Provider=SQLOLEDB.1;Integrated Security=SSPI<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ersist Security Info=False<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />ooling=true;Min Pool Size=3;Max Pool Size=5;Connection Lifetime=120;Initial Catalog=test;Data Source=TESTMACHINESQLEXPRESS; And then in the log, the log grows almost every second 3 entiries !! "Login succeeded for user ‘TESTMACHINEUser’. Connection: trusted…." "Login succeeded for user ‘TESTMACHINEUser’. Connection: trusted…." "Login succeeded for user ‘TESTMACHINEUser’. Connection: trusted…."

quote:Originally posted by MohammedU Read the following articles in my previous post… Check the following download for samples… http://www.microsoft.com/downloads/…50-3e29-45cf-8c02-5597c014a707&displaylang=en
MohammedU.
Moderator
SQL-Server-Performance.com

can someone please help ? just setting "pooling=true" does nothing the connections get dropped and recreated every 1s

]]>