Migrating Audit Data to a New Database

Over time, audit data builds up in the database causing performance issues. This article shows you how to migrate old audit data to a new database, so that you can free up valuable resources and speed up performance.

Prerequisites

Before you begin, please ensure you do the following to ensure a smooth move,

Go to your RecordPoint Site and navigate to Management > Settings > Storage Settings. Note down the Site URLs and Database Names of the Storage Site Locations for future reference.
Ensure you have a new database set up in your SQL Server Management Studio (or similar software) to move the Audit Data into. You do not have to create a new table, as it will be automatically created during the move.

Migration Steps To Move Old Audit Data to a New Database

Copy the AuditData table from the storage site database (the one you noted down previously) into a new database by running the following SQL query. Note that this query will take several minutes to complete depending on the size of your database.

            SELECT * INTO NewDatabase.dbo.AuditDataTable 
            FROM StorageSiteDatabase.dbo.AuditData

Verify if the data was successfully copied by doing the following,

a) Run the following SQL queries and verify that the number of rows are the same in both tables

            SELECT COUNT(*) FROM StorageSiteDatabase.dbo.AuditData           
            SELECT COUNT(*) FROM NewDatabase.dbo.AuditDataTable

b) Verify that the rows contain exactly the same information by running the following SQL query, and ensuring that the result is empty.

            SELECT * FROM StorageSiteDatabase.dbo.AuditData
            EXCEPT
            SELECT * FROM NewDatabase.dbo.AuditDataTable

Delete the audit data inside the storage sites by running the following PowerShell script for each Storage Site's URL. Note that using this script automatically creates a new record in your AuditData table to record the fact that data was deleted. This is the recommended way to purge data as directly deleting data from the database table will not record the deletion event.

Note: On the off chance that the script fails due to a "DeleteEntries" exception, please create an Index named "AuditData_OnSiteOccurred" for the AuditData table in your Storage Site Database like so,