Best practices SQL change scripts?

[frjames]

I am totally new to software deployment thru installers. Work in a TFS environment where we use the VS2010 tools to deploy changes to our hosted web environments: publish and ftp, web deployment projects and ftp, simply push source, database projects that create and deploy sql change scripts in SQLCMD mode to specific ms sql servers, etc.

I've scripted out initial install sql scripts, but curious what you'd recommend for database changes for software updates? Can redgate's sql compare product be run during install to generate a change script between an install sql script and the target sql environment, or do we create sql change scripts per version of the software and run those consecutively, etc.? What about content changes?

I know this has been done a million times before, but I come from a web background and have always either deployed changes manually or relied on others to maintain db changes. So looking for some suggestions on how best to create and manage all of this. Thanks!

[mihai.petcu]

Hello,

or do we create sql change scripts per version of the software and run those consecutively, etc.? What about content changes?

Indeed, the recommended way is to have the upgrade changes or uninstall changes in separate SQL scripts. This scripts should be conditioned to run during their designated operation (upgrade to a certain version, uninstall, etc).

All the best,
Mihai

[frjames]

Should the script itself hold the logic for upgrading from one version to another, or can you selectively run scripts from the installer somehow? If so, any tutorials? Also, any suggestions on tools to generate these sql update scripts. DB is ms sql server 2008 and schema is saved in db project.

[mihai.petcu]

Hello,

Should the script itself hold the logic for upgrading from one version to another

Yes, to upgrade the database. You cannot generate the differences SQL script using the installer. You'll need an external specialized tool for that.

can you selectively run scripts from the installer somehow?

You can run the SQL scripts selectively by conditioning each of them from the SQL Scripts page.

Here're a couple of related articles you can follow:
Configuring Databases with SQL Scripts tutorial
ODBC/SQL how tos

All the best,
Mihai

[frjames]

I've read through all your how tos - specifically, can the installer tell what version the user was on, and then run this sql script, and this sql script, etc. It seems the old version would be uninstalled by then (major upgrade), so all that logic would need to be done in the sql script. I'm not talking about running a script at install, or at uninstall.

[mihai.petcu]

Hello,

The OLDPRODUCTS property is actually list with the Product Codes of the older versions of your installation package. This property is set only in a package which upgrades another package.

You can store previous version's product codes in properties and use them with the OLDPRODUCTS property in your condition.

You can use the following operators in your condition:
>< TRUE if left string contains the right string.
~>< TRUE if left string contains the right string. The comparison is case insensitive.

For example:

Code: Select all

OLDPRODUCTS >< VER_2_1_PRODUCT_CODE

All the best,
Mihai

[frjames]

Getting a list of installed product codes or a blank list helps conditionally run the fresh install script, but I don't understand how that helps me run a batch of version to version upgrade scripts. Can I tell what version they're updating from?

Here is what I want to do:

Fresh Install - Run my install sql script - which will create latest database schema and initial data.

Upgrade - Cosmin said I need an update script that will update any version to current version. I've added a version table and records of installed versions. But I cannot conditionally run blocks of sql based on their latest installed version due to GOs and CREATE ddl statements that must be only statement in a batch. I found some ways around this using a ton of if statements and dynamic sql, but that seems very difficult considering tools like vsstudio database deploy scripts or redgate sql compare don't export that way (to my knowledge). I also don't want to have to edit a old version to version change script once it's been created.

So here is what I want to do: Have multiple sql scripts included in sqlconnection: 1.00.0001.sql (which updates previous version 1.00.0000 to 1.00.0001), 1.00.0002.sql which updates 0001 to 0002, etc. and have YOUR installer batch up the sql files it needs to run. So I need to know what version their upgrading from, and then fire off the next version sql files up til the latest. The sql scripts should be "dumb" as in they just run if they're told to - they shouldn't have version checking logic in them.

The only solution I can think of w/ your product is not to allow clients to skip msi's when updating. This would be a major pain if they're 20 versions back. I know this is a universal issue - and I'm just working w/ ms sql server. Can someone please give me a clear process on how to accomplish database updates using advanced installer - or external apps I can create that AI calls if OLDPRODUCTS is not empty? Thanks.

[mihai.petcu]

Hello,

Getting a list of installed product codes or a blank list helps conditionally run the fresh install script, but I don't understand how that helps me run a batch of version to version upgrade scripts. Can I tell what version they're updating from?

You can have a public property storing the product code of each previous product. This way, when installing a new product and OLDPRODUCTS is not empty (there is no previous version installed), you can use those properties to detect what version is installed (its product code is appended to the OLDPRODUCTS property).

For example if you are deploying version 3.0 of your product:
-> for the sql script that needs to run when version 1.2 is installed on the target machine simply set its condition to:

Code: Select all

OLDPRODUCTS >< VER_1_2_PRODUCT_CODE

VER_1_2_PRODUCT_CODE stores the product code for version 1.2

-> for the 3.0 fresh install sql script that needs to run when no version is installed on the target machine simply set its condition to:

Code: Select all

NOT OLDPRODUCTS

Let us know if you have any questions.

All the best,
Mihai

[frjames]

Are you available by skype? If old products contains version 1.2, it will contain version 1.2 the next update, and thus rerun the same script via your logic. I'd think knowing last installed version would be required, so then I could somehow batch up the scripts necessary to get to the latest version. But I don't see how that can be done w/ your product. I also am NOT going to create update scripts from every prior version to current. I'm obviously missing something.

[mihai.petcu]

Hello,

Are you available by skype?

Unfortunately we only offer sales support via Skype or phone. Since technical support takes more time and may require an investigation, we usually handle that by e-mail or forums.

I also am NOT going to create update scripts from every prior version to current. I'm obviously missing something.

The methods I exemplified above describe how you can detect any previous version when installing a new version. The way you condition the scripts depends completely on your upgrade scenario.

All the best,
Mihai

[frjames]

Please advise how to condition scripts for this scenario:

Latest version 1.5. Prior released versions were 1.0, 1.1, 1.2, 1.3, 1.4. User had version 1.0 installed.

SQL files included in sql connection:

1.5.install.sql:
CONDITION: run if oldproducts is empty

1.1.sql (upgrades db from 1.0 to 1.1):
CONDITION: run if oldproduct contains 1.0 but doesn't contain 1.1?

1.2.sql (upgrades db from 1.1 to 1.2)
CONDITION:

1.3.sql (upgrades db from 1.2 to 1.3)

1.4.sql (upgrades db from 1.3 to 1.4)

1.5.sql (upgrades db from 1.4 to 1.5)

The 1.1-1.4 upgrade scripts won't add anything to oldproducts, so if I somehow get sql 1.1 up to 1.5 to run, oldproducts on next install to 1.6 would have 1.0,1.5 in it. Since oldproducts wouldn't have 1.1, 1.2, 1.3, or 1.4, how can I condition them to NOT run again when 1.6 is installed? You seem very certain I can have separate sql version scripts and condition them for all upgrade scenarios, but it is not making sense to me. Please advise. Thank you.

[mihai.petcu]

Hello,

You can choose between two approaches to your SQL upgrade scenario:

First approach
- you are developing the installer for 1.5 version upgrade
- you have an SQL file that can be installed on a clean machine
- you have an SQL file that upgrades each version to the next
-> create a custom action that uses the information from the OLDPRODUCTS property to determine which SQL script files need to run
-> that custom action should set a public property for each SQL file
-> you can use each public property as a condition

For example (uses the sql files' description from your last post):
a)
- when installing 1.5 on a machine with 1.0
- the installer should execute 1.1.sql, 1.2.sql, 1.3.sql, 1.4.sql, 1.5.sql in this order

b)
- when installing 1.5 on a machine with 1.3
- the installer should execute only 1.4.sql and 1.5.sql in this order

c)
- when installing 1.5 on a clean machine
- the installer should execute only 1.5.full.sql
where 1.5.full.sql is the SQL file that performs a full 1.5 install

Second approach (recommended)
- you are creating the installer for version 1.5
- you have an SQL file that can be installed on a clean machine
- you have an SQL file that upgrades each possible installed version
-> use the conditioning approach I described in my previous posts

For example:
1.0.sql -> upgrades the database from 1.0 to 1.5
1.1.sql -> upgrades the database from 1.1 to 1.5
1.2.sql -> upgrades the database from 1.2 to 1.5
1.3.sql -> upgrades the database from 1.3 to 1.5
1.4.sql -> upgrades the database from 1.4 to 1.5
1.5.sql -> full install

a)
- when installing 1.5 on a machine with 1.0
- the installer should execute only 1.0.sql

b)
- when installing 1.5 on a machine with 1.3
- the installer should execute only 1.3.sql

c)
- when installing 1.5 on a clean machine
- the installer should execute only 1.5.sql

All the best,
Mihai

[frjames]

The First approach is what I want to do but I don't know HOW to do the following:
-> create a custom action that uses the information from the OLDPRODUCTS property to determine which SQL script files need to run
-> that custom action should set a public property for each SQL file
-> you can use each public property as a condition

I'd consider the Second approach if the First approach is too difficult, but I am confused on how this works:

For example if you are deploying version 3.0 of your product:
-> for the sql script that needs to run when version 1.2 is installed on the target machine simply set its condition to:
OLDPRODUCTS >< VER_1_2_PRODUCT_CODE

This means oldproducts contains 1.2? What if it also contains 1.3 or a later version? Wouldn't that condition have the 1.2 script run every time thereafter? OR does OLDPRODUCTS just have the latest version in it?

Thanks,
James

[mihai.petcu]

Hello James,

The First approach is what I want to do but I don't know HOW to do the following:

Unfortunately we cannot help you develop or debug a custom action. You can search the Internet for samples and guidance.

This means oldproducts contains 1.2? What if it also contains 1.3 or a later version? Wouldn't that condition have the 1.2 script run every time thereafter?

This can happen if you have "Allow side by side installs of different product versions" option enabled. You never mentioned this before. All above suggestions apply for "Automatically upgrade older product versions".

OR does OLDPRODUCTS just have the latest version in it?

The OLDPRODUCTS property is a list with the Product Codes of the older versions of your installation package. This property is populated with the installed older products only in a package which upgrades another package.

All the best,
Mihai

[frjames]

We are not going to allow side by side versions.

I was under the assumption that oldproducts was a list of versions that have been installed on said computer. Is it instead all old versions in the product's lifecycle?

Regardless, If old products has 1.2, 1.3, 1.4 and you're installing 1.5 - how does it not rerun 1.2, 1.3, 1.4 scripts if asking if oldproducts contains 1.2, etc? I.e. how do you control that 1.2 script only runs ONCE? Does this make sense?