We have a data warehouse and reporting database that I would like to have in source control. As of now we have just a dev and prod instance, in the future their might be a UAT instance. There are multiple databases in each environment. We have SSIS, SSRS and SSAS already in source control (TFS). I would like to include the database as well. The issue can be that we have multiple developers working multiple projects. Someone might have a view for a report that takes 6 weeks to develop. The next request is just a modification to a view and is ready for prod in 30 minutes. Therefore the commits could vary and it is not always sequential (cause i don't want to wait to commit a change for 6 weeks while testing continues). How can we utilize red gate tools to put the environments under source control? (This also applies to procs and schema changes)
One option is that each instance/database is under its own source control. After testing in dev and it is ready, it is committed. Then sql compare is used to migrate that change to prod when ready to promot, where it is committed there as well. The manual part I am ok with, I am just trying to see if this is the best way.