How to handle SQL deployments to mutliple DBs // Schema Compare
owencampbell
Posts: 3 New member
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?
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
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
Have you visited our Help Center?
The batch file should look like this:
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.