We are working on a project that uses database first model.
At the beginning of the project we decided to use a central db, all developers were using this db.
But at the middle of the project, developers decided to use own DB's, they are cloning the DB to their local server. So, they are using and changing in their local, but migrating the DB changes to centralized DB is so hard, we are doing it by hand.
Is there a good way to manage db changes to centralized db?
but migrating the DB changes to centralized DB is so hard, we are doing it by hand.
And it never occured to you that this will not scale? We run a 4 people team with now 9 environments - it is impossible to do so by hand. On top you WILL make errors, and if those happen during production deploy that is bad bad bad.
That said, this is a problem solved like 50 years ago. Learn from history. Here is our approach:
There is a table SchemaSync in every db tracknig which script executed and the checksum
Missing/changed scripts are executed during deploy or manually (from a zip file) via powershell cmdlet.
This is seriously a standard solution predating SSDT and migrations for ages and likely older than you are. There are even open source implementations https://github.com/DbUp/DbUp and commercial libraries (one of them actually IN VISUAL STUDIO so you can try it out) https://www.red-gate.com/products/sql-development/sql-change-automation/
Like the only way to go.
SSDT: Loves dropping fields. How do you handle db migrations with that? Posible with multiple steps? Not possible. SSDT idea of change scripts is ignorant to data moves. If you ever do a larger refactoring (which I happen to do a lot) then you know that sometimes it is not "create new field" but a number of steps that partially run complex code. SSDT totally can not handle it.