Grant Fritchey (8/5/2009)
The key, the hard part, is figuring out how to do incremental updates that could lead to data loss. Adding a nullable column or updating a stored proc is easy. Modifying existing structure while maintaining the existing data in an automated fashion is hard.
That right there is the trick. Most everything else is easy to implement. But the scripts to go from one structure version to another whenever it is anything harder than adding a nulable column at the end is always the problem. You need a human brain in their to make the decisions as to what is going to happen. Both Red-Gate and the VSTS DB Edition have pretty good SQL comparison tools, and VSTS allows you to compare a DB to your project and sync the two.. What I had typically looked for was how tables had changed and then I built the scripts to handle the conversions..
CEWII