In this article:  

• Objective

• Prerequisites

• Steps to follow

• Validation and confirmation

• Tips

• Related topics and articles

Objective  

This article explains how to migrate the ADAudit Plus database from the built-in PostgreSQL or MySQL database to Microsoft SQL Server without losing existing configurations or audit data. This is helpful when looking for better database performance, centralized database management, or alignment with enterprise SQL standards.

Prerequisites  

• A supported version of SQL Server:

• SQL Server 2008 R2 (EOL)

• SQL Server 2012, 2014, 2016, 2017, 2019, and above

• Windows Server with SQL Server installed

• Local administrator rights on the ADAudit Plus server

• A SQL Server user account with sysadmin privileges (or equivalent roles mentioned below)

• SQL Server Native Client and ODBC Driver for SQL Server (if SQL Server is on a remote server)

• Required firewall ports (UDP 1434 and TCP/UDP port 1433 configured in SQL Server)

• SQL Server Browser and TCP/IP enabled on SQL Server

Steps to follow

Step 1: Prepare SQL Server  

1. Open SQL Server Configuration Manager on the computer running the SQL Server instance (to which the database and/or data is to be moved).

2. On the left pane, click SQL Server Services and ensure that the SQL Server Browser is Running.

3. On the left pane, click SQL Server Network Configuration > Protocols for <the given instance> and enable TCP/IP.
   Note: The given instance refers to the SQL Server instance to which the database and/or data is to be moved.

4. On the left pane, click SQL Native Client <version> Configuration > Client Protocols and enable TCP/IP.
   Note: <version> corresponds to the specific client version you need to configure.

5. Restart the SQL Server service.

Step 2: Assign permissions in SQL   Server

To grant access:

1. Log in to Microsoft SQL Server Management Studio with an account that has been assigned the sysadmin role.

2. Select the server instance to which you will be migrating and navigate to Security > Logins.

3. Check whether the user running ADAudit Plus is on the list.

• If the user is not listed, right-click Logins, click New Login, create a new login, then proceed to the next step.
  

To grant permissions:

1. Right-click the user and click Properties > Server Roles.

2. Check whether the user has been assigned the sysadmin role.

• If the user has, then proceed to the next step.

• If the user has no permissions, select the sysadmin check box, click OK, then proceed to the next step.

To grant the user minimum privileges for the database instead of the sysadmin role, please follow the steps listed below:

1. Right-click the user, click Properties > User Mapping, select the db_datareader, db_datawriter, and db_ddladmin check boxes, and click OK.

2. Right-click the database, click Properties > Permissions, provide Execute permission for the user, and click OK.

3. Right-click the database, click New Query and execute the following query in the database:

GRANT CONTROL ON CERTIFICATE::[ZOHO_CERT] TO <newly_created_user>

Step 3: Install the required SQL Server drivers (if on a remote server)  

1. If ADAudit Plus and SQL Server are running on different servers, download and install SQL Server Native Client, command utilities, and ODBC Driver for SQL Server on the server where ADAudit Plus is installed.

2. Click the relevant link below to download the required utilities and drivers:
   

3. Copy the following two files from the SQL Server installation folder and paste them in the <Installation directory>ADAudit Plus\bin folder:

• bcp.exe- <MSSQL Installation Dir>\Tools\Binn\bcp.exe

• bcp.rll- <MSSQL Installation Dir>\Tools\Binn\Resources\1033\bcp.rll

Step 4: Open firewall ports  

1. Ensure that UDP port 1434 and TCP port 1433 are  open in the firewall settings.

2. To identify the TCP port number, open SQL Server Configuration Manager on the computer hosting the SQL Server instance where the database or data is to be moved:

1. Navigate to SQL Server Network Configuration > Protocols for <the given instance>.

2. Right-click TCP/IP, click Properties > IP Addresses > IPAll, and note the TCP Port number.

Step 5: Stop ADAudit Plus  

1. Open services.msc on the ADAudit Plus server, search for the ManageEngine ADAudit Plus service, and stop it.

2. Ensure both the ManageEngine ADAudit Plus and ManageEngine ADAudit Plus - DataEngine XNode service are stopped.

Step 6: Run the database configuration wizard  

1. Open Command Prompt as an administrator with the account that has permission to create and write the SQL Server database.

2. Navigate to the <Installation directory>ADAudit Plus\bin folder in Command Prompt.

3. Execute the ChangeDB.bat batch file and wait for the migration wizard to show up.

4. In the wizard:

1. Select MSSQL Server as the Server Type.

2. Enter the hostname and instance name.

3. Enter the new database name.

4. Choose whether to migrate data.

5. Choose the authentication type: Windows Authentication or SQL Server Authentication.

6. Click Test Connection. Once the connection is established successfully, click Save.

Step 7: Convert archived files

The data in archived files is stored in different formats across databases. After migrating from one database to another, the format of the existing archived files has to be converted for them to be compatible with the new database. To convert the format of the archived files, follow the steps below:

1. Navigate to <Installation_Folder>\ManageEngine\ADAudit Plus\archive and create a new folder with a suitable name (e.g., tableBackup_new). 

2. Open Command Prompt as an administrator, navigate to <Installation_Directory>\ManageEngine\ADAudit Plus\bin, and execute the following command:

ChangeArchive2BCPSupport "<Archive_Directory>" "<New_Directory>" <Old_Backend_DB> <New_Backend_DB>

1. Replace <Archive_Directory> with the path to the original folder that contains the archived files. The default name of this folder is tableBackup, and it can be found at <Installation_Folder>\ManageEngine\ADAudit Plus\archive.

2. Replace <New_Directory> with the path to the new folder that you just created above (tableBackup_new).

3. Replace <Old_Backend_DB> and <New_Backend_DB> with postgres or mysql or mssql depending on which database you are migrating from and which database you are migrating to.

3. Once the command executes successfully, new files with a format that is compatible with the SQL Server database will be created in the new folder (tableBackup_new). Move the archived files from the original folder (tableBackup) to a secure location for backup.

4. Copy the new files from the new folder (tableBackup_new) and paste them in the original folder (tableBackup).

Validation and confirmation  

1. Start ADAudit Plus.

2. Log in via the web console.

3. Navigate to Support > Support Info > More > Machine Details > Database to view the new database details.

4. Review the audit reports and real-time data to ensure everything is functioning correctly.

Tips

• Ensure that the ADAudit Plus service is completely stopped during the migration.

• Always back up the ADAudit Plus installation and database before starting.

• Perform the migration during non-peak hours.

• Monitor database and system performance post-migration.

• Keep SQL Server up to date with the latest security patches.

Related topics and articles  

• ADAudit Plus database migration guide