SQL DB connections - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

SQL DB connections

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2011-05-04 : 22:49:25

We are using SQL Server 2008.We want to enable both successful logins and attempted connections to Database. I have a question on DB connection.

Does the application open connections to the db
primarily at startup? Or does it open/close connections regularly as
part of normal behavior.

Thanks for you help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-05 : 08:02:05

Depends on the application.

Most will open/close for each request (query) and this is best practice in almost every multi-user environment.

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-05-06 : 08:04:45

create a Server level trigger or we can say Logon Trigger on the SQL Server,create a table auditTab with 2 columns login and logindate then create a trigger

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
Insert into auditTab values(ORIGINAL_LOGIN(),getdate())
END;

and check EVENTDATA function for further information

http://msdn.microsoft.com/en-us/library/ms173781.aspx

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-06 : 08:11:26

^ That's not a good idea. And it only solves half of the problem.

A server side trace is the right approach.

Unless the OP needs to perform immediate actions when a logon or logoff occurs (very rare), then Service Broker is the tool for the job.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources