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

Password Manager Pro allows you to use Amazon RDS MS SQL as a backend database. Amazon RDS makes it effortless to set-up a relational database in the cloud and also allows you to migrate all your data from the local MS SQL database to the Amazon RDS MS SQL database with ease.

Advantages

Amazon's RDS MS SQL database is a cloud database that you can access from any part of the world at any given time. Amazon's RDS MS SQL database has some great set of features such as dynamic scaling, single click high availability, automated backups, etc. You can also create DB instances and DB snapshots, point-in-time restores, and automated or manual backups with Amazon RDS.

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 Password Manager Pro.

Steps Required

Steps to migrate local MS SQL data to Amazon RDS MS SQL data are as follows:

  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 as SQLSERVER_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. Now, rename the below files under PMP\conf directory:
    • 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
    • pmp_key.key to pmp_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 pmp_key.key files from local instance to the EC2 instance.
  10. Download and replace the below files under PMP\conf directory.
    • customer-config.xml
    • database_params.conf
    • masterkey.key
  11. Open database_param.conf file.
    • Replace <%DNS_NAME%> with MS SQL instance name.
    • Replace <database name> with the encrypted database name.
    • Replace Username and Password.
    • change.db.password.encrypted=false
  12. Download MS SQL root CA.
  13. Import a .pem file into Password Manager Pro using the following command:
    1. Open command prompt and navigate to <PMP_Home>\jre\bin.
    2. Execute the following command:

      'keytool.exe -import -v -alias <alias name> -file <certificate path> -keystore  ..\lib\security\cacerts -keypass changeit -storepass changeit -noprompt'

  14. Start the Password Manager Pro service.

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