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.