ApexSQL Audit – Migrate the central repository database

Description

This article describes how the ApexSQL Audit central repository database can be migrated to another SQL Server instance or host.

Migration scenarios

There are a number of scenarios when the migration of the central repository database used for storing audit information may be required. The most common scenario is that the server or SQL Server instance that hosts the repository requires maintenance (hardware or software upgrade), or is about to be replaced.

In such cases, it’s required to migrate the central repository database to a new (target) server/SQL Server instance and re-configure existing auditing instances, using the following migration checklist and action steps:

Export custom configurations for reuse

The migration process demands on reconfiguring the custom specifications and definitions, and ApexSQL Audit provides the ability to export and import following:

  • Auditing configuration

    1. Go to configure tab
    2. Highlight SQL instance or database level configuration
    3. Select Export from Templates drop-down

    Export auditing configuration

  • Report definitions

    1. Go to Reports tab
    2. Highlight the custom report
    3. Choose Export from header control buttons

    Export report definistions

  • Report schedule and alerts – similarly, both schedule and alert definitions can be exported

    Report schedule and alert definitions export

Now, the custom configurations can be easily replayed on the new central repository database by importing it.

Installation

Once the configuration is extracted from the central repository database, the next step is to create a new central repository database on a targeted SQL instance and host (e.g. ApexSQL_Central_repository_new)

Using the steps described in the Installing ApexSQL Audit knowledgebase article, install ApexSQL Audit on the target server/SQL Server instance. Once the GUI and central repository database are installed on the target server we can proceed further with reconfiguring auditing and update our system data to use a new database for storing audit data trail.

Prepare for data migration

Before we update system data to route the audit trail into a new database, we should consider data migration and prepare for it. First, let us archive the old central repository database in use as described in the Archiving the central repository database knowledgebase article.

Re-configuring auditing instances

After the installation of the new server, re-configure ApexSQL Audit auditing instances by adding them and importing the configuration. Here is a step-by-step scenario:

  1. Run ApexSQL Audit GUI and choose Configure tab
  2. Choose Add server and type SQL Server instance name or IP address of an instance you were auditing
  3. The auditing agent properties dialog will be populate the previously used data, so it is important to make sure new central repository host is updated here and click OK

Auditing agent properties

Afterwards, the instance is listed in server tree of the Configure tab, add auditing instances and database, and import custom configurations

Migrating archived audited information

Once the new central repository database is live and in use, you can migrate audited data that was being collected in the past from the old SQL Server instance to a new central repository instance for reporting purposes.

The archived databases use the ArchiveCrd_yyyymmdd_hhmmss database name pattern (e.g. ArchiveCrd_20200522_171318). To migrate the databases, create a backup of all archive databases and restore the backups to the new SQL Server that is being used to host the central repository database.

After the archive databases are up and running on a new instance, go to Options -> Reporting -> Add. Repeat database addition for each archive database that is restored on a new SQL Server instance

Adding new archive databases to data source

Data and files clean-up

On the old central repository host, there might be some left-overs that can be removed. After the product is uninstalled on the old repository host, you can consider keeping or removing data and files as it is detailed in this How to perform full application files cleanup after uninstalling ApexSQL Audit article.