Deployment‐Server - NWFWMD-IT/Hydrologic-Monitoring-Project GitHub Wiki

This page describes the workflow for deploying the Hydrologic Field Survey (Hydro Survey) solution.

Database

System Requirements

Client

The computer used to deploy the database components of the Hydro Survey solution requires the following software:

Component	Description
ArcGIS Pro 3.0.1+	ArcGIS Pro provides both the core Python software and the ArcGIS specific functionality (e.g. `arcpy` module) required to create a geodatabase and deploy the Hydro Survey data model. ArcGIS Pro must be installed for use by all users on the computer. (Note that ArcGIS Server can also provide the required software, but configuration is different. These instructions describe how to use ArcGIS Pro to complete the deployment process.)
SQL Server Management Studio	SQL Server Management Studio (SSMS) allows connecting to the SQL Server instance that will host the Hydro Survey geodatabase and running SQL statements to create and configure that database. Any version of SQL Server that is supported by the target SQL Server instance is also supported here.
Microsoft ODBC Driver for SQL Server	This ODBC driver allows ArcGIS Pro (and Python programs that use its environment) to connect to the target SQL Server instance. Reference the ArcGIS documentation for a list of supported ODBC driver versions.
Hydro Survey source code	A copy of this GitHub repository (repo) must be available on your local computer, or on a shared filesystem that is accessible from your local computer. In pathnames below, the prefix `...\` represents the path to the top-level directory containing your local copy of the repo.

Server

The Hydro Survey solution is designed to run on the NWFWMD ArcGIS Enterprise 10.8.1 infrastructure. The database server that hosts the Hydro Survey content includes the following software:

Component Description

SQL Server 2019 A SQL Server 2019 instance must already exist in order to host the new hydro database that will be deployed using the instructions below.

ArcGIS Enterprise 10.8.1+ No components of the ArcGIS Enterprise base deployment need to be installed on the database server. The client computer used to deploy the Hydro Survey data model, however, will use ArcGIS Pro to create the required enterprise geodatabase components (e.g. system tables, stored procedures) within the new hydro database in ArcGIS Enterprise 10.8.1 (or newer) format. (The ArcGIS Pro 3.0.1 software use to enable the enterprise geodatabase uses ArcGIS 11.0 format. Per the documentation, this format is compatible with the existing NWFWMD ArcGIS Enterprise 10.8.1 applications.)

Component	Description
SQL Server 2019	A SQL Server 2019 instance must already exist in order to host the new `hydro` database that will be deployed using the instructions below.
ArcGIS Enterprise 10.8.1+	No components of the ArcGIS Enterprise base deployment need to be installed on the database server. The client computer used to deploy the Hydro Survey data model, however, will use ArcGIS Pro to create the required enterprise geodatabase components (e.g. system tables, stored procedures) within the new `hydro` database in ArcGIS Enterprise 10.8.1 (or newer) format. (The ArcGIS Pro 3.0.1 software use to enable the enterprise geodatabase uses ArcGIS 11.0 format. Per the documentation, this format is compatible with the existing NWFWMD ArcGIS Enterprise 10.8.1 applications.)

NOTE: All databases hosted by the same SQL Server instance must have unique names. Therefore, if a database named hydro already exists, you must either drop it before performing the deployment workflow described below, or choose a different SQL Server instance. Dropping a database will cause the loss of all of its content. This may be acceptable for development purposes, but likely not for a production database. The deployment workflow below assumes that a hydro database either does not yet exist, or can be safely dropped.

Active Directory

The Hydro Survey enterprise geodatabase (hydro) uses Windows authentication to control access to its content. Users in the hydro database are based on Windows users in the NWFWMD HQ Active Directory domain. The following domain users must exist, and you must know their passwords, prior to configuring the Hydro Survey solution:

User	Description
`HQ\hydro`	Hydro Survey data owner
`HQ\mapserver`	Web service user
`HQ\sde`	Geodatabase administrator

Workflow

Deploying the database that will store the Hydro Survey content comprises three primary tasks:

Create a SQL Server database
Enable the SQL Server database as an ArcGIS geodatabase
Create the Hydro Survey data model

Create SQL Server Database

Firstly, start SQL Server Management Studio (SSMS) and connect to the target SQL Server instance.

Log on to a computer that meets the client system requirements described above
Start SSMS
Connect the target SQL Server instance as a user with sysadmin permissions

Each SQL Server instance can only have one database named hydro. If a hydro database already exists, you can drop it and recreate an empty copy using the steps below. Alternatively, you must choose a different SQL Server instance that does not already contain a hydro database.

NOTE: If you drop an existing hydro database you will LOSE ALL CONTENT in that database. Ensure that you have a backup of the existing hydro database and/or confirm that you do not need its content before proceeding.

If the target SQL Server instance already has a hydro database that you wish to drop:
- In the Object Explorer, expand Databases
- Right-click hydro and click Delete
- In the Delete Object dialog, click Close existing connections
- Click OK

At this point, the target SQL Server instance will not have a hydro database. To create and configure one:

Open geodatabase\create_database_hydro.sql in an SSMS query editor window
In the CREATE DATABASE statement, verify/update the pathnames for the data/log files
Click Execute to run the verified/updated create_database_hydro.sql script

SSMS will write messages to the console indicating success:

Commands completed successfully.

Completion time: 2022-09-16T11:02:10.2135163-04:00

A new, empty SQL Server database named hydro is now created, and configured with the settings and users required to enable it for use as a geodatabase in the NWFWMD environment.

Exit SSMS

Enable Enterprise Geodatabase

Now that the new hydro database exists and contains an sde user with appropriate permissions, enable it for use as a geodatabase.

To do this you will need to locate or create an ArcGIS Server authorization file. You can obtain the authorization file from the NWFWMD ArcGIS Server host. This authorization file includes all ArcGIS Server features for which NWFWMD is currently licensed, including enterprise geodatabases (arcsdeserver feature). The following path is the default name of the authorization file on the ArcGIS Server host computer:

%ProgramFiles%\ESRI\License10.8\sysgen

NOTE: If an existing ArcGIS Server authorization file does not already exist, you can provision one using the My Esri website.

Verify that you can access the authorization file on the server from your computer, or make a copy of it locally

Now, run a Python script to enable the hydro database as a geodatabase.

NOTE: The sde database user is based on the HQ\sde Windows user. Therefore, you must connect to Windows as the HQ\sde user in order to perform the next steps. You can do this by logging in to the console directly as HQ\sde, or by using your existing Windows session as another user (e.g. your personal HQ domain user) with the built-in Run as different user feature when starting individual applications. The instructions below describe the latter option.

Click Start > ArcGIS to view the available applications in that folder
Right-click Python Command Prompt > More > Open file location

File Explorer opens and shows the list of applications in the ArcGIS Start menu folder. From here, you can access the Windows Run as different user feature when starting apps.

NOTE: In Windows 10 you cannot access the Run as different user feature directly from the Start menu, by default.

In File Explorer, hold SHIFT and right-click Python Command Prompt > Run as different user
In the Windows Security dialog, enter the credentials for the HQ\sde user
Click OK

A terminal window opens that is configured to use the ArcGIS Pro Python environment (observe the (arcgispro-py3) prefix on the command prompt).

In the terminal window, navigate to the geodatabase directory in your local copy of the repo:

cd ...\geodatabase

Run the following script to enable the new hydro database as a geodatabase:

python enable_geodatabase_hydro.py -s <sql_server_host> -a <authorization_file>

The script will write messages to the console indicating success:

2022-09-16 11:10:42.467 INFO      ================================================================================
                                  Enable Geodatabase Features for [hydro] Database
                                  --------------------------------------------------------------------------------
                                  SQL Server hostname:     sql-gis
                                  Authorization file:      \\ent-gis\C$\Program Files\ESRI\License10.8\sysgen
                                  Log level:               DEBUG
                                  ================================================================================
2022-09-16 11:10:42.470 INFO      Creating database connection
2022-09-16 11:10:45.847 INFO      Enabling enterprise geodatabase
2022-09-16 11:10:55.724 INFO      Done.

NOTE: If you receive the following error:

RuntimeError: The Product License has not been initialized.

Start ArcGIS Pro as the HQ\sde user and log in to your licensing portal when prompted in order to obtain a license. You can use the Run as different user workflow described above to start ArcGIS Pro as HQ\sde, just as you did for the Python Command Prompt.

The hydro database is now enabled as a new, empty enterprise geodatabase.

Exit the Python Command Prompt terminal window

Deploy Data Model

The last step in the database deployment workflow is to create the Hydro Survey data model within the new hydro enterprise geodatabase. To do this, you will run a Python script that will create the geodatabase objects composing the model.

Recall from the Enable Enterprise Geodatabase workflow above that you started a Python Command Prompt with the HQ\sde user credentials in order to create the geodatabase system objects in the sde schema. Now, you will start another Python Command Prompt as the HQ\hydro user, in order to create the Hydro Survey geodatabase objects in the hydro schema.

In the File Explorer that is showing the contents of the ArcGIS Start menu folder, hold SHIFT and right-click Python Command Prompt > Run as different user
In the Windows Security dialog, enter the credentials for the HQ\hydro user
Click OK

A terminal window opens that is configured to use the ArcGIS Pro Python environment (observe the (arcgispro-py3) prefix on the command prompt).

In the terminal window, navigate to the geodatabase directory in your local copy of the repo:

cd ...\geodatabase

In the Python Command Prompt terminal, enter:

python create_hydro_data_model.py -s <sql_server_host>

The script will write messages to the console reporting each object it creates in the geodatabase:

2022-09-18 18:24:40.941 INFO      ================================================================================
                                  Hydrologic Data Model Geodatabase Deployment
                                  --------------------------------------------------------------------------------
                                  Target database server:  citra
                                  Log level:               INFO
                                  Log file:                None
                                  Disable domain creation: False
                                  ================================================================================
2022-09-18 18:24:40.941 INFO      Creating database connection file
2022-09-18 18:24:45.485 INFO      Creating domains
2022-09-18 18:24:45.485 INFO            Creating domain ADVM Beam Check Initial Exception
.
.
.
2022-09-18 18:28:10.575 INFO      Done.

NOTE: If you receive the following error:

RuntimeError: The Product License has not been initialized.

Start ArcGIS Pro as the HQ\hydro user and log in to your licensing portal when prompted in order to obtain a license. You can use the Run as different user workflow described above to start ArcGIS Pro as HQ\hydro, just as you did for the Python Command Prompt.

The Hydro Survey geodatabase is now deployed and ready for use.

Exit the Python Command Prompt terminal window
Exit the File Explorer showing the contents of the ArcGIS Start menu folder

Services

ArcGIS Pro

The service was published with ArcGIS Pro. The ArcGIS Pro Project can be found on GitHub under the Services folder

To overwrite the feature service -

Open ArcGIS Pro called 'Hydrologic Monitoring'
Login to the Portal
Open the web map 'Hydrologic Monitoring Field Service'
Overwrite the feature service

Web Service

There is 1 feature service deployed to ArcGIS Enterprise in order to execute the solution. The service contains the Location, Location Visit, and related records which is used in Field Maps & Survey123. The service should be editable and have sync enabled.

Hydrologic Field Monitoring Service: https://geoservices.nwfwmd.state.fl.us/portal/home/item.html?id=d8a690ddca904a938380911efb95f015 Hydrologic Field Monitoring REST Endpoint: https://geoservices.nwfwmd.state.fl.us/server/rest/services/Hydro/Hydrologic_Monitoring_Field/FeatureServer

Survey

System Requirements

Download Survey123 Connect and Survey123 Field Application via the Microsoft store or by going to the Survey123 Downloads Page. Ensure the machine as the latest version of both applications installed.

Workflow

Survey

Portal Components

Item	Description
Form	A form was created and published with Survey123 Connect
Web Map	A web map was created and linked as content in Survey123 Connect in order to utilize the offline basemap
CSV	A Measuring Point CSV containing location names, elevations, globalIDs etc. is made available for offline use. This enables the user to select a measuring point in a picklist

Updating Domains

After the domains become available in the database the 'choices' tab in the Survey123 XLXS is requried to be updated

Adding or Removing Fields

After a field is become available in the database the 'survey' tab in the Survey123 XLXS is requried to be updated

Updating the Measuring Point CSV

After a Measuring Point is added or removed from the database OR if the database is dropped & redeployed the hosted measuringpoint csvis required to be updated.

Open ArcGIS Pro and connect to the Hydrologic Field Monitoring Service
Join the MeasuringPoints table with the Location table based on RelatedLocationID-GlobalID fields
Open the MeasuringPoints attribute table, select all the rows, and copy the values into a CSV.
Format the CSV to include the necessary columns from the database. Make sure to utilize the same column names from the previous CSV since these are used in the XLXS configurations

(The following configuration is saved as a model: \\isb1\folders\GIS\S_Hibbard\Assignments\RMD\Hydrologic\HydroSurveyToolbox.atbx\MP_CSV_Export)

CSV Column	Source Column
name	MeasuringPoint.Name
label	MeasuringPoint.Name
locationname	Location.Name
elevation	MeasuringPoint.Elevation
locationglobalid	MeasuringPoint.GlobalID
active	MeasuringPoint.IsActive
display	MeasuringPoint.DisplayOrder
locationmpname	concat(Location.Name," - ", MeasuringPoint.Name)

Save the csv locally. Make sure it is named measuringpoint.csv
Login to the Portal, select the option to 'Update' the measuringpoint csv.
Upload the new csv that was created
Test the change in Survey123 field app by select a location with a Stage or Groundwater measurement. Confirm that the measuring point selection is available and that the elevation pre-populates.

FieldMaps

Workflow

Portal Components

Item	Description
Web Map	A web map was created with the hydro service and offline basemap