Upgrading through the Advance 20/20 Direct SQL Program

The Advance 20/20 Direct SQL Program is a transitionary and temporary step to assist the self-hosted/on premise or traditionally-hosted clients in getting to the latest version of iMIS EMS Enterprise. Although the Advance 20/20 Direct SQL Program is cloud-based, it includes direct access to the iMIS database via SQL Server Management Studio (SSMS), so clients and partners can reconfigure and remove existing non-native iMIS objects (customizations) before transitioning to iMIS EMS.

In This ArticleIn this article

Program purpose

iMIS EMS, like all modern cloud products, is a living product with constant internal changes and improvements. Perhaps the number one customer client stability and upgrade reliability problems in the past have had their roots in non-ASI database customizations. Through the tools native in iMIS, we have many supported ways to customize its use, no longer risking incompatible, unsupported internal customizations.

The alternative to temporary direct SQL access is the iMIS API. The API is robust and stable, and even as we make internal changes and improvements, it provides a stable supported approach to the iMIS data.

Notes: This program is the replacement for both the Advance 20/20 Legacy Program and the Large iMIS EMS Client Data Conversion Policies.

Joining the program

Because this program is designed and intended for clients with special transitional needs, you must meet one of the following options to join the program:

  • Clients who are hosted on-prem with EMS that would like to start the transition to the cloud
  • Clients with a customization that cannot be dropped without disrupting a fundamental business need

To start the process of joining the program, you must first engage with your AiSP/reseller as this program requires you work alongside them. Then, contact ASI Orders and your CPIL where you will sign the iMIS Direct SQL Access Rider. This agreement outlines our enforced policies for the program.

Program specifics

Once the iMIS Direct SQL Access Rider is signed, ASI Cloud Services will start installing your instance of iMIS.

Environment structure

  • Your iMIS website will reside on a shared server.
    • There is no direct access to the shared web/app server.
    • You will have a staging (also known as a development or test) website before launching your website. That website will reside on a shared web/app with other staging websites.
    • Once you are ready to “go-live” with your production website, it will reside on a shared production web/app server. This is separate from the staging web/app server.
  • Your iMIS database will reside on a shared Azure SQL server with other clients in the program.
    • You will be provided SQL access to your production and staging databases.
    • You are limited to three authorized users (not a shared login) to have access to their specific SQL database. This access ensures clients and/or partners can reconfigure and remove existing customizations.
    • The authorized SQL user will retain read/write permissions.
    • To access your database, authorized users must have SQL Server Management Studio (SSMS) locally installed. ASI Cloud Services will work with you to allow access from the authorized user’s IP to the Azure SQL server.

Policies

  • Direct SQL Access may be used only to:
    • Remove customizations and stored procedures
    • Eliminate third-party products and solutions that are not cloud-friendly
    • Address other system issues that may be preventing you from moving to iMIS EMS
  • SQL Access will be removed within (12) months – meaning, you have graduated from the program and are within the iMIS EMS environment.
  • Your staging (also known as a development or test) website will be removed within 12 months. If you have graduated from the program and are within the iMIS EMS environment, you can retain this website but at an additional cost.
  • You are not permitted to add customizations to the database using or otherwise creating additional obstacles to your move to standard iMIS EMS.
  • All customizations will be dropped when graduating from the program and moving to the iMIS EMS environment.
  • All use of the REST APIs must be managed or used so that such use does not impact the performance or reliability of iMIS EMS.
  • If you do not graduate from the program within 12 months, your license will expire. You will need to contact ASI Cloud Services.

Processes

  • The Advance 20/20 Direct SQL Program upgrade schedule is performed every other release. When graduating to the cloud environment, clients are upgraded every release.

Common questions

  • What do I need to know about existing customizations? The following are important notes about customizations:
    • ASI Cloud Services releases updates on a regular basis that could potentially break preexisting customizations, and the client is responsible for resolving these issues.
    • Although you can upgrade with preexisting customizations, you must continually work toward replacing or dropping all customizations and non-cloud-friendly integrations.
    • No new customizations (i.e. triggers, tables, and views) can be added to the database once it has been transferred to the full- cloud hosted environment.
    • Use features like Process Automation, Panel Designer, and the iMIS API to replace customizations.
  • What version of iMIS does my current database need to be on? You must be on iMIS 2017 with at least Service Pack P applied; however, it is highly recommended that you be on the latest service pack.
  • What payment gateway can I use? Global Payments and Stripe are the only allowed gateways.
  • What should I do if I have a self-hosted DataVault? Please inform Cloud Services if you have a self-hosted DataVault.
  • Can I run Crystal Reports? Although Crystal Reports can be used in this environment, it is required that all necessary Crystal Reports are recreated using IQA or Report Writer, since Crystal Reports cannot be used in full-cloud iMIS EMS.
  • Can I still use iBO or SOA? No, the REST API must be utilized (no integration through iBO or SOA).
  • Do I still have access to the iMIS Desktop or iMIS TaskCentre? No, there is no access to Desktop or TaskCentre.

Upgrade path

The following outlines how to upgrade to iMIS EMS through the Advance 20/20 Direct SQL Program:

  1. Contact your CPIL and ask that they work with ASI Orders to enter a ticket to Cloud Services requesting to be added to ASI's hosted environment and start the Advance 20/20 Direct SQL Program upgrade.
  2. Run the Upgrade Scripts.
  3. Client and AiSP or ASI Client Success discuss and analyze the results of the scripts. They determine which customizations they need, how they can replace them, and any other details surrounding the customizations. Any customizations not being used should simply be dropped.

    4. Tip! Color code the output document to easily see which are actively being used (GREEN), which you are unsure about (YELLOW), and which are not being used anymore (RED).

  4. Complete the pre-upgrade tasks.
  5. Gather the following items:
    1. BAK file
    2. KEK file
    3. Website app themes
    4. Images

      5. Notes: If the iMIS 2017 site is already in the Cloud Plus or traditional environment hosted by ASI, provide the name or location of the database to be copied.

    Important! You must have these items ready before the upgrade can proceed.

  6. Provide first and last names of up to three users who will need SQL access via SSMS, along with their email addresses and the IP addresses they will be connecting from.
  7. Cloud Services does the following:
    1. Provide a secure link where you can upload the files (unless already in the Cloud Plus or Traditional environment hosted by ASI).
    2. Move the backup and all associated files to the dedicated testing location
    3. Review live database to ensure it's on at least Service Pack P; however, it is highly recommended that you be on the latest service pack
    4. Upgrade the backup (this could take up to seven business days)
    5. Setup SQL user accounts for access to the database.
  8. Client is to register and onboard with the appropriate gateway:

Performing the tests

During the testing phase, the client, AiSP, or ASI Client Success should complete the following:

  1. Test all third-party applications and integrations. The test site is available while testing and final stages of application integration are completed by the AiSP or ASI Client Success.
  2. The client/AiSP/ASI Client Success is to follow a test plan to review all areas of change.

    3. Important! Any changes made in the test site do not automatically transfer over on a live upgrade. Keep detailed notes about the changes you make in the test environment, so that you can make the same changes after the live upgrade. Items such as content records, queries, and business objects can be manually exported from test and imported into the live system.

  3. The client works with the AiSP or ASI Client Success to reconfigure and eliminate customizations.
  4. Once customizations have been replaced through reconfiguration or out-of-the-box iMIS functionality, the client should prepare to move to iMIS EMS.
  5. The iMIS EMS Checklist is completed, signed, and AiSP or ASI Client Success notifies Cloud Services that all necessary testing has been completed.

Upgrading live

After all third-party applications and integrations are successfully tested, the following steps remain to go-live:

  1. AiSP or ASI Client Success notifies Cloud Services that all necessary testing has been completed and that they are ready to schedule their production upgrade.

    2. Notes: It is recommended that a ticket be submitted to Cloud Services six to eight weeks in advance of the anticipated upgrade date.

  2. ASI Cloud Services will work with AiSP to schedule a date for the upgrade.

    3. Notes: It is recommended the live upgrade is scheduled on a Monday or another day earlier in the week. This ensures you have available resources if something important occurs. Additionally, schedule around any PTO, holidays, big events, or long weekends.

  3. The client's live site is upgraded on the agreed upon date and time.

Next steps

After the production upgrade is complete, the following happens:

  • Your staging site will be removed two weeks after the release of the production site. Clients or AiSP should contact Cloud Services if the staging site is needed past this date.
  • AiSP or ASI Client Success is responsible for reconnecting any third-party application to the live site.
  • Complete the post-upgrade tasks.
  • Set up the gateway in the live site:

Upgrading to full-cloud iMIS EMS

Those who have been through the iMIS Advance 20/20 Direct SQL Program must take the following steps to upgrade to iMIS EMS:

  1. Contact your AiSP or Client Success and let them know that you are ready to take the final step in the upgrade process. Ask that they work with ASI Orders to confirm proper licensing and contracts are in place for an iMIS EMS Enterprise site. Once confirmed, ASI Orders will enter a ticket to Cloud Services requesting the iMIS EMS Enterprise test site be created.
  2. Cloud Services creates the test site and gives the client, AiSP, or ASI Client Success the link to the iMIS EMS test site.
  3. Testing begins and is either completed by the partner and/or client.

    4. Notes: The ASI Upgrade Program Team is available for consultations throughout the upgrade testing phase with the AiSP or ASI Client Success as needed. Please submit a support ticket requesting to talk to the upgrade program team.

  4. The iMIS EMS Checklist is completed, signed, and AiSP or ASI Client Success notifies Cloud Services that all necessary testing has been completed.
  5. A production upgrade date is scheduled after the client finishes their testing.

    6. Notes: Contact ASI Hosting at least six to eight weeks in advance of the desired upgrade date. It is recommended the live upgrade is scheduled on a Monday or another day earlier in the week. This ensures you have available resources if something important occurs. Additionally, schedule around any PTO, holidays, big events, or long weekends.

  6. The client's live site is upgraded on the agreed upon date and time.