ApexSQL Knowledgebase: ApexSQL Log Pre-Installation Notes

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly.

FAQs

Quick answers to common questions.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools.

Online Documentation

All of our help files for download or convenient viewing online.

Videos

Watch product demos, training videos, or tutorials of our products' main features.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

ApexSQL Log Pre-Installation Notes

DESCRIPTION
This article describes what you should know about installing ApexSQL Log's server-side components on your SQL Server. It enumerates what exactly is installed on your server. ApexSQL Log's installation requirements are fairly small and their overall effect on performance is negligible. Installing ApexSQL Log on your server also does not require you to restart your server.

What does ApexSQL Log install on my SQL Server?

A. At the file level, ApexSQL Log installs the following; these are saved in the SQL Server instance’s ‘Binn’ folder.

For ApexSQL Log 2005:

1) ApexSqlServerXprocs.dll - the DLL containing all extended procedures used by ApexSQL Log and other ApexSQL products with server-side components.
2) ApexSqlConnectionMonitor.exe - the executable for Connection Monitor component of ApexSQL Log.
3) ApexSqlServerHelper.exe - the executable used by ApexSQL Log extended procedures for task done externally to SQL Server process.
4) ApexSqlServerHelper.sys - the service executable used by ApexSQL Log extended procedures.
5) ApexSqlLogActivation.exe - the executable used by ApexSQL Log to keep track of activation and evaluation states.
6) ApexSqlLogApiActivation.exe - the executable used by ApexSQL Log API to keep track of activation and evaluation states.

For ApexSQL Log 2008.01 - 2008.03, the names were changed and ApexSQL Recover activation was added:

1) ApexSqlServerXprocs2008.dll
2) ApexSqlConnectionMonitor2008.exe
3) ApexSqlServerHelper2008.exe
4) ApexSqlServerHelper2008.sys
5) ApexSqlLogActivation2008.exe
6) ApexSqlLogApi2008Activation.exe
7) ApexSqlRecover2008Activation.exe - the executable used by ApexSQL Recover to keep track of activation and evaluation states.

Starting from the ApexSQL Log 2008.05 server-side components were split on ApexSQL Log and ApexSQL Recover:

1) ApexSqlLog2008Activation.exe
2) ApexSqlLogApi2008Activation.exe
3) ApexSqlLog2008ConnectionMonitor.exe
4) ApexSqlLog2008ServerHelper.exe
5) ApexSqlLog2008ServerHelper.sys
6) ApexSqlLog2008Xprocs.dll

ApexSQL Recover activation is removed from the ApexSQL Log server-side components and installed as part of ApexSQL Recover server-side components.

B. At the server-object level, the following are added to the master database:

For ApexSQL Log 2005--
xp_ApexSqlConnectionMonitor
xp_ApexSqlConnectionMonitor_Info
xp_ApexSqlConnectionMonitor_Stop
xp_ApexSqlConnectionMonitor_Enable
xp_ApexSqlConnectionMonitor_Disable
xp_ApexSqlLog
sp_ApexSqlConnectionMonitor_Start

For ApexSQL Log 2008.01 - 2008.03--
xp_ApexSqlConnectionMonitor2008
xp_ApexSqlConnectionMonitor2008_Info
xp_ApexSqlConnectionMonitor2008_Stop
xp_ApexSqlConnectionMonitor2008_Enable
xp_ApexSqlConnectionMonitor2008_Disable
xp_ApexSqlLog2008
sp_ApexSqlConnectionMonitor2008_Start
xp_ApexSqlLogApi2008
xp_ApexSqlRecover2008

Starting from ApexSQL Log 2008.05--
xp_ApexSqlLog2008ConnectionMonitor
xp_ApexSqlLog2008ConnectionMonitor_Disable
xp_ApexSqlLog2008ConnectionMonitor_Enable
xp_ApexSqlLog2008ConnectionMonitor_Info
xp_ApexSqlLog2008ConnectionMonitor_State
xp_ApexSqlLog2008ConnectionMonitor_Stop
sp_ApexSqlLog2008ConnectionMonitor_Start
xp_ApexSqlLog2008
xp_ApexSqlLogApi2008

C. New table added to the MSDB database
One table called msdb..APEXSQL_LOG_LOGIN is also added. Please note that you can choose to remove this table after installation IF YOU DON'T NEED IT. This table is used for storing login information as part of ApexSQL Log’s Connection Monitor (please read the next section for more information on the connection monitor). The connection monitor is an optional server-side component that can be stopped and/or disabled if you don't need some specific auditing information namely:

For SQL Server 7 and 2000:
- NT user name
- application name that the change originated from
- and client host name that the change originated from

For SQL Server 2005, 2008:
- application name that the change originated from
- and client host name that the change originated from

The connection monitor tracks these information as they're not provided in the transaction log. If you don't need these connection information, please disable and/or stop the connection monitor after you install ApexSQL Log. The steps can be found here for ApexSQL Log 2008.01 - 2008.03 versions: Managing the ApexSQLConnectionMonitor2008.exe process and for ApexSQL Log 2008.05 Managing the ApexSqlLog2008ConnectionMonitor.exe process. Please drop the msdb..APEXSQL_LOG_LOGIN table after.

For SQL Server 2005 and 2008, the NT user name information will always be made available as long as you're connected to the same server where the transaction logs that you are attempting to read, originated from.

What is the ApexSQLConnectionMonitor.exe for?
To obtain connection-specific information, ApexSQL Log uses active connection monitoring through the ApexSQLConnectionMonitor.exe process. This process is designed to be very lightweight, fast, and non-intrusive so it does not interrupt the normal functioning of Microsoft SQL Server. Once the Connection Monitor is installed (it is a part of installed server side components), it will, for each new connection, store a row into the APEXSQL_LOG_LOGIN table. During auditing, ApexSQL Log queries this login table to obtain connection specific information for each transaction. ApexSQL Log returns this information as blank if information cannot be obtained.

Note: Connection monitoring works only for Microsoft SQL Server 2000, SQL Server 2005, and SQL Server 2008. It requires that the account under which SQL Server service is running has login permission to the SQL Server itself.

The following information is made available by the ApexSQL connection monitor:

For SQL Server 7 and 2000:
- NT user name
- application name that the change originated from
- and client host name that the change originated from

For SQL Server 2005, 2008:
- application name that the change originated from
- and client host name that the change originated from

Connection monitoring also allows user names on operations from linked servers (that occurred while it was alive) to be known. Moreover, with connection monitoring, ApexSQL Log can show user names even for dropped users. As not to consume machine resources, ApexSQL Log stops the process when it fails consecutively for 5 times in 1 minute or less.

The ApexSQL Log Connection Monitor inserts one new row into msdb.dbo.APEXSQL_LOG_LOGIN database for each new login. So this table grows directly in proportion to the number of logins made to the server. The amount of data is dependent on the number of logins. Please see this troubleshooting item for more information: click here.

As discussed in previous section--if you don't need the information that the Connection Monitor provides, please stop and/or disable it.

Do all these cause performance overhead?
ApexSQL Log has no performance overhead during audit trail capture since it is not involved in it at all. The application simply reads the transaction logs to gather audit data. This allows auditing to be performed during low load times or even be offloaded to another server. Additional login information which are not available from the transaction log are the only information that are actively captured. This capturing process is done by ApexSQL's Connection Monitor which has minimal overhead.

SQL Server 2005 and SQL Server 2008 database's transaction log contains NT user's SID information from which full user name can be extracted. In this case, if application name and client host name are not necessary, ApexSQL's Connection Monitor can be stopped and disabled. If you don't need the Connection Monitor, you also wouldn't need the msdb..APEXSQL_LOG_LOGIN table. You can drop or truncate the table. Should you decide to enable and run the Connection Monitor in the future, the table will be automatically re-created if it doesn't exist.

SEE ALSO
Managing the Connection Monitor process
Here's a KB article that describes exactly what you need to do to stop, start, enable, or disable the connection monitor WITHOUT uninstalling it: Managing the ApexSQLConnectionMonitor2008.exe process.

Uninstalling ApexSQL Log's Server-side Components
To uninstall the server-side components completely, please refer to this article: http://www.apexsql.com/training/2007/08/how-to-uninstall-apexsqlconnectionmonit_09.htm.

AUTHOR
Janice Lee

LAST REVIEW DATE
09 December 2009

Labels: ApexSQL Log, ApexSQL Log API

posted by Janice Lee on Friday, June 12, 2009

0 Comments: