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

SQL Compare - Automate multiple DBs

Hi, I've got an instance with say 50 DBs on. I have a Seed DB that is cloned whenever a new client comes on board and at the moment I currently compare what I've got in source control to the seed schema, generate a script and then use Multi Script to pass it out to the other 49 instances

We have had an issue with people making changes to DBs outside of source control for individual DBs as and when issues cropped up. 

I can sit and compare all 49 DBs to the seed 1 at a time via SQL Compare but that is time-consuming and it won't scale as the app grows. Is there a way to automate the compare process with the Red Gate tools?
Tagged:

Answers

  • Options
    Sergio RSergio R Posts: 610 Rose Gold 5
    I assume that you have a SQL Source Control\SQL Compare scripts folder on Source Control, is that right?

    The best option here would be to use SQL Change Automation, more specifically the Powershell module.

    This should enable you to create a script that would create a build from source control and then deploy that build to all the databases.

    We have had an issue with people making changes to DBs outside of source control for individual DBs as and when issues cropped up. 
    Do you want to automatically correct this schema drift?

    1- If you do you will need to create a release for each database in your script (so that it deploys the right script)

    2- If not you will create a single release based on what is the expected schema of the target and then deploy this release to all the databases.
    Since SQL Change Automation Powershell does a Pre-Update Schema Check any deployments to databases that have an unexpected schema will fail

    Please see the following document for more information regarding this:
    https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployment-with-sql-source-control-projects

    Sergio
    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.