There is no single preferred tool/approach because - as with everything else - there is a large lump of "it depends".
Conceptually however I think its fairly simple - script the changes, version control the scripts, automate running the scripts in order.
In terms of "script the changes" - this doesn't necessarily mean that you have to hand code them, SQL Server (for example) will let you save a script instead of run the changes if you use the designer in the management tools.
For version control the scripts (and automate running in order) I have a pattern that involves sticking the SQL into application code a whole series of methods called "DoUpdateNNN" that do the updates - a former colleague did some nice code that iterates over those by reflection. Each schema update sits in a transaction so it can be rolled back if it fails. The nice thing about this approach (wrapping it all in code) is that if you do need to do something that is challenging in SQL then you have the full capabilities of the language to play with as a means to an end.
To make sure we only do what is needed, I have a table in the database that tells me what version it is (its actually a list of all the schema changes with the date they were run).
Given all of this, my update code can:
- Look for a database and if its not there will create it with just the version table
- Identify what version of the code the schema is at
- Run any the necessary schema updates, in sequence.
I do this because it is relatively simple and very effective i.e. it works - in the real world, really quite a lot. The only thing I don't cover is rolling the schema back - ideally one would do a full backup before a schema update (and this has not yet - in 15 years - been an issue for me).
Really need to get this opensourced and nuget-ed!