Need help/direction for Migrating existing IdentityIQ instance from MSSQL to RDS/MySQL DB

IdentityIQ (IIQ) IIQ Discussion and Questions
3

Hello, @jimcpl1000

Welcome to the SailPoint community! It’s great that you’re taking on this new challenge with enthusiasm. Given your background with other identity management systems, you’ll find some familiar concepts, but there will also be unique aspects to SailPoint IdentityIQ (IIQ).

Recommendation

Given the small amount of data currently in your system and your newness to SailPoint, I recommend starting fresh with the MySQL database on AWS RDS rather than attempting a full migration from MS SQL Server.

Rationale

  1. Simplicity: Migrating databases across different database management systems (from MS SQL Server to MySQL) can be complex and error-prone due to differences in SQL dialects, data types, and indexing mechanisms.
  2. Clean Slate: Starting anew allows you to set up the database optimized for MySQL from the ground up, avoiding any legacy issues or misconfigurations carried over from the previous setup.
  3. Learning Opportunity: Setting up the database and configuring SailPoint IIQ will give you valuable hands-on experience, which is especially beneficial since you’re new to the platform.
  4. Minimal Data Loss: With only a few users and configurations, the amount of data to re-enter is manageable.

How to Proceed with Re-Creating the Schema and Reconfiguring SailPoint IIQ

Step 1: Prepare the AWS RDS MySQL Database

  • Create the Database Instance: Ensure your AWS RDS MySQL instance is up and running. Note the endpoint, port, username, and password.
  • Security Groups: Configure AWS security groups to allow connections from your SailPoint UI and Task machines.

Step 2: Install the MySQL JDBC Driver

  • Download the Driver: Obtain the MySQL Connector/J from the official MySQL website.
  • Place the Driver: Copy the JDBC driver JAR file into the WEB-INF/lib directory of your SailPoint IIQ installation. Since you’re using a Podman container, you’ll need to ensure the driver is included in the container’s classpath.

Step 3: Configure SailPoint IIQ to Use MySQL

  • Modify iiq.properties: Edit the iiq.properties file to point to your new MySQL database.
database.driver=com.mysql.cj.jdbc.Driver
database.url=jdbc:mysql://<RDS_ENDPOINT>:3306/<DATABASE_NAME>?useSSL=false&serverTimezone=UTC
database.username=<DB_USERNAME>
database.password=<DB_PASSWORD>
  • Adjust Connection Pool Settings: Ensure the connection pool settings are appropriate for MySQL.

Step 4: Initialize the Database Schema

  • Use SailPoint’s Scripts: SailPoint provides SQL scripts for database initialization, located in the WEB-INF/database directory.
  • Run the Scripts:
    • Create Tables: Execute create_identityiq_tables.mysql.
mysql -h <RDS_ENDPOINT> -P 3306 -u <DB_USERNAME> -p<DATABASE_NAME> < create_identityiq_tables.mysql

Step 5: Start SailPoint IIQ

  • Restart the Application: If it’s already running, restart your SailPoint IIQ application so it picks up the new database configuration.
  • Monitor Logs: Check the SailPoint logs to ensure there are no errors connecting to the MySQL database.

Step 6: Re-Populate Data

  • Manual Entry: Since the data volume is low, manually recreate the users and configurations via the SailPoint IIQ UI.
  • Import Data (Optional):
    • From Files: If you have exports or backups in XML or CSV format, you can use SailPoint’s import utilities.
    • Scripts and Workflows: Re-deploy any custom scripts or workflows you might have.

Step 7: Verify Functionality

  • Testing: Perform thorough testing to ensure all functionalities are working as expected with the new database.

Alternative: Full Migration (Not Recommended in This Case)

If you decide that a full migration is necessary, here’s an outline of how you might approach it:

  1. Export Data from MS SQL Server:
  • Use SQL Server Management Studio to generate scripts for both schema and data.
  1. Convert Scripts for MySQL:
  • Adjust data types and syntax to be compatible with MySQL.
  • Tools like SQLines or commercial solutions can assist with this process.
  1. Import into MySQL:
  • Run the converted scripts against your MySQL database.
  1. Update SailPoint Configuration:
  • Modify iiq.properties as described earlier.
  1. Test the Migration:
  • Start SailPoint IIQ and verify that all data is intact and functional.

Challenges with Full Migration:

  • Data Type Differences: MS SQL Server and MySQL have different data types, which can cause issues during migration.
  • Syntax Variations: SQL scripts may need significant adjustments to run correctly in MySQL.
  • Time-Consuming: The process can be lengthy and may not be justified given the small amount of data.

Additional Tips

  • Backup Everything: Before making changes, back up your current SailPoint IIQ configurations and any custom code.
  • Documentation: Refer to the SailPoint IdentityIQ Installation and Administration Guide for detailed instructions, especially regarding database configurations.
1 Like