Multi-Script, Multi-DB Deployments

  • SQLRNNR

    SSC Guru

    Points: 281210

    Here are some important points for me (some already mentioned):

    1. Source control.

    2. Ensuring that QA or beta is exactly like production (some automated process would be good here).

    3. Manual entry of the scripts into the cmd scriptlet. Too much chance for a typo.

    That said, I have used the same(similar) methodology in a few places for script deployment.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • S. Kusen

    SSChampion

    Points: 10849

    CirquedeSQLeil (8/24/2010)


    Here are some important points for me (some already mentioned):

    1. Source control.

    2. Ensuring that QA or beta is exactly like production (some automated process would be good here).

    3. Manual entry of the scripts into the cmd scriptlet. Too much chance for a typo.

    That said, I have used the same(similar) methodology in a few places for script deployment.

    Thanks for the feedback, Jason. Points one and two have been a theme here and I'll keep that in mind for future articles that are submitted. For point number 3, how would you approach that?

    All in all, as a first time submitter- would you have any pointers to improve on?

  • Varghese Pathisseril

    SSC Rookie

    Points: 25

    Good article with some value added deatils and examples.

  • SQLRNNR

    SSC Guru

    Points: 281210

    S.K. (8/24/2010)


    CirquedeSQLeil (8/24/2010)


    Here are some important points for me (some already mentioned):

    1. Source control.

    2. Ensuring that QA or beta is exactly like production (some automated process would be good here).

    3. Manual entry of the scripts into the cmd scriptlet. Too much chance for a typo.

    That said, I have used the same(similar) methodology in a few places for script deployment.

    Thanks for the feedback, Jason. Points one and two have been a theme here and I'll keep that in mind for future articles that are submitted. For point number 3, how would you approach that?

    All in all, as a first time submitter- would you have any pointers to improve on?

    I should have also noted that I thought the article was good.

    One thing we use to get past that currently is to use CM to deploy scripts from specific locations in TFS. That seems to work pretty well. I am still looking for a real good method to bypass the need for typing of each of the scripts.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Abi Chapagai

    SSCrazy

    Points: 2666

    Steve,

    We used to have almost same methodology but xp_cmdshell was not used since it was not recommend to use at my company. It is definitely a helpful article but roll back script should be kept in mind along with this script.

    Thanks for sharing.

    Abi

  • jhopkins-1015766

    Valued Member

    Points: 68

    Very well-written article!

    The process you describe is strikingly similar to one I developed for our environment, major exception being that we use source control, and the deploy is executed from the "DBA" source control working structure- thus enforcing that all changes are in the codebase.

    One benefit you didn't really emphasize is the transparency and documentation trail left behind by this process, especially if you incorporate script logging (basically Print statements recording timestamped progress) in the change scripts and/or deploy scripts. We actually invoke SqlCmd via a series of calls in a DOS batch file, passing source code files from the DBA working location. The DOS "Deploy Scripts" are named referencing a sequential Deploy#, and copied locally to the server in an "Installs\SQL" location by the Deploy Job; the "Prepared Deploy" folder, including Deploy Instructions from the Developer, as well as DBA-mediated scripts like Alter Tables etc., also named with the Deploy#, is archived. This provides a very clear documentation trail of exactly what happened and when.

    Now if we could only get the Developers to follow the prescribed development methodology, to allow this process to work as well as it should...

  • nishanth.marathe

    Newbie

    Points: 9

    Hi,

    Article is well written. I had one question though.

    Say you are executing lots of script files. (hundred's).

    Is there a way to Show a script file executed successfully or not?

    Something like ABC.SQL => SUCCESS. or XYZ.SQL => FAIL. I could not find a way to get the name the script file being executed

  • curious_sqldba

    SSC-Dedicated

    Points: 36281

    Yes. It does show but it goes sequentially,if the script fails on anyone of the servers then it wont go to the other. You will need to specify begin catch. I have a script for that. Let me know what exactly you want to do and then i can send you. thanks

Viewing 8 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply