Opinion on automated Deployment to Production

  • Hi everyone,

    I thought I'd poll everyone's opinions and experiences on this subject.

    How does the group feel about automating Production Deployments/Pros and Cons?

    There is a cross DB platform deployment tool FLYWAY that helps automate deployments by version'ing sql scripts ran and logs deployment info as part of it's process into a DB table.

    I'd like to be cautious and look at all angles and get the groups personal experiences.. as 2 (or more) heads are better than one.

    My perspective is reviewing the code before running and testing on a non production server first before it gets to production will reduce risk. (Granted as a DBA we'll have to review and see what the code changes are in action -- for example a alter table to a column would grow transaction log...so that needs to be monitored still if automated.. and DBA needs to be around)

    Every place I've worked production promotions/deployments have been tedious and manual, this also helps slow things down-that in my opinion can help catch mistakes too. I'm not against automating if there are checks and balances.. but wanted to hear people's thoughts as well.

    Thanks for input!

    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • Can be useful if done correctly.

    Test it to a staging environment first and make sure you're aware of all the gotchas and caveats the system introduces.

  • Manic Star (8/6/2015)

    Can be useful if done correctly.

    Test it to a staging environment first and make sure you're aware of all the gotchas and caveats the system introduces.

    Thanks for replying! So far the plan is similar to what you've suggested. This may save DB Deployment time and also please developers at the same time.

    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • An approach we have adopted for one of our production systems is automated deployment via a web service.

    To help put that in context our development team continually creates what has been termed packages that include database and application code. The database element contains a single file other than the SQL Files used for the deployment of the code The file is a manifest file which details the build version of the package, all sql scripts to be deployed and there execution order.

    The package is generated from the source control system via an internal web site that allows the developers to build the code, they can then deploy to the development environment by using functions on the same site which in turn call a web service developed by the DBA team which will get the package, check the version matches what was requested, check that they are an authorized user and subsequently deploy the package. The web service uses a config file which determines where the package is deployed to and runs under an account which has only the permissions required. I.e. the account used for the web service in DEV can deploy to DEV SQL Servers but not QA \ PROD.

    We have a similar web service for QA and for production that can be called also, but the list of authorized users grows ever smaller at each stage, with deployment to production only allowed if the package has been marked as validated first by our DB Engineering team and a DBA representative. With the package signed off the web service will allow deployment to production.

    All the meta data that supports the web services, authorized users etc is stored in a configuration database managed by the DBA team.

    The feedback from our development teams has been positive as they can now get the code into DEV almost immediately, QA after review by DB Engineering and production once approved by DBA an a release manager.

    The web services also have numerous other functions for reporting what packages have been deployed, their deployment history, what files were deployed, any errors encountered, automated alerting to the DBA \ DB engineering team of failures and scripts with poor performance.

    It has been a lot of work for myself and others in the DBA team creating this process but it make deployment much much easier.

    MCITP SQL 2005, MCSA SQL 2012


    Be very careful when using generic tools to script out code for production deployments. I would frankly use Data tier application before I even consider using a third party tool. There are plenty of reasons for Automated deployments to production I am only warning about the usage of one size fits all software to achieve the results. Either way make sure your DR is setup properly to allow you to fall back when needed.

    Jayanth Kurup[/url]

Viewing 5 posts - 1 through 5 (of 5 total)

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