June 19, 2018 at 12:46 am
An important point, when working with own migration scripts, is defensive scripting. Do not just create a table, but check, if the table already exists before. The same with columns / indexes.
When creating a procedure / function / trigger, I create it always as dummy first (if not exists) and use ALTER afterward IF OBJECT_ID('dbo.p_whatever') IS NULL EXEC ('CREATE PROCEDURE dbo.p_whatever (@dummy INT) AS PRINT 1');
When executing inserts, it is not too hard to include a WHERE NOT EXISTS into the statement. And updates should of course have always a WHERE condition.
Regarding SourceControl: we use a batch file, which looks every 5 min, if someone created a new database script. If yes it will be executed on a copy of the production database, the database objects will be scripted out into a script folder (using RedGate's SQLCompare) and changes to this folder will be checked in into our SVN.
God is real, unless declared integer.
June 19, 2018 at 2:31 am
I can appreciate a migration-based approach for relatively small-scale database development work but I just don't understand migration-based development when used for team-based development. The problem for me is the merge. How do you resolve a conflict? Unless you do it manually, how do you even detect a clash? The migration scripts I've seen deal with several objects and mix the migration of data with the ALTER statements. The whole point of an object-level source repository is that it makes it easy to detect conflicts, and resolve them. If you have a conflict in a migration script that is taking the database from a version to the next, surely a conflict leads inevitably to a rewrite? Obviously, it is nice to avoid conflicts by means of ensuring that each developer works on a different schema, but my experience is that even then, there are occasional forays into other schema, especially dbo. Can one just forbid branches?
Best wishes,
Phil Factor
June 19, 2018 at 2:48 am
I see, what you mean - when developer A works on a procedure for 4 days and developer B makes a small change (e.g. a new column or fixing a typo) in the same procedure on day two (without telling it), there is a chance, that B's change will be overwritten by A (but at least, we can see this in the SVN history). We have no real solution for this (on the other hand it happend only once or twice in the last few years - we are a small team with only ~10 devs). For non-parallel but near-time changes: each developer has to apply the current deployment scripts (he has just to execute a batch file), before making changes to procedures etc.
I tested RedGate SourceControl (and products from other companies) for several months before, but they make all to many errors when creating deployment scripts (often not working (mostly because of wrong execution order of altered triggers and created / dropped columns), in the worst case it tried to drop ~ 20 important (and unchanged) tables, just because it decide, that it had to rebuild a verybig table (with a lot of indexes / FKs), just because the formula of a computed column changed a little bit (instead of just dropping / "recreating" this computed column).
God is real, unless declared integer.
June 19, 2018 at 11:39 am
Yes, all synchronization is a black art, because RDBMSs are moving targets and it is always an act of faith that the two databases being compared are actually two different versions of the same database! A synchronisation script needs to be eye-balled because any such tool will tend to choose the safest strategy in the light of the knowledge it has. It can occasionally be absurd or wrong! Hand-cut scripts will always win out. if a database sync tool produces a script that is in the wrong dependency order than that is a bug that needs to be reported back to the tool's developers,
Best wishes,
Phil Factor
June 20, 2018 at 1:47 am
Phil Factor - Tuesday, June 19, 2018 2:31 AMI can appreciate a migration-based approach for relatively small-scale database development work but I just don't understand migration-based development when used for team-based development. The problem for me is the merge. How do you resolve a conflict? Unless you do it manually, how do you even detect a clash? The migration scripts I've seen deal with several objects and mix the migration of data with the ALTER statements. The whole point of an object-level source repository is that it makes it easy to detect conflicts, and resolve them. If you have a conflict in a migration script that is taking the database from a version to the next, surely a conflict leads inevitably to a rewrite? Obviously, it is nice to avoid conflicts by means of ensuring that each developer works on a different schema, but my experience is that even then, there are occasional forays into other schema, especially dbo. Can one just forbid branches?
The very large Oracle, Peoplesoft, etc. ERP systems use migrations, with hundreds of developers working on things.
How do you detect clashes? Well, certainly there has to be communication and coordination for changes, and frequent merges. Actually, this is really a place where you need to develop on the trunk, so that as a developer makes changes, they can determine at commit time if they've altered something strange in the db. They get in the habit of pulling any changes back to their dev system before they commit. That way, they can see if something changes.
In a shared dev database, which is what we had a lot of at JD Edwards, what happens is that you make mistakes, or a dev does overwrite changes from another, but they typically happen in short order, so you redo the work, or hopefully have it saved in a script. Then the developers have to work out what changes are broken and how to resolve them. This can involve reordering scripts (renaming) or combining them to ensure the order of changes for object A is correct.
Don't forget that a db often contains hundreds of objects, which often don't have interactions of dependencies on changes, especially for large companies that seem to eschew DRI. In those cases, as long as all the changes are deployed, it doesn't really matter if I add the column to table A and then add the corresponding column that will be the FK (undeclared) in table B or reverse the order. As long as both get done, the application works.
Branches and merges are way more complex and difficult in migrations development, which means you can't let them linger for long. They're doing to be hard.
On the other hand, comparison methods get things wrong and can't be used for some problem domains.
Hand cut scripts suffer from the human fragility and frailty of repeating work correctly or including all changes.
The best option is a combination of some tooling and some human work, as well as hiring the more careful people you can.
Or make limited, small changes, additive only, and rarely remove/rename things. Even then, only with careful examination from multiple people.
June 20, 2018 at 1:54 am
t.franz - Tuesday, June 19, 2018 2:48 AMI tested RedGate SourceControl (and products from other companies) for several months before, but they make all to many errors when creating deployment scripts (often not working (mostly because of wrong execution order of altered triggers and created / dropped columns), in the worst case it tried to drop ~ 20 important (and unchanged) tables, just because it decide, that it had to rebuild a verybig table (with a lot of indexes / FKs), just because the formula of a computed column changed a little bit (instead of just dropping / "recreating" this computed column).
If you have time, I'd be interested in a repro. There are some cases where SQL Compare doesn't work in the best method, but overall these tools do a better job than most humans. They're good to implement, precisely because the system can't depend on one person. I've rarely made mistakes, but if I'm unavailable, and I sometimes am (or I leave), the system becomes very unstable with the next individual. As a result, having a process to survive personnel changes is a good idea.
That being said, it's important to document and be aware of holes. For me, I rarely drop columns, since that's usually a way to cause a riskier deployment. I'd rather rename the column and then drop in a separate, later deployment. For some changes, it does seem rebuilding a table makes sense, but not if it's large. I've added the idea of a "risk" option for SQL Compare (https://redgate.uservoice.com/forums/141379-sql-compare/suggestions/34610251-risk). This doesn't change the behavior, but it does provide information for me to examine the script more closely, or pull these changes to a separate deployment.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply