Will introducing a long standing Dev branch create unnecessary maintenance overhead?

Question

I am seeing bad practices occurring in our version control where my colleagues are creating their own versioning system with stored procedures so that they can compare results before/after changes and we end up with multiple stored procedures in the database with slightly different names e.g. StoredProcedureV1, StoredProcedureV2

I am considering introducing a long standing Dev branch as a suggested solution to resolve this practice and also support me in introducing work item tracking and code reviews. The branch would have a CI/CD pipeline to build and deploy the changes to our shared dev/test SQL instance. Changesets would then be promoted with a merge into Main which would have a similar CI/CD pipeline to deploy to the production server.

Is this going to create an unnecessary maintenance overhead (likely for me) performing merges from Dev to Main for little benefit?

Could the same be achieved through manual/approved releases? Or does this lack flexibility as I understand that all changesets on the branch would be released.

Background

We are a small team (currently 3 staff) who are responsible for developing the organisations Data Warehouse environment.

Our development is done in Visual Studio 2017/SQL Server Data Tools and we have been using TFVC in VSTS/Azure DevOps for about year, for everyone involved this is our first exposure to version control.

All projects are kept in one repository with a Main branch, I have used branches occasionally for big changes but they are not part of a common workflow and my colleagues are only using the Main branch.

We have two servers available each with their own instance of SQL Server one as production and the other considered as dev/test. Local instances are not an option at the moment due to the size of the databases and resources required to satisfy information governance requirements for a local environment.

At present the dev/test instance is not being used effectively, it is basically an environment for our first version of a CI/CD pipeline to deploy our dacpac changes to first and then onto the production environment.

There are fundamental issues with our workflow as there isn't really one, other than checking in a changeset and the CI/CD pipeline, which I recognise is my fault. We are not making use of work item tracking, there is no code review process and there is little documentation. Testing is done manually, as yet no one has an understanding how automated testing can be done with the projects we are using.

As the lead, my lack of experience and confidence in these areas is holding me back from introducing these things but changes need to be introduced gradually.

For the particular issue with my colleagues introducing their own versioning system I feel that I need to be able to present a suitable solution before challenging on the practice.

Answer 1

Having a dev branch is very common amd the merges to master should be trouble free as long as you make sure all the changes go through dev and are not made directly to master.

However, it wont nescessarly elliminate the sprocV1 sprocV2 issue.

Sometimes inorder to have a zero downtime release you will need to have both sproc versions live at the same time.

To make sure they are not needed, or at least cleaned up afterwards you need to look at the release procedure.

Answer 2

Aside from the hot cut-over case, i think the problem here is testing. Your devs want to know that they aren't breaking anything at the database level, and essentially run both procs and compare the results, or leave it in so they can swap back without raising an incident.

To the incident process, see what you can do about making incident/problems less onerous.

To the testing issue. Make a testing process, one that can be run on request/nightly whatever cadence works best.

I'm going to assume you have a commands that:

• deploy a database, including running in data-migrations, schema changes, and new stored procs.
• backup and restore a database.
• have access to either a full/scrubbed/reduced production equivalent database.
• have a database test folder, with yours tests encoded as sql stored-procedures.

write a script (or find someone else's) that:

1. deploys the "prod-like" database
2. snapshot that database.
3. pick a stored procedure, run it in, execute it, capture all output results sets into some tabular format, one table per result set.
4. reset the database to the snapshot.
5. repeat from step 3 for each test.
6. upgrade the database
7. snapshot the database
8. pick a stored procedure, run it in, execute it, capture all output results sets into some tabular format, one table per result set.
9. reset the database to the snapshot.
10. repeat from step 8 for each test.
11. use a diffing tool on each pair of results. If they are identical the test has passed. otherwise they have failed.
12. Make a test report using some standard format
13. add report and output files to your ci/cd job
14. notify devs of a failed test.