May 6, 2016 at 1:19 pm
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.
November 11, 2016 at 3:34 am
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.
November 11, 2016 at 3:50 am
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
November 11, 2016 at 4:13 am
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]
November 11, 2016 at 12:33 pm
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply