How to configure SQL auditing data central repository automatic backups (4262848)

Although ApexSQL Audit provides high versatility for SQL auditing, it lacks internal functionalities for data repository redundancy or disaster recovery. There is the capability to archive the central repository but that would require further manual manipulation to backup those archives and create some disaster recovery plan. Inheritably, the active repository remains unattended. For disaster recovery, the application will have to rely on the native SQL Server backup method.

The problem starts with the idea that scheduled backups should be focused on transaction logs, with the advantage to enable restoration at a point in time and, naturally, prevent multiplying database backups. This requires that the repository database is set with the Full recovery model and it is always created with the Simple model by the SQL auditing application. Using the Simple model has the advantage to minimize the transaction logs and preserve storage space (all operations against the repository database are already logged in it) and, as consequence, transaction logs would be useless for backups.

In this article we will explain how to resolve the SQL audit data repository database recovery model, set it to Full automatically, and set scheduled backups.

Requirements:

• SQL Server Agent active on the SQL Server where the central repository is hosted

SQL audit data central repository creation

The central repository database (named “ApexSQLCrd”) is initially automatically created during the ApexSQL Audit main application installation. As mentioned in the introduction, it is created with the Simple recovery model. If we assume that we can simply change the database after that to the Full recovery model and continue with database disaster recovery solution creation based on incremental transaction log backups, it will not be possible. The application has the built-in archiving scheduler which will trigger archiving repository data based on lapsed period or database size limit:

More information about the central repository database archiving can be found in the Archiving the Central repository database – ApexSQL Audit article.

This process will rename the database, keep it as the data source, and in the end, initiate a new, blank database with the original name (“ApexSQLCrd”). After this process, the created disaster recovery solution will stop working as the new database will again be created with the Simple recovery model.

Setting the Full recovery model

Since the central repository database is always created with the Simple recovery model, we would need to automate the switching upon database creation. We can easily achieve that using the SQL Agent with a SQL Job and the alert mechanism that comes with the agent.

Open SSMS and from the Object Explorer find the Jobs node under the SQL Server Agent node. Right-click on the Jobs node and from the context menu choose the New Job… option to invoke the New Job window. In the General tab, enter the recognizable job name, like “Convert DB” seen in the screenshot, and choose the account as the job owner:

The account will need to have enough privileges to perform database properties changes, so it is safe to use the built-in “sa” account.

Switch to the Steps tab and start with the new step creation:

In the General tab of the New Job Step window, set the following parameters:

• Enter the step name Go to the Steps tab and add a new step
• Choose the Transact SQL Script (T-SQL) action type (it is selected by default)
• In the command field insert the following command: exec sp_msforeachdb ‘alter database ApexSQLCrd set recovery full’ (the command will convert the “ApexSQLCrd” database to Full model)
• Confirm with OK

The rest of the parameters can be left as they are, which includes the parameters in the Advanced tab also.

To enable the transaction log backup, it is necessary to create an initial, full database backup. This can also be accomplished within the same job. So, with the previous step configured, the view will return to the New Job window and from there, we need to add another step. The step parameters are:

• Enter the step name
• Choose the Transact SQL Script (T-SQL) action type again

• In the Command field enter the following command:

  BACKUP DATABASE [ApexSQLCrd] TO DISK = N'C:\ApexSQLCrd.bak'
        WITH NOFORMAT,NOINIT,NAME = N'ApexSQLCrd-Full Database Backup',SKIP,NOREWIND,NOUNLOAD,STATS = 10
        GO

  This is a native SQL Server backup command which only requires additional customization based on the backup location

• Confirm with OK

  

Created steps will be listed in the Job step list for review and the job configuration can be confirmed with OK:

Creating the SQL Server Agent alert

We created the job and defined what should be executed, now it is necessary to automate that execution on database creation and this is done through the SQL Server Agent alerts.

From the Object Explorer find the Alerts node, right-click on it and choose the New Alert… option to invoke the New Alert configuration window:

In the General tab, set the following parameters:

• Alert name (e.g. Database created)
• For the Database name, choose the “master” database (this targets the monitoring activities on the “master” database which is used during database creation)
• The Severity field should be set to 010 – Information
• The option Raise alert when message contains should be enabled and
• The Message text field should be populated with the text: Starting up database ‘ApexSQLCrd (Note that the message does not end with an apostrophe as the rest of the name is dynamically created, with a timestamp)

The alert will be triggered when the information type message is written in the SQL Server log with the text that matches the Message text criteria, and when a database is created the message will contain that string that was entered:

What is left for the alert configuration is to switch to the Response tab, tick the Execute job option and from the dropdown list, pick the name of the job that was created in the previous step:

Optionally, notification on alert can be enabled to send an email containing the message that a new SQL audit data repository database is created, but that option is also available from within the ApexSQL Audit:

More information about notification email configuration can be found in the How to create a custom alert with an email notification article.

Automating transaction log backups

To finish with this SQL audit database disaster recovery concept, we just need to set the scheduled backup job. This is also configured through the SQL Server Agent.

Let’s create another job and name it, for example, Transaction log backup:

We should create one step with the Transact SQL Script (T-SQL) action type for this job to execute the following command:

BACKUP LOG [ApexSQLCrd] TO DISK = N'C:\ApexSQLCrd.bak'
  WITH NOFORMAT,NOINIT,NAME = N'ApexSQLCrd-Full Database Backup',SKIP,NOREWIND,NOUNLOAD,STATS = 10
  GO

Make sure that the target folder is the same as the initial, the full backup folder:

When the job step settings are confirmed and the view returns to the New Job window, switch to the Schedules tab and add New schedule:

Within the New Job Schedule configuration window, we just need to name the schedule item and customize the frequency for the backup execution, for example, to execute the backup every day at midnight:

The confirmed configuration will return to the New Job configuration window and the schedule item will be listed there:

Conclusion

The presented concept can be used as a solution to ensure SQL audit data recovery in case of some unexpected SQL Server failure and data corruption. The solution provides an optimal method for SQL data recovery with the possibility to restore data to a specific point in time.