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/A | N/A | Admin users will have the Update Database button on the Login page and the Update SQL Databases link in the menu when:
|
| 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:
- Run the normal Aeries Web update process using the Aeries Updater for both Aeries Web and Aeries Reporting
- Navigate to the database update page at <Web Site Domain>/adminupdatesql.aspx or log into Aeries and click on the Update Database button
- Sign into the Database Server using an account with db_owner permissions
- Select one or more databases from the list
- 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:
- Aeries Web was updated
- There were changes to the database structure associated with the update
- Aeries is locally hosted (i.e., self-hosted)
- The user is logging in with Admin rights
- 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
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.
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. NOTE: This is no longer a required step, as the update process now handles the necessary functions.
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.
- 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.
- 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.
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.NM | Description |
|---|---|
| DB_LastUpdatedUserName | The 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_LastUpdatedDate | The date/time of the last update - only populated if the update is successful |
| DB_LastMigrationScriptsHash | The 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_LastSprintApplied | The 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_LastSprintHash | The 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_LastWorkItemApplied | The 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_LastWorkItemHash | The 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_VersionApplied | The 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).