Deploying LYNX History Tables for Colleague UniData

The Series25 LYNX Interface uses a database integration to connect to any student information system which is hosted locally on an institution's servers.

The integration has two parts:

  1. A series of custom tables that track historical changes to SIS data
  2. The LYNX-APP, a lightweight Java application that exchanges data between those tables and the hosted Series25 database

You will be creating a new database to hold these tables. This article explains how to deploy the LYNX Interface scripts necessary for database integration with Colleague UniData. You should perform this step after configuring your Colleague UniData environment for LYNX.

 Once the scripts are in place, you will be ready to configure CNColleagueLYNXLink to connect this database with Colleague and Install the LYNX-APP to connect it with your hosted Series25 environment.

Technical Requirements

  • Microsoft SQL database version 2012+

Ensure that this is a new database on a SQL Server instance that does not have your SIS database located within it. This can be a SQL Server instance that already has other databases on it.

Do not run the scripts against the "master" or another Microsoft-delivered database.

Download the SQL Scripts Package

  1. Find the CNColleagueLYNXLink_v4.zip folder you downloaded earlier.
  2. Extract the contents of folder ~\CNColleagueLYNXLink_v4.zip\MS_SQL_Schema\ to get the latest version of the following scripts:

The subdirectory contains the following scripts (with .sql extension). Four (A, B, C, F) are "creation" scripts while the remaining two (D, E) are "quality control" scripts.

Script

Name

Purpose

Aa_uni_sis_mssql_security.sql

Creates database user to provide access to LYNX database tables for the LYNX-APP

Bb_uni_sis_mssql_schema.sql

Creates database tables, sequences, and triggers owned by user created in the A script

Cc_uni_sis_mssql_routines.sql

Creates database functions and procedures owned by user created in the A script

Dd_uni_sis_mssql_schema_qc.sqlChecks that LYNX database is deployed properly
Ee_uni_sis_mssql_data_qc.sql

Returns count values for which LYNX data tables are populated

Ff_col_sis_mssql_unidata.sql

Creates staging tables which will stage data between Colleague (UniData) and LYNX staging tables

Creates procedures that will perform regular cleanup of staging tables

Deploy the SQL Scripts

Follow this procedure to properly deploy the scripts in your MSS database.

Prepare the LYNX Username and Password

These scripts create and reference a user which provides access to the LYNX application. This is a new user. (Do not re-use credentials for an existing user.)

Before running any scripts, choose a username and password for this new user. You will use these values to update the scripts.

  1. Open the A script (a_uni_sis_mssql_security.sql).
    1. Search for 'DB_USERNAME'.
    2. Update LYNX_USER to your chosen username.
    3. Update LYNX_USER_PASS to your chosen password.
    4. Save changes to the A script after making these updates.
  2. Open the B script (b_uni_sis_mssql_schema.sql).
    1. Update LYNX_USER to the chosen username.
    2. Save changes to the B script.
  3. Open the C script (c_uni_sis_mssql_routines.sql).
    1. Update LYNX_USER to the chosen username,
    2. Save changes to the C script.
  4. Open the F script (f_col_sis_mssql_unidata.sql).
    1. Update LYNX_User to the chosen username,
    2. Save changes to the F script.

Run the Creation Scripts

You are now ready to run the A, B, C, and F scripts which create objects in your MSS database.

  1. Log in to the database (on the database server) as an administrative user.
  2. Run the updated A script within the SQL Server database to create the LYNX user/schema and set permissions.
  3. Log in to the database (on the database server) as the newly created LYNX user.
  4. Run the updated B script within the SQL Server database.
  5. Run the updated C script within the SQL Server database.
  6. Run the updated F script within the SQL Server database.

Run the Quality Control Scripts

CollegeNET provides two scripts that perform a quick check to see if the LYNX database is deployed properly. These are included in the scripts package as D and E.

After running the creation scripts, run the D and E scripts and resolve any reported errors. If you encounter anything you're not sure how to fix, email your assigned CollegeNET Technical Analyst for assistance.