Customer Database Update A Practical Solution
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sSnisarenko/customerdatabaseupdateapracticalsolution.asp
-
A disagree only ate the trigger cannot be nested afirmation, from BOL:
Nested Triggers
Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows nested triggers. If nested triggers is off, recursive triggers is also disabled, regardless of the recursive triggers setting of sp_dboption.
Of course the order off creation is yet unimportant.
Apart this minor disagrement the article is excellent. Customers db update madness is a major issue at my actual job and I´m spreading this article to my coleagues. I hope this can generate a productive discussion between us (with a usefull and robust final application as result).
Jean C. Bulinckx
Soft. Eng.
-
Excellent article by Serhiy Snisarenko !!
I was looking for a such an automation script for some time. Liked the first part of the article and learned some good DOS commands. I found the
second part of the article PREPARING UPDATE PACKAGE bit lengthy. Over all a very good, informative article. Serhiy, Keep up the good work,expecting similar article in future!
-
I suspect the note about nested triggers in the article is stating that one trigger cannot refer to another trigger - it is implicit by performing an action against another table. Thus, as you eventually state, the order of creation of triggers is unimportant and each trigger is unaware of the presence of other triggers on the tables.
I was interested to read this article as I had recently finished building my own auto update utility for updating client's databases to the latest version. I have taken a very different approach...
Each time I create a table, modify a stored proc, etc I save the code executed by enterprise manager (if modifying tables on the database diagram) or the ALTER/CREATE statement from query analsyer in a file on disk. Also, any DML that I need to execute on the database (such as populating some default data for a new column) are also saved as a script file. The files are organised into folders as such
Scripts /
20060401/
20060403/
20060410/
Thus the changes made to the DB in a version - I use the current date as the version number - are all stored in the folder. The filenames of the scripts are unimportant - but their timestamps are important as this ensures they can be executed in the correct order. The names of the folders should also appear in a sorted order - hence the YYYYMMDD naming convention - the folder name is used as the database version identifier.
Once I wish to close off a database version, I simply open a command prompt, go into the folder (eg Scripts/20060410) and type
dir *.sql /b /od > DoxScriptFiles.txt
This creates an ordered list (by file timestamp) of the SQL script files to execute.
The entire scripts folder is always shipped to customers so all scripts to upgrade from any DB version are included. I have a batch file + SQL server script which
a) determines the current version of the customer's database by examining the databaseVersion table
b) performs a backup marked with the current timestamp and the current DB version
c) Executes the scripts in each folder one by one - the folders, named as they are, ensure the the versions are applied in each order. The scripts to execute in each folder are read from the DoxScriptFiles.txt text file - again to ensure they are executed in the correct order
d) A versionUpgradeDetails table stores the date and time of each script execution as well as the complete text output
e) After each script is executed, I check the output for any error messages
f) As each version's set of scripts is successfully executed, I add an entry to the databaseVersion table.
If an error occurs, I inform the user and then perform a database restore. If no errors occur, all is well.
I have adopted this approach because
a) I personally save all change scripts so it is no extra work for me
b) There is a readily available audit trail of changes made to the database
c) It allows me to ship out the latest binaries of our software as well as the same database upgrade scripts. The customer needn't know what version of the database / binaries they are running - the "upgrade Dox" batch file takes care of determining the version and executes the scripts in the correct order as I made them - this ensures all dependencies are accounted for.
If anyone's interested I can post the code for the batch file and the accompanying SQL script file (which does most of the work of looping through the folders and executing the scripts).
It's a different approach which may have its drawbacks but it is much easier to deploy and doesn't require any extra processes during deployment to determine dependencies, etc...
My 2c - but anyhow, it was a very good article - very thorough and nice to see another point of view on the topic
Cheers
-
Thank you all for the interest in my article, and for your comments.
Generally I agree with Jean C. Bulinckx's comments regarding the triggers but in this particular article I described the process where the order of trigger creation should not affect the database upgrade.
Regarding the solution suggested by Ian Yates: I used this approach when I was the only DBA and the only developer in the small start-up company; but when I moved to more complicated environment with many developers working at several sites, and had to support a number of clients running different version of our product, I realized that I need more comprehensive solution. However, when I install service packs or hot fixes in between the main releases, I use the similar solution but I wrap my SQL code into XML.
And though the article might feel a bit long it is only because I tried to discuss as many common problems as possible, and provide ready solution for each situation.
Thanks again,
Serhiy Snisarenko
-
One thing that I would add to the batch files is to store the %ERRORLEVEL% value before going onto the next step. This way you can add flexability based on the result of the previous command. Depending on what happens, you can get different errorlevel values. It is one thing that you may find as you encounter different errors you may modify your batch files.
Example
osql ....
set ERRLVL=%ERRORLEVEL%
echo osql return : %ERRLVL%
if %ERRLVL%==0 goto nextstep
if %ERRLVL%==1 goto warning
if %ERRLVL%==2 goto error
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply