New: Overcoming Database DevOps Challenges. Read now.

How to handle SQL deployments to mutliple DBs // Schema Compare

Hi, I'm currently working on an application which has 1 DB per client.. Lets say I've got 100 DBs

1. Every time we do a deployment I need to roll the same SQL out to all 100DBs. Whats the best way of doing this either via RedGate tools or as part of an automated deployment process?

2. Assuming every DB is a clone of a "MASTER".. How do i ensure that all DB's have the same schema?

3. Are there any best practises for scenarios like this?
Tagged:

Answers

  • Alex BAlex B Posts: 971 Diamond 3
    Hi @owencampbell,

    1. If you are certain every target database has the same schema as each other, you could just do a comparison using SQL Compare from the updated database version to one with the schema to be updated (the customer database schema version) and then use SQL Multiscript perhaps to run that script on each target database.  A more automated way with further checks would be to use SQL Change Automation (SCA).  Use the package deployment will create one deployment resource that will update each of your target environments to the same schema.

    2. Both methods above will do this as you are deploying the same script in each case; though the latter can be from various states (as long as they are a known migration state), but the same deployment resource is used in each case and so the schema will end up being the same for all targets.

    3. We recommend SCA for the reasons mentioned above, but you should get in touch with one of the consultants listed on this page for further information on this.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • LenaFischmannLenaFischmann Posts: 3 New member
    1. Create a batch file that executes the required scripts against different SQL Server instances.
      The batch file should look like this:

      :CONNECT <server1>\,<instance1>
      --SQL1 to execute
      GO
      :CONNECT <server2>\,<instance2>
      --SQL2 to execute
      GO
      

    2. Execute the batch file using SQLCMD:

      SQLCMD –I D:\Scripts\BatchMultiple.bat

    A disadvantage of this method is that it requires numerous manual and time-consuming actions, which are very error prone. Each time when new scripts are executed, the batch file must be modified or a new one has to be created.

Sign In or Register to comment.