What are the challenges you face when working across database platforms? Take the survey

Source Control in DW and Reporting Databases

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.



  • Options
    Sergio RSergio R Posts: 610 Rose Gold 5

    If I understood you correctly you are using the dedicated model.
    If that's the case SQL Source Control can help: you could have all the developers committing to a single DB folder in the repository.

    To deploy to another environment you could, as you mention, use SQL Compare.
    If you want to automate the process and run automated tests you could use DLM Automation.

    Thank you,
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
Sign In or Register to comment.