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:
- A series of custom tables that track historical changes to SIS data
- 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.
- 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
- Find the CNColleagueLYNXLink_v4.zip folder you downloaded earlier.
- 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.
Creates database user to provide access to LYNX database tables for the LYNX-APP
Creates database tables, sequences, and triggers owned by user created in the A script
Creates database functions and procedures owned by user created in the A script
|D||d_uni_sis_mssql_schema_qc.sql||Checks that LYNX database is deployed properly|
Returns count values for which LYNX data tables are populated
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.
- Open the A script (a_uni_sis_mssql_security.sql).
- Search for 'DB_USERNAME'.
- Update LYNX_USER to your chosen username.
- Update LYNX_USER_PASS to your chosen password.
- Save changes to the A script after making these updates.
- Open the B script (b_uni_sis_mssql_schema.sql).
- Update LYNX_USER to the chosen username.
- Save changes to the B script.
- Open the C script (c_uni_sis_mssql_routines.sql).
- Update LYNX_USER to the chosen username,
- Save changes to the C script.
- Open the F script (f_col_sis_mssql_unidata.sql).
- Update LYNX_User to the chosen username,
- 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.
- Log in to the database (on the database server) as an administrative user.
- Run the updated A script within the SQL Server database to create the LYNX user/schema and set permissions.
- Log in to the database (on the database server) as the newly created LYNX user.
- Run the updated B script within the SQL Server database.
- Run the updated C script within the SQL Server database.
- 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, e-mail your assigned CollegeNET Technical Analyst for assistance.