Guidelines and tooling for the InVision Solution Development Cycle

by

You’ll probably need to make changes to, or continue developing InVision solutions after they have been published to production. Today, we’ll present a simple best practice pattern or guideline for continued development of InVision solutions, and the new InVision Data Migration Tool (InVision DMT for short) which helps you in this process.

The pattern

The pattern outlines the most important steps of the production -> dev -> production cycle for InVision solutions. You are not required to follow all (or any) of these steps, but we do recommend it.

  1. Development phase – adding new features and changing the current configuration
    1. Backup – Restore production databases to dev environment
    2. Do changes in dev environment
  2. When the development phase is done, test merging data from production to development
    1. Back up the dev database
    2. Using InVision DMT, merge / sync data from production to dev database to ensure everything works. If everything worked ok, restore the dev database so it gets back to its original state before test. (You can also just change the target connection string in the project config…your choice).
  3. Ready for publishing to production
    1. Schedule maintenance job with the customer
    2. Take production website offline
    3. Stop InVision Windows Services
    4. Backup production database
    5. Using InVision DMT, merge / sync data from production to dev
    6. Delete production database
    7. Backup – restore dev database to production using the original production database name
    8. Start Invision Windows Services
    9. Take production website online

InVision Data Migration Tool (InVision DMT)

InVision DMT is a simple desktop application (requires .NET 4.6 or later) that basically allows you to copy or merge data from tables in one database to another. Allowing InVision DMT to auto build the mapping (by comparing the table names in the source and destination) will get you going quickly. If you need to customize table or column mapping, you can do that as well. Once a valid configuration has been set up, you can choose to run the data migration job from the tool, or extract the complete SQL script and execute it manually or pass it on to a DBA.

Getting InVision DMT

InVision DMT can be downloaded as a .zip file from \<file server>InstallsD – SoftwareProfitBaseProfitbase InVision2.0 (or requested by contacting Profitbase) and extracted to any folder on your disk. It does currently not have a dedicated installer. Open the Shell folder and launch Profitbase.InVision.Designer.exe

Creating a new InVision DMT project

In the Home tab, select Data Migration Tool,  press New Project and provide a name. Once the project tab is open, specify the source and target connection strings. Next, press Auto Build Mapping which will create a default mapping based on the name of the tables found in the source and target. If no issues are detected, you can execute the job.

Key features

  • Auto Build Table Mapping
    Creates a default table mapping by comparing the names of the tables in the source database to ones in the target database. If you want to compare names using approximation (for example if the table names differ by the postfix token), check the “Use approximation for table name comparison” in the Settings section.
  • Add custom Table Mapping
    Allows you to manually add a mapping between a table in the source and target database.
  • Specify custom column mapping
    Double click or right click a table mapping to open the custom column mapping editor. You need to provide a custom column mapping if the columns or column data types of the source and target tables do not match, or if you want to perform a merge instead of a reload between tables without primary keys.
  • Verifying Configuration
    You can verify the configuration to check if the setup looks ok. The verification process will check if all the table and column mappings are set up correctly, but it will not check if the generated sql query for each object is going to be valid. For table mappings without a custom column mapping, column names and data types will be checked for compatibility.
  • Showing Script
    Pressing Show Script will generate the entire data migration SQL script for the project, so you can execute it manually in SSMS or some other tool.
  • Executing the job
    Pressing Execute will execute the job (obviously), running the steps in sequence. By default, if an error occur, the job will stop so you can fix the issue and executing again will start from where it left off. You can configure this behavior in the Job execution settings section.

Things to know / be aware of when executing DMT jobs

  1. When InVision DMT executes a job, it first disables all FK constraints in the database before reenabling them after the job has completed (either successfully or with failure).
  2. Transactions are not being used during execution of DMT jobs, so if a job fails, any changes will not be rolled back.

 

– The InVision Team