Development of a National Adult Protective Services Data System: Namrs Pilot Final Report (volume 2). 4. Namrs Pilot Database

09/25/2015

The NAMRS Pilot Database stored all the data for the NAMRS Pilot.

Function

There were several functional types of data that were stored in the database:

  • NAMRS Component Data--This included the Agency, Case, and Key Indicators Component datasets that were entered/uploaded by the states.

  • Announcements/Resources--These were the announcements and resource files that were displayed in the NAMRS Pilot Website. These items were loaded into the database through the website by administrators and technical users.

  • Case Component XML Validation Rules--These were the data validation rules that were used to validate the Case Component XML files that were uploaded by state users.

  • Error Logs--These were "ELMAH logs" that tracked errors, exceptions, and security violation attempts in the website.

  • User Profiles--This was contact and other information about NAMRS Pilot users.

  • Credentials and Roles--This data included the credentials and roles for all users. This was standard Microsoft Identity 2 tables.

Schemas

The six functional types above comprised the 95 different tables that made up the database. Those tables were broken up into four schemas:

  • DBO--The DBO schema contained the identity and ELMAH tables.

  • Core--The core schema contained all the tables for data that was not submitted by states, such as the announcements, resources, user profiles, and validation rules.

  • Lookup--The lookup schema contained all the lookup tables.

  • NAMRS--The NAMRS schema contained all the tables for the Agency, Case, and Key Indicators Component datasets.

Appendix A provides ERD for the physical database structure. The ERD is provided as a PDF and can be modified using Adobe Acrobat software. Appendix B lists the definition of all the database tables and data elements.

Views

There were nine different views in the database that were used only for data extract functionality (which was a manual process run by a system administrator). These views were not accessed by the website.

Stored Procedures

There were only five stored procedures in the database. Three of them were used by the ELMAH component in the site. One was used to build the data extracts and one was used to load the data warehouse. The last two were both manual processes run by a system administrator.

Custom Functions

There were only two user-defined (scalar-valued) functions in the database. Both were used in the data extract views.

The database ran on Microsoft SQL Server 2014 (Web Edition), installed on a VM in the Microsoft Azure Cloud. Since this was a VM, normal maintenance had to be performed. In addition to manual service pack updates, there were two scheduled maintenance plans:

  • Nightly--The nightly maintenance plan ran at 1 a.m. every night and backed up all databases on the server (to Azure Storage), checked database integrity, reorganized indexes, and updated statistics.

  • Weekly--The weekly maintenance plan ran at 3 a.m. on Saturdays and rebuilt all indexes on all databases on the server.

Access

The NAMRS Pilot Database was accessed by the following:

  • NAMRS Pilot Website--The website accessed the database so that it could store and retrieve data for the website.

  • NAMRS Pilot Case Loader--The case loader application accessed the database so that it could load data from Case Component XML files.

The NAMRS Pilot Database accessed the following:

  • NAMRS Pilot Storage--The nightly backup process created a full database backup each night. The backup file was stored in NAMRS Pilot Storage.

  • NAMRS Pilot Data Warehouse--Data from the database was stored into the data warehouse. This was a manual process performed by a system administrator.

Security

There were two levels of security for the NAMRS Pilot Database.

Network Security

An Azure VM basically ran behind a firewall. One must expose "endpoints" which are essentially TCP ports. Each endpoint could be protected with an Access Control List (ACL) which allows one to limit access to the endpoint to one or more IP addresses (or address ranges that are specified as classless inter-domain routing [CIDR] addresses).

The VM that ran this SQL Server only has the following endpoints exposed:

  • Powershell--This allowed powershell scripting against the VM.

  • Remote Desktop--This allowed a system administrator to get remote access to the server. Two-factor authentication was enabled for all remote desktop access.

  • SQL Server--This allowed the SQL to be queried and managed.

Each of the endpoints was open only to a single IP address--the WRMA office. These servers could not be accessed from any other IP address on the Internet other than the WRMA office. Also, the SQL Server endpoint was open to IP addresses for Azure Web Apps. This was required so that the NAMRS Pilot Website (which is an Azure Web App) could access the database. Since a Web App can change IP addresses without notice (as it moves around in the cloud), the database had to be open to the full range of IP addresses that could be used by Web Apps.

Database Security

Each person/component that accessed the database had database credentials. The only accounts available in SQL Server were for the system administrator and for the website to access the database.

View full report

Preview
Download

"NAMRSpilot-V2.pdf" (pdf, 1.83Mb)

Note: Documents in PDF format require the Adobe Acrobat Reader®. If you experience problems with PDF documents, please download the latest version of the Reader®