Tool to upgrade SQL Express database after deployment

Question

I have an app which uses SQL Express and my customers are mainly people who don't have a dedicated IT staff, so wouldn't know how to run scripts. So the deployment and upgrades has to be easy. The deployment part is all figured out. What I need to know is when version 2 of the product comes out and its time to update the database structure or data, is there a better way than the traditional method of scripts. Often times the problem with scripting methods is, you have a development database being used by a bunch of developers and there is not track of who made what changes, so as to be able to incorporate them into the deployment database.

Answer 1

Keep track, or else use a product like Red Gate's Compare tool to generate a script, then have your installer program run the upgrade script. The user will never need to do more than double-click setup.exe.

Answer 2

Not too sure if you allowed to intergate with Microsoft SourceSafe as its SQL Express, but if you can that could be an option worth looking into.

Integrating SourceSafe / VSS with SQL Server 2005

Answer 3

I would recommend using SQLCompare. It allows you to make all of your changes without having to worry about scripts and then you can quickly synchronize the Dev/Stage environment with the Production Environment or create a script to do so that can be run on a remote machine.

If you don't want to shell out the $$$, keep all of your changes in source control just like your code. You can either keep each change script individually, or let the trunk of your scc system be the final version of the DB and run through the versions of each DB object (I prefer the change script method, but have seen the other work).

Answer 4

You should also have a table in your database to keep track of the current version, so as to not repeat the same updates, or do them out of order - say a customer is applying several patches. +1 for SQL Compare.

---

Also, SQL Compare will wrap everything in a transaction. Which you can also do. They create a temp table to hold all errors and roll back at the end if any of them fail(iirc). Helps so you can see errors all at once. If a db update fails, you could also store this and then have the customer send it to you for troubleshooting.