How to change the database from MySQL/PostgreSQL to MSSQL

How to change the database from MySQL/PostgreSQL to MSSQL

The steps mentioned below are applicable only if you migrate the DB from PgSQL to MSSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to MSSQL DB from the existing PgSQL DB. 
 
For builds 11303 and below: 

Step 1: Stop ManageEngine ServiceDesk Plus service.

Step 2: Take a backup of the existing data present in PgSQL database using the backupData.bat batch file.

From the command prompt, go to [ServiceDesk Plus-Home]\bin directory and execute the backUpData.bat command to start the data backup.

cmd> backUpData.bat --trimmed 

      

where, ServiceDesk Plus Home --> Drive:\ManageEngine\ServiceDesk. This backup will be stored under the trimmedbackup folder in ServiceDesk Plus Home directory. 

Step 3: Invoke ChangeDBServer.bat under [Service Desk-Home]\bin folder.

cmd>  ChangeDBServer.bat

      

Step 4: Provide the details of the SQL server (i.e.) Hostname, username, and password and click Test, By doing this, we will be able to check the connectivity with the SQL server.

      

The message should say connection established as displayed above. Then click Save.

      

Step 5: Once your are back in the command prompt, type in run and hit enter.

      

This will load all the modules and will start the application. Once all the modules are loaded, you should see a message "Server started" in the command prompt. 

      

After you see this message, open up a browser and try to access the localhost url that is displayed in the command prompt. If you see the login page, try to login with the default administrator credentials by clicking on the credential listed below. 

NOTE: A database called ServiceDesk would be created in the SQL server.

Step 6: If login action is successful, close the browser and come back to the command prompt and hit CTRL+C which would terminate the batch job that's running. You should see the modules being stopped and destroyed. At the end, when you are prompted for a confirmation for terminating the process, press 'Y' and hit enter. 

      

Step 7: Now invoke restoreData.bat under [Service Desk-Home]\bin folder as indicated below. This would bring up a GUI to choose the backup file. During this point, you will need to choose the trimmed backup that you had taken in Step 2. 

cmd> restoreData.bat > 1.txt

      

You need to choose only the part1.data file while initiating a restore. The system would automatically pick up the other parts in the backup (if exists). You need not manually select part2.data or perform any other action. 

Step 8:  You will be prompted to enter the backup password while performing the restore. The default backup password is SDP123!. If this doesn't work, it probably means, the password was changed. The application administrator would have received an email when the password was updated from where the password could be fetched.

      

If you are not aware of the backup password, then you will have to perform step 3 and 4 and change the DB back to PgSQL, start the application, check the Backup Password set under Admin --> Backup Scheduling. Once you have the password, follow steps 3 and 4 and connect the application back to MSSQL DB and try the restore again. 

After you put in the password, you will be asked if the restore is performed on the Production Environment. Please choose 'Yes' in the prompt and proceed with the restore. 

      


Step 9: After you see the message "Data Restored Successfully", start ManageEngine ServiceDesk Plus service once. As the application is starting as a service, it would usually take a couple of minutes to load. Give it a 3 to 5 minutes and then try to access the application. 

      

The restore process is marked as successful only if the restore window says 'Data restored successfully'. If you do not see this message and if the restore window disappears, it means that the restore has failed. In situations like this, please navigate to  [Service Desk-Home]\bin in file explorer and you should see a 1.txt file.  Please send the file along with the zip of the logs folder to support for analysis. 


For builds 11304 and above: 

Step 1: Stop ManageEngine ServiceDesk Plus service.

Step 2: Take a backup of the existing data present in PgSQL database using the backupDataOld.bat batch file.

From the command prompt, go to [ServiceDesk Plus-Home]\bin directory and execute the backUpDataOld.bat command to start the data backup.

cmd> backUpDataOld.bat --trimmed 

      

where, ServiceDesk Plus Home --> Drive:\ManageEngine\ServiceDesk. This backup will be stored under the trimmedbackup folder in ServiceDesk Plus Home directory. 

Step 3: Invoke ChangeDBServer.bat under [Service Desk-Home]\bin folder.

cmd>  ChangeDBServer.bat

      

Step 4: Provide the details of the SQL server (i.e.) Hostname, username, and password and click Test, By doing this, we will be able to check the connectivity with the SQL server.

      

The message should say connection established as displayed above. Then click Save.

      

Step 5: Once your are back in the command prompt, type in run and hit enter.

      

This will load all the modules and will start the application. Once all the modules are loaded, you should see a message "Server started" in the command prompt. 

      

After you see this message, open up a browser and try to access the localhost url that is displayed in the command prompt. If you see the login page, try to login with the default administrator credentials by clicking on the credential listed below. 

NOTE: A database called ServiceDesk would be created in the SQL server.

Step 6: If login action is successful, close the browser and come back to the command prompt and hit CTRL+C which would terminate the batch job that's running. You should see the modules being stopped and destroyed. At the end, when you are prompted for a confirmation for terminating the process, press 'Y' and hit enter. 

Step 7: Now invoke restoreData.bat under [Service Desk-Home]\bin folder as indicated below. This would bring up a GUI to choose the backup file. During this point, you will need to choose the trimmed backup that you had taken in Step 2. 

cmd> restoreData.bat --dbmigration > 1.txt

      

You need to choose only the part1.data file while initiating a restore. The system would automatically pick up the other parts in the backup (if exists). You need not manually select part2.data or perform any other action. 

Step 8:  You will be prompted to enter the backup password while performing the restore. The default backup password is SDP123!. If this doesn't work, it probably means, the password was changed. The application administrator would have received an email when the password was updated from where the password could be fetched.

      

If you are not aware of the backup password, then you will have to perform step 3 and 4 and change the DB back to PgSQL, start the application, check the Backup Password set under Admin --> Backup Scheduling. Once you have the password, follow steps 3 and 4 and connect the application back to MSSQL DB and try the restore again. 

After you put in the password, you will be asked if the restore is performed on the Production Environment. Please choose 'Yes' in the prompt and proceed with the restore. 

      


Step 9: After you see the message "Data Restored Successfully", start ManageEngine ServiceDesk Plus service once. As the application is starting as a service, it would usually take a couple of minutes to load. Give it a 3 to 5 minutes and then try to access the application. 

      

The restore process is marked as successful only if the restore window says 'Data restored successfully'. If you do not see this message and if the restore window disappears, it means that the restore has failed. In situations like this, please navigate to  [Service Desk-Home]\bin in file explorer and you should see a 1.txt file. Please send the file to support for analysis. 

          • Related Articles

          • How to change the database from MSSQL to PostgreSQL

            The steps mentioned below are applicable only if you migrate the DB from MSSQL to PgSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to PgSQL DB from the existing MSSL DB.    For ...
          • How to connect to the ServiceDesk database?

            Open command prompt on the server and change the directory to MangeEngine\ServiceDesk\bin, invoke the batch file 'changeDBServer.bat' For MSSQL database  Connect to the query analyzer of the SQL server and execute the queries. For PostgreSQL database ...
          • How to change the port on which MySQL runs

            The following are the instructions to change the port for the MySQL database. 1. Edit the ManageEngine\ServiceDesk\Bin\StartDb.bat file and change "33366" to the port on which the default MySQL runs. 2. Edit the ...
          • How to connect to the AssetExplorer database?

            To find the database connected to your installation, open Command Prompt with elevated permission, navigate to [AE-Home]\bin directory and invoke the batch file 'changeDBServer.bat'. [AE-Home] is Drive:\ManageEngine\AssetExplorer\ directory. ...
          • Failed to start the database

            For the error "Failed to start the database", Check what database being used and, execute changeDBServer.bat. For Pgsql,  Start PGSQL from the command link, execute StartDB.bat 65432 under C:\Manageengine\ServiceDesk\bin 65432 is the default port ...