Support
 
Phone Live Chat
 
Support
 
US: +1 888 720 9500
US: +1 800 443 6694
Intl: +1 925 924 9500
Aus: +1 800 631 268
UK: 0800 028 6590
CN: +86 400 660 8680

Migration

Steps for DB & Data - Migration / Move

ADAudit Plus allows an administrator to audit an Windows Server Environment from a choice of database formats: MySQL / MS SQL / PostgreSQL. Below are the methods and procedures to configure and backup / move data for data migration.

   
Product Installation Move to Another Server / Drive MySQL / PostgreSQL DB to MSSQL DB

Move from one DB to another

  MySQL MS SQL PostgreSQL
MySQL N/A Yes Yes
MS SQL Yes No** Yes
PostgreSQL Yes Yes N/A

** MS SQL -> MS SQL - pertains to MS SQL version changes.

 

Move DB to another Drive / Computer

Backend Database is MySQL / PostgreSQL:

We recommend you to move the full Installation folder (Not the DB data folder alone). Steps to move the installation folder to another drive or computer.

Backend Database is MS SQL:

Steps to move the DB data to another drive. (Detach & Attach)

 

MS SQL Version Change

MS SQL server has in-built methods to migrate data from one version to another. (For Ex: SQL Server 2008 R2 to SQL Server 2012).

 

MS SQL DB Migration using Detach & Attach Methods

Recommended for: Full DB Backup | Move DB data to another drive | Move DB data from one SQL Server to another SQL Server with different version like (Ex: SQL Server 2008 R2 to SQL Server 2012).

  • Login to Microsoft SQL Server Management Studio with proper privileges.
  • Go to Databases and right click your DB (Ex: adap), then Task --> Detatch.
  • In Detach DB Wizard: Check both the Check boxes (Drop Connections and Update Statistics), Click Ok.
  • Go to DB storage path (Ex: By default 64 bit, C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA).
  • Cut / Copy the .mdf and .LDF file (Ex: adap.mdf and adap_log.LDF).
  • Save it in the desired new location.
  • Now go to Microsoft SQL Server Management Studio.
  • Right click the Databases --> Attach
  • In Attach Databases wizard, click add button under Database.
  • Now select the corresponding .mdf file where you have stored (Ex. adap.mdf), it will automatically take the .LDF file too.
  • Click Ok (Your DB is now connected from the new location).
 

MS SQL DB Migration using Import Data Method

Recommended for: (Within Local or Remote Server Environment): Full DB data migration from one DB to Another. | Migrate Particular Table Details from one DB to another DB.

    • Right click the destination DB. (Ex: adap_new)
    • Select Tasks then 'Import Data'. Now you will see the SQL Server Import and Export Wizard.
    • Within, Choose a Data Source Wizard; Select the following:
      • Source Server Name
      • Authentication previously used
      • Database, Then Click Next
    • Within, Choose a Destination Wizard, Choose the following:
      • Server Name (Automatically detected, You could change the destination, if you want)
      • Authentication
      • Database (Automatically detected, You could change the destination, if you want), Then Click Next.
    • In Specify Table Copy or Query:
      • Choose 'Copy data from one or more tables / views' Then Click Next.
    • In Select Source Tables and Views:
      • Choose the tables to be copied from source, select the tables from left column , Using 'Edit mappings' , select particular table operations, then Click Next.
    • Check 'Run Immediately', then click Next.
    • Click Finish.
    • The operation wizard & execution process will complete. Click Close.

Now, You are using the New Database / Database Datas.

 

MS SQL DB Migration Using Generate Scripts Method

Recommended for: Taking a particular table backup.

  • Go to SSMS Wizard.
  • Right Click the DB name (Ex: adap)
  • Go to Tasks --> Generate Scripts. Now you will see Generate & Publish Scripts
  • Click Next, you will view the Choose Objects Wizard.
    • Select DataBase objects to script - Select 'Script Entire database and all database objects' and then Click Next.
  • In Set Scripting Options:
    • Select 'Save scripts to a specific location' in Output Type.
    • Select 'Single file per object' in 'Save to file',
    • Then click on Advanced Options, to enable the following:
      • Include If NOT EXISTS - True
      • Script DROP & CREATE - Script DROP & CREATE
      • Script Use DataBase - True (If you set true, your new database name must be same with old)
      • Types of data to script - Schema and Data
    • Select destination 'Directory Name'.
    • Then click Next, you will see Summary Wizard.
  • Click Next.
  • Now you will see the '.sql files' for every table within the database for the selected directory.
  • Click Finish.
  • If you need any Data to be migrated, mention the respective SQL files.
 

MS SQL DB Data/Tables Using bcp.exe Method:(Bulk Copy Program)

Recommended for: Taking backup of SQL DB tables.

Ex: bcp <DB_NAME>.dbo.<TABLENAME> out "<STORAGE_PATH>\<TABLENAME>.dat" -n -T -S "<SERVERNAME>\INSTANCE_NAME"

If you are using the local Server, you need not mention: -S "<SERVERHOME>\INSTANCE_NAME"

Restore Above Backup Using BULK INSERT.

  • Go to SSMS Wizard.
  • Right click the DataBase Name , then select 'Add New Query'.
  • Execute the following command. Before Executing the table must be there in DB.
    BULK INSERT <DB_NAME>.dbo.<TABLENAME> FROM '<STORAGE_PATH>\<TABLENAME>.dat' WITH (DATAFILETYPE='native');

MySQL / PostgreSQL DB Migration

 

MySQL / PostgreSQL to MS SQL DB Migration

In a simple three stage process, migrate the MySQL / PostgreSQL data and run the ADAudit Plus Server with MS SQL Server.

  1. Backing up MySQL / PostgreSQL Data
  2. Configuring MS SQL Server
  3. Migrating MySQL / PostgreSQL Data to MS SQL
  1. Backing up MySQL / PostgreSQL Data
  1. Stop the ADAudit Plus Server/Service.
  2. Invoke the <ADAudit Plus Home>\bin\migrateSQLData.bat in command prompt, to backup the data available in MySQL database. By default backup file will be stored under <ADAudit Plus Home>\backup directory with the file name like 'backup_adauditplus_<Build_Number>_MM_DD_YYYY_hh_mm.data'.

Note: You can also change the backup directory. Execute the batch file to backup by passing the absolute path of the directory as argument in the command prompt.
Usage: <ADAudit Plus Home>\bin\migrateSQLData.bat  <absolute path of the directory>

  1. Configuring MS SQL Server

Common Settings to be performed in MS SQL Server

  • Open SQL Server Configuration Manager.
  • Go to ‘SQL Server Services’ and ensure the service 'SQL Server Browser' is running.
  • Goto SQL Server Network Configuration --> Protocols for SQLEXPRESS (the given instance while configuring the MS SQL) --> Enable TCP/IP. Then restart the SQL Server (SQLEXPRESS - the given instance) Service.
  • Set the following configuration for the SQL Server Configuration Manager:
    • SQL Server Network Configuration --> Protocols for <instances> --> Enable everything.
    • SQL Native Client Configuration --> Client Protocols --> Enable all.

Providing credentials to other users in the domain

  • Go to SQL Server Management Studio.
  • Expand the following <MACHINE_NAME>\SQLEXPRESS --> Security --> Logins.
  • Check whether the user provided in the ADAudit Plus Service is already in the list;

If not, right click the Logins, New Login and provide a corresponding user name. The New user must have the sysadmin server level role and database level role of db_owner.
Follow the steps to provide the sysadmin role permission:
Right click the user, click 'Properties'
Go to 'Server Roles' -- >Check sysadmin and click 'OK'

Note: Details about user roles: Refer the documents in the following links:

For Server Level Roles | For Database Level Roles | For Additional Resources

Server Role of the user should be 'sysadmin' and Database Role of the user should be 'db_owner'.

The members of sysadmin server role can perform any activity in SQL Server and have completes control over all database functions.

The members of db_owner database role can perform any activity in the database.

MS SQL Server in local computer

Copy the following files to <ADAudit Plus Home>\bin folder.

  1. bcp.exe- <MSSQL Installation Dir>\Tools\Binn\bcp.exe
  2. bcp.rll- <MSSQL Installation Dir>\Tools\Binn\Resources\1033\bcp.rll

MS SQL Server in remote computer

Note: Please install the corresponding SQL Native Client / Command line Utilities in the ADAudit Plus machine as per the SQL version and CPU type of ADAudit Plus machine.

Click Download --> 'Install Instructions' --> Scroll to 'Command Line Utilities' or 'Native Client'

MSSQL Server Version

Command line Utilities

Native Client

2005

Download

Download

2008 R2

Download

Download

2012

Download

Download

After installing the Command Line utilities, please copy the following files:
Copy the files to <ADAudit Plus Home>\bin folder.

  1. bcp.exe- <MSSQL Installation Dir>\Tools\Binn\bcp.exe
  2. bcp.rll- <MSSQL Installation Dir>\Tools\Binn\Resources\1033\bcp.rll

Windows Firewall Settings

If the Firewall is enabled in MS SQL Server machine, the TCP and UDP Ports need to be opened.

UDP Port is normally 1434.

To check the TCP Port settings open SQL Configuration Manager:

  • SQL Server Network Configuration --> Protocols for <instances>
  • --> Right click TCP/IP --> Properties --> Goto IP Addresses Tab and scroll until TCP Dynamic Ports and enter the current value in your Firewall.

Invoke the <ADAudit Plus Home>\bin\ChangeDB.bat in command prompt, to configure the MS SQL SERVER credentials like ServerName, Port, UserName and Password.

  1. Database Setup Wizard pops-up.

  1. In the wizard screen, select Server Type as MSSQL ServerAvailable MS SQL Server Instances are listed in a combo box. Enter the Host Name and Named instance of the SQL Server from the instances.
  2. Select the authentication type using the "Connect Using:" options.
  3. The options are:
    1. Windows Authentication

For Windows Authentication, the credentials of the Domain user are automatically taken.

    1. SQL Server Authentication

For SQL Server Authentication, enter the User Name and Password.

  1. Click Test Connection to check whether the credentials are correct. If the test fails, the credentials may be wrong, recheck and enter the correct credentials.
  2. Click Save button to save the MS SQL Server configuration. Note that, it will take few minutes to configure the settings of the SQL Server database.
  1. Migrating MySQL / PostgreSQL Data to MS SQL
  1. Invoke the <ADAudit Plus Home>\bin\run.bat to start the ADAudit Plus server in the command prompt.
  2. After the server is started completely, stop the server by terminating the run.bat in the command prompt or invoke the <ADAudit Plus Home>\bin\shutdown.bat
  3. Invoke the <ADAudit Plus Home>\bin\restoreMigratedSQLData.bat, browse and select the created backup file. Now click on 'OK' and wait till the database is completely restored.

Note: Executing the restoreMigratedSQLData.bat will delete the existing data, if any.

  1. Start the ADAudit Plus Server/Service to work with the MS SQLSERVER as the database.
 

MySQL To MySQL / PostgreSQL to PostgreSQL

Note: The product versions must be same.

    • MySQL / PostgreSQL Full Backup
      • Stop the product (Start --> Programs --> ADAudit Plus--> Stop ADAudit Plus).
      • Confirm the product is stopped.
      • Take full backup (copy & paste) in another MySQL folder under ADAudit Plus.
      • Check if MySQL / PostgreSQL folder size are equal.
    • Backup from Database
      • Start the db using <installation-folder>\ADAudit Plus\bin\startDB.bat
      • Check how many underscore(_) tables are in database.

             If MySQL is Backend:

                Go to database\ADAudit Plus\mysql\bin

                mysql.exe -u root -P 33307 adap

            If PostgreSQL is Backend:

               goto <installation-folder>\ADAudit Plus\pgsql\bin folder

               psql.exe -Upostgres -p33307 -h127.0.0.1 adap

                MySQL / PostgreSQL Query (Execute the following one after another):

               show tables like 'audlogoninfo_%';

               show tables like 'audlogonlogoffinfo_%';

               show tables like 'audusermgmt_%';

               show tables like 'audgroupmgmt_%';

               show tables like 'audcomputermgmt_%';

               show tables like 'audaccountcreation_%';

               show tables like 'audoumgmt_%';

               show tables like 'audgpomgmt_%';

               Check and note the total numbers = Count . ---------------------->>(A)

      • Go to <installation folder>\ADAudit Plus\bin directory. Execute backupDB.bat (Don't terminate until the process is finished).
      • Go to <installation-folder>\ADAudit Plus\backup direcotory.

          Inside, you will see the zip file name (Ex: 100726-1711.zip -  format YYMMDD-mmss) and folder 'EventData'.

          Inside the EventData\100726-1730\ Folder: Count the zip files which must be equal to the previous count (A).
            (If count is not equal, do not proceed further.)

  • Steps to remove old data files:
    • If MySQL is Backend,
      • Goto \ADAudit Plus\bin directory. Execute the startDB.bat.
      • Download and copy the dropDB.txt and createDB.txt and rename as dropDB.bat and createDB.bat in \ADAudit Plus\bin.
      • Execute dropDB.bat.
      • Execute stopDB.bat.
      • Delete all ibdata1.file , ib_logfile* under \ADAudit Plus\mysql\data directory.
    • If PostgreSQL is Backend,
      • Go to \ADAudit Plus\bin directory. Execute the stopDB.bat. Wait until the database gets stopped.
      • Go to \ADAudit Plus\pgsql directory. Rename / move the folder named 'data'.
  • Restore Database from Backup files. (New Installation / New Drive)
    • If MySQL is Backend,
      • Go to \ADAudit Plus\conf\my.ini file and add 'innodb_file_per_table' under (mysqld).
      • Go to \ADAudit Plus\bin directory.
      • Execute startDB.bat.
      • Execute createDB.bat.
    • If PostgreSQL is Backend,
      • Go to \ADAudit Plus\bin directory.
      • Execute the initPgsql.bat.
      • Execute startDB.bat.
  • Note: Common Steps for both MySQL & PostgreSQL (Don't terminate until the process is completed)
    • Execute restoreDB.bat (Ex: restoreDB.bat . by default restoreDB.bat ..\Backup\100629-2023.zip).
    • Execute RestoreEventTables.bat. (Ex.RestoreEventTables.bat . by default, RestoreEventTables.bat ..\Backup\EventData\100629-2025\).
 

MySQL ↔ PostgreSQL or MS SQL → MySQL / PostgreSQL

(To be Supported Soon)

 

Move in-built ADAudit Plus DB to Another Drive

Follow the steps to completely move the ADAudit Plus Installation to a different drive.

Note: We do not recommend database move alone to another drive.

Note: Do not delete the previous installation until it is working fine from the new location.

  • Stop ADAudit Plus Server (Start --> Run --> type services.msc --> Stop 'ManageEngine ADAudit Plus').
  • Remove / Uninstall the ManageEngine ADAudit Plus Service (Start --> All Programs --> ADAudit Plus -->NT Service--> Uninstall NT Service).
    • Note: If the above option is unavailable: Open the command prompt (Right Click --> Run as Administrator), Go to \ADAudit Plus/Bin and type "wrapper.exe -r ..conf\wrapper.conf".
  • Copy ADAudit Plus from the C: (Old Drive) and paste it in the D: (New Drive).
    • Note: Ensure the folder size is same on both locations.
  • Re-Install NT Service from the new location, Go to (New Installation Drive) D:\ManageEngine\ADAudit Plus\Bin --> Execute InstallNTService.bat ).
  • Start  ADAudit Plus Service, Start->Run->services.msc->Start the Service "ManageEngine ADAudit Plus".
 

Active Directory Auditing and Reporting trusted by

A single pane of glass for complete Active Directory Auditing and Reporting