July 18, 2018 at 4:31 am
Wanted to kick off a discussion about Devops in the context of the enterprise data warehouse (EDW)
One of the challenges with the EDW is we always have ETL related data for lineage/audit records for instance, stored in tables we have records that co-ordinate data load i.e. date last loaded - next ID etc. On top of this we have data to describe tables (related to BIML) - this data needs to be deployed as it controls the building of packages. On top of this we can have static data for master data that may originate from MDS/Power Aps.
This means in order to have successful DEVOPS include CD, we need to separate out the data that needs to be deployed from the data that should not be deployed i.e lineage/audit records.
We have tried to use Redgate SQL Compare / Data Compare to generate scripts and then use DLM scripts with Jenkins for CD (with GIT). Whilst it works it is far from stable - primarily related to scripts from Data Compare.
With DEVOPS we can't tolerate instability for obvious reasons so we've had to resort to creating our own Data based scripts which isn't ideal.
Is anyone else running DEVOPS/CD for the EDW?
Interested to learn how other developers are getting on.
July 18, 2018 at 7:08 am
Hey!
I work for Redgate, just wanted to state that up front.
Instead of using Compare and Data Compare, have you tried looking at SQL Source Control? It works with whatever source control system you're using and is much more likely to do what you want. Further, it has the ability to source control the data within tables. So, if you have specific tables that need to deploy their data, along with structural changes, it's much more likely to be able to help. We also have a suite of tools for working with Jenkins (and other automation engines) that are likely to work better for you than Compare & Data Compare (as much as I love them). Further, if you already have the Toolbelt license, the SQL Change Automation tools are included, so you can put them to work for no additional cost.
We have supported tons of people working in all sorts of environments, including data warehouses. There's a lot of general documentation and how-to information published here that might help.
One thing I can tell you, all the support that I've done for helping people set up and maintain DevOps, none of the tools I've used works 100% without some degree of manual scripting around bits of shortcomings. I've had to set up pre & post deployment scripts back when I was using only Visual Studio Team System to deal with security, changes to the database that could result in data loss (can't deploy those at all with Team System), and other stuff. I'm sure you'll have to do a little of that with Redgate tools as well. The devil, as always, is in the details.
By the way, we work with partners on doing this kind of thing if you're looking for additional help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 19, 2018 at 8:52 am
Yep - we're learning about capability for tools to migrate changes. Latest problem we had was pushing a change to a column constraint.
We're able to overcome issues but seeing builds fail is rubbish when we have changes to make. When we keep commits for 1 change it make life easier.
Part of our issue with source control is that we have sql schema/data changes, BIML scripts, c# components, analysis services cubes, RDL's, SA jobs. If it was just SQL it would be much easier.
We're able to get everything into git that we need to build. What extra benefit does sql source control offer? We use sourcetree client with git to make it easier. Doesn't SQL Compare handle the change order and referential integrity?
July 19, 2018 at 8:55 am
I should add that we snapshot production db to dev to keep data fresh. Using SAN tools make it quick for multi tb dw 🙂 and we're using the Powershell DLM scripts which I assume is now called SQL Change automation. We're finding the DLM tools not as capable as SQL Compare which is understandable.
Do you think SQL Source Control removes the need for SQL Compare/Data Compare? Does SQL Source control play well with Change Automation?
July 19, 2018 at 9:14 am
leehbi - Thursday, July 19, 2018 8:52 AMYep - we're learning about capability for tools to migrate changes. Latest problem we had was pushing a change to a column constraint.
We're able to overcome issues but seeing builds fail is rubbish when we have changes to make. When we keep commits for 1 change it make life easier.Part of our issue with source control is that we have sql schema/data changes, BIML scripts, c# components, analysis services cubes, RDL's, SA jobs. If it was just SQL it would be much easier.
We're able to get everything into git that we need to build. What extra benefit does sql source control offer? We use sourcetree client with git to make it easier. Doesn't SQL Compare handle the change order and referential integrity?
Compare does the basics, yes. Source Control also handles data as a part of source control. It also integrates directly between SSMS and your source control system so you don't have to do, whatever you're doing now, to get the scripts updated in your source management software.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 19, 2018 at 9:16 am
leehbi - Thursday, July 19, 2018 8:55 AMI should add that we snapshot production db to dev to keep data fresh. Using SAN tools make it quick for multi tb dw 🙂 and we're using the Powershell DLM scripts which I assume is now called SQL Change automation. We're finding the DLM tools not as capable as SQL Compare which is understandable.
Do you think SQL Source Control removes the need for SQL Compare/Data Compare? Does SQL Source control play well with Change Automation?
Ah, we're getting into the edge cases a little here. I think you may have a little more flexibility calling Source Control from the command line, but yeah, the integration between source control and the Change Automation tools is tighter. It really depends on what you're doing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2018 at 5:58 am
Thanks. We will evaluate source control with DLM scripts.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply