Migrating PAM360 Database from Local MS SQL to Amazon RDS MS SQL Database

PAM360 uses PostgreSQL as the default backend database, and it comes bundled with the product. However, you can migrate from the on-premise database into an on-demand service using Amazon RDS. With this, you can migrate all your data from the local MS SQL database to the Amazon RDS MS SQL database with ease.

Amazon Web Services (AWS) Relational Database Service (RDS) is a managed service that makes it easy to set up, operate, and scale a relational database in the cloud. It supports several database engines, including Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and Microsoft SQL Server.

Benefits of using Amazon RDS Instance

This document explains in detail the process of migrating your data from the local MS SQL database to the Amazon RDS MS SQL database in PAM360.

Migrating PAM360 Database from MS SQL to Amazon RDS MS SQL

The following are the high-level steps to migrate data from local MS SQL to Amazon RDS MS SQL. Perform them on the respective with the required administrator/superuser privileges.

  1. Create a S3 bucket and store the Local SQL backup (.bak) file.
  2. Navigate to RDS >> Option Groups >> Add Options and create a RDS Option named asSQLSERVER_BACKUP_RESTORE.
  3. Leave the IAM Role field as default.
  4. Modify the RDS Instance and change the Database Options >> Option Group to the newly created SQLSERVER_BACKUP_RESTORE.
  5. Click Apply changes immediately to save the DB changes.
  6. Connect RDS SQL Instance through EC2 windows instance and execute the following rds_restore_database stored procedure to Restore DB:
    exec msdb.dbo.rds_restore_database
    @restore_db_name='database_name',
    @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name.extension';
  7. Execute the following query after a successful restoration:
    use write_the_name_of the restored_database;
    OPEN MASTER KEY DECRYPTION BY PASSWORD='type_the_master_key_password';
  8. Make copies of the following files under <PAM360 Installation Directory>\conf directory and rename them:
    • wrapper.conf to wrapper.conf_old
    • wrapper_lin.conf to wrapper_lin.conf_old
    • wrapper_lin.conf to wrapper_lin.conf_old
    • wrapper_lin.conf_mssql to wrapper_lin.conf
    • pam360_key.key to pam360_key.key_old
    • database_params.conf to database_params.conf_old
    • customer-config.xml to customer-config.xml_old
    • customer-config.xml_mssql to customer-config.xml
  9. Replace the masterkey.key and pam360_key.key files from local instance to the EC2 instance.
  10. Download and replace the below files under <PAM360 Installation Directory>\conf directory.
    • customer-config.xml
    • database_params.conf
    • masterkey.key
  11. From the <PAM360 Installation Directory>\conf folder, open the database_params.conf file in a text editor and perform the following actions:
    • Replace the <%DNS_NAME%> with the MS SQL instance name.
    • Replace <database name> with the encrypted database name.
    • Update the Username and Password with the RDS MS SQL instance detail.
    • Search for db.password.encrypted=true and change it as db.password.encrypted=false.
    • Now, save and close the database_params.conf file.
  12. Download the RDS MS SQL RDS region's root CA certificate.
  13. Navigate to the <PAM360 Installation Directory>\jre\bin folder using the command prompt and execute the following command to import the .pem file into PAM360:
    'keytool.exe -import -v -alias <alias name> -file <certificate path> -keystore  ..\lib\security\cacerts -keypass changeit -storepass changeit -noprompt'
  14. Start the PAM360 service.

You have now successfully migrated data from Local MS SQL Database to Amazon RDS MS SQL Database.





Top