Security

Routine Database Update Process

Accessing the Update Aeries SQL Databases Page

Update Aeries SQL Databases - Connect to a Database

Update Aeries Databases - Databases to Update


Overview  

The Update Aeries SQL Databases process replaces the database update function of Aeries AdminCS. It is only available to self-hosted clients. This process allows an Admin user to update the SQL database structure to the current version for one or more of their databases from Aeries web.


NOTE:  All self-hosted customers will be prompted to run the Update Databases process for all their Aeries databases when this feature is initially released. This is because the new process is looking for new records in the District Options Table (DPT) that will not exist until it is run at least once. It is recommended to run the Update Databases process to create these records.




NOTE: 
- To update a database using the new process a database must have been first updated through the February 8, 2022 AdminCS update.
- Once the new update process is used to update a database, AdminCS will no longer be necessary to update that database in the future.
- The new update process is only available to self-hosted customers. Hosted customers will have their updates applied as a scheduled service provided by Aeries.



Security  

Table
Permission
Description
N/AN/AAdmin users will have the Update Database button on the Login page and the Update SQL Databases link in the menu when:
  1. an Aeries update includes changes to the database structure
  2. Aeries is locally hosted
  3. Aeries is accessed via localhost on the server, or through an HTTPS connection on a local computer


The Admin will need to log into the Update Aeries SQL Databases page with a SQL Login that uses SQL Server Authentication and db_owner permissions in order to gain access to the SQL databases.



Routine Database Update Process

As updates to Aeries Web are made available, often they are accompanied by updates/migrations to the Aeries database. To apply these updates the following process can be followed:

  1. Run the normal Aeries Web update process using the Aeries Updater for both Aeries Web and Aeries Reporting
  2. Navigate to the database update page at <Web Site Domain>/adminupdatesql.aspx or log into Aeries and click on the Update Database button
  3. Sign into the Database Server using an account with db_owner permissions
  4. Select one or more databases from the list
  5. Click Update Selected Databases

See below for more detailed information on the pages and the update process.



Accessing the Update Aeries SQL Databases Page

The Login page will include an Update Database button and the menu will have an Update Aeries SQL Databases link when the appropriate conditions are met:

  1. Aeries Web was updated
  2. There were changes to the database structure associated with the update
  3. Aeries is locally hosted (i.e., self-hosted)
  4. The user is logging in with Admin rights
  5. The user is accessing Aeries as localhost on the Aeries server or is connected to Aeries with HTTPS on their local computer


Once the database administrator has run the Aeries Web update, they should immediately log into Aeries to perform any necessary SQL database updates before their users log in. We recommend that the Admin use the Update Database button on the Aeries Login page, or if they are working on the Aeries Server they can access the page directly without logging in by going to  localhost/your_Aeries_instance/AdminUpdateSql.aspx


Update Database button and Update SQL Databases link


Using localhost on the Aeries server to access the Update Aeries SQL Databases page directly

 

The Update Database button and the Update SQL Databases link in the menu will disappear once the SQL update has completed. If the Admin only updated the database they are currently logged into rather than all prior databases, they can still access the Update Aeries SQL Databases page by adjusting the url to    your_Aeries_url/AdminUpdateSql.aspx or by logging into a prior year database that hasn't already been updated and use the Update Database button.




Update Aeries SQL Databases - Connect to a Database  

Once the Admin reaches the Update Aeries SQL Databases page they will need to log into the database server using a SQL Login with SQL Server Authentication that has db_owner permissions to the databases. 


The Database Server dropdown will include all servers that are identified in the AeriesNetConnections.config file. In the below example the AeriesNetConnections.config file includes databases on two different servers.


Update Aeries SQL Databases - Connect to a Database - Database Server dropdown


Updates can only be performed on one server at a time. If multiple servers need to be updated the Admin will need to log into one server, perform the SQL updates on the desired databases, log out of the Update Aeries SQL Databases - Databases to Update page, and then log into the next server and repeat the update process with the databases on that server.


Click on the Go back to Aeries Portal button to return to the Aeries Home page.


Update Aeries Databases - Databases to Update  

The Update Aeries SQL Databases - Databases to Update lists all of the databases that are in the AeriesNetConnections.config file for the currently connected server. 

To list all databases on the database server rather than limiting to just the ones in the AeresNetConnections.config file add this setting to the AppSettings.config file:

<LimitDBUpdateToConfig>False</LimitDBUpdateToConfig>




NOTE:  Databases that are Offline, Detached or in Read-Only mode on the SQL server will not appear in the list of databases.


Log Out button - click on the Log Out button to go back to the Update Aeries SQL Databases - Connect to a Database page. From the Connect to a Database page the admin can connect to a different database server or go back to the Aeries portal. 


SQL Update File Status - lists the status of the SQL Update package.


Verbose Mode - when the Verbose Mode option is checked, the Full Server Debug Message will display after the updates have completed. Currently, the following information will be included for each database:

  • The Database Name
  • Time is took to complete the update
  • Any SQL error messages
  • All of the migration scripts that were applied to the database. Also, the completion email will include the migration scripts that were applied to the database.


Force An Update - This option will force all available SQL update scripts to run against the selected databases instead of starting from the last known state.


Update Aeries SQL Databases - Databases to Update - Verbose Mode example


Databases to Update - displays the following information for the databases that are attached to the currently connected server:

  • Year - Academic Year of the database
  • Database Name
  • Last Updated - when the last SQL update was applied. This value will come from either Location Data (LOC.D1) if AdminCS was the last method used, or from District Options Table (DPT) if Aeries Web was last used to update the database structure.
  • Details- provides information about the last SQL Update.
    • Updated via AdminCS -  will display if the last SQL update was performed using AdminCS
    • Version Applied - the last SQL update performed using Aeries Web
    • Applied by User - the Aeries Admin that ran the update
    • Various Status Messages and any Error Messages that may occur.
      • Databases that display a red AdminCS Update Required message in the Details column are not updated to the baseline February 8, 2022 version required in order to use this process. Clicking on the red message and then OK in the pop-up will open up a page to the login page for Aeries.com where you can log in and download the most recent AdminCS update. Apply the AdminCS update to get the database to the baseline SQL database structure, and then run the Update Aeries SQL Databases process in Aeries to bring it to the most recent SQL structure.
        Update Aeries SQL Databases - Databases to Update - AdminCS Update Required example

      • Databases that display a red New Database message in the Details column must have a baseline version of SQLModel.SQL run against them before using the Update Aeries Databases process. Clicking on the red message will bring up a pop-up message with a link to the New Year Rollover page where the SQLModel.SQL can be downloaded. See  New Year Rollover Processing and Reports for more information.
        Update Aeries SQL Databases - Databases to Update - New Database example


The grid allow for sorting and filtering to limit the list of databases. 


Click on the databases to select them, or use the Select All button to select all displayed databases. 


Click on the Update Selected Databases button to begin the update process. A confirmation message will come up listing all of the databases that will be updated. Click on OK to initiate the process. 

A yellow status box will come up indicating that the updates are processing. The Update Selected Databases button will not be available until the updates have completed.


As the process runs, the following information is stored in the District Options Table (DPT) for each database that is updated:

DPT.NMDescription
DB_LastUpdatedUserNameThe Aeries username of the user running the process (not to be confused with the SQL DBO login) - only populated if the update is successful
DB_LastUpdatedDateThe date/time of the last update - only populated if the update is successful
DB_LastMigrationScriptsHashThe MD5 hash of ALL of the scripts in the entire SprintSQL file the last time this database was successfully updated - only populated if the update is successful
DB_LastSprintAppliedThe sprint number of the last script that was applied - will be populated even for partial updates, which will allow the update to start from the point of failure next time
DB_LastSprintHashThe MD5 hash of all the scripts for the last sprint that was successfully applied - will be populated even for partial updates, which will allow the update to start from the point of failure next time
DB_LastWorkItemAppliedThe Work Item number of the last script that was applied - will be populated even for partial updates, which will allow the update to start from the point of failure next time
DB_LastWorkItemHashThe MD5 hash of the scripts for the last work item that was successfully applied - will be populated even for partial updates, which will allow the update to start from the point of failure next time
DB_VersionAppliedThe current Aeries version that was used to run the update (e.g., 9.22.3.31) - only populated if the update is successful


In addition, the Location Data (LOC.D1) will be populated with the Aeries Version date for each database that successfully updates. For example, for the 9.22.3.31 version , the date will be 3/31/2022.


A completion email is also generated when the long running process has completed updating all of the databases. Information specific to the DB update process will be included in the message body.  This information will be included per database that was selected to update:

  • The name of the database
  • If the verbose option was checked at the time the process was started, any verbose messages will be included.
  • If there were any errors, the error message will be included.
  • The Time Elapsed (ms) in updating the database
  • If the verbose option was checked at the time the process was started, then all of the migration scripts that were applied will be included.


If a database encounters a SQL error during the update no further scripts will be run against that database.  However, other databases will not be affected by an error occurring for another database (i.e., other databases can still be updated successfully after a prior database encounters a SQL error).