Big savings, Better ROI! Exclusive discounts on ManageEngine Products!* Boost your business *T&C apply
    Click here to shrink
    Click here to expand Click here to expand

    PostgreSQL/MySQL to MS SQL Migration

    ADAudit Plus allows administrators to store and retrieve Windows servers' audit log data from MySQL, MS SQL, or PostgreSQL databases.

    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 2008 R2 (EOLed by Microsoft)
    • SQL server 2012
    • SQL server 2014
    • SQL server 2016
    • SQL server 2017
    • SQL server 2019

    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.

    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 an account that has been assigned the sysadmin role → 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 → Proceed to 2(ii).
    • If the user is not listed, right click on Logins → New Login → Create a new login → Proceed to 2(ii).

    (ii). To grant permissions- Right click on the user → Properties → Server Roles → Check whether the user has been assigned the sysadmin role-

    • If the user has → Proceed to 3.
    • If the user has not → Select sysadmin in the checkbox and click OK → Proceed to 3.

      Note: To grant the user only the minimum permission required instead of sysadmin role, follow the two steps found below:

      • Right-click on the user → Properties → User Mapping → Select db_datareader, db_datawriter, db_ddladmin in the checkbox and click OK.
      • Right-click on the database → Properties → Permissions → Provide 'Execute' permission for the user and click OK.
      • Also, execute the below query in the database: Right-click on the database → New Query → GRANT CONTROL ON CERTIFICATE::[ZOHO_CERT] TO [newly_created_user]

    Enabling communication with the MS SQL server

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

    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 <the given instance>. Right click on TCP/IP → Properties → IP Addresses → IPALL → TCP Port Number.
    • Open the UDP and TCP ports under firewall settings.

    Move DB and/or data

    • Stop ADAudit Plus.
    • Invoke (ADAudit Plus Home)\bin\ChangeDB.bat in command prompt. Make sure that the user running the command prompt is same as the one running ADAudit Plus.

      ad-audit-plus-db-migrations-move-db-data

    • DB Configuration wizard will pop-up → Select server type as MS SQL →  Select the Host Name, Instance Name, and Database Name.
      • If you want to migrate existing data from the PostgreSQL/MySQL to MS SQL, then select Yes for the Migrate Data option. Otherwise, select No (for a new installation of ADAudit Plus).
      • If your MS SQL server is SSL enabled, then select Yes for SSL Encrypted. Otherwise, select No.

        Note: The name of the MS SQL Server Instance has to be entered manually in case you have not chosen the Auto Discover option.

        Note: To learn how to create an SSL certificate in MS SQL server, follow the steps in this page.

    • Select the Authentication type. If you have selected Windows Authentication, the credentials are automatically taken. If you have selected SQL Server Authentication, enter the corresponding credentials.
      • Note: If SQL Server Authentication is used to move the DB and/or data, access and permissions to the MS SQL instance are automatically provided. However, when Windows Authentication is used, access and permissions have to be granted, as explained in step 2.
    • Click Test Connection to check whether the credentials are correct.
    • Click Save.
      The migration procedure will start and it will take a few minutes to complete. Successful migration will end with the below screen-

      ad-audit-plus-db-migrations-procedure-start

    Converting the format of archived files after migration

    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, say, tableBackup_new.
    2. Open the Command Prompt as an administrator, navigate to <Installation_Directory>\ManageEngine\ADAudit Plus\bin, and execute the following command:
      3. ChangeArchive2BCPSupport "<Archive_Directory>" "<New_Directory>" <Old_Backend_DB> <New_Backend_DB>

      In the above command,

      • 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.
      • Replace <New_Directory> with the path to the tableBackup_new folder that you just created in step 1.
      • 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.

      For example: If you are migrating from PostgreSQL to MS SQL, and your archive directory and new directory are C:\ManageEngine\ADAudit Plus\archive\tableBackup and C:\ManageEngine\ADAudit Plus\archive\tableBackup_new respectively, then the command will be as follows:

      ChangeArchive2BCPSupport "C:\ManageEngine\ADAudit Plus\archive\tableBackup" "C:\ManageEngine\ADAudit Plus\archive\tableBackup_new" postgres mssql

      Once the command executes successfully, new files whose format is compatible with the MS SQL database will be created in the tableBackup_new folder.

    3. Move the archived files from the original folder (tableBackup) to a secure location for backup.
    4. Copy the new files from the tableBackup_new folder and paste them in the original folder (tableBackup).

    Don't see what you're looking for?

    •  

      Visit our community

      Post your questions in the forum.

       
    •  

      Request additional resources

      Send us your requirements.

       
    •  

      Need implementation assistance?

      Try onboarding

       

    On this page

    Get download link