ApexSQL Audit Central Repository Database design

All configuration and audited records from all audited SQL Server instances are stored in a centralized auditing repository database called ApexSQLCrd. The tables in the ApexSQLCrd database are


ApexSQL.Accounts#Const, ApexSQL.Accounts#Var – Holds the information on user accessibility, the definition of access level for users defined in Options | Manage accounts tab
ApexSQL.AlertReports – stores the information about alerts that happened.
ApexSQL.ApplicationName – a look-up SQL table, stores names of the applications used to make audited events.
ApexSQL.ArchiveHistory#Const, ApexSQL.ArchiveHistory#Var – Tables that store historical information about archiving of central repository database
ApexSQL.ArchiveSchedule#Const, ApexSQL.ArchiveSchedule#Var – Store information on schedule archiving settings
ApexSQL.BeforeAfterChange, ApexSQL.BeforeAfterEvent, ApexSQL.ColumnName – Tables that keep before and after value changes of audited records using Before-After auditing
ApexSQL.CentralRepository#Const, ApexSQL.CentralRepository#var – stores information about the central repository database (a name, version…). Some of the tables are split between variables and constants (the suffixes #var and #const are added). We use this design, to logically update a table, while rows are physically inserted.
ApexSQL.ClientHostName – a look-up SQL table, stores names for the computers which generated audited events.
ApexSQL.ComplyInstance#Const, ApexSQL.ComplyInstance#Var– stores information about ApexSQL Audit instances (SQL Server location).
ApexSQL.DatabaseName – a look-up SQL table, stores audited SQL databases names.
ApexSQL.DataSource#Const, ApexSQL.DataSource#Var – stores information about data sources used in reports, set in the Options web report tab.
ApexSQL.EncryptionKey – for internal usage.
ApexSQL.Event – Stores data and metadata of audited events
ApexSQL.LoginName – a look-up SQL table, stores names for the logins used to generate events.
ApexSQL.ObjectName – a look-up SQL table, stores audited object names.
ApexSQL.Package#Const, ApexSQL.Package#Var– stores information about packages that are deployed to ApexSQL Audit central instance (these packages contain a list of events bulk inserted per package).
ApexSQL.ReportDefinition#Const, ApexSQL.ReportDefinition#Var – stores custom report definitions
ApexSQL.ScheduleDefinition#Const, ApexSQL.ScheduleDefinition#Var – tables that store report schedule configuration
ApexSQL.ScheduledReportsHistory#Const, ApexSQL.ScheduledReportsHistory#Var – store historical and status information of the schedule reports
ApexSQL.SchemaName – a look-up SQL table, stores audited schema names.
ApexSQL.SensitiveColumnEvent, ApexSQL.SensitiveColumnSet – A dedicated table for keeping audit records of sensitive data auditing
ApexSQL.ServerName– a look-up SQL table, stores audited SQL Server instance names.
ApexSQL.SystemEvent – stores internal system events
ApexSQL.SystemSettings#Const, ApexSQL.SystemSettings#Var – for internal usage.
ApexSQL.UserOptions#Const, ApexSQL.UserOptions#Var – stores user options

For example, the table ApexSQL.DatabaseName looks like this:

Table ApexSQL.DatabaseName

There are fewer views in ApexSQLCrd, most of them are used to help SQL engine prepare data sets while managing ApexSQL Audit features, here is a quick introduction to it:

ApexSQL.AccessedColumnsProxyView, ApexSQL.AccessedColumnsProxyView – displays access sensitive columns from data source, central repository and archive databases
ApexSQL.ApplicationNameProxyView – provides list of application names from the metadata of audit trail records
ApexSQL.BeforeAfterChangeProxyView, ApexSQL.BeforeAfterChangeProxyMatrixView, ApexSQL.BeforeAfterChangeView, ApexSQL.BeforeAfterChangeMatrixView – show the before-after changes from the data source, central repository and archive databases included
ApexSQL.ClientHostNameProxyView – Display list of client hosts from the metadata of audit trail records
ApexSQL.ColumnNameView, ApexSQL.ColumnNameProxyView – These views are used to merge data across multiple tables and databases when displaying before-after auditing events from data source, including central repository and archive databases
ApexSQL.DatabaseNameProxyView – Shows list of audited databases based on metadata of audit trail records
ApexSQL.EventView, ApexSQL.EventProxyView – shows all captured records for audited events
ApexSQL.LoginNameProxyView – Displays list of audited logins based on metadata of audit trail records
ApexSQL.ObjectNameProxyView – Shows list of audited database objects based on metadata of audit trail records
ApexSQL.SchemaNameProxyView – Displays list of audited schemas based on metadata of audit trail records
ApexSQL.SensitiveColumnNameView, ApexSQL.SensitiveColumnNameProxyView – Provides quick access to sensitive audit records from audit trail
ApexSQL.ServerNameProxyView – Shows list of audited SQL Instances based on metadata of audit trail records