Installation Procedure

Introduction

The migration scripts are very long running based on the database size and number of records in the tables. To migrate the data within the down time window, scripts are categorized as:

  • Preinstallation Scripts
  • Installation Scripts

Preinstallation Scripts

All customers currently using CampusVue Student version 16.0.X or earlier should follow these preinstallation instructions. 

The preinstallation scripts should be executed a few days before the actual upgrade, preferably during non-peak hours. Anthology Inc. recommends running them one (1) week before the actual upgrade. The scripts should be executed in the order listed below.

  • TFS0193057-00-SyPersonMigration-Objects.sql
  • TFS0193057-01-SyPerson_Stage-Update.sql

The scripts create jobs and will be executed in the background. The default batch size is 100,000. The batch size can be modified if any performance issue occurs while executing the scripts.

The above scripts insert records into the following new tables and perform data migration to new tables:

  • SyPerson
  • SyPersonEmail
  • SyPersonAddress
  • SyPersonPhoneNumber

Installation Scripts

The following scripts are executed in the first step of the installation. The scripts create jobs in the background and will be executed as part of the installation. Manual intervention is not required.

  • TFS0193057-02-SyPerson_Delta-Processing.sql
  • TFS0193057-03-Person_DDL.sql
  • TFS0193057-04-SyPerson-Update.sql
  • TFS0193057-05-SyPersonFinal-Update.sql

Preinstallation Steps

Note: All customers currently utilizing version 16.0.X or earlier should follow these preinstallation instructions. 

Depending on size of the CampusVue Student database, preinstallation step may run into extended period of time. So it is advised to determine the preinstallation time during upgrade testing. Based on the test upgrade outcome, determine the time to start the preinstallation script. It is recommended to execute the installation during non-peak hours.

  1. Copy the script TFS0193057-00-SyPersonMigration-Objects.sql in SQL Management Studio (SSMS) and select the correct production database.

  2. Click Execute.

    The following objects are created as part of the script:

    • dbo.tblPersonMigration_Errors
    • usp_PersonMigration_SyPerson_PlEmployerContact
    • usp_PersonMigration_SyPerson_Stag_PlEmployerContact
    • usp_PersonMigration_SyPerson_Stag_SyOrganizationContact
    • usp_PersonMigration_SyPerson_Stag_SyStaff
    • usp_PersonMigration_SyPerson_Stag_SyStudent
    • usp_PersonMigration_SyPerson_SyOrganozationContact
    • usp_PersonMigration_SyPerson_SyStaff
    • usp_PersonMigration_SyPerson_SyStudent
    • usp_PersonMigration_SyPersonAddress_PlEmployerContact
    • usp_PersonMigration_SyPersonAddress_SyAddress
    • usp_PersonMigration_SyPersonAddress_SyOrganizationContact
    • usp_PersonMigration_SyPersonAddress_SyStaff
    • usp_PersonMigration_SyPersonAddress_SyStudent
    • usp_PersonMigration_SyPersonEmail_PlEmployerContact
    • usp_PersonMigration_SyPersonEmail_SyOrganizationContact
    • usp_PersonMigration_SyPersonEmail_SyStaff
    • usp_PersonMigration_SyPersonEmail_SyStudent
    • usp_PersonMigration_SyPersonPhoneNumber_PlEmployerContact
    • usp_PersonMigration_SyPersonPhoneNumber_SyOrganizationContact
    • usp_PersonMigration_SyPersonPhoneNumber_SyStaff
    • usp_PersonMigration_SyPersonPhoneNumber_SyStudent
    • usp_PersonMigration_SyRegistry_InsUpd

  3. On successful execution of step 2, copy/open the script TFS0193057-01-SyPerson_Stage-Update.sql and make sure it is connected to correct database.

  4. Click Execute.

    The following jobs are created and executed:

    Job created

  5. Monitor the jobs.

  6. If the jobs are creating a performance issue, try to lower the batch size by executing the following statement. Change the highlighted section to a smaller number based on the server performance.

    Batch size

  7. Monitor the jobs.

    1. Expand SQL Server Agent in SQL Server Management Studio.

      Job Activity Monitor

    2. Double-click Job Activity Monitor.

      If any exceptions occurred, the job icons are displayed as shown below:

      Icons on monitored jobs

    3. If there is any exception in the script logic, the error message will be recorded in the tblPersonMigration_Errors table.

      Logged exception

    4. Progress of the script can be also monitored from the registry key.

      Registry key

    5. If all the jobs are executed successfully, verify the registry key. The RegValue for all keys should be 1.

      RegValue=1

  8. If all jobs are completed successfully, execute following statement on the publisher database (before install):


    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'Nk_SyPersonAddress_RecordId_CVueSourceTypeId' and object_id = object_id('SyPersonAddress'))
	DROP INDEX SyPersonAddress.Nk_SyPersonAddress_RecordId_CVueSourceTypeId


IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'Nk_SyPersonAddress_RecordId_CVueSourceTypeId' and object_id = object_id('SyPersonAddress'))
	CREATE NONCLUSTERED INDEX [Nk_SyPersonAddress_RecordId_CVueSourceTypeId] ON [dbo].[SyPersonAddress]
	(
		[RecordId] ASC,
		[CVueSourceTypeId] ASC
	)
	INCLUDE (
		  StreetAddress
		, City
		, [State]
		, Zip
		, YearsAtAddress
		, EffectiveBeginDate
		, EffectiveEndDate
		)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = on, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [C2000_Index]
	GO

  9. On Successful completion of the script, proceed with the installation steps.

Installation Steps

  1. Start the regular installation through the installer.

    This logic covers two parts:

    1. Handle delta records

    2. Handle updating the SyPersonId in the following tables:

      • SyStudent
      • SyStaff
      • SyOrganizationContact
      • PlEmployerContact

  2. The following additional jobs will be created.

    • C2K (<DBName>) Processing SyPersonAddress Delta(Person Centric Logic)
    • C2K (<DBName>) Processing SyPersonEmail Delta(Person Centric Logic)
    • C2K (<DBName>) Processing SyPersonPhoneNumber Delta(Person Centric Logic)
    • C2K (<DBName>) Processing SyStaff(Person Centric Logic)
    • C2K (<DBName>) Processing SyStudent(Person Centric Logic)
    • C2K (<DBName>) Processing PlEmployerContact(Person Centric Logic)
    • C2K (<DBName>) Processing SyOrganizationContact(Person Centric Logic)

    Additional Jobs

  3. Monitor the jobs.

    1. Expand SQL Server Agent in SQL Server Management Studio (SSMS).

      Job Activity Monitor

    2. Double-click the Job Activity Monitor.

      If exceptions occur, the job icons are displayed as shown below:

      Job Status icons

    3. If an exception occurs in the script, the error message will be recorded in the tblPersonMigration_Errors table. Use the following statement to find error messages.

      Find errors

    4. Monitor the progress of the script from the registry key.

      Find errors in Registry

    5. If all jobs are executed successfully, verify the registry key. The RegValue for all keys should be updated with ‘1’.

  4. If all jobs are completed successfully, the installer will proceed with the rest of the database installation.