Jump to: navigation, search

SpeechMiner 8.5.2 to 8.5.3 Upgrade Procedure

This document explains how to upgrade SpeechMiner from version 8.5.2 to version 8.5.3a

Pre-upgrade Requirements

 
  • When upgrading to a later build for the same version, SMUpgrade is not required. Contact Genesys Customer Care to verify if you need an SQL script for the specific upgrade.

Upgrade Checklist

The following checklist summarizes all the procedures required for upgrading SpeechMiner.Make sure to complete all of the required procedures.

Item to Check Details
Storage Requirements To successfully complete the upgrade process, the data partition in the SQL server must have available disk space. The minumum required storage for the upgrade should be twice the size of the production database .mdf file.
Perform Pre-upgrade Tests Since upgrading SpeechMiner can take several hours it is important to avoid delays. For this reason, it is recommended to test SMUpgrade before you begin the upgrade procedure.

To test SMUpgrade, perform one of the following:

  • Create a copy of the database and send it to your Genesys counterpart. Genesys will test the SMUpgrade process in a lab environment.
  • Create a copy of the database on a separate SQL server. Provide Genesys with the details of the SQL server and Genesys will test the SMUpgrade process on this temporary server.
Check for Customization If any customizations were implemented on your DB, make sure that they are part of the new version, or that they can be used in the new version without changes. Contact Genesys Customer Care for assistance.
Purging Old Data Most systems have a data retention policy in place. Data (for example, audio,exploration data, etc.) that is older than the specified period of time is automatically deleted. If you do not have a data retention policy in place, it is recommended to determine what data should be saved and what can be discarded, since deleting the data before the upgrade will reduce both the time it takes to run the upgrade process and the storage-space requirements.

Rollback Plan

To ensure that you can revert back to SpeechMiner 8.5.2, keep the 8.5.2 DB active on the server, rather than just keeping a backup file.

Do not delete the 8.5.2 data folders (index, grammars, etc.). Instead, configure the 8.5.3 system with new data folders. Create the following new folders to ensure that you will not lose 8.5.2 data:

  • Create the following empty folders:
    • Input
    • Interaction Receiver Input
    • Filtered
    • Index (this folder will be populated during the upgrade procedure).
  • Copy the content of the following existing folders to new folders with the same name:
    • Store
    • Grammar
    • Backup

For detailed information about the folders you should create, refer to Required Folders

Since the 8.5.2 DB and data folders are saved and available, back-out steps are not required if problems arise with the upgrade process before you uninstall 8.5.2. The 8.5.2 system should still be configured and functional.

After you uninstall SpeechMiner 8.5.2 and install SpeechMiner 8.5.3, the only way to revert back to 8.5.2 is to install 8.5.2 again and update the config files using SMConfig. However, since the DB and data folders would not have been deleted, they should be available and ready to use without changing the system configuration.

Upgrade Procedure

The following table lists the approximate times required to complete the upgrade steps:

Step Time
Stop the system (step 1) 15 minutes
Back up the database (step 2) 120 minutes
Create target database (step 3) 30 minutes
Run SMUpgrade (steps 7 to 14) 10 to 20 hours
Configure and start the system (steps 15 to end) 60 minutes


  1. Using SMConfig->Services->Stop Services, stop the 8.5.2 system.
  2. Create a copy of the source DB and upgrade it to the latest build:
    The source DB must be in build 8.5.201. Refer to the versionTbl table to determine the correct version.
    If you have a build that is later than 8.5.201.257, contact Genesys Customer Care.
    If the source DB is not the latest build and you do not want to update it to the latest build, create a copy of the source DB and update the copy to the latest build.
    Important
    These steps are necessary because the 8.5.2 DB schema needs to be updated to the latest schema in order for the rest of the upgrade process to succeed.

    Use the copy of the source DB as the baseline for the 8.5.3 upgrade.

    1. Back up the 8.5.2 index folder to a backup folder (see Configuring SpeechMiner—Index).
    2. Create a copy of the source DB (back up the DB and then restore it in another location).
    3. Configure the copy of the DB with the Index backup. This will be the baseline for the upgrade.
    4. Implement the schema changes on the baseline DB to bring the schema into line with the latest 8.5.2 build version. For this step you will need assistance from Customer Care.
    1. Using SMConfig->Services->Stop Services, stop the 8.5.2 system.
    2. Create a copy of the source DB and upgrade it to the latest build:
      The source DB must be in build 8.5.201. Refer to the versionTbl table to determine the correct version.
      If you have a build that is later than 8.5.201.257, contact Genesys Customer Care.
      If the source DB is not the latest build and you do not want to update it to the latest build, create a copy of the source DB and update the copy to the latest build.
      Important
      These steps are necessary because the 8.5.2 DB schema needs to be updated to the latest schema in order for the rest of the upgrade process to succeed.
      Use the copy of the source DB as the baseline for the 8.5.3 upgrade.
      1. Back up the 8.5.2 index folder to a backup folder (see Configuring SpeechMiner—Index).
      2. Create a copy of the source DB (back up the DB and then restore it in another location).
      3. Configure the copy of the DB with the Index backup. This will be the baseline for the upgrade.
      4. Implement the schema changes on the baseline DB to bring the schema into line with the latest 8.5.2 build version. For this step you will need assistance from Customer Care.


     
  3. Create the 8.5.3 target DB as follows: Or
  4. Run the following query on the new database, before running SMUpgrade:
    declare @QMRole int
    select @QMRole=roleId from rolesTbl where
    roleName='QM Manager'
    insert into rolesTbl select 20,roleName,internalRole,protectedRole,grantRoles,createdOn,createdBy,lastUpdate from rolesTbl where roleId=@QMRole
    update rolesTbl set grantRoles=REPLACE(grantRoles,CAST(@QMRole AS varchar(10)),'20')
    update rolePermissionsTbl set role=20 where role=@QMRole
    delete rolesTbl where roleId=@QMRole
  5. Run the following query to create the index ix_originalTime in the TextData table:

    ALTER TABLE [dbo].[TextStatus] DROP CONSTRAINT [FK_TextStatus_TextData]
    GO
    ALTER TABLE [dbo].[TextData] DROP CONSTRAINT [PK_TextDataTbl]
    GO
    ALTER TABLE [dbo].[TextData] ADD CONSTRAINT [PK_TextDataTbl] PRIMARY KEY NONCLUSTERED
    (
    [textId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[TextStatus] WITH NOCHECK ADD CONSTRAINT [FK_TextStatus_TextData] FOREIGN KEY([textId])
    REFERENCES [dbo].[TextData] ([textId])
    GO
    ALTER TABLE [dbo].[TextStatus] NOCHECK CONSTRAINT [FK_TextStatus_TextData]
    GO
    CREATE CLUSTERED INDEX [IX_originalTime] ON [dbo].[TextData]
    (
    [originalTime] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
  6. If the MS-SQL server is an Enterprise Edition, run EXEC sp_create_DB_storage_partitions on the target database.
  7. If your source and target databases are on different servers, make sure the servers are linked in both directions, using the stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin, as needed.
  8. Install and run SMUpgrade (to migrate the data from the 8.5.2 DB to the 8.5.3 DB), as follows:

    Prerequisites:

    • When migrating a large database, make sure that the hard drive that hosts the target database has enough storage space.

    Usage

    1. Query the versionTbl table to ensure that your 8.5.2 source database is updated to the latest 8.5.2 schema.
    2. Verify that your recovery model is either Simple or Bulk-logged. To determine which recovery model you have, right click db > properties > options > recovery model.
    3. Use the SpeechMiner Installer to install the SMUpgrade component. It is recommended to install and run the SMUpgrade component on the SQL server.
    4. Configure the following in the \utopy\tools\bin\release\SMUpgrade.exe.config file:
      • file locations
      • tables to skip (comma separated list)
      • number of threads running concurrently on a large table
      • bulk copy usage

      It is recommended that the bulk-load folder be located on the SQL server. The bulk-load folder must be shared and the user running the SQL server service must have full control over it.
      If you do not set the bulk copy usage, the callAudioTbl upgrade will take up to two times longer on a large DB.
      Only use the skip-tables configuration if specifically requested by Genesys Customer Care.

       <appSettings>
      <add key="ErrorLogFile" value=".\SMUpgradeLog.txt" />
      <add key="LogFile" value=".\tableLog.txt" >
      <add key="TimingsFile" value=".\tableTimings.txt" />
      <add key="SkipTables" value="callTasksTbl" />
      <add key="NumThreads" value="10" />
      <!-- <add key="bulkpath" value="[PATH]" /> -->
      </appSettings>
    5. Run SMUpgrade.exe.
      Log in and select the appropriate 8.5.2 source and 8.5.3 destination databases.
      The databases that appear in the old databases drop down list include ver8_5_2 in their file name. The databases in the new databases drop down list, include ver8_5_3 in their file name. You can also type relevant databases that are named differently.
      Important
      It is highly recommended to use the sa credentials or a user account with bulk insert permissions.
      The user account must belong to the db_owner role in the target database. By default, the DBUser does not include the db_owner role.
    6. The GUI shades the tables as follows:
      • Green—The table is finished. Both the source and the target DBs contain the same number of records. This conclusively shows that either the data has been copied or there is no data to copy.
      • Yellow—The number of records in the source and target DBs is not indicative of whether the data in both DBs is identical or not. It is therefore not known whether the table is finished or not.
      • Red—The table is not finished. The number of records in the source and target databases are not the same, and indicating that the data has not been copied in full.
      • Click Full Upgrade to run the upgrade, or Resume Last if your previous upgrade was interrupted.
      • Resuming the last upgrade will shorten the time to run, but might cause problems. To avoid such problems, restore the database again and run the full upgrade. You can also define tables to skip in the configuration file. Every step in the upgrade process is shown in the GUI.

        You can stop the upgrade by clicking the Close button. You will be prompted to confirm your action. Note that the window closes immediately, but the process still runs for a while, as it needs to re-enable the indexes it disables when it starts running.
        The time each step took is written to the TimingsFile. The location of this file is defined in the configuration file.
        IN CASE OF FAILURE: Review all status, error and exception notifications in the ErrorLogFile.

      • Continue with the upgrade instructions below.
      • If the SpeechMiner Maintenance Job exists, and the Update time table step is included, delete the Update time table step. Make sure the last step in the job is set to quit the job upon both success and failure.
      • Optional: Uninstall 8.5.2 from all servers. The two versions (8.5.2 and 8.5.3) cannot be running side by side at the same time. Only one version can be registered as the active SpeechMiner service on each server. The installation binaries can be left on the server.
      • Update Microsoft .NET Framework.
      • Update Microsoft C++ Redistributable.
      • Install the 8.5.3 platform on all servers.
      • Install 8.5.3 Web on the Web server.
      • Install 8.5.3 SMART on users' desktops, as required.
      • Deploy SQLCLR on the DB server. Using SQL management, run the commands that are in the C:\Program Files (x86)\Genesys\Software\Support\sqlclr.sql script in the SpeechMiner 8.5.3 database.
      • Update the package folders with the 8.5.3 .gram files. The .gram files are located in the <Installation Folder>/Support/Grammars.
        Alternatively, if you have not made changes to any file in these folders, you can delete their content completely. SMConfig will copy the grammar files to <Installation folder>/Support/Grammars.
      • Manually copy the files in <Installation Folder>\Support\Grammars\Confidence to the Global Packages folder.
      • Run SMConfig.
        Important
        If your target database was restored from a backup file, you may need to "fix" an orphan dbuser. To do this, simply run EXEC sp_change_users_login 'Auto_Fix', 'dbuser'.
        1. Configure the Sites & Machines, panel as necessary, and save the changes. Make sure you save this panel even if you have not made any changes.
        2. Configure the Services panel and save the changes. Do not start any of the services.
        3. Configure the Index panel and save the changes.
        4. Update the SpeechMiner License with the new 8.5.2 licenses provided by Genesys Licensing.
        5. In the Reports panel, update the MRSLibrary.dll on the report server.
        6. Deploy the reports to the report server.
        7. Start SMART and perform the following:
          1. Right-click on each active Program icon and choose Activate program.
          2. Run the following SQL code in order to force apply of all the topics:
            update smartTopicTbl set saveDate = dbo.time2tod(GETUTCDATE()) where version = 0
          3. Click the Apply button.
          4. In the new Apply popup window, choose Apply all.
          5. Click the Apply button.

          6. Using SMConfig, start the UPlatform services on all the servers.
          7. Update the Stored Procedures by copying any existing custom Stored Procedures from the 8.5.2 DB to the 8.5.3 DB.
            It is not necessary to copy Stored Procedures that are used by gauges, and are in the GaugeWidgetProcedures table, because they are copied automatically.
          8. Open the SpeechMiner web-based interface and test the functionality.
          9. Update the Database Jobs:
            • All database jobs that point to the 8.5.2 DB should be changed to point to the new 8.5.3 DB. Examples of DB jobs that might need to be changed:
              • DB maintenance job
              • sp_agentFilterCleanByDays
              • sp_updateUntilYesterdayMaxChannels
            To change a DB job, we recommend that you edit the Job Step property using the SQL Management studio.
          10. In the SpeechMiner web-based interface, manually reschedule 8.5.2 reports that should continue to run on a scheduled basis.
This page was last edited on July 12, 2018, at 17:09.
blog comments powered by Disqus