Introduction

Log P.I.® is an application designed to read native SQL Server transactions logs without creating performance hits. Log P.I.® provides solutions for auditing, recovery, and performance. Log P.I.® consists of a Client component and a Server component. It is designed to be installed on the client machine which polls the SQL Server at a rate determined by the user. On the server, Log P.I.® does not run in same address space as SQL Server because the log is interrogated via an agent service

LogPI_Agent service on the SQL Server machine). The Log P.I.® software alleviates problems encountered by some trigger based logging methods because all the log activity is reported. Log P.I.® provides fundamental security, auditing, and business process related answers to questions such as:

1. Who changed the data in the database?
2. What did Jane Doe change, specifically?
3. Is our business processes in conformance?
4. Why did John Doe's permissions allow him to delete records?
5. When the system was performing poorly, who/where did it affect and for how long?

Log P.I.® attaches itself to "active" log files on your Microsoft SQL Server®

as well as to backed up log files. With Log P.I.®, you can monitor any number of SQL servers, database, and transaction logs simultaneously; retrieve truncated and dropped tables; audit business processes; view the overall status and health of the logging sub system; get up to date summaries of your database's health and size; get summaries and details of the actual work load experienced by Microsoft SQL Server®; and get summaries of the set up and performance of your server.

The features of Log P.I.® are as follows:

• The ability to salvage lost data without restoring a back up
• Gives the user the ability to find or view all or specific operations within the log
• Summary of the set up and performance of your server/s
• Instant up to date summary of your database health & size, as well as the size per table
• Current overall status & health of the logging sub system
• Summary of the work load represented in the specific log
• Graphical representation of database data size
• Graphical representation of transaction log data size
• Summary of transaction log including load, activity & performance
• Analysis of the transaction log including throughput summary, throughput detail, table activity, user activity

The benefits of Log P.I.® are as follows:

• You can recover lost data with out restoring a back up
• You can view, find & undo operations within the log. “Who did what & when to the log?”
• Gives the user the ability to quickly check the set up of the server
• You can see when there is undue growth or decline and changes in the size of the database
• Helps in analyzing and determining the cause of performance issues in the logging sub system
• Gives insight into current status of the log

Installation

1. Log P.I.® comes with a big footprint. The ZIP download (at least for the evaluation version) is 79,343KB (79MB).

   The setup application file (LogPI_Setup.exe) is 79,349KB once extracted. Once installed on the client, the evaluation edition takes up 87.2MB.

2. You will need the following security permissions to install and run Log P.I.®:

Client Install

• The logged on user must be a member of the local administrators group. Administrator rights are required to install the application itself,

  MSDE 2000 sp3 and the .NET Framework on the client machine.

Client Run

• The logged on user must be an administrator or power user.
• The logged on user must have a valid account on the SQL Server that the user wants to interrogate.
• The logged on user must have rights to inquire service statuses remotely on all applicable SQL Servers. This is required to read the Log P.I.® ® Agent

  Service status on the server machine.

Server Install

• The logged on user must be a member of the local administrators group as well as an administrator on the SQL Server instance

Server Run

• The Log P.I.® ® Agent Logon Account must be an administrator or power user on the server as well as have the necessary network security permissions

  to access and read any backed up log files on remote machines.

3. It is advisable to NOT install Log P.I.® on your production server (see Figure 1). Also, on the client (Windows 2000, 2003, and XP only), I highly recommend that you do not install it on a client that already has .NET installed as well as Microsoft's Server Desktop Engine because it will install .NET v. 1.0.3705 and Log P.I.®'s version of MS SQL Server's Destop Engine (MSDE 2000 sp3a) . If you have .NET 1.1 installed, it will just ignore it and install v. 1.0.3705 anyway. Also, if you uninstall Log P.I.®, it will not uninstall .NET v. 1.0.3705. Keep that fact in mind. I am not too sure what happens to existing MSDE installations but if you already have MSDE installed, keep note of your current version before the installation.

Figure 1

WARNING!: This software has been designed to be installed on a client machine (such as a DBA's workstation) with access/rights to the SQL Servers and NOT on the SQL Servers machines themselves.

4. If you receive the "Process performance counter is disabled"

   operating system error shown in Figure 2 below on your Windows client after the installation; beware, you are in for a battle. This occurs when the Process performance counters are disabled on your computer or your logon user does not have sufficient rights to view System performance information. You may end up tweaking the registry, using ExCtrlList utility (Windows 2000 Resource Kit), and/or expanding the performance counter libraries manually by using Windows 2000 EXPAND (which is what I finally had to do). If you receive this error there are several solutions available to you by contacting Technical Support during office hours (07h00 - 16h00 (EST)) at (345) 945 0873 or Fax (345) 945 3006 or support@logpi.com. Also, there is an online form you can fill out at www.logpi.com for problems.

Figure 2

Environment

The following is the minimum & recommended hardware and software requirements for

running Log P.I.®

Client Hardware Requirements

=====================

Hardware Minimum Requirements

----------------------------------

Computer Pentium III 1 GHZ or higher

Memory (RAM) 256 MB

Available Hard Disk Space 150 MB

Monitor 1024x768 or higher resolution & 16 bit color or higher

Hardware Recommended Requirements

----------------------------------------

Computer Pentium III 2 GHZ or higher

Memory (RAM) 512 MB

Available Hard Disk Space 150 MB

Monitor 1024x768 or higher resolution & 16 bit color or higher

Client machine Operating System Requirements

================================

Windows 2003 Server, Windows 2000, Windows 2000 Server or XP

Server Requirements

==============

Component Minimum Requirements

------------------------------------

Hardware Same as required for SQL Server®

Microsoft SQL Server® 7.0 (sp3) or better

Network Software Requirements

========================

TCP/IP must be enabled at the operating

system level before installing Log P.I. ®

Using Log P.I.®

The first time you launch Log P.I.®, you will be asked to select which servers you initially want to view (Figure 3):

Figure 3

After that, you will be asked to view a 2 minute demonstration (Figure 4). This is a great demo and well worth checking out! See Figures 5 through 26 linked here (screen shots are 1024 pixels in width to provide better detail). There is also a recorded audio/visual Introduction Tour you can check out that you can choose from the Help menu within the application or from Windows' Programs menu under Log P.I.®

Figure 4

Demo Screenshots

View the screenshots

Other Things to Know about Using Log P.I.®

Server Side Component

The LogPIServerComponentSetup.exe needs to be run so that the server components are installed. These components are essential if you wish to interrogate the Log.

The Log P.I.® Agent can be installed by running the Server Component Set up (LogPIServerComponentsSetup.exe) on the server you wish to monitor, this set up can be found in "\program files\logpi\server components\install" on the client machine. Navigate to the server concerned and run the LogPIServerComponentsSetup.exe.

If the Log P.I.® Agent has not been installed (see Figure 26), you will not be able to interrogate the log and will not be able to use the following functions:

• Log Analysis
• Search / Browse Log
• Retrieve Truncated / Dropped Data
• Backed Up Logs Search & Analysis
• Selected Logs Search

Figure 26

The Log P.I.® Agent installs the following on the server:

• LogPI_Agent.exe - the is the actual agent that reads the SQL Server Log
• sclm0001.msg - the associated message file for the above Log P.I.® Agent
• LogPI.dll - the Log P.I.® User Name Trace subsystem
• MSvCP70.dll & MSVCR70.dll - the Microsoft Visual C++ runtime
• LogPISvrMgmt.exe (see Figure 27 - this is the Log P.I.® Server Management Utility used to configure the Log P.I.® Agent and install/configure the Log P.I.® User Name Trace which is a background process monitoring NT logins to the specified SQL Server instance)

Figure 27

The Log P.I.® User Name Trace installs the following objects on the selected server instance (see Figure 28 for a server with user name trace installed):

• master..xp_LogPI_Trace_Start - this starts the user name trace
• master..xp_LogPI_Trace_Stop - this stops the user name trace
• master..xp_LogPI_Trace_Status - this provides information about the user name trace configuration & its current status
• master..xp_LogPI_Trace_Clean - this cleans the trace table LogPI_User_Logins
• master..xp_LogPI_Trace_Setup - this allows you to change the current trace settings. Please note that the trace has to be restarted to apply any changes made

Figure 28

Export/Print Capabilities

The results of the Search/Browse log can be exported or printed as follows:

• Save Transaction As TSQL Script     : The SQL query for

  the specific transaction selected can be saved as a script. This option can also

  be selected through a right click. A pop up box will then allow you to select in

  which folder you wish to save this script.

• Copy Transaction As TSQL Script To Clipboard   : The SQL query for

  the specific selected transaction can be copied to the clipboard and used as

  required. This option can also be selected through a right click. You can then

  paste and run the script as desired.

• Save "Undo" Transaction As TSQL Script: The SQL query for the

  selected transaction can be saved and then run, this will cause the undo

  (opposite of the query) to be run for example: if a row was deleted, an insert

  for this same row will be generated and can then be saved away. This option can

  also be selected through a right click. A pop up box will then allow you to

  select in which folder you wish to save this script.

• Copy "Undo" Transaction As TSQL Script To Clipboard : The SQL

  query for the specific selected transaction can be copied to the clipboard and

  used as required, this will cause the undo (opposite of the query) to be run .

  This option can also be selected through a right click. You can then paste and

  run the script as desired.

• Save All Transactions As TSQL Script: The SQL query for all the transactions

  selected can be saved as a script. This option can also be selected through a

  right click. A pop up box will then allow you to select in which folder you wish

  to save this script.

• Copy All Transactions As TSQL Script To Clipboard: The SQL query for all the

  selected transactions can be copied to the clipboard and used as required. This

  option can also be selected through a right click. You can then paste and run

  the script as desired.

• Save All "Undo" Transactions As TSQL Script: The SQL query for all

  the selected transactions can be saved and then run, this will cause the undo

  (opposite of the query) to be run for example: if a row was deleted, an insert

  for this same row will be generated and can then be saved away. This option can

  also be selected through a right click. A pop up box will then allow you to

  select in which folder you wish to save this script.

• Copy All "Undo" Transactions As TSQL To Clipboard: The SQL query

  for all the specific selected transactions can be copied to the clipboard and

  used as required, this will cause the undo (opposite of the query) to be run .

  This option can also be selected through a right click. You can then paste and

  run the script as desired.

• Print Results: Normal Grid View:  Will print all the statements for all

  the transactions displayed.

• Grid Tree View:  Currently disabled.

Frequently Asked Questions

General

1. Can Log P.I.® monitor Oracle databases?

   No unfortunately not, Log P.I.® has been specifically developed for SQL Server 7 & 2000.

2. Can I run multiple copies of Log P.I.® Client on my machine?

   No, only one copy of Log P.I.® client can run at any one time.

3. Do I need to install another instance of the Log P.I.® agent to monitor more than one instance of SQL server?

   No, there only needs to be one instance of the agent running per physical server, regardless of the number of instances.

4. Is it possible to convert my evaluation copy into a production version?

   Yes, after completing the order form and once the transaction has been completed, you will be mailed a Product Key number which will convert the evaluation copy.

5. Am I limited to the number of servers I can monitor?

   No, Log P.I.® can monitor an unlimited amount of SQL Servers™, provided that you have purchased sufficient server licenses.

6. What impact does Log P.I.® have on my system?

   Log P.I.® has no overhead, SQL Server™ reads the Transaction Log already. Log P.I.® merely interrogates these statistics.

7. What Do I need to run this product?

   In order to run Log P.I.® you need a computer with minimum 1 GHZ Intel Pentium III processor, 256 MB RAM, 150 MB Hard drive and minimum 1024 x 768 monitor screen resolution. The operating system needs to be Windows 2000 or XP. TCP/IP needs to be enabled at operating system level prior to installation.

8. What datatypes does Log P.I.® support?

   Log P.I.® supports all datatypes except for BLOB type's text and image. A future release of Log P.I.® will sort BLOB types. The “text in row” option is currently supported.

9. How does the Log P.I.® licensing work?

   Log P.I.® is sold per instance of SQL Server™ that you wish to monitor.

10. Will Log P.I.® run on Windows 2000 operating system?

    Yes, Log P.I.® will run on all versions of Windows 2000, 2003 and XP.

11. How do I install the LogPI Agent Service?

    The LogPI Agent service needs to be installed by running the LogPIServercomponents.exe found on the CD or in the folder C:\Program Files\LogPI\Server Components\Install on the server that you wish to monitor. This will then install the LogPI Agent Service & a utility namely LogPI Server Management Utility which allows you to manage the LogPI Agent Service as well as install & manage the Log PI User Name Trace.

Server

1. How often is / are the server(s) polled?

   The default setting for server polling is 1 minute, the Polling interval goes from 1 to 60 minutes in increments of 1 minute. The polling interval may be adjusted according to user's requirements; these adjustments will then be saved to the registry.

2. Can I check the memory usage of my server?

   Yes, Memory of your physical server can be checked. Navigate to the memory tab on the Server information screen. This will then show various areas to which memory has been allocated. Based on the findings you can then can then decide whether or not the load has increased from the normal, and decide whether or not to make recommendations for a memory upgrade.

3. How do I determine the load on my server?

   The Server load can be checked by selecting the Load tab on the Server Information screen. Information will be broken down into Processes / Clients & SQL Batches / Transactions.

4. Can I compare Server statistics simultaneously?

   Yes, Log P.I.® enables you to view a specific servers' statistics and then immediately click on the next server and those statistics will be visible.

Database

1. Can I see how many transactions are currently active on a specific database?

   Yes, navigate to the database activity tab on the Server Information screen. The number of transactions per database is displayed in the grid.

Transaction Log

1. How many Logs can I interrogate at any one time?

   The number of Logs you wish to monitor is dictated by the number of server/s your machine has access to. Hence there is no limit on the number of Transaction Logs that can be interrogated.

2. How do I monitor a specific server's log?

   Firstly you need to decide what you would like to monitor. If you would like to get an overall idea as to the contents of the log, then navigate to the Transaction Log summary function. To view a specific SQL data modification statement, navigate to the Search / Browse log function. These functions are both available in the Tree under each specific server.

3. I have installed Log P.I.®, how do I attach to a Log?

   You do not need to attach to a Log. Log P.I.® automatically attaches to the Transaction Log of all servers registered or visible to your machine, as long as the Log P.I.® agent is installed on the server.

4. I cannot see any usernames in the Search / Browse Log results, what do I need to do?

   Please ensure that you installed the User Name Trace when you ran the LogPIServercomponents.exe and installed the LogPI Agent Service. The User Name Trace can be installed from the Server Management Utility by selecting the User Name Trace Configuration tab.

5. I cannot access or read my backed up logs, they are saved on another machine elsewhere on the network.

   In order to be able to read backed up logs saved elsewhere you need to go to the Server Management Utility and change how the user is logged on. Change “Log On As” from “Local System Account” to “Specified Account”. The Specified account needs to have rights to log onto the specified machine and be able to read the back up files.

Support

For technical support please use one of three options:

Web

Log onto www.logpi.com and navigate to Frequently Asked Questions. Questions here may assist you or chose one of the options listed below.

Telephone

Telephone (345) 945 0873

email

Send an email to support@logpi.com and a response will be forthcoming within 12 hours

Conclusions

For a product that was just released in February 2004, this product is extremely comprehensive and has many more features than other similar products such as ApexSQL Log (i.e., UPDATE shows Before and After Values without being forced to go into a different view - see Figure 29). 

This feature will be even more

comprehensive and in depth in the next release.

Figure 29

My four favorite features of this product are:

1. Undoing a transaction by copying the necessary TSQL
2. Retrieving data lost when a table is truncated and retrieving table schemas and dropped data from dropped tables
3. Recovering lost data without restoring a back up
4. Graphical representation of data (eleven options are available)

The only additional options I would like to see is:

1. Reading of backups from tape drives
2. Support of datatypes for BLOB (however, BLOB types saved with the 'text in row' is supported), text, and image (this may be a limitation of SQL in itself)
3. Recognize whether a newer version of .NET or MSDE has been previously installed during the initial installation and provide the option to uninstall .NET when the entire LogPI product is uninstalled
4. Export from the Search/Browse log results in XML format

Log P.I.® provided this roadmap for the future:

"Shortly we will have some new functionality, namely Record Audit Trail. This new functionality will improve the way in which we currently represent updates and will provide an audit trail for the history of a row.

Our roadmap of new functionality will be as follows:

• Record Audit Trail.
• More DDL commands such permission related DDL statements, stored procedures, triggers, functions – currently we show Creates & Drops of tables, keys and indexes.
• Alerts – on schema updates, permission changes etc.
• Improved functionality on performance statistics.
• Extend our existing Retrieve functionality."

Ratings

I will rate each of the following using a scale from 1 to 5. 5 being the best

and 1 being the worst. Comments are in the last column.

Product Information

Web Site: http://www.logpi.com

Developer: Coherent Software Corp
Pricing:

Log P.I.® ® pricing is based per named instance of Microsoft SQL Server® that you wish to monitor.

• Base Price (1 SQL Instance) ===> $750.00
• Price Per SQL Instance Thereafter (2 - 15) ===> $600.00
• Corporate License (16 -50 SQL Instances)

  ===> $9,150.00

• Site License (Unlimited SQL Instances) ===> Please call

Maintenance is available at 15% per annum. Renewal of the maintenance contract is calculated by the current prevailing price of the number of licenses purchased on the anniversary date of the original purchase.

Maintenance Contract includes the following:

• Log P.I.® Product Support
• Updates
• Enhancements

Note:

All members of SQLPASS Program receive 20% discount.

The number of client machines has no impact on the licensing.

Example:

I have 7 instances of SQL Server® I would like to monitor. What do I pay?

You pay $5002.50 Including Maintenance Contract

Calculated as follows:

For a resellers list, go here => http://www.logpi.com/index.htm?lpi_pricing.htm

Should you require a detailed quotation, please contact us via e-mail at sales@logpi.com or contact a sales representative on (345) 945 0873 during office hours (07h00 - 16h00 (EST)