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

PAM360 allows you to use Amazon RDS MS SQL as a backend database. Amazon RDS makes it simple and easy to set-up a relational database in the cloud. You can migrate all your data from the local MS SQL database to Amazon RDS MS SQL database with ease.

Advantages

The main advantage of using a cloud database is that you will be able to access it 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 PAM360.

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 PAM360\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
    • 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\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 PAM360 using the following command:
    1. Open command prompt and navigate to <PAM360_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 PAM360 service.

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