0

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.

example

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.

2 Answers 2

0

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!

1
  • nice! thanks for the answer. I'm going to see more about that program. Commented Nov 20, 2014 at 14:27
1

I would suggest reading this whitepaper on database version control from DBmaestro TeamWork (where I work)

Basically, it discuses how database enforced change management combines the enforcement of version control processes on the database objects with the generation of the deployment script when required based on the version control repository and the structure of the environment at that time.

This approach uses build and deploy on-demand, which means the deploy script is built (generated) when needed, not as part of development. This allows for efficient handling of conflicts, merges, and out-of-process changes.

It handles the challenges by:

Ensuring all database code is covered – structure, business logic written in the database language, reference content, database permissions, and more are managed

Ensuring the version control repository can act as the single source of truth – the enforced change policy prevents anyone (developers, DBAs) using any IDE (even command line) from modifying database objects which were not checked-out before and checked-in after the change. This guarantees that the version control repository will always be in sync with the definition of the object at check-in time.

Ensuring the deployment script being executed is aware of the environment status when the script is executing – building (generating) the deployment script when needed (just before executing) guarantees it is aware of the current environment status.

Ensuring the deployment script handles conflicts and merges them – by using baselines in the analysis, the nature of the change is known and the correct decision whether to promote the change, protect the target (ignore the change), or merge a conflict is easy.

Generating deployment scripts for only relevant changes – the integration with application life-cycle management (ALM) and change management systems (CMS) enables you to assign a reason to the change, as is done in the file-based version control or task management system.

Ensuring the deployment script is aware of the database dependencies – the sophisticated analysis and script generation algorithm ensures the DDLs, DCLs, and DMLs will be executed in the correct order based on the database dependencies, including inter-schema dependencies.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.