PostgreSQL/MySQL to MS SQL Migration

By default, ADAudit Plus comes bundled with PostgreSQL database. To move the DB and/or data from PostgreSQL/MySQL to MS SQL, set-up the prerequisites first and then initiate movement.

The following versions of MSSQL are supported:

  • SQL server 2005
  • SQL server 2008
  • SQL server 2008 R2
  • SQL server 2012
  • SQL server 2014
  • SQL server 2016
  • SQL server 2017

1. Configuring the MS SQL server

  • Open the SQL Server Configuration Manager on the computer running the MS SQL instance (to which the DB and/or data is to be moved).
  • In the left pane, click SQL Server Services → Ensure that the SQL Server Browser is running.
  • In the left pane, click SQL Server Network Configuration --> Select Protocols for (the given instance) --> Enable TCP/IP.

    Note: The given instance refers to the MS SQL instance to which the DB and/or data is to be moved.

  • In the left pane, click SQL Native Client Configuration --> Select Client Protocols for (the given instance) --> Enable TCP/IP.
  • Restart the SQL Server Service.

2. Providing permissions to the MS SQL instance

The user account associated with ADAudit Plus must have access and appropriate permissions to the MS SQL instance (to which the DB and/or data is to be moved).

Note: If SQL Server Autentication is used to move the DB and/or data, access and permissions to the MS SQL instace are automatically provided. However, when Windows Authentication is used, access and permissions have to be granted, as explained below-

(i). To grant access- Login to Microsoft SQL Server Management Studio with a sysadmin account --> Select the server instance to which you will be migrating --> Security --> Logins. Check whether the user running ADAudit Plus is on the list-

  • If the user is already listed
  • If the user is not listed, right click on Logins --> New Login --> Create a new login

(ii). To grant permissions- Right click on the user -> Properties -> Server Roles -> Check whether the user has sysadmin permission-

  • If the user does
  • If the user does not --> Select sysadmin in the checkbox and click OK

    Note: To grant the user only the minimum permissions required instead of sysadmin permission, right click on the user --> Properties --> User Mapping --> Select db_datareader, db_datawriter, db_ddladmin in the checkbox and click OK.

  • Also, execute the below query in the database: Right click on DB --> New Query --> GRANT CONTROL ON CERTIFICATE::[ZOHO_CERT] TO [newly_created_user]

3. Enabling communication with the MS SQL server

If ADAudit Plus and the MS SQL instance are running on different computers-

(i). Download and install- SQL native client (sqlncli.msi), command line utilities (SqlCmdLnUtils.msi), and ODBC Driver (msodbcsql.msi); in the computer on which ADAudit Plus is running.

(ii). Copy the following 2 files from the MS SQL server installation folder to the ADAudit Plus bin folder-

  • bcp.exe- \Tools\Binn\bcp.exe
  • bcp.rll- \Tools\Binn\Resources\1033\bcp.rll

Note: If ADAudit Plus and the MS SQL instance are running on the same computer-

Copy the following 2 files from the MS SQL server installation folder to the ADAudit Plus bin folder-

  • bcp.exe- \Tools\Binn\bcp.exe &
  • bcp.rll- \Tools\Binn\Resources\1033\bcp.rll

4. Opening UDP and TCP ports (applicable only if the firewall is enabled in the MS SQL Server computer)

  • UDP port number is 1434.
  • To find the TCP port number, open SQL Server Configuration Manager on the computer where the MS SQL instance to which the DB and/or data is to be moved, resides. --> SQL Server Network Configuration --> Protocols for . Right click on TCP/IP --> Properties --> IP Addresses --> IPALL --> TCP Port Number.
  • Open the UDP and TCP ports under firewall settings.

5. Moving product from one server/drive to another

  • Stop the ADAudit Plus server (Start → Run → type services.msc → Stop "ManageEngine ADAudit Plus" ).
  • Stop the DB,
    Open a command prompt as an administrator/>
    Navigate to <installation dir>\ManageEngine\ADAudit Plus\bin
    Execute StopDB.bat
  • Remove the ADAudit Plus service,
    Open a command prompt as an administrator
    Navigate to <installation dir>\ManageEngine\ADAudit Plus\bin
    Execute the command : wrapper.exe -r ..\conf\wrapper.conf
  • Note: The 4th step found below, is required only for build numbers 6000 and above.

  • Remove the ADAudit Plus - DataEngine service,
    Open a command prompt as an administrator
    Navigate to <installation dir>\ManageEngine\ADAudit Plus\apps\dataengine-xnode\bin
    Execute the command : dataengine-xnode.bat -r
  • Copy the entire ADAudit Plus folder to the new server or drive.
  • Ensure both folder sizes are the same.
  • Open the command prompt [with Administrative privileges]
    <installation-directory>\ManageEngine\ADAudit Plus\bin --> Execute
    InstallNTService.bat.</installation-directory>
  • Open the command prompt [as an administrator]\ManageEngine\ADAudit Plus\bin → Execute initPgsql.bat.
  • If you want to start the product as service, go to Services.msc → 'ManageEngine ADAudit Plus' Service → Right click on properties → Click on 'Log on' tab and select 'This Account' → Provide the suitable credentials.
Copyright © 2019, ZOHO Corp. All Rights Reserved.
ManageEngine
Get download link