Jump to: navigation, search

SpeechMiner 8.5.3 to 8.5.4 Upgrade Procedure

This document explains how to upgrade SpeechMiner from version 8.5.3 to version 8.5.4

Pre-upgrade Requirements

  • Request the most recent release of the 8.5.4 software from your Genesys representative.
  • Request the SpeechMiner license from Genesys Licensing.
  • Verify that the following are installed:
  • 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.3, keep the 8.5.3 DB active on the server, rather than just keeping a backup file.

Do not delete the 8.5.3 data folders (index, grammars, etc.). Instead, configure the 8.5.4 system with new data folders. Create the following new folders to ensure that you will not lose 8.5.3 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.3 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.3. The 8.5.3 system should still be configured and functional.

After you uninstall SpeechMiner 8.5.3 and install SpeechMiner 8.5.4, the only way to revert back to 8.5.3 is to install 8.5.3 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.

Important
To improve the encryption algorithm it is recommended that you change the Audio Encryption password. For details, see the Using the SMConfig to Configure SpeechMiner section in the Administration Guide.

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.3 system.
  2. Create a copy of the source DB and upgrade it to the latest build:
    • The source DB must be in build 136. Refer to the versionTbl table to determine the correct version.
    • If you have a 145 build you must make the following change to the SMUpgrade.exe.config:
      add key="TableDataFile" value=".\TableData.xml" / should be changed to add key="TableDataFile" value=".\TableData_From145.xml" /.
      Edit TableData_From145.xml. Change <ToBuild>8.5.400.0</ToBuild> to <ToBuild>8.5.400.xxx</ToBuild>.
      Note: xxx stands for the current 8.5.4 build. The exact number can be copied from TableData.xml.
    • If you have a build that is not 136 or 145, 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.3 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.4 upgrade.

    1. Back up the 8.5.3 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.3 build version. For this step you will need assistance from Customer Care.
  3. Create the 8.5.4 target DB as follows: Or
  4. 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
  5. Verify that your system does not contain duplicate interactions:
    1. Run the following query to find duplicate interactions:

      SELECT externalID, Count (*)
      FROM callMetaTbl
      Group BY externalID
      Having count (*) > 1
    2. If duplicate interactions were found, run the following query to remove the duplication. The interaction with the lowest Call ID will remain in the system.

      DECLARE @ids VARCHAR(max)
      DECLARE @deletedCallsNum int
      with x as (select *, rn= ROW_NUMBER()
      over (PARTITION BY externalID order by callid)
      from callMetaTbl)

      SELECT * into #duplications from x
      WHERE callid NOT IN
      (SELECT MIN(callId) FROM x)
      and
      rn > 1

      While exists (Select * From #duplications)
      begin
      set @ids = ''
      SELECT * into #duplicationsToDelete from (select top (1000) * from #duplications) as y
      SELECT @ids = @ids + ', ' + CONVERT(varchar(10), callid) from #duplicationsToDelete
      set @ids =Stuff(@ids, 1, 2, '')
       delete from indexq where resourceId in (select callid from #duplicationsToDelete)
      insert into indexq select callid,2,null,1,calltime,1 from #duplicationsToDelete

      exec sp_deleteCallsAndGetTheirPath @ids, @deletedCallsNum OUTPUT
      delete from #duplications where exists (select 1 from #duplicationsToDelete dtd where #duplications.callid = dtd.callid)
      drop table #duplicationsToDelete
      end
      drop table #duplications
  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. Open the TableData.xml file and replace Table Name="callRecognizer" DoneIfSizeIsEqual="false" with the following:
        <Table Name="callRecognizer" DoneIfSizeIsEqual="false" ><br>
      <AddedColumns><br>
        <Column Name="FolderWaitTimeMinutes" Value="60" /><br>
      </AddedColumns>
    </Table>


  9. Install and run SMUpgrade (to migrate the data from the 8.5.3 DB to the 8.5.4 DB), as follows:

    Prerequisites:

    • When migrating a large database, make sure that the hard drive that hosts the target database has enough storage space.
    • Verify that you are working with English US localization settings. You can not upgrade using localization settings that are not English US.

    Usage

    1. Query the versionTbl table to ensure that your 8.5.3 source database is updated to the latest 8.5.3 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.3 source and 8.5.4 destination databases.
      The databases that appear in the old databases drop down list include ver8_5_3 in their file name. The databases in the new databases drop down list, include ver8_5_4 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.
    7. Click Full Upgrade to run the upgrade, or Resume Last if your previous upgrade was interrupted.

      8. 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.

    8. Continue with the upgrade instructions below.
  10. 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.
  11. Optional: Uninstall 8.5.3 from all servers. The two versions (8.5.3 and 8.5.4) 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.
  12. Install the 8.5.4 platform on all servers.
  13. Install 8.5.4 Web on the Web server.
  14. Install 8.5.4 SMART on users' desktops, as required.
  15. 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.4 database.
  16. Update the package folders with the 8.5.4 .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.
  17. Manually copy the files in <Installation Folder>\Support\Grammars\Confidence to the Global Packages folder.
  18. 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. In the Reports panel, update the MRSLibrary.dll on the report server.
    5. Deploy the reports to the report server.
    6. 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.
    7. Using SMConfig, start the UPlatform services on all the servers.
    8. Update the Stored Procedures by copying any existing custom Stored Procedures from the 8.5.3 DB to the 8.5.4 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.
    9. Open the SpeechMiner web-based interface and test the functionality.
    10. Update the Database Jobs:
      • All database jobs that point to the 8.5.3 DB should be changed to point to the new 8.5.4 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.
    11. In the SpeechMiner web-based interface, manually reschedule 8.5.3 reports that should continue to run on a scheduled basis.
This page was last edited on April 11, 2018, at 11:30.
Comments or questions about this documentation? Contact us for support!