exciting

Unfortunately no; I think our dev and QA folks are still very new to database testing. Being on the release end of the pipeline, i've focused more on getting the releases standardized.

Would you recommend that? That's what I'm having our DB team explore to integrate DB testing (i.e., SELECT validations) and add this to our Pipelines.

I also suck at typing.

abso-frikking-lutely

and in some cases it's terabytes of data

Yes, changes are open-ended in terms of how much time they take

I'm just going to add this index as part of the upgrade [........] how long is this going to take? 🤷

We've evaluated a few over the last several years; part of the struggle of being a BU for a corporation is that the infrastructure is often a compromise. Our focus has really been on just getting the CI pipeline in place.

Why refactoring here? Rollbacks can be simple for code (procs/functions/packages/etc.). For tables, this is really a pipeline challenge.

And is gonna depend on the rate of change in your databases. High transaction dbs are very different than datawarehouses, for example.

I am as puzzled as you are. But there always has been something in the database-related code which made the automated rollback something unpredictable. ¯\(ツ)/¯

There could be. Doesn't have to be, but I have seen some strange db environments that make me scratch my head and require special handling.

and how can we do a homogeneous process against different database vendors?

we always plan for a migration or roll-forward. We make sure our changes are small so we can keep the revision plan tight.

I blame everything on @grant.fritchey

Need to get him a trackpad. 😄

It was probably me.

np...just ended up turning down the volume.

Also you make the Patrick Stewart tool, Make it So / SQL Compare

Ha! Yep. That's the foundation for a lot of the other tools. too.

what would be the case where you want to separate schema from code repos?

As much as possible, I want the code and the db to be on exactly the same version. No deviation.

yes, we are saying the same thing, I was just offering your a strategy for your rollback issue

vaidik: they're all edgecases and should be avoided at most costs, usually when schema is shared between more than one source,

I tend to want the db separate, because often there are multiple apps (ETL, reports, etc.) that query the db. I prefer the db as a first class citizen, not a dependency of an app. However, when you deploy code, often there is a need to have some versioning in the db that allows an app to be sure it works with vXX+. If db v3 adds a column and db v3.2 adds a second one, my app might need to be sure the db is at v3.2 or turn off feature flags.

yeah there is no one size fits all for every system

but a schema migration tool might help with the version stuff

ah right. sharing schema with more than one app just feels like a nightmare - too hard to control

but yeah in that case, you would want the schema in a separate repo with its own workflow

and delivery mechanism

YES. SOURCE CONTROL is TRUTH!

💯

absolutely. the endless battle of data refresh and data masking

Exactly, that's one of the very real issues that continues to come up

You are not only needing to mask the data but you're also needing immediately?

Yes, random prod-looking data (but definitely NOT production data) en masse in a random new environment would have saved many hours of debugging.

"immeditately" might be defined differently in different industries. But yes, normally people are trying to reduce the lag

We've got tools to address both those issues.

@scottd.harris and @rasika.vaidya I'll send you a PM

Redgate supports SQL, Oracle and these other PostgreSQL, MySQL, DB2, Aurora MySQL, MariaDB, Percona XtraDB Cluster, Aurora PostgreSQL, Redshift, CockroachDB, SAP HANA, Sybase ASE, Informix, H2, HSQLDB, Derby, Snowflake, SQLite, Firebird Hope this helps!

yep; that's a cultural problem yo uhave to overcome.

yup

I won't allow my engineers to use them unless they have no other viable option (which is rare)

Does depend. I'm in favor of ORM tools, but, not all code can be generated. Some is still going to be traditional, 3-5% max.

ask a dba how they feel about orms

I'm, technically, a dba. I love ORM tools.

🙂 orms generalize all your things at the cost of performance for the non standard things

explain an orm join query 😉

not all orms are equal here, but the usually fall into the same traps

True. However, 90-95% of the work an ORm tool does perfectly fine. It's all about identifying that last bit and making adjustments. Most ORMs support procedures.

correct

its that 5% that ends up costing you a lot later in most of my experience

you start making really odd/special business logic to get around some shity join or query the orm is building

Mine too. It's a big part of why, as a DBA, I've embraced ORMs and supported the developers in their use. It makes it easier for me to say, "All good, except over here."

as a dba engineers just say "sorry thats how my orm does it" 🙂

you give them a query that performs 10x better but the orm will make your life hell actually trying to run it

there's no one answer here.

did people abandon the idea of micro ORMs?

you just mean object mappers right? not sure the micro orm term?

my personal preference is for a library that helps mapping the db records to objects and then. most of the queries are built from simple db libs

it referred to simple DSLs, where you could easily use db language if needed, and then object mapping the results

it was picking up 5 years ago with Dapper on .Net and JOOq on Java

ahh yeah the problem with tools that abstract the db underneath is they usually perform poorly under load.

they also like orms try to generalize things between db's and usually performance is much better when you specialize

Very poorly. However, like any other tool, you can use them in ways that work and in ways that hurt, a lot.

100%

oh that's an awesome question! I really see a seperation of responsibilities that are now traditionally combined in the role of DBA. Server admin (backups, configuration, security) and development (performance tuning, etc).

Anyone has worked with Robot Library Python or using Groovy to test the changes?

right now, we're using Octopus deploy. we have one pipeline, with variables.

We have not started database devops journey. I would like to learn more your experiences

absolutely!

what different database platform you are doing DB devops? oracle, sql server, mongoDB ?/

what is the best way to talk to you? One of my colleague may join

My number one recommendation is to have curated data sets for all work. This can be santized prod, or (preferred) a small known set of domain cases that you maintain, just like you maintain tests. This is the best investment I think you can make for db work.

mm, a big deal, we do is test data management practice, and develop our extensions to achieve that, selecting a catalog for the pipeline, and a final task to decomise that data

You can also use masking with distributed Referential Integrity to translate production data in a way that cross-app data references remain consistent.

are there any tools to manage test data sets? I've looked into this without success

it would be good to have something as easy to use as the repositories for containers and libraries

we have to develop our tool for that (yep, we have that thing called iSeries /DB2)

but, it’s just a Java client that move data between de ‘universe data’ to the environment and have a table to control the data that we have to delete when the pipeline ends

would love to be able to rebuild a datastore from a dataset at any time…the references between databases and domains where those are owned by different teams is where that becomes hard

that's interesting but I feel there is a simpler gap to fill which is just a tool to manage the set of domain cases to be able to do different tests as @stephen referred

thx…will check that out

that concept does feel to align

There are masking tools, Redgate makes one, others are out there. I haven't seen good subsetting tools, which is something I wish Redgate or other vendors would work on because these are important. Some of my customers that have had success will pull rows from a prod db and store them in a "testtemplate" db, masking as needed. They then use this as the base image for data virtualization, restores in qa, containers, etc. This is the basis for all work. Over time, they continually add new edge /corner cases from prod as they need to. As an example, one customer has a multi-TB db that is templated back to about 10GB. This is regular work for devs to keep the template up to date. Disclosure: I work for Redgate.

Steve can you elaborate on the templated bit?

Sure, you keep a db around that is essentially your template. You can handle this in a few ways, but essentially think about having a regular process that: 1. backups up template db 2. restores this in Qa/dev/etc. Everytime we hydrate a new environment for a developer, a QA env, a CI build, we use this same db. This way everyone always has the same basis for starting work. Think of this as a new branch off main for a C#/Java dev. They always have a known starting point. Since dbs grow and change, as we get a ticket or bug, and we realize that we don't have data to match this, say the "Steve Jones account from prod", we copy this data from prod-> template, santize it if needed, and then the next time the process above runs, we have a new db that allows us to work. Ideally, that process is both scheduled and demand driven. These days, I would expect customers to do this with data virtualization or containers. Having a known starting place for every bit of work that is updated as we deploy changes and grow. This isn't simple, because changes to the prod db (Deployments), need to be run against the template db as well, so that developers can keep up to date. Likewise, anything you pull from VCS wrt db code, needs to be applied. This is where migration based frameworks or tooling, like FlywayDB, ensure that you automatically upgrade your copy of the template db when you start work. If devs have updated the VCS with other code, it gets applied to the copy of the template db.

This isn't simple, and it's a change, but it's somewhat like the chance for C# devs that needed to start writing unit tests. A PIA to start, but over time, it becomes a part of work. Maintaining test data is the same thing, albeit with a different process.

makes sense, thank you. I've done something similar with reference data sets, wondering if you seen any kind of procedural data creation to get to these states or to make them easy to maintain? I find the process on clojure.spec interesting on this side of things but I've not used it in the real world.

There is tooling for non-transactional data, so list of countries, statuses, postal codes, etc. This is easier to maintain because this is really the same in all environments. For transactional data, it's harder. There are masking tools, such as Data Master for SQL Server or Oracle (https://www.red-gate.com/products/dba/data-masker/) that can make this a repeatable process, but this doesn't help with the scale of transactional data. Masking all data in a 10GB db is easy, and works well on dev laptops and CI build machines. This is less useful on a 1TB db. Both for time and resources. Instead, we need subsetting tools. I have customers asking about this, and I've tried to get Redgate to invest in this, but so far, no luck. I do need more customers to make this a priority, or even prospects, to justify investment (https://www.red-gate.com/blog/database-devops/database-subsetting-wed-love-hear) , because this will dramatically change agility in the world. There are some other solutions, like random generation, but these don't necessarily cover the cases against which we need to test. Most of the solutions I see from Oracle, IBM, MS, etc. are really putting a large load on users. Companies that develop a process usually love it, but it's a small percentage (< 5%) that build a process to maintain this.

Usually it's a one time project, which starts to fall apart over time as requirements changes.

Feel free to ask more questions, or join the RG happy hour tonight. This is one of those things that is actually not that hard, but it is complex. Lots of protocol and process to implement and stick to.

thanks I will bring some if they arise

cheers

QA is a magic cloud for me 🙂

@occasl We`re testing Robot python library, groovy, and Dbunit

Seriously, though, we use the SQL Change Automation tools, and a lot of the Red Gate tools to do comparisons, and it does a lot of basic sniff tests

@santiaca which Python lib (link?)

when it comes to functional unit testing, i'm not exactly sure what QA and our dev teams use. I will check though. I'm mostly focused on what's changing, because i'm interested in the stability every time.

@stainsworth331 sure, I'd love to know

Does it make sense to test the database outside of testing the application itself?

@occasl Robot framework. You can do differents kinds of asserts

@nick.kritsky sometimes. It depends on the change made because it may not be surfaced in the UI.

@santiaca Is that built on Selenium?

@occasl we've got both approaches of the State based model and Migrations based approach

@occasl No, but you can use it

Cool...thx for sharing. We're using Katalon, so hoping we can integrate DBUnit into that.

It's Java/Groovy based.

Free Fridays! (4 hours dedicated to a project)

I'm not a manager, just a nerd. However, as a nerd, I ask the boss to allow me time for research & learning. Has to be part of the job.

I think i just answered that, but we can talk more about that if you want!

or that is , the other me on the screen

yeah i just wondered

so we use personal OKRs as a way to sort of have certain personal goals decided over a longer period of time that helps an individual grow holistically (tech skills, leadership, communication, project management, etc.)

and these goals are set with mutual consent

however often certain individuals have a hard time achieving those goals

for some reason even with their goals clearly laid out, they have a hard time executing

have you had similar experiences?

I think every manager has 🙂

I love the idea of the personal OKR approach, but again for me, it goes back to the abstract versus checklist approach

I'd be curious if your abstract thinkers are the ones that have a tough time with goals (or is it vice versa)

For me, i have to have squishy goals for my abstract people

both actually. and most of them are old timers, when we didnt have structures and processes like these

we are a startup

so the people i am talking about come from a time of being super unstructured and constantly being in "war mode"

Yes.... and there are a lot of internal customers. And of course, the internal processes end up having an external customer impact eventually.

Exactly…much like Dr. Spear was talking about this morning, know what problem are they (the business) are trying to solve and how we fit in help hone in on the individual customer, internal or external

In team coaching the purpose and the team's 'job' is derived from asking about and meeting the needs of all stakeholders, internal and external!

This is such a great question! When looking at value streams which tends to constitute multiple teams, its better to normalise the work into key types of work. We abstract that out to Feature , Defect, Risk and Debt at a top level and apply that across teams. And use this level for Flow Metric and data from Application Management Tools help hone in on bottlenecks and identify where improvements will provide the most impact on value. We have see a lot of value in having an Enterprise Flow Team that have a larger visibility across value streams can augment the learnings by providing the systems thinking. This is where Ways of Working teams can help cross pollinate great ideas. Not easy - its a learning process, through experimentation.

Thanks for joining!

Calico or Cilium are great choices

We're using Canal, kinda combines Calico and Flannel.

yes, Sysdig also is focusing on Calico for vanilla k8s, gke, rancher eks etc and openshift OVS if you're running on that

We're on Rancher...which makes K8s a lot easier 🙂

that is exactly correct @prasad.gamini we did some customer usage analysis, and found 58% of containers are running as root in prod https://sysdig.com/blog/sysdig-2020-container-security-snapshot/

yikes

😱

so, so. Not as good as other products.

@matthew.cobby I have the same feeling. Their UI is unusable for a lot of images.

Still waiting for them to fix that.

You have to push images to a registry to scan - Sysdig’s inline scanning  scans directly in the customer env and only sends the results back to Sysdig. You don’t have to share images/registry credentials with a 3rd party tool

With Aqua - periodic rescanning required -- Sysdig continuously evaluates all running images for new vulnerabilities

B/c of their LD_preload approach, Aqua has no support for Go based apps (doesn’t support static binaries)

sysdig k8s integration is very rich - can tell you a specific vulnerability maps back to a particular service/namespace/cluster etc

Secrets and configmaps are near identical in mechanics, but they afford greater separation possibilities via RBAC

Fewer built-in roles allow “get/list secrets”

https://www.youtube.com/watch?v=u409G5PsO1w this goes into more detail @occasl

also if you're looking at k3s - https://sysdig.com/blog/k3s-sysdig-falco/

checking on this one specifically... we have API integrations for many of the CI/CD tools - bamboo, circle, gitlab etc

inline scanner or direct call to the api will work for teamcity @occasl

Appreciate that, thank you.

thanks!

So my understanding from the replies is that it becomes a strangler pattern due to lack of support.

Not sure if we fully covered this, but automated governance is definitely a trend I hear more and more about at each DOES conference. Also “policy as code”. Talk to @tapabrata.pal and @john_z_rzeszotarski if you haven’t already.

I appreciate it! Thank you.. will have a chat with these folks.

when you say “security”, do you mean the tools? the team? the practice?

team

@stephen Thanks. I heard @rradclif speak a few times at past conferences. I actually don't think it is a bad thing to eventually strangle off old systems and technologies due to lack of support. The same argument can be made for finding people with the skills to maintain these old systems - eventually the lack of a talent pool with strangle them if nothing else.

Which book were you discussing regarding the mainframe? I do not see us every getting rid of ours. Too stable and reliable 🙂

@robyn.talbert, you can find her talks at prior DOES conferences on YouTube. Here is one example: https://www.youtube.com/watch?v=TwkJvsmZpF4

Thanks so much Mark!

One approach is to adopt inline scanning  in the pipeline/registry. This means you scan directly in your env and only send the results back to a 3rd party tool like Sysdig. You don’t have to share images/registry credentials with the tool either. This gives you better security by keeping you in full control of your PII data/image contents @denver.martin

I like this approach, @pawan.shankar. And I’m curious about what the submarine doors look like in your process @denver.martin. I’m going to DM about this!

@stephen we put a Fortinet Virtual appliance on our side in the AWS, then the 3rd party does as well, they can Fortinet or any other vendor (Cisco, Pulse, Checkpoint, etc) and we then connect via IPSec Tunnel. this means we both can close the connection if needed. The other option was to connect via AWS Peering, but that is sharing a lot more open and is controled by only SG rules. You could do ACL but then it become more management. Like a submarine both sides can close the door if there are issues..

More about the Ops side then the Dev side of things..

Cool! — that makes sense. Thanks for the info.

A peer review of code before merging to trunk works for both code and IaC. When it has been through two pairs of eyes, it can be considered formally accepted. So, that's how we do it pretty much like you are saying. There is a trial of annotating the repositories so that all CMDB-things get updated accordingly.

You don't need change reviews if anything those are supposed to do becomes transparent results of a pipeline.

Check out ServiceNow's speaking session TODAY at 1:35 PM: From velocity to value – scaling the DevOps impact 📣 @eric.ledyard will be in #ask-the-speaker-track-4 for Live Q&A during the session!

@ferrix True - you might not need them. Tho in the form of pull-requests they can be usefull for triggering other things (both fully and partially automated) which can provide alerts/flags (like SONARQUBE does for tech-debt, only this would be a security/vulnerability/ea-equivalent of that for a scanning tool. Some of which exist - yes/ Verity/Verily, evens ome OSS ones)

Yeah, we have SonarQube, ?AST, license checking and all kinds of things in the pipeline.

Definitely agree with PRs as a key point to surface code scanning issues (if you have quiet tools that don’t generate too many false positives). It’s the main point of integration for Muse.

Test cases for expected behaviour

Clean commit messages showing intent.

Test results? Or testing the config with a new deployment? I'm thinking here of an auditor coming back to check on Jan 1, but asking about a vulnerability or a patch from the previous Oct 1. How can they verify that changes were actually implemented? Certainly checking a VCS might work, but I'm curious if auditors have issues with this.

Log the installed package list after a patching activity and store with other proof

yeah - I was thinking its like config-as-code using TDD, CI (with scanning tools), refactoring, with PRs built-in to the flow (and some config rules to determine what outputs are treated as warnings, vs must-review, vs etc.)

@steve.jones If you are so lucky to produce a positive from a vulnerability analysis, you can prove it in staging and even production. So, pretty much the same pattern as coming across a bug and proving it gone with a test.

Thanks

we also made PR and Commits as part of the approve change management process…

here’s an example of chatops + code scanning: https://github.com/curl/curl/pull/5971#discussion_r490253770

first step toward what you’re describing perhaps (and we’re working on better and more full-featured interactivity)

Go, Jenkins

Jenkins is supported out of the box

Great use case. Now with AIOPs, metrics, logs, and observable data along side Flow Designer and Integration Hub it’s easier then ever to have more automated ops

@scott.dedoes is that question for ServiceNow or for @srujit.biradawada?

ServiceNow

Hi Scott, great question. (Laurent I think he’s referring to what Ben said). We’re working with pretty much any tools that are holding, configuring and using configuration data. The management piece Ben is talking about means we keep a repository of all the configuration information and add access control to that data (instead of having people work directly with the configuration information).

So a dev working on something moving to production will use our product to make configuration changes and we’ll apply intelligent policy to that before passing the change on to the actual deployment tools.

Thanks for the follow up and apologies as I'm not an engineer but very interested in learning. So the ServiceNow product doesn't actually deploy the features for customers? Another part of feature flags that my question pertains to is the feature releases for ServiceNow's products. Does the company deploy these using their own feature flag platform or with third party tools? My company is working on an integration with ServiceNow for a mutual customer and just trying to understand the ServiceNow products and release process better. Thanks!

If by ServiceNow products you mean ServiceNow scoped applications and update sets (deployment between ServiceNow instances) we have a CI/CD solution for that which utilizes integrations with some 3rd party tools - there are more details here: https://docs.servicenow.com/bundle/orlando-servicenow-platform/page/administer/integrationhub-store-spokes/concept/cicd-spoke.html

There is a deep dive YouTube video on it here: https://www.youtube.com/watch?v=I9BRmKjc_8s