✖
 
 

Change or Migrate PostgreSQL to Microsoft SQL Server

RecoveryManager Plus is bundled with a PostgreSQL database by default. To migrate from PostgreSQL to Microsoft SQL, follow the steps below and initiate the migration.

The following versions of Microsoft SQL are supported:

In a simple three stage process, you can migrate the PostgreSQL data and run RecoveryManager Plus with Microsoft SQL Server.

Backing up PostgreSQL Data

Before migrating from PostgreSQL to Microsoft SQL Server, it is important to back up the existing PostgreSQL database to preserve product data and allow recovery if needed. The steps below explain how to perform the backup.

  1. Stop the RecoveryManager Plus Server or Service.
  2. Invoke the  <RecoveryManager Plus Home>\bin\backupDB.bat in command prompt, to backup the data available in PostgreSQL database. By default backup files will be stored in <RecoveryManager Plus Home>\Backup\ as OfflineBackup_YYYYMMDDHHmmss.

Configuring the Microsoft SQL Server

This section explains the necessary configurations required when migrating from PostgreSQL to Microsoft SQL Server in RecoveryManager Plus. The steps include:

Configuring common Microsoft SQL Server settings

Perform the following steps in SQL Server Configuration Manager:

  1. Open the SQL Server Configuration Manager.
  2. Go to SQL Server Services and ensure the service SQL Server Browser is running.
  3. Navigate to SQL Server Network Configuration > Protocols for SQLEXPRESS (the instance given when configuring the Microsoft SQL) >
    • Enable TCP/IP.
    • Restart the SQL Server (SQLEXPRESS - the given instance) Service.
  4. Set the following configuration for the SQL Server Configuration Manager:
    • SQL Server Network Configuration > Protocols for <instances> > Enable everything.
    • SQL Native Client Configuration > Client Protocols > Enable all.

Assigning user credentials and permissions

The user account associated with RecoveryManager Plus must have access and appropriate permissions for the Microsoft SQL instance where the database or data will be moved.

Note: If SQL Server Authentication is used to move the database and/or data, access and permissions to the Microsoft SQL instance are automatically provided. However, when Windows Authentication is used, access and permissions have to be granted. Follow the steps listed below to assign permissions.

To grant access,

  1. Log in to Microsoft SQL Server Management Studio using an account with the Sysadmin role.
  2. Select the server instance to which you will be migrating and navigate to Security > Logins.
  3. Verify if the account used by RecoveryManager Plus is listed under Logins.
    • If the account exists, proceed to the grant permissions section.
    • If not, right-click Logins > New Login, create a new login for the account, and then proceed to the grant permissions section.

To grant permissions,

  1. Right click on the existing user account or the one created in Step 3 of the previous section and click Properties > Server Roles.
  2. Go to Server Roles and check if the sysadmin role is assigned.
    • If not, select the sysadmin checkbox and click OK.

Note: For more details about user roles, refer to the following:

The server role of the user should be sysadmin and the database role of the user should be db_owner. The members of the sysadmin server role can perform any activity in the Microsoft SQL Server and have complete control over all database functions. The members of db_owner database role can perform any activity in the database.

Setting up Microsoft SQL Server on local or remote machines

CDepending on whether SQL Server is installed locally or remotely, follow the steps below to set up Microsoft SQL Server for RecoveryManager Plus.

To configure Microsoft SQL Server on a local computer:

Copy the following files to <RecoveryManager Plus Home>\bin folder.

  1. bcp.exe- <Microsoft SQL Installation Dir>\Tools\Binn\bcp.exe
  2. bcp.rll- <Microsoft SQL Installation Dir>\Tools\Binn\Resources\1033\bcp.rll

To configure Microsoft SQL Server on a remote computer:

Note: Install the corresponding SQL Native Client or Command Line Utilities in the RecoveryManager Plus machine as per the Microsoft SQL Server version and CPU type of RecoveryManager Plus machine.

SQL Server version Command Line Utilities (cmdlnutils) Files to be download for CmdLnUtils Native Client (ncli) File to be downloaded for Native client ODBC Driver (odbc) File to be downloaded for ODBC Driver
2008 https://www.microsoft.com/en-in/download/details.aspx?id=44272 SqlCmdLnUtils.msi https://www.microsoft.com/en-in/download/details.aspx?id=44272 sqlncli.msi Not Needed Not Needed
2012

64 bit:

https://www.microsoft.com/en-in/download/details.aspx?id=36433

32 bit:

https://www.microsoft.com/en-in/download/details.aspx?id=36433

Ref: https://www.microsoft.com/en-us/download/details.aspx?id=29065

 The previous column contains the download link. https://www.microsoft.com/en-in/download/details.aspx?id=50402 sqlncli.msi Not Needed Not Needed
2014 https://www.microsoft.com/en-US/download/details.aspx?id=53164 MsSqlCmdLnUtils.msi Not Needed Not Needed https://www.microsoft.com/en-us/download/details.aspx?id=56833 msodbcsql.msi
2016 https://www.microsoft.com/en-us/download/details.aspx?id=56833 MsSqlCmdLnUtils.msi Not Needed Not Needed https://www.microsoft.com/en-us/download/details.aspx?id=56833 msodbcsql.msi
2017 https://www.microsoft.com/en-us/download/details.aspx?id=53591 MsSqlCmdLnUtils.msi Not Needed Not Needed https://www.microsoft.com/en-us/download/details.aspx?id=56833 msodbcsql.msi
2 019

64 bit:

https://go.microsoft.com/fwlink/?linkid=2230791

32 bit:

https://go.microsoft.com/fwlink/?linkid=2231320

 MsSqlCmdLnUtils.msi Not Needed Not Needed 64 bit: https://go.microsoft.com/fwlink/?linkid=222330432 bit: https://go.microsoft.com/fwlink/?linkid=2223303 msodbcsql.msi

After installation, copy the following files to RecoveryManager Plus Home>\bin folder.

  1. bcp.exe → Microsoft SQL Installation Dir → \Tools\Binn\bcp.exe
  2. bcp.rll → Microsoft SQL Installation Dir → \Tools\Binn\Resources\1033\bcp.rll

Configuring Windows firewall settings

If Windows Firewall is enabled on the Microsoft SQL Server:

  1. Ensure the default UDP port 1434 is open.
  2. Verify the TCP port settings in SQL Server Configuration Manager:
    • Go to SQL Server Network Configuration > Protocols for <instances>.
    • Right-click TCP/IP > Properties > IP Addresses tab.
    • Scroll down to TCP Dynamic Ports and note the port number.
    • Add this port to the firewall.

Microsoft SQL Server TCP settings window.

Migrating PostgreSQL data to Microsoft SQL

  1. Stop the RecoveryManager Plus server.
    1. To stop RMP if it is running as a console:

      2. Run the shutdown.bat file from <RecoveryManager Plus Home>\bin\ folder in the Command Prompt to stop the RecoveryManager Plus server.

    2. To stop RecoveryManager Plus when it is running as a service: Go to services.msc > stop RecoveryManager Plus service.
    3. To migrate the database from PostgreSQL to Microsoft SQL with all data intact:

      4. Open Command Prompt and navigate to <RecoveryManager Plus Home>\bin. Enter changeDB.bat command to migrate the database with the data intact.

      (OR)

      To change the database from PostgreSQL to Microsoft SQL without migrating the data: Open Command Prompt and navigate to the location <RecoveyManager Plus Home>\bin. Enter the command changeDB.bat false to migrate the database without data.

    4. The Database Setup Wizard pop-up will appear on the next screen.

      5. RecoveryManager Plus Database Setup Wizard.

    5. Select Server Type as Microsoft SQL Server. Choose one of the available Microsoft SQL server instances listed in the corresponding drop-down box. Enter the Host Name and Named instance of the SQL Server from the instance drop-down.
    6. Under Connect Using options, select one of the authentication types. The options are:
      1. Windows Authentication

        2. For Windows authentication, the credentials of the Domain user are automatically taken.

        Microsoft SQL setup with Windows Authentication.

      2. SQL Server Authentication

        3. For Microsoft SQL Server Authentication, enter the User Name and Password.

        Microsoft SQL setup with SQL Server Authentication.

      3. Click Test Connection to check whether the credentials are correct. If the test fails, the credentials may be wrong; recheck and enter the correct credentials.
      4. Click Save to save the Microsoft SQL Server configuration. Note that it will take a few minutes to configure the settings of the SQL Server database.
      5. Start the RecoveryManager Plus service to work with the Microsoft SQL server as the database.

      If the Microsoft SQL server you wish to migrate to has Force encryption enabled, select the check box against SSL connection and follow the steps mentioned below.

      1. Covert your Microsoft SQL server certificate to .cer format.
        • Open IIS Manager.
        • In the middle pane, click Server Certificates.
        • Open the certificate you want to use, then click the Details tab.
        • Click Copy to file.
        • Click Next in the Certificate Export Wizard window that appears.
        • On the Export Private Key screen, select No, do not export the private key, then click Next.
        • On the Export File Format screen, select either DER encoded binary X.509 (.CER) or Base-64 encoded X.509 (.CER), then click Next.
        • Enter a name for the file, click Next, and then Finish.
        • Copy the CER file and place it in <Installation_folder>\RecoveryManagerPlus\conf on the machine where RecoveryManager Plus is running.
      2. Open Command Prompt and navigate to <Installation directory>\jre\bin folder of RecoveryManager Plus. Use the command below to associate the Jave KeyStore:

        3. keytool -import -v -trustcacerts -alias myserver -file pathofthecert\certname.cer -keystore"..\lib\security\cacerts" -keypass changeit -storepass changeit -noprompt, where pathofthecert is the location where the certificate has been stored and certname is the certificate name. The certificate will be added to your Java KeyStore.

      Navigate to next Previous Topic Next Topic Navigate to next

Copyright © 2023, ZOHO Corp. All Rights Reserved.