Why DBUpdater?

Historical reasons
During the lifetime of an application, quite often there is the need to change the database structure in order to:
- implement new features
- extend functionalities
- fix bugs
- increase integrity with foreign keys
- increase speed and security with indexes
- move business logic into the database by adding stored procedures and triggers

When a new version of your software is released, you have to apply changes to your customers' existing database so new code can work for them, too. If you have a clients with one version and release a new one, you have to run a script to the underlying database. If you have 5 different versions spread, you need 5 different scripts or difference scripts from version 1 to 2, from 2 to 3 etc.
This is a critical point in software maintenance and this phase requires to take into account the following situations:
- the customer already uses the database
- is not possible to lost customer data
- customers are not happy to halt its work in order to send the database to software farm for update
- customers are far from the software farm site
- customers are, preferable, very numerous

All the above problems leads to the need to develop a methodology to update the customer database. The traditional approaches (hand-write sql statement, home-made tool, database live update, …) all suffer for limitations and problems.

Manual update
This kind of updates requires the programmer to trace all changes made in the database and manually write the sql statements to send to customers. This headache job normally takes hours of tedious work
It is so easy to make a change and forget to write the sql statement for delivery it. You will be aware of these changes only when the client complain you that the software raises an error.
In addition, In real life, customers database are never with the same structure and in the same version. Specialized sql statement must be used to pass from, i.e., version 1 to 3, skipping version 2. And what about version 1.1, 1.1.1 etc… normally uses to fix a bug before the official release?
Ultimately the only way to synchronize some database is to get access the actual database.

In summary, the major disadvantages of this approach are:
- error in special customer database situations.
- customer database availability.
- high risk of forget updates.
- Time consuming delivery stage.
- Requires a software engineer responsible of the database structure

Home-made tool
Yes, you can do that. But, before you start, be sure that:
1. Have programmers with enough skill. It is very easy to start, and the problem seems not so complicated. Just compare two tables using ADO Schema Info and that’s all. But, what if you want to maintain the dependencies between tables, primary and foreign keys, indexes, constraints, views, stored procedure encryption, users, db spaces, optimization, …
2. Be sure to allocate enough human resource for develop the first prototype. Are you sure that the time spent for develop the tool costs less that a full feature DbUpdater license?
3. Maintenance. If you are able to implement a full featured tool, great. We are happy for you. In the real time, all kind of software requires maintenance. A Database Updater too requires sometimes to implement new features, fix bugs, improve functionalities, … Even a simpler change takes long time if it concerns a software written months (or years) ago.

Freeze delivered database
This is the most used approach. It consists of saving (freeze) a copy of the database version used by the last application delivered. Each time an application update is delivered, the database structure it uses must be saved. The, before to send new executable, the development team needs to execute a database comparison between the previous and the current database structure. The comparison tool generates a set of sql statements that must be send and executed in the customer site in order to synchronize its database.
Moreover, if customers can have different version of the application, the development team needs to produce as many sql statement scripts as many different version are present in the market. For example, if the application is in the version 5, then it mandatory to build the sql statement to synchronize database from version 1 to 5, from 2 to 5, and so on.
Alternatively it is required to send all the sql statement from 1 to 2, from 2 to 3, and so on, and run them in sequence.

Even if this approach presents advantages with respect to the previous approaches, some drawback are still present.

- Release maintainance. It is critical to have a strong database versioning maintenance and a strong link between application and database versioning.
- The approach used to generate sql statements is to compare the previous version of the database “freezed” after release with the current version under delivering. Even if it is expected that the “freezed” database has exactly the same structure as the customers, in real life this is not always the case. So, the “freezed” database must be considered as “theoretical” and the sql statement generated by the comparer tool quite often fail in real life.
- unable to take into account customer data already present in the database. Suppose you have introduced a new unique index. In the development database this index is ok because no conflict exists in the data contained in the table. But what will be happen in the customer database?.

Live update
This is a step further of the previous one. With this approach all the drawback of the previous approach are overcame because the databases are live compared. Live comparison can takes into account actual database structure and content and build sql statement specialized for the database under examination.
This approach requires an on-line connection between the two databases. This can be accomplished by remote connection but this requires band usage and enough authorization. In addition, some feature are still difficult to implement, such as renaming table and fields.

DbUpdater approach is very similar to Live update with the difference that it can also work off-line.