Change/Migrate PostgreSQL to MS-SQL Server

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

Backing up PostgreSQL Data
Configuring MS SQL Server
Migrating PostgreSQL data to Microsoft SQL

Backing up PostgreSQL Data

Stop the RecoveryManager Plus Server/Service.
Invoke the <RecoveryManager Plus Home>\bin\backupDB.bat in command prompt, to backup the data available in PostgreSQL database. By default backup file will be stored under <RecoveryManager Plus Home>\Backup\OfflineBackup_YYYYMMDDHHmmss directory .

Configuring MS SQL Server

Common Settings to be performed in MS SQL Server

Open SQL Server Configuration Manager.
Goto ‘SQL Server Services’ and ensure the service 'SQL Server Browser' is running.
Goto SQL Server Network Configuration → Protocols for SQLEXPRESS (the given instance while configuring the MS SQL) →Enable TCP/IP. Then restart the SQL Server (SQLEXPRESS - the given instance) Service.
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.

Providing credentials to other users in the domain

Go to SQL Server Management Studio.
Expand the following → MACHINE_NAME → \SQLEXPRESS → Security → Logins.
Check whether the user provided in the RecoveryManager Plus Service is already in the list;

If not, right click the Logins, New Login and provide a corresponding user name. The New user must have the sysadmin server level role and database level role of db_owner.
Follow the steps to provide the sysadmin role permission:
Right click the user, click 'Properties'
Go to 'Server Roles' → Check sysadmin and click 'OK'

Note: Details about user roles: Refer the documents in the following links:

For Server Level Roles: http://msdn.microsoft.com/en-us/library/ms188659.aspx

For Database Level Roles: http://msdn.microsoft.com/en-us/library/ms189121.aspx

For additional resources: http://www.mssqlcity.com/Articles/Adm/SQL70Roles.htm

Server Role of the user should be 'sysadmin' and Database Role of the user should be 'db_owner'.

The members of sysadmin server role can perform any activity in SQL Server and have completes control over all database functions.

The members of db_owner database role can perform any activity in the database.

MS SQL Server in local computer

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

bcp.exe- <MSSQL Installation Dir>\Tools\Binn\bcp.exe
bcp.rll- <MSSQL Installation Dir>\Tools\Binn\Resources\1033\bcp.rll

MS SQL Server in remote computer

Note: Please install the corresponding SQL Native Client / Command line Utilities in the RecoveryManager Plus machine as per the MS SQL Server version and CPU type of RecoveryManager Plus machine.

MS SQL Server Version	Command line Utilities	Native Client
2008 R2	Download	Download
2012	Download	Download
2014	Download	Download
2016	Download	Not needed
2017	Download	Not needed

After installing the Command Line utilities, please copy the following files:
Copy the files to → RecoveryManager Plus Home → \bin folder.

bcp.exe → MSSQL Installation Dir → \Tools\Binn\bcp.exe
bcp.rll → MSSQL Installation Dir → \Tools\Binn\Resources\1033\bcp.rll

Windows Firewall Settings

If the Firewall is enabled in MS SQL Server machine, the TCP and UDP Ports need to be opened.

UDP Port is normally 1434.

To check the TCP Port settings open SQL Configuration Manager:

SQL Server Network Configuration → Protocols for <instances>
Right click TCP/IP → Properties → Goto IP Addresses Tab and scroll until TCP Dynamic Ports and enter the current value in your Firewall.

Migrating PostgreSQL data to Microsoft SQL

Stop the RecoveryManager Plus server.

To stop RMP if it is running as a console:

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

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

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.

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

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.
Under Connect Using options, select one of the authentication types. The options are:

Windows Authentication

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

Migrating PostgreSQL data to Microsoft SQL

SQL Server Authentication

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

Migrating PostgreSQL data to Microsoft SQL

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.
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.
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.

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.

Open Command Prompt and navigate to <Installation directory>\jre\bin folder of RecoveryManager Plus. Use the command below to associate the Jave KeyStore:

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.

Previous Topic Next Topic