I have a requirement to source control our database.
Currently we have a database with many stored procedures. Our developers are changing these procedures on a central development database and recording the change locally ready for build. When we build, it is a case of manually updating these procedures on a live database. This was fine for when we were a small team, however as we grow, it's becoming unmanageable.
We want to move to a more efficient, less error-prone and more automated setup. I have a setup working that uses docker to run databases locally on each developers machine, with a Git repo containing the docker files, a DACPAC and, hopefully, all stored procedures, tables, functions etc.
Is there an obvious way to do this? It will need to allow for showing the diff in the stored procedure when a pull request is generated, so that it can be easily code reviewed etc.
Also, ideally this should be as simple as pressing save on the file, so I was maybe thinking could a SSMS project work? I don't want our developers having to manually copy procedures into a repo etc.
Also also, when someone gets latest from the git repo, we want it to update the developers local database, this is where I thought DACPACs would come into play?
Please forgive me if I've got some of the theories wrong behind why some of these technologies are used.
If my requirements might not be possible, another solution would be much appreciated.
You can use Visual Studio to create a Database project, then sync the changes of the database with this project. This project can be source-controlled in a Git repository.