In my experience in the corporate environment there is no easy choice.
Three methods are listed below: (regardless of the toolbox used).
1) Dump the whole scheme to a file and save the file in the repository
PROS: Easy
CONS: large file - difficult to edit manually - hard to see what has changed from the latest version - cannot deploy it, so some mechanism will be needed to prepare a DIFF script between Dev and Test / Live systems
2) Dump each database object into a separate file stored in the repository.
PROS: It's very easy to see what has changed. It can easily create deployment scripts for most objects (although some things will still require a DIFF script, such as changing the definition of columns)
CONS: you need to run scripts in a certain order - managing this process can be quite difficult.
3) Consider each change as a separate operation with its own sequential numbered SQL script.
PROS: It’s easy for developers to create scripts, the same scripts can be run on each platform (theoretically)
CONS: A management nightmare - an order can become a problem, it is very difficult to see what has changed in a release, or when a given object has changed.
Having run all 3 options, I would say that 2 are great to work with them, but it took more time to configure in the first place - getting all the scripts executed in the correct order took time - and STILL it took to use the diff tool for the database for scripting for UAT / Live. Therefore, I would recommend a combination between 1 and 2.
Bonyt source share