Skip to main content

Reveal Review Publication

Instance to Instance Project Migration (v10.1)

The version 10.1 migration tool will use the User_Management DB from Instance 1 and User_Management DB from Instance 2 to copy user and project info and then update the project DB with the newly assigned User IDs.

  1. Backup the source User_Management DB from which you are copying the project, and restore onto the SQL server where the destination User_Management DB to which you are copying the project resides. Name the restore something unique to not impact DBs on the server.

    Note

    The source and destination user management databases must contain the text "User_Management" (case sensitive) in the name or they will not be recognized by the migration tool. You may need to temporarily rename to migrate.

  2. Backup the project DBs to move and restore on the SQL server of the destination User_Management DB. Project names should be unique on that server so you should not need to rename.

  3. The migration tool can reside either directly on the DB Server or load server with access to the DB Server. The server where the migration tool runs must have .NET 4.8.

  4. The user running the tool must have the following permissions to the SQL User_Management databases and project database being migrated:

    1. Add "Alter" permission to source UM, target UM, and any projects DB that need to be migrated.

      Reason: Required to create dbo.migration table in source UM and add temp fields/indexes to projects DBs.

    2. Add “Execute” permission to stored procedure Reveal_UserManager_GetSystemSettings in source and target UM databases.

      Reason: We use this to lookup the system settings to get SSO values. Migration tool will not open without this.

    3. Add “Execute” permission to stored procedure Reveal_UserManager_GetCaseInfo to source and target UM databases.

      Reason: We use this to lookup the projects in the source and target UM databases. Migration tool will not load the projects without this.

    4. Add db_datareader and db_datawriter to source UM and target UM and projects DB that need to be migrated.

      Reason: We need to be able to run select and update statements against these DBs.

    Note

    The user server role must be “public” and the user must have basic “Connect” permission to the UM and project databases.

  5. The config file for the migration tool is in the Debug folder, along with the executable file: D:\CaseMigrationTool_10.1_with_Keycloak_V2\bin\x64\Debug.

  6. The only changes you need to make to the config is location of log files and DB Server info. The User_Management DB name may need to change if named differently for a specific instance.

    image2.png

    Example in the screenshot:

    connectionString="Data Source=DBSERVER;Initial Catalog=User_Management

    This will need to be a legitimate User_Management db on the db server. If you put in a db that doesn't exist the tool will fail and log:

    [Error] Cannot open database "_User_Management_I_Dont_Exist" requested by the login. The login failed.

    If you enter a non-UMDB db it will fail and log:

    [Error] Could not find stored procedure 'Reveal_UserManager_GetSystemSettings'.

  7. The migration tool will update User IDs in the Doc_Action_Log table; you will need to drop the two table triggers. Run this query to the project DBs being migrated:

    DROP TRIGGER [dbo].[TG_UPDATE_DOCUMENT_ACTION_LOG]

    DROP TRIGGER [dbo].[TG_DOCUMENT_ACTION_LOG]

  8. You can now run the migration tool. The EXE is in the Debug folder.

    Choose the Source User_Management DB and destination User_Management DB.

    image3.png

  9. Hit the Migrate button to begin. Often you will have the same user in the source and destination DB; if you do then a popup warning message will appear before the migration begins. If you continue with the migration the users that are already in the destination DB will be kept as is.

    image4.png

  10. Clicking OK will start the migration. The User_Management DB info moves initially and then the project DBs are updated. The log section shows the progress.

    image5.png

  11. When migration completes you get a message. You can check logs for the UMDB or project DBs that were migrated.

    image6.png

  12. When migration completes, run this query against the migrated project DB to restore the triggers.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [dbo].[TG_DOCUMENT_ACTION_LOG]

    ON [dbo].[DOCUMENT_ACTION_LOG]

    INSTEAD OF DELETE

    AS

    BEGIN

    IF @@rowcount > 0

    BEGIN

    RAISERROR( 'Document action log entries cannot be deleted', 18, 1 )

    ROLLBACK

    END

    END

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [dbo].[TG_UPDATE_DOCUMENT_ACTION_LOG]

    ON [dbo].[DOCUMENT_ACTION_LOG]

    INSTEAD OF UPDATE

    AS

    BEGIN

    IF UPDATE(Log_Id) OR UPDATE(item_id) OR UPDATE(Document_Action_Type_Id) OR UPDATE(Document_Action_Text)

    OR UPDATE(Document_Action_Created_For) OR UPDATE(Document_Action_Date) OR UPDATE(Document_Action_Created_By)

    OR UPDATE(Tag_Id) OR UPDATE(Is_Issue_Tag) OR UPDATE(IsBulk) OR UPDATE(Folder_Id) OR UPDATE(Details)

    OR UPDATE(Assignment_Job_Id)

    BEGIN

    RAISERROR( 'Document action log entries cannot be updated except for reports and ai_reported fields', 18, 1 )

    ROLLBACK

    END

    else

    BEGIN

    UPDATE document_action_log

    SET

    AI_Reported = INSERTED.AI_Reported,

    Reported = inserted.reported

    FROM INSERTED

    WHERE INSERTED.log_id = DOCUMENT_ACTION_LOG.log_id

    END

    END

  13. Lastly, run validation on the migrated projects in the new instance.

In this section: