# DEPLOYING ELYSE

## OVERVIEW

A complete Elyse installation requires the following packages:

* Database layer
* Application backend
* Application frontend

These instructions cover the database layer. For application layer deployment, see the README.TXT files with the deployment packages. To build the application layer from source, go to the repository at [GitHub - SilkwoodSoftware/ElyseApplicationLayer: Application layer for the Elyse database. · GitHub](https://github.com/SilkwoodSoftware/ElyseApplicationLayer.git) and refer to the file [`DEVELOPER_BUILD_GUIDE.md`](DEVELOPER_BUILD_GUIDE.md).

## CREATE THE ENVIRONMENT

### Step 1: Install SQL Server Express

Download and install SQL Server Express 2022 or later. During installation, choose `Custom` and ensure that **`Full-Text and Semantic Extractions for Search`** is selected on the Feature Selection page. Otherwise the full text search features of Elyse won't work.  Also during the installation, check **`Enable FILESTREAM for Transact-SQL access`**.  Ensure that **`Enable FILESTREAM for file I/O access`** and **`Allow remote clients access to FILESTREAM data`** are UNCHECKED. Otherwise the database stored procedures exclusive access to the files can be bypassed.
Under `Security`, select `Mixed Mode Authentication` and set a password for sa. Click `Add Current User` to provide database access to the current user.  

### Step 2: Install SQL Server Management Studio

Download and install MS SQL Server Management Studio.

### Step 3: Enable Filestream

On the target server, enable Filestream.

See: [https://learn.microsoft.com/en-us/sql/relational-databases/blob/enable-and-configure-filestream?view=sql-server-ver16](https://learn.microsoft.com/en-us/sql/relational-databases/blob/enable-and-configure-filestream?view=sql-server-ver16)

1. On the Start menu navigate to All Programs > Microsoft SQL Server 2022, then right-click SQL Server 2022 Configuration Manager and select Run as Administrator.
2. In the list of services, right-click SQL Server Services, and then select Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM \[SQL Server (SQLEXPRESS)].
4. Right-click the instance, and then select Properties.
5. In the SQL Server Properties dialog box, select the FILESTREAM tab.
6. Select the **Enable FILESTREAM for Transact-SQL access** check box. Leave the FILESTREAM for file I/O access unchecked.
7. Select Apply and then OK. Leave SQL Server Configuration Manager open as you will need to return to it to restart the server.
8. Launch SQL Server Management Studio.  In the dialog that appears, enter `.\SQLEXPRESS` as the `Server Name` (or enter the Named Instance that you set when installing SQL Server if you did not use the default or did not install SQL Server Express).  Set `Encrypt` to `Optional` and check the `Trust Server Certificate box`.  
9. In SQL Server Management Studio, select New Query to display the Query Editor.
10. In Query Editor, enter the following Transact-SQL code:

`EXEC sp_configure 'filestream_access_level', 1; RECONFIGURE;`

11. Select Execute. The expected response is that the filestream access level has been changed from 0 to 1, or from 1 to 1.
12. Open a new query and enter 
`SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled');` 
The result should return 1. 
12. Return to SQL Server Configuration Manager. Select SQL Server Services on the left. Right-click on the instance of the server \[SQL Server (SQLEXPRESS)]. Restart the SQL Server service.

Refer to Microsoft documentation regarding best practices for management of FILESTREAM.

### Step 4: Create the FILESTREAM Container Folder

Create the folder where the FILESTREAM container is going to reside on the target server.

The appropriate location for a FILESTREAM container folder depends on your specific environment, requirements, and best practices for file storage. Consider the following:

1. **Separate Physical Drive**: Ideally, FILESTREAM data should be stored on a separate physical drive from the operating system and the SQL Server database files to improve performance and manageability. This reduces I/O contention as BLOB data can grow significantly.
2. **Storage Capacity**: Choose a location with ample storage space to accommodate the growth of FILESTREAM data based on the projected size and growth rate of the BLOB data.
3. **Performance**: Use fast storage media to ensure good performance, especially if the FILESTREAM data will be accessed frequently. Solid State Drives (SSDs) are often a good choice for performance-critical applications.
4. **Backup and Recovery**: Consider the backup and recovery strategy for the FILESTREAM data. The location should be included in regular backup routines and be accessible for restore operations.
5. **Security**: The location should be secure, with access restricted to the SQL Server service account and administrators. Proper NTFS permissions should be set to prevent unauthorized access.
6. **Redundancy**: If high availability is a concern, consider using redundant storage such as RAID arrays or storage area networks (SANs) to guard against data loss due to hardware failure.
7. **Compliance and Regulations**: Ensure that the location complies with any data storage regulations or policies your organization or industry might have, such as data residency requirements.

Given these considerations, an example of an appropriate location for a FILESTREAM container could be `D:\FILESTREAMData\YourDatabaseName\`, where D: is a dedicated data drive separate from your operating system and SQL Server system databases.

### Step 5: Configuring Internal Connectivity & Port Assignment
#### Enable the TCP/IP Protocol
1.	Launch SQL Server Configuration Manager.
2.	On the left, expand `SQL Server Network Configuration`.
3.	Click on `Protocols for SQLEXPRESS`.
4.	In the right pane, you will see TCP/IP. It likely says Disabled.
5.	Right-click `TCP/IP` and select Enable.
6.	A warning will pop up saying the changes won't take effect until the service is restarted. Click OK.
#### Set the Port to 1433
1.	Right-click TCP/IP again and select Properties.
2.	Click the IP Addresses tab at the top.
3.	Scroll all the way to the bottom to the IPAll section.
4.	Clear anything in TCP Dynamic Ports (leave it blank).
5.	In TCP Port, type `1433`.
6.	Click OK.
#### Restart the SQL Service
1.	In the left pane of the same window, click `SQL Server Services`.
2.	In the right pane, right-click `SQL Server (SQLEXPRESS)`.
3.	Select `Restart`.

### Step 6: Load the Database

The Elyse database contains graph tables and is preconfigured with various data. The initial deployment can only be achieved by restoring from a database backup file. For upgrades, use Visual Studio SQL Server Data Tools.

If the path of the FILESTREAM container on the target server is different from the source server you will need to modify the file paths during the restore process in SQL Server Management Studio (SSMS).

**Note**: SSMS must be run with necessary permissions to access the folder containing the backup file.

#### Restore Process

1. **Start Restore Process**:
   
   * In SSMS on the target server, under Object Explorer, right-click on `Databases` and select `Restore Database...`
   * In the `Restore Database` dialog, select `Device:` and click the `...` button, then `Add` to browse for the backup file.
   * Add the backup file and click `OK` to load the backup contents.

2. **Modify FILESTREAM Paths**:
   
   * Once the backup is loaded, switch to the `Files` page in the `Select a page` panel on the left side of the `Restore Database` dialog.
   * In the `Database files` grid, you'll see the `Logical Name`, `File Type`, and `Restore As` columns. The `Restore As` column shows where the database and FILESTREAM files will be restored.
   * You can leave the path and filename as per the default in the `Restore As` column, or assign a custom location. To assign a custom location, for the FILESTREAM file (identified with type 'FILESTREAM'), change the `Restore As` path to the new FILESTREAM directory on the target server. You can do this by typing the new path directly into the `Restore As` field or by clicking the `...` button to browse to the new FILESTREAM directory. The parent folder must exist.  Select the folder and enter a File name. 

3. **Options and Relocation**:
   
   * Go to the `Options` page in the `Select a page` panel on the left side.
   * Under the `Restore options` section, you can choose to `Overwrite the existing database (WITH REPLACE)` if necessary.
   * In the `Restore the database files as` section, you can see a more detailed view and edit the restore paths for the data and log files, ensuring that all paths are correct and accessible on the target server.

4. **Complete the Restore**:
   
   * After all paths have been corrected, click `OK` to start the restore process with the new FILESTREAM path(s).

5. **Verify the Restore**:
   
   * Once the restore process is complete, verify the database. In Object Explorer, expand Databases > Elyse\_DB > Programmability > Stored Procedures. Scroll down to the stored procedure `reading.usp_SEL_files_contains`. Right-click and select Execute Stored Procedure. In the Execute Procedure dialog enter the word 'structure' into the Value column of the first row, `@containsstring`. Then select OK. The result should be a table of data and a message 'Transaction successful'. This indicates that the database is working, including the full text indexing. If you see an error message about full text indexing not installed you will need to uninstall and reinstall SQL Server with full text indexing included.

6. **Update Permissions**:
   
   * Refer to the README.TXT in the backend deployment package and add the login for the application or user.
   * Ensure the SQL Server service account on the target server has full control permissions on the new FILESTREAM directory.

The script `CleanDatabaseRestore.sql` may also be used to restore a database.

**Important**: The Ownership Chain password must never be stored outside of the database. It is only used internally and there is no need for any external process to know this password. It is recommended that the Ownership Chain password be reset to a long random string which is not recorded anywhere.

If a new copy of the database is installed by restoring over the old one, restart the backend and web server (e.g., IIS).

Install the frontend of the application layer by following the README.TXT instructions in the frontend deployment package.

## BOOTSTRAPPING USER ROLE PERMISSIONS

### Read-Only Access

In order for a user to have access to Elyse, they must be added to the same Windows User Group which is mapped to the Windows Authenticated login that the backend uses to connect to SQL Server. This provides access to the read-only privileges of Elyse.

Once the application layer has been installed and configured, and a user added to the above Windows User Group, test the application by selecting **Connected User > Who is this?** from the grey navigation bar at the top. It should return the username of the connected user.

### Onboarding a User

Before a user can be granted permissions within Elyse they must be onboarded. To onboard a user, they must be connected to Elyse and select **Connected User > Self Onboard**. A dialog will confirm the successful transaction or confirm if the user is already onboarded. This creates a record of the user's security identifier within Elyse and creates an Elyse User ID. It is not possible to onboard a user without the user first being connected to the database with no privileges.

### Single-User Environment

For a single-user installation of Elyse, all role privileges must be granted by directly adding records into the database after following the self-onboarding process above.

1. Open SQL Server Management Studio. Navigate to Object Explorer > Elyse\_DB > Tables. Scroll down and find the table `user_restr.sid_list`. Right-click and select Select Top 1000 Rows. There should be a record of the onboarded user. Note the value in the `sid_id` column (usually 1 for the first user).
2. Right-click on `user_restr.user_role_link` and select Edit Top 200 Rows. Enter the following records (change the `sid_id` if it is different):

| role_name   | sid_id |
| ------------ | ------- |
| Configurator | 1       |
| Controller   | 1       |
| Editor       | 1       |
| Reader       | 1       |
| Reviewer     | 1       |

3. To grant Authorizer privileges, find the table `user_restr.authorisers`. Right-click and select Edit Top 200 Rows. Enter the `sid_id` (1) into the `authoriser_sid_id` row.

The user now has privileges for all roles. In the application, select the **Select Roles** command on the navigation bar. Selecting available roles will reveal application layer commands for the selected roles.

### Multi-User Environment

In a multi-user server environment, once the database has been fully deployed and in production, a user can be granted role privileges by a user with Authorizer privileges. A user can only be granted Authorizer privileges by two other Authorizers. The first two Authorizer users must be manually entered into the database by the following process:

1. The two Authorizer users must be added to the Elyse Windows User Group via Active Directory so they can connect to the database.
2. Both users must then each self-onboard by selecting **Connected User > Self Onboard** from the application.
3. Once onboarded, grant the Authorizer role privileges as follows: Open SQL Server Management Studio. Navigate to Object Explorer > Elyse_DB > Tables. Scroll down and find the table `user_restr.sid_list`. Right-click and select Select Top 1000 Rows. There should be records of the onboarded users. Note the values in the `sid_id` column (usually 1 and 2 for the first two users).
4. Find the table `user_restr.authorisers`. Right-click and select Edit Top 200 Rows. Enter the two sid IDs in the `authoriser_sid_id` column.

For the user to access Authorizer commands from the application layer, select the **Select Roles** command on the navigation bar in the application. Check the checkbox against Authorizer. The user will now have access to the Authorizer level commands in the application. The Authorizer level help instructions will now be visible in the Help System, accessed via the ? button on the right-hand side of the navigation bar.

## GLOBAL SETTINGS

The `base.global_settings_groups` table contains sets of global settings used by the stored procedures. The values can be listed by executing `reading.usp_SEL_global_settings`.

The stored procedures will refer to the record which has a `setting_group_name` of Master. Any number of settings groups can be created so that global settings can be easily switched. However there must always be one record with a name of Master. To switch between pre-configured settings groups, change the existing record with a name of Master to something else and then change the new settings group record name to Master.

The table `base.global_settings_groups` contains the following settings columns:

| Column                                         | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| ---------------------------------------------- |   |
| `language_id`                               | Selected language for visible text messages. Select from valid values in `messaging.language_list`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| `is_register_code_applied`            | Valid values are 'Yes' or 'No'. Used by the stored procedure to decide whether the register code prefix is to be applied to an auto-generated document id.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| `register_code`                             | Register code is a type of namespace. It is an optional code prefix that is appended to an auto-generated document id to differentiate distinct registers within an organisation. Register codes should not be used to encode metadata but rather should be used just to keep IDs from separate registers visibly distinct, for example if the different registers are accessed through different interfaces and the user will need to know which interface to use. The register code could be used to reduce the risk of document IDs being duplicated across different organisations, while still keeping the ID length short, as opposed to using a GUID for example. The organisation's stock ticker could be used for this purpose. If two organisations merge then the document IDs won't clash. |
| `auto_gen_doc_id_length`            | Valid values are 4, 6 or 8. This is the number of letters in the auto-generated document ID. Select the lowest applicable number. Use 4 where the maximum prospective number of document IDs will be less than 100,000. Use 6 when the maximum will be less than 25,000,000 and 8 when it is in the billions. See `internal.usp_SEL_random_doc_id` for more details.                                                                                                                                                                                                                                                                                                                                                                                                                       |
| `auto_gen_doc_id_on_null`       | Valid values are 'Yes' or 'No'. This is a flag to instruct `usp_INS_document_id` to autogenerate a document id if none is supplied.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| `doc_id_format`                          | This column is used by the document ID autogenerate function to determine if the auto-generated document ID is to be an alpha string (e.g., BCDF) or an integer (e.g., 12345). Valid values are 'Alpha' or 'Integer'.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `doc_id_last_integer`                 | This column is used as a record of the last integer type document ID used.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| `date_style`                                | The default date style used for representing date values. The content of this column must match an entry in `base.datetime_styles`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| `culture_code`                              | Culture code is used to localise applications. It is a combination of ISO 639-1 language code and ISO 3166-1 country code.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| `doc_autogen_max_tries`               | The maximum number of attempts for generating an auto-generated document id.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| `default_filter_group`                   | The default filter group that will be used by stored procedures which are intended to provide common functions. This avoids the need for the application to include data which is hard coded to match configuration data (e.g., a default filter group which can be aligned with a configuration which allows retrieval of files which are published format, as opposed to source files, and which are the current latest revision and are linked to a document ID which is published, as opposed to withdrawn).                                                                                                                                                                                                                                                                                       |
| `default_form_id`                        | The default form ID is used by stored procedures when no form ID has been supplied by the calling function. With limited exceptions, all file and document data can only be returned via reference to a form ID which specifies which fields are to be returned. This field cannot be NULL.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| `default_doc_free_text_name_id` | This is used by stored procedures for a default free text name id where none has been supplied. It can be configured to point to document 'Title' for example.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| `default_tag_tree_id`                 | This is used as a default if no tag tree id is supplied.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| `default_tag_browsing_tree_id`     | This is used as a default if no browsing tree id is supplied.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| `duplicate_management`                      | This indicates whether a file which is a duplicate of an existing file is to be rejected. Valid values are On, Off, or NULL.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| `created_last_minutes`                   | This field is an integer number of minutes. It is used to retrieve documents or files that were recently created.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `enforced_retention`                        | This is a flag that informs stored procedures whether enforced retention of files is on or off. Valid values are On, Off or NULL. NULL will be treated as Off.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| `file_read_log`                          | This is a flag that informs stored procedures whether the file read log is on or off. The file read log logs when files are opened. Valid values are On, Off or NULL. NULL will be treated as Off.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| `file_delete_log`                        | This is a flag that informs stored procedures whether the file delete log is on or off. The file delete log logs when files are deleted. Valid values are On, Off or NULL. NULL will be treated as Off.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| `store_plain_text_content`            | This is a flag that informs the stored procedure whether or not to store plain text content. Valid values are On, Off or NULL. NULL will be treated as Off.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| `store_thumbnails`                          | This is a flag that informs the stored procedure whether or not to store file thumbnails. Valid values are On, Off or NULL. NULL will be treated as Off.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| `default_doc_date_name_id`         | This is the default document date name ID to be used. For example, it can be used as the next review date.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| `booking_in_time_box`                 | Time limitation, in minutes, after which a book-in record cannot be deleted.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |

**Note**: For reasons of database integrity, other than `date_style` there are no stored procedures for updating global settings. Global settings can only be changed with direct DBA access to the table.
