Using ApexSQL Log when local installation and remote installation of server-side components isn’t possible (4304025)

Using ApexSQL Log when local installation and remote installation of server-side components isn’t possible

Applies to ApexSQL Log

Summary This article explains how to use ApexSQL Log even when installation of ApexSQL Log and/or server-side components is not possible/allowed on the server machine due to performance, security, or any other reasons. Description ApexSQL Log is a tool that can audit transaction log files on both local and remote servers. While auditing on local servers doesn’t require ApexSQL Log server-side components, auditing databases on a remote server requires that the user installs them on the server that hosts SQL Servers that will be audited. Prior to 2016 version, ApexSQL Log server-side components installed 2 stored procedures on directly on the SQL Server, but this is no longer the case. The only remaining server-side component is a service installed on the system that hosts SQL Server which enables remote reading of online transaction log file – nothing is installed on the SQL Server itself. Nevertheless, due to various reasons, policies, etc, installation of ApexSQL Log server-side components is not always an acceptable solution. Let’s assume that there is a SQL server that we need to audit, but we cannot install ApexSQL Log on it locally. In addition, the ApexSQL Log server-side components required for remote auditing also cannot be installed on the system. Using a “bridge” server As an alternative plan, we can leverage another, different server as a “bridge”, to audit our target server. For this to work, there are several conditions that need to be fulfilled. First of all, the database in question has to be in full recovery mode, and a full transaction log chain must be maintained. Also, a full backup and transaction log backup(s) must be created. Why are transaction log backups essential? The answer to this question lies in the core of ApexSQL Log which is not a database reader but transaction log reader. Live database and database backups are snapshots of the current database state and because of this they are important part of the process. But, they do not contain information regarding the change history – the transaction log stores this data, and is hence irreplaceable in the process. Here are the steps that need to be executed:

1. Create a transaction log backup to ensure that the latest changes made on the SQL server are available
2. Copy/Move existing full database backup and all transaction log backups to the alternative SQL server and perform native restoration of a database backup
3. Install ApexSQL Log on the alternative machine (and activate it) and connect to the restored database in the ApexSQL Log session wizard

4. In the ‘Select SQL logs to analyze’ step of the wizard uncheck online transaction log and check (add) original transaction log backup(s) you’ve copied from the Target server

   

5. Complete the session wizard by adding any applicable filters.
6. Apply the exported results to the Target server

Taking database offline Another option is to take the database offline in order to use ApexSQL Log to perform auditing on a different server. To achieve this, the following steps need to be executed:

1. Detach a database from the Target server – this will take the database offline and enable direct manipulation over database files
2. Copy .mdf,  and .ldf (and all other database files, such as .ndf and filestream) files to the system hosting the alternative server
3. Attach database to the bridge server
4. Perform auditing with ApexSQL Log
5. Detach the database from the bridge server
6. Copy .mdf and .ldf files back to the Target server
7. Reattach database to the Target server