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|
** MS SQL -> MS SQL - pertains to MS SQL version changes.
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:
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).
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).
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.
Now, You are using the New Database / Database Datas.
Recommended for: Taking a particular table backup.
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.
In a simple three stage process, migrate the MySQL / PostgreSQL data and run the ADAudit Plus Server with MS SQL Server.
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.
Common Settings to be performed in MS SQL Server
Providing credentials to other users in the domain
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:
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.
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'
After installing the Command Line utilities, please copy the following files:
Copy the files to <ADAudit Plus Home>\bin folder.
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:
Invoke the <ADAudit Plus Home>\bin\ChangeDB.bat in command prompt, to configure the MS SQL SERVER credentials like ServerName, Port, UserName and Password.
For Windows Authentication, the credentials of the Domain user are automatically taken.
For SQL Server Authentication, enter the User Name and Password.
Note: Executing the restoreMigratedSQLData.bat will delete the existing data, if any.
Note: The product versions must be same.
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)
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.)
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.