InstallShield and SQL Server transaction

[Kotiomkin]

Hi guys!

I have an InstallScript project that create and update a SQL Server database via script files. These files are launched by sqlcmd.exe command and all works correctly.

Now, if something during installation goes wrong, InstallShield rollback automatically, but (obviously) SQL database remains in a wrong state.
:eek:

How do I do rollback also the database? Consider that I launch a few script in series (I can't launch all in a single process, for some work ragions...), so I couldn't use BEGIN TRANSACTION command because it is limited to a single session...

Ex.

Script1 execution

Script2 execution

Script3 execution

For example if Script2 fails, I want rollback not only the Script2 execution but all works from beginning...

All suggestions are appreciated!
🙂

Thanks

Kotiomkin

[Christopher_Pai]

This question requires the experise of a DBA. Once the DBA understands how InstallShield schedules ( install, uninstall, logon, rollback ) scripts and executes them, he can consider the actual schema, data and objects that need to be deployed and (hopefully) design scripts to support the rollback requirement.

Frankly this isn't an easy thing and I don't think many shops attempt to get at this level of sophistication. If you wanted to be very generic ( and not elegant ) you could have several scripts

1) INSTALL - if exist database; backup current database
2) INSTALL - if exist database; upgrade database
3) INSTALL - if exist backup; delete backup
4) ROLLBACK - if exist backup; drop database
5) ROLLBACK - if exist backup; restore backup

[Kotiomkin]

HI Christopher
thanks for your answer!

Weel ... script are launched by me, not by IS. So I know extacly where I can freely manage any events (create db on a new installation, update on setup updating, and so on ...).

I don't think that create a backup and restore it in event of trouble is a good job, because it is a little complicated (and can generate other problems...).

I know also that in SQL Server exists the possibility to save/discard changes automatically by programming on transactions.

My problem is that I launch every scripts in a separate independent process, and every process has it own transaction.
It could be a good think if every single script execution (that has its single transaction) was enclosed in a top transaction managed in IS... But I don't know how to do it :confused:


Thanks to all people that want to contribute.

:cool:

[Christopher_Pai]

IS can't possibly manage SQL Server in that way. This is probably a good time to read:

http://robmensching.com/blog/archive/2007/08/17/Zataoca-Custom-actions-are-generally-an-admission-of-failure.aspx

There are three common reasons custom actions are introduced.

...

c. The setup developer wrote a custom action to configure some platform because the platform technology didn't provide installation primitives for itself. This one is just sad and, unfortunately, happens more often than not. My favorite platform to pick on is the Internet Information Services or IIS. The ever popular platform for ASP.NET that hosts a great many web sites around the globe provides basically no mechanisms to enable install, uninstall, rollback, upgrade and patching of web sites, virtual directories, app pools, etc. The platform failed the setup developer.