ApexSQL Audit – Migrate the central repository database

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

Migration scenarios

There are a number of scenarios when 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:

Archiving

The first step is to create an archive of the ApexSQL Audit central repository database as described in the Archiving the central repository database knowledgebase article. The archive database uses the ArchiveCrd_yyyymmdd_hhmmss database name pattern (e.g. ArchiveCrd_20140522_171318). Create a backup of the archive database for later use on the target server /SQL Server instance (e.g. ArchiveCrd_20140522_171318.bak).

Installation

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 (e.g. PRODUCTION_SERVER_NEW), restore the archive backup on the SQL Server instance that you specified as the main application host during the installation.

Re-configuring auditing instances

After the installation of the main application, it’s required to re-configure ApexSQL Audit auditing instances installed on remote servers. An auditing instance reads the connection configuration for the main application and central repository database stored in a Windows registry key. To set the new configuration:

  1. Open Windows Registry
  2. Expand the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ApexSQLAudit key
  3. Double click the SystemConfig value and copy the text from Value data to any text editor (e.g. Notepad)

    Re-configuring ApexSQL Audit auditing instances via Windows Registry

  4. The text contains information about the connection configuration used in an XML form

    <?xml version="1.0" encoding="utf-16"?>
    <SystemConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <CentralHostName>PRODUCTION_SERVER</CentralHostName>
      <CentralPort>64561</CentralPort>
      <Folder>C:\ProgramData\ApexSQL\ApexSQLAudit</Folder>
      <Version>2014.2.1234.0</Version>
    </SystemConfig>
    

    The following information enclosed within appropriate tags must be changed according to the newly installed main application settings:

    – <centralhostname> holds the name of the server where the main application is installed with the central repository database. Change the current value to the corresponding one (e.g. PRODUCTION_SERVER to PRODUCTION_SERVER_NEW)

    – <centralport> holds the port number used by the main application for communication with auditing instances. The default is 64561 and it’s set during the installation of the main application.

  5. Restart the ApexSQL Audit service using the Windows Services applet
  6. Additionally, repeat the archiving procedure on the PRODUCTION_SERVER in order to collect the audited information stored after the first archiving we described. So besides the ArchiveCrd_20140522_171318.bak database backup, you should have an additional one too (e.g. ArchiveCrd_20140522_180000.bak)

Restoring archived audited information

After the PRODUCTION_SERVER_NEW is installed and auditing instances re-configured to send captured information to it, you can shut down the PRODUCTION_SERVER server. There are two archives created and backed up during the migration process (ArchiveCrd_20140522_171318.bak and ArchiveCrd_20140522_180000.bak). Restore the backups to the SQL Server instance on PRODUCTION_SERVER_NEW which hosts the central repository database in order to make them available for the ApexSQL Audit Web reports.