First of all, I apologize if you can't understand very well what I am saying and it can be edit for better understanding of my post. The next example is for better understanding of databases with control version.
Suppose I have a software used by several businesses. This software need a database to function correctly, that means every business have one. I have a central database using migrations and scripts to have control version but this central database do not comunicate with the others. Every business have a different version from each other, including the central database that always have the last version.
To update the databases to my last version I apply idempotent scripts (like Entity Framework) so I can detect the version and continue from there. Let say the business with the version 15.11 have an error with the database and need to be solve right away. I recreate the bug, fix it, create a script, and bring the update to the company. Now this company have a different version, like 15.11.1. and the last script it's included in the central database.
So here comes my question, if I decide to update in the future that same database to the last version, my idempotent script do not apply this change again?
Another question is: Can I apply differents scripts in a database? i.e. if in the update are 5 scripts and I only want to apply the #1, #3 and #5. The idea is for reduce the updating time if I have change already applied.
I am open for all kind of opinions and if I have some concepts wrong, please feel free to correct me.
Thank you.
Best Answer
As far as I understand, an idempotent script is something that can be applied multiple times but has the same effect as if you only applied it once (i.e. it can be re-run without having an adverse effect). It's definitely worth having idempotent scripts as part of your release so that you're not causing issues if you accidentally run the same script multiple times.
In terms of managing the deployment and only deploying scripts #1, #3 and #5 of a 5 script release, it may be worth looking at some software for managing the deployment between databases. One such example is from Redgate, who are in the process of developing SQL Release.
This type of software will enable automated deployment of changes between environments, comparing each environment and should only apply the version changes required. They help prevent mis-deployments between your environments and could be safer than just skipping scripts!