Just a general question

Hello folks,
Just a quick? question to try to make sure I am not going down another
rabbit hole.
I am planning to use a Custom Action to invoke an SQL Script to make changes
to my DataBase schema.
I plan to simply query the database to see whatever version/level it is at,
and then depending where it is at, I will iteate over the various SQL Update
scripts, and modify the DataBase.
This means that I would need to include some number of SQL Scripts with
every deployment.
I could eventually query for a minimum level of structure before continuing.
I presume this would be relatively straightforward.

Does this seem reasonable? Deploy x-number of SQL scripts within the MSI
itself. Query the database. Patch the schema. Maybe even backup the database.

Thanks,

........Cameron

[Please do not mail me a copy of your followup]

=?Utf-8?B?Q2FtZXJvbl9D?= <CameronC@discussions.microsoft.com> spake the secret code
<3B7E3266-5893-44B9-B90B-9ECDD54AE462@microsoft.com> thusly:

>I am planning to use a Custom Action to invoke an SQL Script to make changes
>to my DataBase schema.
>[...]
>Does this seem reasonable?

In general I advise *against* doing database schema/upgrade/migration/etc.
tasks in your installer. Create a database utility that does these
things as a normal application and invoke that application at the end
of a successful install/upgrade.

I've done it both ways and there are no real advantages to doing it
during the install transaction and there are many disadvantages.
--
"The Direct3D Graphics Pipeline" -- DirectX 9 draft available for download
<http://legalizeadulthood.wordpress.com/the-direct3d-graphics-pipeline/>

Legalize Adulthood! <http://legalizeadulthood.wordpress.com>

Thanks for the advice. It was very timely.
I was trying to ensure somehow, that the DataBase schema updates would
ALWAYS be in synch with the application version.
I was concerned that they might go out of synch if I continued to deploy in
two separate steps.

Again, thanks for the info,
I will discard my plans to incorporate the Schema changes into the MSI
package.

........Cameron


"Richard [Microsoft Windows Installer MVP" wrote:

> [Please do not mail me a copy of your followup]
>
> =?Utf-8?B?Q2FtZXJvbl9D?= <CameronC@discussions.microsoft.com> spake the secret code
> <3B7E3266-5893-44B9-B90B-9ECDD54AE462@microsoft.com> thusly:
>
> >I am planning to use a Custom Action to invoke an SQL Script to make changes
> >to my DataBase schema.
> >[...]
> >Does this seem reasonable?
>
> In general I advise *against* doing database schema/upgrade/migration/etc.
> tasks in your installer. Create a database utility that does these
> things as a normal application and invoke that application at the end
> of a successful install/upgrade.
>
> I've done it both ways and there are no real advantages to doing it
> during the install transaction and there are many disadvantages.
> --
> "The Direct3D Graphics Pipeline" -- DirectX 9 draft available for download
> <http://legalizeadulthood.wordpress.com/the-direct3d-graphics-pipeline/>
>
> Legalize Adulthood! <http://legalizeadulthood.wordpress.com>
> .
>

[Please do not mail me a copy of your followup]

=?Utf-8?B?Q2FtZXJvbl9D?= <CameronC@discussions.microsoft.com> spake the secret code
<DBB26448-AAFF-43C5-9E6F-0BD19BD49D65@microsoft.com> thusly:

>I was concerned that they might go out of synch if I continued to deploy in
>two separate steps.

Yes, this can be an issue.

However, it is easily solved by recording the schema version in the
database and having the application query the schema version at
startup. If the schema version of the database doesn't match that
expected by the application, then perform the schema upgrade process
or handle it some other way.

Its easiest to keep database management in a utility that's separate
from the install. First, it keeps the install simple and simpler
installs are more reliable. Second, it lets people upgrade their
database without having to run the installer.

Suppose I have to restore my database from a backup that was taken
before the upgrade? If I have an upgrade utility, then this is not a
problem, particularly if the application performs the upgrade
automatically after sensing the downgraded schema.

Having the database maintenance in a separate utility makes it easier
to debug and test the upgrade process as well.
--
"The Direct3D Graphics Pipeline" -- DirectX 9 draft available for download
<http://legalizeadulthood.wordpress.com/the-direct3d-graphics-pipeline/>

Legalize Adulthood! <http://legalizeadulthood.wordpress.com>