clementhuge (8/23/2010)
Hello,I might have read the article too quickly. So I apologize if I miss some information.
The methodology you are taking is very similar to the methodology in place at my work.
However, in real world, it is almost impossible to just let it run via sqlcmd, except for minor updates.
Like a lot of companies, There are 4 to 5 environments (dev, staging, integration prérelease, production) and a lot of environment variables make usually the things go wrong.
All can be variablize, no question. I love SqlCmd, do not get me wrong.
In the best world ever, developers would not messed up their code but they do all the time.
Second point, you apparently miss the rollback script. You should be able to rollback as soon as the scripts are deployed to check that rollback scripts work. Then you can after one day, decide to rollback if you see that performance is poor or that there are unpexpected behaviors by your applications.
Finally, I do not understand why you use xp_cmdshell. You can put you sqlcmd line directly on dos and you can just change the line within you agent first step.
I am guessing you can use xp_cmdshell if you like to parameterized in some sort of database repository all the scripts that you went through. Still, you should activate and deaxctivate xp_cmdshell as you would keep a security hole in place otherwise.
I appreciate the feedback!
I wasn't targeting the rollback as part of the article, but trying to show a way that we execute multiple updates against multiple databases. We ensure that the scripts have already been executed in the lower environments and proven by the time they get to the production release. Certainly, a second SQL Agent job could be utilized to handle the rollback in a single unit of deployment.
We utilize xp_cmdshell so that we can keep everything self-contained within SQL Server. When you say "change the line within you first agent step", can you elaborate? In the job step that invokes xp_cmdshell and calls sqlcmd, I am enabling xp_cmdshell, and then returning its state back to what it was after sqlcmd execution is complete. If it was disabled, it goes back to disabled.