October 20, 2015 at 4:10 am
The client I'm working with at the moment want to streamline some of their processes and try to automate where possible.
One of the main tickets that gets raised is to release scripts to various DBs on various servers, there is no CI or source control for scripts, the dev's write them and pass them through on tickets or fileshares.
So was going to write something in PowerShell or SQLCMD to grab the file and based on the folder its in run it against a .ini file stored in the same area which knows the DB and Instance to deploy it to.
Just after thoughts, or peoples other ways of doing this sort of task as generally I would always run the scripts in manually.
October 20, 2015 at 4:35 am
Whats wrong with using Data Tier Applications ? you don't need CI ( although it would be good) or source control since the source and destination are compared directly for changes. IT also reduces the possibility of manual errors.
In SSDT you can actual create a project off the files on hardsik and then create a DAC off the solution.
October 20, 2015 at 4:47 am
The core app is a 3rd party app and DB, they just hook in and deploy their own changes on top of what they develop for the client.
Do have some which are all internally build, but most of the DAC's fail to build off the current databases due to validation failures, full text catalogs, synonyms, CLR, assemblies, asymmetrickeys etc.
October 20, 2015 at 5:01 am
hmm DAC does have some limitations, I don't think this will help since its very difficult to maintain but I did implement this for a client recently.
A source database has a table which contain sql server object definitions and the destination database contains a stored procedure which contains a dynamic sql proc which executes the definition as fetched from the source table by passing an ObjectID.
This way the client can update the definition in the source table and automatically deploy the latest changes to the destination without having to implement release scripts etc.
its very difficult to manage esp with all the single quotes within the object definitions in the source table, but the client is very happy since they don't need to rely on non tech savvy end users for performing deployments.
Personally I would setup Collabnet ( free source control) and make the one time investment of versioning files for deployment.
October 20, 2015 at 8:40 am
We do have Source Control, but each SProc / Trigger / View is in a separate file, so perhaps similar to your "Attached to tickets or on a fileshare"
We have separate master folders for each project, with sub folders - one of which is PATCH which is all the DDL changes.
We concatenate all script files with dates newer than "last time" into a single file. (Separate files for DDL and the Sprocs etc. i.e. basically one-per-sub-folder).
We then have a pair of "Release scripts" - one for DDL and the other for all Sprocs, Triggers, Views etc.
We can then run them against QA and then production. If we run them against QA and the sequence of objects raises an error ("A needs to exist before B") then we restore QA, re-arrange the objects in the script (basically chop out B and move it to the end!!) and try again.
If it runs "clean" against QA we assume it will run clean against Production (although we can restore Production DB to DEV Server and test that if we want to minimise downtime at the rollout, normally our maintenance window is long enough that we don't bother, and sort out any such issues at the time)
Benefit, as I see it, is that we have a single script which is repeatable. If I had a PowerShell script which ran "everything in a given folder" then I feel there would be risk that the contents changed / were executed in a different order (OR I couldn't FORCE the order when I needed to have A-before-B)
For us the Rollout Scripts are in our Version Control System - so we can see how they evolved / changed during rollout to QA (and to Production if they changed further).
This process also allows us to include DML scripts [i.e. at a specific point in the script-execute rollout process] when the need arises.
October 20, 2015 at 1:18 pm
anthony.green (10/20/2015)
The client I'm working with at the moment want to streamline some of their processes and try to automate where possible.One of the main tickets that gets raised is to release scripts to various DBs on various servers, there is no CI or source control for scripts, the dev's write them and pass them through on tickets or fileshares.
So was going to write something in PowerShell or SQLCMD to grab the file and based on the folder its in run it against a .ini file stored in the same area which knows the DB and Instance to deploy it to.
Just after thoughts, or peoples other ways of doing this sort of task as generally I would always run the scripts in manually.
DAC might be best if microsoft centric, I havent worked much with it but that one would be microsoft's sales pitch
I believe redgate sells one too (or idera) - but you need to pay
For free...
Have you considered "Flyway" ? It's an open-source tool for CI and allows you to deploy to every type of database (MS SQL, mysql, postgresql, oracle)... well all the relational ones
It also keeps a schema history of changes and writes to a table within your database so you can confirm code was deployed. I've just begun researching this and don't know about the complexity/roll back capabilities though.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply