How the schema migration tools work?

Question

I have db based application and I delete the schema & db content every time whenever there is any changes in the schema. Now it is in development mode. Soon we release the application in production. But we suspect there could be many changes after we release the application in production use. The software shall be installed on many locations across the globe. (not a web based app)

How the schema migration tool work in this case? or How can we use them in typical database based applications where we suspect the changes in the schema?

Answer 1

Download the RedGate tools and find out. I believe that they all have trial periods and I'm sure that SQL Compare does.

Typically, you point them to two databases and they list out all of the differences between the two. You can configure them to ignore certain things, like comments, constraint names, etc. From there you can select which items you want to synchronize and in which direction and the tool will either generate a script for you to do it or it will make the changes for you.

In your situation you would probably generate the script and then use that as a basis for what you would send to your customers or what you would include in your install scripts. Just keep a database for each version of your application and you can then generate scripts to go from one version to another by pointing at the two databases. You can keep them empty (or just fill the lookup tables) so that they don't take up too much space.

You can also use SQL Data Compare to keep lookup tables synchronized. Just use it for the lookup tables though, not the actual main data tables.

No matter which route you go, you're going to have to do some custom coding, either because you need to convert data while changing structure or for a similar reason.