Can I commit SQL data fix scripts via Bitbucket?

[Fabian Rzadkiewicz]

Realizing most of our service tickets can be resolved by executing data fix script in SSMS, I am looking for a way to automate the process. 
now my question is can i commit the SQL data fix scripts via bitbucket to Jenkins? Can I skip new feature branch, dev brnech, master brench and just use bitbucket as a repo before commiting? Thanks

[Najjar _Innovura_ · Accepted solution]

You may use bamboo for that. 

One of our customers had a similar case where usually the support runs dB scripts to resolve incidents as a workaround. We have implemented bamboo for their convenience and traceability 

So Whenever the second line of support wanted to run a script to resolve an incident, they open Bamboo where we store each script as a build job.

The support clicks customize run and a screen shows up with variables, that they replace with the customer's ID and Email so when the script runs Bamboo replaces the variables with their values and it runs and shows green or red, with a complete log for what happened. 

And the real beauty is bamboo links job run with jira ticket for future references

[Fabian Rzadkiewicz]

That might be exactly what I am looking for, thank you very much.

Let me just confirm, you store each script as a build job in Bamboo, but the bamboo is still connected with jenkins, and further integrated with source control tool of the db and the whole database lifecycle management and CI/CD. Am I right?

We are deploying our scripts manually in SSMS but in your case, I would pull the script to Bamboo, and perform a single click deployment on commit?

[Najjar _Innovura_]

To answer your question, we didn't have Jenkins in our customers implementation. We used only Bamboo for CI/CD.

Bamboo is Jenkins but with more easier interface and expensive remote agents.

So the dB scripts were stored in SCM (Bitbucket) and those dB scripts were merely modified as they were built to fix a problem that is reoccurring. 

The second line of support  modifies a couple of variables inside the script and they run them. As a way to collect the variable changes across different runs we stored the scripts as build Jobs to have a reference for each run and the logs. 

The above implementation works for Jenkins as well since Jenkins have a plugin for customized and parameterized builds.

Do you modify the scripts for each new incident, or do you use the same scripts over with small variations? 

[Fabian Rzadkiewicz]

A bit of both. Surely there are reoccuring issues (a subject of hard code change scheduled in future releases respectively), but since the system itself is huge, new issues emerge on regular basis.

I'm still struggling to understand where in your solution is SQL Server localDB and a source control tool perhaps...

[Najjar _Innovura_]

We used redgate to source control the change on the database and also redgate schema compare for CD to sync different environments. 

Problem engineers solves problems (workarounds) and stores them (the workarounds) as scripts in a git repository as a cmd script, then they build a job on Bamboo that encapsulate those script and parameterize them

And the scripts were written as cmd sqlplus scripts.

[Fabian Rzadkiewicz]

That's it! Thank you very much:)