search

department-of-veterans-affairs / va.gov-cms

Editor-centered management for Veteran-centered content.
https://prod.cms.va.gov
GNU General Public License v2.0
97 stars 69 forks source link

Verify vets-api connection to VES database & define next steps #12348

Closed jilladams closed 1 year ago

jilladams commented 1 year ago

Currently blocked awaiting Platform support (slack threads ongoing)

Description

https://dsva.slack.com/archives/C0229UT3HSR/p1674492166999389

The Oracle VES database we want to use for Income Limits backend must be accessible via vets-api in order to move forward. Joshua Faulkner owns that db and we are nudging integration with Platform team. Until that connection is established and can be tested, API is blocked.

Once they confirm it's available and testable, we can test and resolve this. This will unblock the next step of writing the actual API in Ruby.

Prereqs

Acceptance Criteria

Team

Please check the team(s) that will do this work.

dsasser commented 1 year ago

Latest from Joshua regarding escalating to ESECC:

It passed VAEC technical review and is now at ISSO review and approval, this process takes a while.

jilladams commented 1 year ago

Being tracked in RFC-009658 in the ESECC portal: https://esecc.va.gov/CGWeb/MainUI/Changemanagement/StaffRFC.aspx?boundtable=IChangeManagementTicket&CloseOnPerformAction=false&ID=12650&windowWidth=1050&openTime=1676411788165

jilladams commented 1 year ago

ESECC approved: https://dsva.slack.com/archives/C0229UT3HSR/p1677597409530699?thread_ts=1674492166.999389&cid=C0229UT3HSR

Next step: Plat implementation. Then we can verify.

dsasser commented 1 year ago

Status Update

The ESECC ticket closed. Moving to testing the connectivity from within the expected subnets (ones that vets-api uses).

Test results

Next Steps

It appears we may need to escalate this to ESECC as some part of the connection between subnets/VPCs isn't allowing traffic though as expected. This may require coordination with CMS Devops engineers since they are the only ones on our team able to access resources on the test subnets.

jilladams commented 1 year ago

@dsasser for next steps, I'll be a bit of a bottleneck these days -- are you comfortable chasing that? Let me know if/where you get blocked and happy to help with unblocking as needed. (I recognize you have a sprint goal on another ticket, so let's discuss if this isn't actually practical given your other commitments.)

jilladams commented 1 year ago

Backend refinement update:

dsasser commented 1 year ago

It has come to my attention that I may have misunderstood how SOCKS can play a role with accessing the VES database from a local dev environment. It is likely to be impossible to reach the database from SOCKS alone. If it turns out to be true, this will make development difficult if at all possible with a live database connection.

We understood the VES database to be a "simpler" drop-in solution for the IncLim backend than rolling our own or utilizing the vets-api postgresql instance(s). With the "new" information about SOCKS, it may actually be more difficult to create and maintain the new API with the VES db as a backend. Since the vets-api postgresql db is native to vets-api powered APIs, there won't be such a challenge with initial development or maintenance with regards to limitations on the local side of things. That, along with the lack of Housebound limits in the VES database, may mean we should consider if VES is even worth pursuing over other alternatives. The viability of using vets-api postgresql hasn't been flushed out, but it is one of our options, and one that eliminates 3rd party-owned resources should it prove an acceptable option.

jilladams commented 1 year ago

13189 = Alternative to VES DB discovery.

dsasser commented 1 year ago

Status Update

We tested connectivity from an ostensibly allowed subnet, but it was not successful. Testing over SOCKS was also not successful.

Next Steps: A Troubleshooting Call

We need to fully validate the connectivity is/is not established by bringing in: Joshua Faulker, Eric Oliver, and Platform (probably Kyle Matheny since he has been involved already) to a troubleshooting call whereby we can fully validate connectivity status from all 3 subnets. This has proven difficult/impossible for two reasons: Platform has indicated they have high priority concerns, and Eric Oliver has been under tight sprint deadlines.

jilladams commented 1 year ago

Progress on booking some of Eric's time: https://dsva.slack.com/archives/C03LFSPGV16/p1681231109596099?thread_ts=1680712011.784739&cid=C03LFSPGV16

dsasser commented 1 year ago

Status Update 4/19/2023

We need confirmed tests in all 3 subnets before escalating to ESECC. Eric Oliver is slated to test in all 3 subnets using a java jar file delivered by Joshua Faulkner. This will do two things:

  1. Make it easier to test. There will be no need to setup a sql client to test connectivity.
  2. Inform connectivity tests as confirmed/unconfirmed with confidence. The consistent output of the java jar file test will provide certainty that our connection is/is not established at a network level. This will be useful information should we need to escalate to ESECC.

Relevant slack conversation: https://dsva.slack.com/archives/C0229UT3HSR/p1681836819887469?thread_ts=1674492166.999389&cid=C0229UT3HSR

davidconlon commented 1 year ago

Note: Covered in mid-sprint check in that this is at risk for not completely coming across the line in 82 (but still hopeful!) Understand the collab and dependencies here. (We knew all this walking into the sprint/sprint planning)

dsasser commented 1 year ago

Status Update 4/21/2023

@olivereri tested connectivity to the Oracle database from 3 VPCs on 4/20/23. As Eric notes here, the tests had mixed results. The details are summarized below.

Summary of VES Oracle DB Connectivity Tests

Production VPC: ❌ Failed Staging VPC: ❌ Failed Sandbox VPC: ✅ Successful

jilladams commented 1 year ago

A small 🎉 for one ✅ .

dsasser commented 1 year ago

Status Update 5/4/23

A troubleshooting call is scheduled for Thursday May 11th with Network Edge Operations NOC, Joshua, myself, and Eric Oliver.

dsasser commented 1 year ago

Status Update 5/17/23

Connectivity established!

From the troubleshooting call on the 11th, the VA Network Edge Operations team noted they found a typo in the original implementation, and corrected this. Today, Eric successfully connected to the target network from all three subnets.

wesrowe commented 1 year ago

Options for VES-vets-api connection:

Don't want real-time – will migrate data periodically. Concerns are performance, outages, etc.

Option A: Import VES tables directly into vets-api

Option B: keep CSV process the way it is using S3 just automate export from VES

We picked Option B