I have a product that I'm currently authoring that relies on SQL server for the backend. One issue I'm trying to resolve is to improve the 'upgrade' story. So v1 will have a particular schema and v2 may include some enhancements to this schema (new tables and new columns).
I'm aware of the SDKs from RedGate and ApexSQL - but would like to avoid.
I've had a read through the SMO docs, but I'm new to it and struggling to see if this can be applied in this situtaiton. Ideally, I'd like this to make this programatic (SMO or other) - the base cases seems straight forward enough, but I really don't want to re-invent the wheel if I can help it. Does anyone have any experience of similar requirements or ideas about how I could approach?
Maybe not exactly what your're looking for (since it's not SMO) but having a look at Entity Framework Code First Migrations might help you:
http://msdn.microsoft.com/en-us/data/jj591621
Changes in the model-classes can be versioned and can either be applied directly to a database or, if you do not have direct access to your database, you can generate SQL-Code for your new version and hand it to your database-administrator.