ApexSQL Log is a SQL Server database transaction log reader which allows users insight into SQL Server database transaction log files and backup. ApexSQL Log can be used both locally or remotely in order to perform auditing and recovery jobs. Users can audit database changes and present in a comprehensive grid, where they can be analyzed and inspected in great detail, including who made the change and when, as well as the before-after change values and full history of affected rows regardless of the auditing method (local or remote).
In addition to its auditing capabilities, ApexSQL Log allows users to roll-back or roll-forward any changes which have been audited in SQL Server database transaction log files or backups, including online transaction log, detached transaction logs or transaction log backups. With this, ApexSQL Log can be used to undo (or redo) any changes which have occurred on the database and revert affected data to its original state, as if the changes have never occurred.
In case of local auditing, ApexSQL Log is installed directly on the machine which hosts SQL Server, hence SQL Server instances are accessed locally. In case of remote auditing, ApexSQL Log is installed on another workstation and SQL Server instances are accessed remotely. In remote auditing, server side components (a simple windows service which allows remote auditing) needs to be installed on the machine which hosts SQL Server which will be audited, yet nothing is installed on the SQL Server itself – detailed information on server side components can be found here.
Deciding on using ApexSQL Log locally or remotely greatly differs between users and is based on their environments and company policies, and that’s why there is no universal option. In order to decide on the “better” solution for their particular case/environments, users need to understand all pros/cons of local and remote auditing.
In general, local auditing is recommended whenever it is possible or suitable. It offers better performance since the network traffic will not be a decisive performance factor, and allows users to perform ApexSQL Log jobs from a centralized position. Local auditing is recommended in cases when working on the server machine is available, or in cases when there are one or more SQL Server instances present on the same machine.
Best performance – online transaction log file is accessed locally, hence there is no network performance limitations or traffic dependencies. If any transaction log backups or detached .ldf files are included in the auditing job, they should be copied over when possible and read locally to ensure the best possible performance of both auditing and recovery jobs
Simple solution - only the main application needs to be installed on the machine which hosts SQL Server, no additional components are required at any point
Does not suffer from network downtimes
Since application is installed directly on the server machine which hosts SQL Server that will be audited, user must access the production server directly in order to use the application or schedule a job which is not always possible or the best solution
If a demand to audit SQL Servers located on different machines exists, ApexSQL Log needs to be installed and each server in full which means for each machine the user would have to have an ApexSQL Log client which is accessed ‘on the spot’
As mentioned above, remote auditing requires installation of server-side components on remote machines which host SQL Servers that need to be audited, and central application is installed on one (or more) workstations of user’s choice to remotely access those SQL Server instances. Performance-wise, it is slower than local auditing since it suffers from network traffic and environment limitations which can slow down processing. Nevertheless, remote auditing is most preferred and recommended in cases when there is a limitation in access to the server machine which hosts SQL Server instances, and it cannot be accessed on a regular basis to use ApexSQL client on it, or when there is a requirement to audit many SQL Server instances on multiple server machines.
Centralized console – Only one ApexSQL Log client is used to manage all remotely (and locally) audited SQL Servers – this is very helpful when auditing more remote machines, especially in comparison to the local auditing solution of one machine = one client
Onetime installation of server-side components on all remote machines enables auditing of all SQL Server instances on those machines and allows users to access them from one or more workstations or even to transfer the ApexSQL Log client from one machine to other if the need requires it
May suffer performance impact due to network congestion or limitations
Installation on both workstation (client) and each remote server (server-side components) is required
In addition to local and remote auditing, there is an alternate solution. Since ApexSQL Log requires either main application or server side components to be installed on the machine which hosts SQL Servers to be audited, there are some cases when none of these solutions are applicable to the environment due to company policies or requirements.
If this is the case, users can use a ‘bridge’ server located on the machine on which ApexSQL Log can be installed, which has access to different SQL Server on the same or other machine where application or server-side components can be applied. More on how to use a bridge server for auditing and recovery, as well as the complete guide on how to set is up can be found in this article.
User doesn’t need to install anything on a production server and can perform auditing or recovery anywhere they whish
Requires a lot of backup/copy jobs and disk space to hold those in order to fully transfer or create an identical copy of production database
If the database is transferred/moved, it needs to be taken offline creating a downtime period on the production server
Q: Can I combine local and remote auditing?
A: Yes, some servers can be audited locally while others are accessed remotely in conjunction as per user’s preference.
Q: Can multiple ApexSQL Log clients be used to audit the same SQL Server instance?
A: Yes, there is no limit to the number of main consoles (client’s) which can be used to audited one SQL Server instance
Q: Are the any differences in features when working with ApexSQL Log locally or remotely?
A: No, all ApexSQL Log features are fully supported and available in both local and remote auditing without any limits
