documentsn.com

Migration fromPostgreSQL databaseto MS SQL Serverwww.microsoft365managerplus.com

Document OverviewM365 Manager Plus comes bundled with a PostgreSQL database to store the product's data from itsreporting, monitoring, management, and auditing modules. The solution also supports MS SQL Serverand enables you to migrate product data from the built-in PostgreSQL to an MS SQL database. Thisguide will walk you through the database migration process.Supported versions of MS SQL Server: 2005, 2008 R2, 2012, 2014, 2016, and 2017Prerequisites for the migration processMake sure that the following conditions are satisfied in the MS SQL Server to which you want tomigrate the data:1. The SQL Server browser must be up and running.2. For SQL Server network configuration, TCP/IP protocol must be enabled.3. All the client protocols must be enabled.4. MS SQL Server access is delegated to a user with sysadmin and db owner permissions atthe server and database levels respectively.Refer to Appendix A for configuring prerequisites 1, 2, and 3. Refer to Appendix B for configuringprerequisite 4.Migrating from PostgreSQL to MS SQLMigrating data from M365 Manager Plus’ default PostgreSQL database to MS SQL consists of thefollowing two steps:1. Backing up the M365 Manager Plus database2. Migrating PostgreSQL data to MS SQLwww.microsoft365managerplus.com01

Step 1: Backing up the M365 Manager Plus databaseIf you're migrating the database of a fresh installation, then you can skip this step.1. Stop M365 Manager Plus. (it's essential to stop M365 Manager Plus before performingthe backup).a. If M365 Manager Plus is running in console mode, click the Windows icon. Search forM65 Manager Plus, and choose Stop M365 Manager Plus.b. If M365 Manager Plus is running as a service, click the Windows icon. Search forServices. In the window that appears, choose ManageEngine M365 Manager Plus, andselect the Stop the service option on the left side.2. Navigate to M365 Manager Plus installation directory \bin.Note: By default, M365 Manager Plus is installed in: C:\ManageEngine\M365 Manager Plus3. Run the backupDB (Windows batch) file as an administrator. Do not terminate until the process isfinished.4. Data in the default database of M365 Manager Plus will be backed up and stored under M365 Manager Plus installation directory \backup\OfflineBackup Backup Time .Note: If the below error shows up while running the backupDB file, make sure that theproduct has been stopped before proceeding.www.microsoft365managerplus.com02

Step 2: Migrating PostgreSQL Data to MS SQLNote: Make sure that M365 Manager Plus is stopped before the migration process.1. If MS SQL Server is installed in a remote computer, install the necessary command line utilitiesand native Client, and then proceed to the next step.Note: Download the corresponding command line utilities and native clients from the linksbelow. On clicking the Download button in the page dislayed, you will see a listing of SQL NativeClient or command line utilities. The command line utilities have the term SQLCMD in them, andthe native client file can be found under the name sqlncli. Install the corresponding SQL NativeClient or command line utilities as per the MS SQL Server version and CPU type of the machinein which M365 Manager Plus is installed.SQL Server versionCommand line utilitiesNative client2005DownloadDownload2008 Download2016DownloadDownload2017DownloadDownload2. Copy the following files to M365 Manager Plus installation directory \bin folder.i. bcp.exe- MSSQL installation directory \Tools\Binn\bcp.exeii. bcp.rll- MSSQL installation directory \Tools\Binn\Resources\1033\bcp.rll3. Navigate to M365 Manager Plus directory \bin.4. Run the ChangeDB batch file as an administrator. In the Database Setup Wizard that appears,select MS SQL Server as the Server Type.5. Enter the Host Name and Port number of MS SQL Server.6. Select the SQL Server Instance from the drop-down.www.microsoft365managerplus.com03

7. Select the Authentication type.a. If you choose Windows Authentication, provide the Domain Name, User Name, andPassword of the user account that has access to the server.b. If you choose SQL Server Authentication, provide the User Name and Password of the userwho has access to the MS SQL Server instance.8. If you want to migrate all the data stored in the default database and also keep a copy of it in thenewly-migrated database, check the box next to Migrate existing data. If you do not want to migratethe data, you can leave it unchecked.Note: By choosing either option, data will remain in the default PostgreSQL database evenafter the migration. However, the MS SQL database will be active and used to store all theproduct data.9. Click on Test Connection. If the test fails, then recheck your credentials and try again.10. Click Save to finish the MS SQL Server configuration.11. Start the M365 Manager Plus Server/Service to work with MS SQL Server as the database.www.microsoft365managerplus.com04

Appendix AConfiguring MS SQL ServerIf you already have a functional MS SQL Server, then this step is not required. Follow the stepsbelow to configure a new MS SQL Server installation.1. Run compmgmt.msc in Command Prompt to open the SQL Server Configuration Manager.2. Go to SQL Server Services, and ensure that SQL Server Browser is running.3. In the left pane, click SQL Server Network Configuration, double-click Protocols forSQLEXPRESS.Note: SQLEXPRESS is the instance name provided while configuring MS SQL Server. Thisname will be used for reference.4. In the pop-up that appears, go to the Protocol tab, and enable TCP/IP. Restart the SQLServer SQLEXPRESS Service.5. Go back to SQL Server Configuration Manager. In the left pane:Navigate to SQL Server Network Configuration Protocols for SQLEXPRESS, and enableall the protocols.Navigate to SQL Native Client Configuration Client Protocols, and enable all theprotocols.www.microsoft365managerplus.com05

Appendix BDelegating MS SQL Server access to usersTo complete the migration process, you need a database admin account for the MS SQL connection.If M365 Manager Plus is already running as a Windows service, you can use its service account as theMS SQL database admin account. Check if the account has the required privileges. If there is nopreviously configured service account in MS SQL Server, follow the steps given below to create a newuser account, and assign required permissions to it.1. Log in to SQL Server Management Studio.2. In the left pane, navigate to Machine Name SQLEXPRESS Security Logins.3. Right-click on Logins, and select New Login.4. Provide a Login Name, and choose whether to use Windows Authentication or SQL ServerAuthentication.If you choose Windows Authentication, enter the Windows NT name of the user to whomaccess must be granted.If you choose SQL Server Authentication, you will be prompted to create a newUsername and Password.5. The new user must have the sysadmin role in the server level and db owner role in thedatabase level. Follow these steps to provide the sysadmin and db owner role permissionsto the new user:Navigate to Machine Name SQLEXPRESS Security Logins. Right-click the user Properties.Go to Server Roles, select the sysadmin box, and click OK.www.microsoft365managerplus.com06

Go to User Mapping in the left pane. In the Users mapped to this login list, check thedatabase and in the Database role membership for list, select db owner, and click OK.Note: For details about user roles, refer to the following documents:For Server-Level Roles: spxFor Database-Level Roles: spxIn general, the configured account needs one of these three sets of privileges to complete themigration process successfully:Required database roleRequired permissionsSet 1db ownerNot requiredSet 2db datareader,Not requireddb datawriter,db ddladmin,db backupoperator.www.microsoft365managerplus.com07

Set 3db ddladminALTER ANY TABLE,ALTER ANY AGGREGATE,ALTER ANY DEFAULT,ALTER ANY FUNCTION,ALTER ANY PROCEDURE,ALTER ANY QUEUE,ALTER ANY RULE,ALTER ANY SYNONYM,ALTER ANY TYPE,ALTER ANY VIEW,ALTER ANY XML SCHEMACOLLECTION,ALTER ANY REFERENCES,CONTROL ONCERTIFICATE::[ZOHO CERT]TO [user],CONTROL ON SYMMETRICKEY::[##MS DatabaseMasterKey##]TO [user],CONTROL ON SYMMETRICKEY::[ZOHO SYMM KEY]TO [user]Important:Please note that you must have the db owner permission while migrating PostgreSQL to MS SQLfor the first time. After a successful migration, you can revoke the db owner permission for theaccount, and provide the set 2 or set 3 permissions.M365 Manager Plus is an extensive Microsoft 365 tool used for reporting, managing, monitoring, auditing,and creating alerts for critical incidents. With its user-friendly interface, you can easily manage ExchangeOnline, Azure Active Directory, Skype for Business, OneDrive for Business, Microsoft Teams, and otherMicrosoft 365 services from a single console.

MS SQL database admin account. Check if the account has the required privileges. If there is no previously configured service account in MS SQL Server, follow the steps given below to create a new user account, and assign required permissions to it. 1. Log in to SQL Server Management Studio.