Deployment Solutions that include Data?

  • Cole.Mietzner

    SSC Enthusiast

    Points: 167

    Hi all, I have been tasked with finding the best solution to have one touch deployment to our individual environments. I am finding a lot of options but most of them are for schema changes only. I need to be able to deploy schema AND Data (backfill of a table) changes. It would also be nice if I could deploy SSRS, SSIS, and SSAS changes as well but this is not as high priority as the first 2.

    Does anyone have and ideas on a solution that will just execute SQL files that I provide?

    Another requirement would be that it has to run through Jenkins.

  • S t e f

    SSC Eights!

    Points: 837

    Hi

    We use several tools for our CI development which may help you out:

    TFS or Git for our source control

    Red Gate Schema Compare to create our sql diff files

    Red Gate Data Compare to deploy any static data tables

    SQL Test for unit testing

    TeamCity for our continuous integration on our build server and unit testing after each check in

    TeamCity to create NuGet packages

    Octopus to deploy to each of our environments

    Hope this helps.

    Stef.

  • ThomasRushton

    SSC-Insane

    Points: 22591

    There's a handy blog post from Redgate on putting all that lot together - https://www.simple-talk.com/blogs/database-continuous-integration-with-sql-ci-and-jenkins

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • alex.yates

    SSC Rookie

    Points: 43

    Hi there,

    Do you have Redgate licences? If so using the tools recommended above should work well. Redgate will work out the upgrade script for you so you don't need to write it yourself. I've documented one way to do it here, although there are other ways. This way, for example, assumes you use Redgate SQL Source Control:

    http://workingwithdevs.com/database-ci-with-jenkins-a-step-by-step-tutorial/[/url]

    Redgate can also include your static/reference data. However, if you want to deal with larger amounts of data you could use something like SQL Data Compare command line or SSIS.

    However, it sounds like you want something to just run some SQL files that you want to write yourself. In this case it is worth looking at something like ReadyRoll (by Redgate), DBup (open source) or FlyWay (open source). That said, this approach does have it's drawbacks.

    If you are looking for tooling you should ask yourself, do you intend to work with the state of the database (with tools like the Redgate SQL Toolbelt or SSDT) or do you want to work with upgrade scripts (ReadyRoll, DBup, FlyWay etc).

    Generally working with scripts, as you suggest, provides more control but also more complexity. I've tried to explain in more detail here:

    http://workingwithdevs.com/delivering-databases-migrations-vs-state/[/url]

    If you'd like more support get in touch. www.dlmconsultants.com[/url]

  • Jeff Moden

    SSC Guru

    Points: 993924

    If RedGate Data Compare is used against relatively small amount of reference data, then it works just fine. The problem is that they generate data scripts that are RBAR on steroids in the form of INSERT/VALUES for each row. If you have a lot of data, then you should consider exporting the data to a "native" file and bulk inserting it when you get it to your destination. If the editions or releases of SQL Server are different, you may have to settle for delimited files, instead, but will still be much faster than 1 INSERT/VALUES per row for a million rows AND it will won't take as much room on your distribution media.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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