Assessing Online (AOL) Maintenance

The Digital Team is responsible for maintaining the ASP app and databases which drive the assessing online application.

Maintenance Cycle Checklist

December (Q3)

See tabbed notes for December (on this page) for detailed instructions.

• MSSQL: Request SQLDBA's to clone the current live database (assessingupdates20YYQ1 ) to a new database called assessingupdates20YYQ3 where YY is the current financial year.

• IIS: Update global.asa with new connection string

• IIS: Create sandbox default.asp page for testing by copying the current default.asp to default20YYQ3.asp ,

• IIS: Update the dates and tax rates in sandbox,

• IIS: Update sandbox to connect to the assessingupdates_20YYQ3 connection string,

• MSSQL: Update MS SQL assessingupdates20YYQ3 database with data from MS Access,

• MSSQL: Run any adjustment processes in assessingupdates20YYQ3 database,

• ACQUIA: Copy new forms into pdf folder at /sites/default/files/pdf_templates/pdf/FY20YY,

• AWS: (using Postman) Create a new connection the the dbconnector proxy in local/stage and prod environments which connects to the assessingupdates20YYQ3 database - note the connection token.

• IIS: Test sandbox, and re-import data as needed/provided by Assessing Team,

When ready to deploy:

• ACQUIA: Update the token in DBCONNECTOR_SETTINGS envar for the relevant environments

• IIS: Backup the current default.asp page by copying into default.asp20YYQ3.bak .

• IIS: To put the new page and data live, simply delete default.asp and then rename default20YYQ3.asp todefault.asp.

• IIS: Cleanup any remaining asp pages you may have created and any redundant databases and tables.

July (Q1)

See tabbed notes for July (on this page) for detailed instructions.

• Request SQLDBA's to clone the current live database (assessingupdates20YYQ3 ) to a new database called assessingupdates20YYQ1 where YY is the next financial year.

• Create sandbox default.asp page for testing by copying the current default.asp to default20YYQ1.asp,

• Update sandbox to connect to the assessingupdates20YYQ1 MS SQL database,

• Update MS SQL assessingupdates20YYQ1 database with data from MS Access,

• Run any needed adjustment processes in assessingupdates20YYQ1 database,

• To put the new page and data live, simply delete default.asp and then rename default20YYQ1.asp todefault.asp.

• Cleanup any remaining asp pages you may have created and any redundant databases and tables.

ASP Application

The ASP application is served by an IIS Server from zpcobweb01.web.cob.

On the Windows server hosting the IIS Server, the ASP files which make up the assessing online app can be found at d:\wwwcob\assessing.

The files which are appear to be live (i.e. have no redirect to boston.gov) and which currently live in the folder are:

Filename	Last Modified	Description
..\global.asa (wwwcob)	2022	Contains the database connection strings for the whole cityofboston.gov website. See drawer below.
paymentinfo.asp	07/2017
payments.asp	11/2015
res_prop_sales_archive.asp	03/2016	Suspect this file is not live, just an archive of res_sales_prop.asp which has not been redirected.
taxratehistory.asp	03/2016
global.asa (assessing)	08/2013	Contains connection strings which do not appear to be used.
paymentshistory\default.aspx	03/2014	Unknown, appears to be a file uploader - inserts records into the Treasury database on vsql01
search\default.asp	11/2022	This is the main script which drives the AOL application. See drawer below.
search\hotline.asp	06/2020	Unknown, looks unused.
search\json.asp	05/2016	Unknown, looks unused.

wwwcob\global.asa

This file contains connection strings for the whole cityofboston.gov website.

The strings used by Assessing on-line are found around line 450: Create a new set of Sesion variables copying the pattern from the previous quarters connection string.

'--Project Data Connection SQL
Session("assessingupdates_20YYQ3_ConnectionString") = "DRIVER=SQL Server;SERVER=VSQL01.web.cob;DATABASE=assessingupdates_20YYQ3;"
Session("assessingupdates_20YYQ3_ConnectionTimeout") = 15
Session("assessingupdates_20YYQ3_CommandTimeout") = 30
Session("assessingupdates_20YYQ3_RuntimeUserName") = "***"
Session("assessingupdates_20YYQ3_RuntimePassword") = "***"

Note:Do not use IPAddresses in connection strings. Also use the DNS for the SQL Server cluster, not the DNS entry for the actual Database server. i.e. do not use the server zpdmzsql01.web.cob instead use the cluster vsql01. The cluster will always redirect traffic to the currently active database server during maintenance or fail-over conditions.

wwwcob\assessing\search\default.asp

This file contains the assessing online search service.

There is a section titled BEGIN CONSTANTS (line 20) to END CONSTANTS (line 70). This block of code contains a number of constants. Principally these are dates, but also some tax rate information. This data is provided by the Assessing Team in Dec each year. Nothing outside of this block requires routine maintenance.

Maintenance Cycle

December (Q3)

Create sandbox ASP page for testing.

Create a copy of default.asp named default20XXQ3.asp.

Update DB connection string.

Once the SQLDBA's have cloned the current live database:

In the global.asa for the IIS server (in the root of wwwcob folder) there are a number of database connection strings defined. Search for assessingupdates and you will find a block defining the assessing-online apps connection strings. Make a new connection string called assessingupdates_20YYQ3 and point it at the new database created by the DBA's.

The constant for AssessingSearchDB in (line 58 of) default20YYQ3.asp indicates the actual connection string for the page to use. Change it to assessingupdates_20YYQ3 (which will then run SQL queries on the page in the assessingupdates20YYQ3 database in the MSSQL Server).

Make changes to dates and tax rates for next year.

Using data provided by the Assessing Team, update the constants in default20YYQ3.asp. Refer to the notes in the default.asp page next to each constant for guidance.

Testing/verification of the new data can now be completed at: https://cityofboston.gov/assessing/search/default20YYQ3.asp

The page rendered by this script should have a banner indicating this is still a test page along with info on the date being simulated (see below) and the connection string being used.

This banner will automatically be removed when the page is deployed (i.e. when the file is renamed default.asp)

While testing, you can add &date=YYYY-MM-DD to the URL to test the site for a future date and see that the date fields and the availability of exemption forms links are altering on the page as expected.

July (Q1)

Create sandbox ASP page for testing.

Create a copy of default.asp named default20YYQ1.asp.

Update DB connection string.

Once the SQLDBA's have cloned the current live database:

In the global.asa for the IIS server (in the root of wwwcob folder) there are a number of database connection strings defined. Search for assessingupdates and you will find a block defining the assessing-online apps connection strings. Make a new connection string called assessingupdates20YYQ1 and point it at the new database created by the DBA's.

The constant for AssessingSearchDB in (line 56 of) default20YYQ1.asp indicates the actual connection string for the page to use. Change it to assessingupdates20YYQ1 (which will then run SQL queries on the page in the assessingupdates20YYQ1 database in the MSSQL Server).

Testing/verification of the new data can now be completed at: https://cityofboston.gov/assessing/search/default20YYQ1.asp

Typically the dates in the form do not change, and the tax rate should also not change. Really, the only expected change to default20YYQ1.asp is to change the DB connection string name.

Assessing Forms

There are a number of forms which are provided as part of the assessment process during the first part of each calendar year.

PDF's are generated by a service on boston.gov so the templates for these forms are saved in docroot/sites/default/files/pdf_templates/pdf/FY20YY on the boston.gov webserver.

When a constituent requests a form, it is processed through the bos_assessing managed endpoint at boston.gov/assessing-online/ This endpoint adds a barcode (related to the parcelid) and selected tax bill information onto the form.

See Assessing Form generation notes here, and more detailed notes on PDF generation here.

Various assessing forms are made available to constituents based on a calendar. The calendar is set in the constants fields in wwwcob\assessing\search\default.asp (see box above).

Once those dates are set, the form display is controlled by code and no further action is needed to enable or disable them.

PDF files can be modified and copied to the boston.gov web server using this command (from the docroot folder):

Non-Prod Environments

rsync -arz -essh -P sites/default/files/pdf_templates/pdf/FY20YY/* "[e-mail]:/mnt/gfs/bostond8dev/sites/default/files/pdf_templates/pdf/FY20YY/"

This command copies to the dev environment. All other Acquia non-prod environments map this folder, so running this command simultaneously copies across for all non-prod environments.

Prod Environment

rsync -arz -essh -P sites/default/files/pdf_templates/pdf/FY20YY/* "[e-mail]:/mnt/gfs/bostond8/sites/default/files/pdf_templates/pdf/FY20YY/"

Maintenance Cycle

December (Q3)

Each year new forms for exemptions and other request are generated and will be provided by the Assessing Team.

• Ensure dates (AbatementExemptionStartThisYear, AbatementDeadlineThisYear and ExemptionDeadlineThisYear) are set correctly in wwwcob\assessing\search\default20YYQ3.asp

• Upload the forms (typically 4) to a new folder at docroot/sites/default/files/pdf_templates/pdf/FY20xx.

• Update the the json and fdf files in the same folder.

July (Q1)

There is nothing to be done with forms in the July updates.

MSSQL Database

The database which contains the data for the assessing online app is contained onvsql01 (aka zpdmzsql01, vsql02, zrb01(?)). This may well be migrated to vsql71 at some point.

The assessing department validate and transfer data to the Digital Team via an MS Access database. This database contains tables of data that should be mapped and uploaded to databases on the MS SQLServer.

MSAccess data is imported into tables prefixed "_" in the MSSQL Database.

Stored procedures are then written to update/merge data from the import tables into the main tables used by the app. The stored procedure is saved in the DB as a record of the updates and manipulations performed.

There is a bash script on the cityofboston IIS server (zpcobweb01) at /wwwcob/assessing/search/extract.sh which migrates and pre-processes the data from MSAccess to the MS SQL Server. In a perfect world, this script can just be run to preform the migration (it takes some hours to complete).

Maintenance Cycle

December (Q3)

Obtain the updated copy of the MS Access database.

In the assessingupdates20YYQ3 database, delete all tables prefixed "_", these are import tables from the previous Q1 update.

Following these instructions, use the extract.sh script to copy tables from Access to assessingupdates20YYQ3 in MS SQL.

Notes from Assessing Team will detail any particular instructions on data manipulations that are required.

To keep a record and for replaying updates, and data updates (from import tables to main tables) and data manipulations should be coded into stored procedures and executed from the stored procedure. The stored procedure should be saved in the DB, and named sp_20YYQ3_import. If multiple sp's are used, then number them sequentially (sp_20YYQ3_import_1 etc).

For example, in 2023Q3 there is a (incorrectly named...) stored procedure sp_update_current_owners which should be executed to make additional data changes to parcel/property ownership.

Testing/verification of the new data can then be completed at: https://cityofboston.gov/assessing/search/default20YYQ3.asp

July (Q1)

Obtain the updated copy of the MS Access database.

In the assessingupdates20YYQ1 database, delete all tables prefixed "_", these are import tables from the previous Q3 update.

Following these instructions, copy tables from Access to assessingupdates20YYQ1 in MS SQL.

Notes from Assessing Team will detail any particular instructions on data manipulations that are required.

To keep a record and for replaying updates, and data updates (from import tables to main tables) and data manipulations should be coded into stored procedures and executed from the stored procedure. The stored procedure should be saved in the DB, and named sp_20YYQ1_import. If multiple sp's are used, then number them sequentially (sp_20YYQ1_import_1 etc).