Support
 
PhoneGet Quote
 
Support
 
US Sales: +1 888 720 9500
US Support: +1 844 245 1108
Intl: +1 925 924 9500
Aus: +1 800 631 268
UK: 0800 028 6590
CN: +86 400 660 8680

Direct Inward Dialing: +1 408 916 9890

 
 

Database maintenance in O365 Manager Plus

O365 Manager Plus comes bundled with PostgreSQL. Users can migrate to MS SQL if required.

What causes database growth in this solution?

O365 Manager Plus stores the historical data of all basic Office 365 reports, audit logs, and service health data indefinitely since they're essential for ensuring security and compliance. Population of these data is the major cause for the database growth.

How does archiving help?

Archiving helps to avoid uncontrollable database growth over time. Data older than the configured number of days will be saved as zipped CSV files and deleted from the product database. Archiving is supported for auditing and monitoring reports of O365 Manager Plus. The archived files can be restored any time with a single click using O365 Manager Plus.

Configuring archiving

  1. Go to the Settings tab of O365 Manager Plus.
  2. Navigate to Configuration → Audit Configuration → Archive Settings in the left pane.
  3. Click on the Click here to enable option.
  4. Select the Enable Archive check box.
  5. Enter the age of the data to be archived, the path where the archived files will be stored, and the password for the archived files.
  6. Click Save.

Restoring archived data

  1. Go to the Settings tab of O365 Manager Plus.
  2. Navigate to Configuration → Audit Configuration → Archive Settings in the left pane.
  3. Click the Restore option found under the Actions column, corresponding to the report you need to restore.

Migrating from PostgreSQL to MS SQL

Migrating data from O365 Manager Plus' default server, PostgreSQL, to MS SQL is a three step process:

  • Backing up PostgreSQL data
  • Configuring MS SQL server
  • Migrating PostgreSQL data to MS SQL server

Backing up PostgreSQL Data

  1. Stop O365 Manager Plus.
  2. Open the Command Prompt as an administrator.
  3. Run the backupDB.bat file located in O365 Manager Plus installation directory\bin.
  4. Data in PostgreSQL server will be backed up and stored by default under O365 Manager Plus installation directory\backup\OfflineBackup_ <Backup_Time>.

Configuring MS SQL Server

Steps to be followed in MS SQL Server

  1. Run compmgmt.msc in the Command Prompt to open Open SQL Server Configuration Manager.
  2. Go to SQL Server Services and ensure that SQL Server Browser is running.
  3. In the left pane, click QL Server Network Configuration → double-click Protocols for SQLEXPRESS.
    Note: SQLEXPRESS is the instance name provided while configuring MS SQL Server. This name will be used throughout the document.
  4. In the pop-up that appears, go to the Protocol tab and enable TCP/IP. Restart the SQL Server SQLEXPRESS Service.
  5. Go back to the SQL Server Configuration Manager. In the left pane:
    1. Navigate to SQL Server Network Configuration → Protocols for SQLEXPRESS, and enable all the protocols.
    2. Navigate to SQL Native Client Configuration → Client Protocols, and enable all the protocols.

Delegating MS SQL server access to O365 Manager Plus technicians

  1. Log in to SQL Server Management Studio.
  2. In the left pane, navigate to MACHINE NAME > SQLEXPRESS > Security > Logins.
  3. Right-click Logins and select New Login.
  4. Provide a Login Name, and choose whether to use Windows Authentication or QL Server Authentication.
    1. If you choose Windows Authentication, enter the Windows NT name of the user to whom access must be granted.
    2. If you choose SQL Server Authentication, you will be prompted to create a new User Name and Password.
  5. The new user must have the sysadmin role in the server level and db_owner role in the database level. Follow these steps to provide the sysadmin role permission:
    1. Navigate to MACHINE NAME → SQLEXPRESS → Security → Logins > right-click the user → Properties.
    2. Go to Server Roles, select the sysadmin check box, and click OK.
Note: To learn more about user roles, click the links below:

Server level roles: http://msdn.microsoft.com/en-us/library/ms188659.aspx

Database level roles: http://msdn.microsoft.com/en-us/library/ms189121.aspx

If MS SQL Server is installed in the same machine as O365 Manager Plus

Copy the following files to <O365 Manager Plus installation directory>\bin folder.

  1. bcp.exe - <MSSQL installation directory>\Tools\Binn\bcp.exe
  2. bcp.rll - <MSSQL installation directory>\Tools\Binn\Resources\1033\bcp.rll

If MS SQL Server is installed in a remote computer

Note: Please install the corresponding SQL Native Client or command line Utilities in the O365 Manager Plus machine as per the MS SQL Server version, as well as the CPU type of the O365 Manager Plus machine.
SQL Server Version Command line Utilities Native Client
2005 Download Download
2008 R2 Download Download
2012 Download Download
2014 Download Download
2016 Download Download
2017 Download Download

After installing the command line utilities:

Copy the following files to O365 Manager Plus installation directory\bin.

  1. bcp.exe - <MSSQL installation directory\Tools\Binn\bcp.exe
  2. bcp.rll - <MSSQL installation directory\Tools\Binn\Resources\1033\bcp.rll

Migrating PostgreSQL Data to MS SQL

Stop O365 Manager Plus before migrating the database.

  • To migrate the database from PostgreSQL to MS SQL with all data intact:
    1. Open the Command Prompt and navigate to <O365 Manager Plus installation directory>\bin.
    2. Enter the command changeDB.bat to migrate the database with the data intact.
  • To migrate from PostgreSQL to MS SQL without data:
    1. Open the Command Prompt and navigate to <O365 Manager Plus installation directory>\bin.
    2. Enter the command changeDB.bat false to change the database without migrating the data.
    3. change-databsase-office365

    4. In the Database Setup Wizard that pops up, select Server Type as MS SQL Server.
    5. Enter the Host Name and Port number of MS SQL server.
    6. Select the QL Server Instance from the drop-down.
    7. Select the Authentication type.
      1. If you choose Windows Authentication, provide the Domain Name, User Name, and Password of the machine that has access to the server.
      2. If you choose SQL Server Authentication, provide the User Name and Password of the O365 Manager Plus technician who has access to it. Refer to: Delegating MS SQL server access to O365 Manager Plus technicians.
      3. migrate-office365-database

      4. Click on Test Connection. If the test fails, the credentials may be wrong. Recheck and enter the correct credentials.
      5. Click Save to finish MS SQL Server configuration.
      6. Start the O365 Manager Plus Server/Service to work with the MS SQL SERVER as the database.

Restoring the O365 Manager Plus database

  • Open the Command Prompt as an administrator in O365 Manager Plus Installation Directory. By default, the path to the installation directory is C:\ManageEngine\O365 Manager Plus.
  • Execute the command below to restore the database.
  • restoreDB.bat compressed_file_name.zip
A holistic Office 365 administration solution