Merging Database Schemas

posted @ Thursday, February 9, 2006 8:45 PM

 

For business applications, databases can be the ugly step child of an application, in particular during the development process. From the test driven development aspect, the question is often, how can I maintain consistent data for the environment so I can invoke atomic tests, quickly and repeatedly in an automated fashion? Another common issue is versioning of the database schema over the lifetime of the development process.

At work, I was tasked with merging an existing application into a web framework that required table modifications for integration (it wouldn’t be considered an ‘integration’ without modifications, right?). While the modifications are something you could maintain in a physical document somewhere; that introduces a human error factor that I’m not comfortable with. To help manage this task, we decided to use a product called SQL Compare from Red-Gate software. Not only will SQL Compare provide differencing between my various tables, I can export those table differences as well as synchronize between databases. As an added bonus, I can create a NAnt task to invoke this process comparison during my build, which we can use as additional documentation for our clients which require this type of information for Sarbanes-Oxley compliance.

I am curious how others handle this type of problem and what other issues you have with database-driven applications during the development phase.