Do you use SCA for application support data update scripts?

skuhn

We are currently using SCA for our application's database and we are exploring how to manage data update scripts that arise as part of day to day application support. These scripts are typically used to correct specific data anomalies or to correct data resulting from user error. While we acknowledge that some of these updates are routine enough that we should build application screens to support the updates, this is a legacy application with a great deal of development activity driven by the business which leaves us no time to build screens for data maintenance.

My question is whether anyone has used SCA to maintain and deploy these types of scripts?  They would create a lot of "noise" if included in the main SCA project as there are typically dozens of them each week, so we were considering a second SCA project just for these scripts.  Just wondering if anyone else has a similar problem and has a good workflow / process for these types of scripts.

Kendra_Little

Hi,

Thanks for the extra info -- that makes sense. 

Putting these in a separate project, it seems like it will be tricky for you to do a full CI/CD process -- because to build the project you would need the schema of the database. You could do something like merge the schema in from the other project regularly -- but since  you have ~12+ of these a week, that'd be really frequent and a lot of work. And it sounds like your testing requirements don't need that. 

Since your goals for this are really to version this and to automate execution, I would personally consider using Flyway for these kinds of scripts. Flyway doesn't support a full CI/CD process, and it doesn't help you author the scripts like SCA does for schema changes -- it's much more of a "script automation" tool. But in this case, that sounds like more what you need: https://flywaydb.org/download

Kendra

Kendra_Little

Hi @skuhn ,

I know we  do have many customers who include data update scripts in their SCA project. However, the folks who I have talked to about this are dealing with a much lower volume of them, such that they don't have the concern you have about noise.

Can I ask what your desired process is for testing/ validating these data modification scripts? And whether it is helpful or desirable for these scripts to be run against any dev/test environments you have in your pipelines?  

Kendra

skuhn

Thanks Kendra,

The current process that we use is that we test/validate the scripts against our Training database which is a copy of Production taken once a day. Once confirmed then we will run the script against Production.  Applying the script to Production is a manual process that we would like to automate. Additionally we don't put the scripts in source control currently but rather get saved to a network share. So we definitely want to get them into source control as well as automate deployment. 

Since these scripts are very much dependent on the state of data and since our dev and test environments are typically stale from a data perspective, running them in dev and test as part of the pipeline wouldn't work well.  What we might do is have a two stage pipeline for these scripts that just includes our Training and Production environments. Our we can run them through our normal pipeline but include conditional logic based upon environment, or even data state.

Scott

skuhn

Thanks again, it does appear that flyway suits our use case.