Good afternoon ladies and gentlemen boys and girls. Welcome and thank you for joining us once again for another edition of DB Ayl. So yeah my name is Chris and I'd like to introduce my split personality Chris. Yes that's right I am also Chris and Chris do you want to tell the lovely people what this whole thing is about?
DB Ayl is essentially a podcast where we sit down and we have a beer and we discuss topics that interest us. Sometimes that's things that we've seen throughout the week so maybe news or interesting articles and sometimes that's something that's been more redgate related. So today I believe we've got a mix right? Absolutely yeah I think we're going to be talking about source controlling databases.
Oh that does sound fun. Isn't it quite but of course before we talk about source controlling databases before we tackle the news segment Chris what comes next? It's time for the beer. Hey my favourite.
So Chris obviously it was your turn this week to bring in the beer. Do you mind telling us what you've provided today? Yeah so this week I brought one of my I wouldn't go as far as to say favourites but one that I've tried a few times I really like it. It's called a white ale from iStock or the company is iStock and they're an Icelandic craft beer company they're based in a small village called and I'll have to try and make sure I get this name right a curie curie no no not happen it's spelled A-K-U-R-E-G-R-I and it's somewhere north in Iceland so a cold place basically.
Probably yeah absolutely and they they try and do some craft style beers and this one in particular is a Belgian style beer and I'm a big fan of Belgian beers as am I so I thought I'd give this one a go for us today. Well thank you very much sure we without further ado should we get this bad boy on the road yeah let's crack it open yeah I'm not the smoothest right I'm not gonna make the same mistake as I did on the last podcast yeah this time before we actually have a taste yourself a little bit don't want to get too hammered at two o'clock in the afternoon so first of all the notes was on the nose well there's nothing on my nose I keep it fairly clean hey we are here all day thank you thank you I know I have to sit next to him oh it smells like beer well it says it brewed with coriander and orange peel if I'm honest I'm not getting it I mean I'm getting beer the coriander definitely comes through on the taste though yeah yeah yeah not getting much orange peel but nonetheless a cracking beer yeah I'm a big fan and also it's got a very very sexy biking on the front it does it kind of looks like you oh thank you although I am of course very clean shape you wish well now we've got our beer Chris I'm thinking we should maybe start talking to these lovely people about database sauce controlling I guess that's what we're here to do we've probably shouldn't put it off too long so source controlling a database I have a database and I want to put it into a DevOps-esque environment I want to bring it into my DevOps culture and the first step really to do that is to source controller and I can do that in a number of different ways so there's very space of talking out there that can help me do that or I could do it manually I could take various change scripts and push them into a source control of work well that sounds like my preferred way Chris that doesn't seem to be any problem with doing it entirely manually of course some of these are going to be more effort than it were or maybe just more effort than I really should be putting into such a practice ideally you want to get to the point where it becomes a matter of course you make a change and then you can make the source control right yeah absolutely I mean the number of people that we both speak to who have major issues with well we've got a development team of we're our 10 people and all of them are connected to the same database making changes and I can't actually for the life of me keep track of who's making what change when and why so now we just got to figure out what we are going to deploy somehow we also get a case where people can overwrite each other so I make a change and then you come in and you overwrite that change that's because I make better changes yeah this happens quite a lot when we make collaborative documents you'll see in future podcasts if I start to get edited out quite a lot you know why and welcome to the Chris show still yeah we want to take a database we want to push it into a source control environment red gate provides a number of different ways of doing that and the distinction between that is what well there are two key approaches that we can take and that's not not just to mention that you know red gate is the only provider of tooling that does this I mean as we said on the last podcast red gate obviously provides tooling for the Microsoft SQL Server Snag and for Oracle databases but you know there's lots of different databases out there lots of platforms that we can use lots of third party tooling and even tooling directly from those database vendors as well now red gate obviously if we take as we did last time we take the SQL Server equivalent there are two main approaches to source controlling databases across the board and that is the state-based and the migrations-based approach and if you want one Chris please explain the high level difference between the two the high level difference by the third you've got a very good analogy that has definitely not become overused in the slightest it's not as though every new starter here's my analogy yes a very very high level summary would be that they are two different approaches okay maybe a little bit less of a high level right okay well let's let's dig in then so my analogy that well I say it's mine I stole it I can't remember where from probably shout out to our good friend Alex Yates at DLM Consultants potentially for this one but is it a bit like a cake okay someone comes to you and they say Chris here is a cake I like this and here is some ingredients that you need to make the cake okay now I'm a little bit less interested in dollars with getting a cake no sadly not I don't have a cookie if you want it I'm around the group but essentially we've got a cake over here and we've got some ingredients on the on the other side and we need to get from ingredients to cake okay I think I'm with you so we have to figure out what's going to happen in this middle bit to get us from the ingredients to the cake right and that is the state based we are going from one point in time the component ingredients to the cake okay I see so what's the other way so the migrations way is almost as though you're providing the recipe in the middle okay you want this kind of ability to know exactly what's going to happen at every stage we don't want to have to get that the oven has to be turned to 400 and something degrees Fahrenheit or 200 degrees centigrade what we want to know is set it to 200 degrees centigrade then mix this together then add an egg then do this and so and so on so the idea being we know for each incremental step that is required to make this cake so we know we will always get the same outcome whether we make it again and again and again and again and again so in summary we're going from a case where we're going from step A to step B we're just going to figure out what changes we've got to deploy to go to A to B or the case where we have a essentially a selection of small changes that we apply sequentially yeah exactly we go from one to two or we go from one to one point one to one point three and so on okay good I thought you were going to go through all of them all the way one point one point but no definitely not okay so that's a high level view of migrations we apply small changes sequentially to get from A to B what would be the benefits of doing that though why would I want to work with migrations and do lots of little changes rather than do one big change well big bang changes are I mean they can be pretty good especially where you've got a lot of developers say working on one copy and you need to really easily understand exactly what they're changing what you want is some kind of granularity over the changes that they're making so you can almost view a history of every single object if you've got lots of dependencies for example it's easier to deploy these sort of all together and just make sure you get the order correct but a migrations approach is probably a better way in that sense where you have for example a small data store and what you really want is say we're adding lots of columns or splitting things all over the place and if we've got lots of data movements then we want to be able to more easily move that data around so a good example would be if we take a full name column so it's got Chris Kurzweil in it or Chris Unwin or some sort of Chris because all of the names in all of our tables are Chris that is going to be 90% of the jokes in this podcast I think that everyone I record is Chris well I was talking to my good friend in our other development team Chris the other day and I was just saying if we take a full name column and we want to split Chris Kurzweil into say a first name and last name column like Chris and Kurzweil okay I mean you could change your last name to Chris Chris if you wanted I feel like that's the natural progression of things hey but to me to my name is Chris Chris yeah and eventually we just refer to each other all those Chris we replace all words with Chris Chris Chris Chris Chris Chris Chris Chris Chris Chris the revolution is coming my friends but yeah so we were talking about splitting this column yeah and we want to get to the first and last name but the problem is if we take things in a sort of state-based way and we say okay let's look at where we were in point one we've got a full name column and here is where we are in point two we don't have a full name column but we sure as heck have a first name and last name column clearly I just have to drop a column and create a couple of new ones I mean obviously that's going to cause us problems then why is that because we want to avoid the data loss ah yes because databases and tables specifically aren't just code you mean they contain data which is amazing astounding but a database has data in it who would have guessed what you're essentially saying then is that the migration space approach can be better at handling those issues or those changes where a data loss event might occur absolutely so we want to avoid the opportunity for the data to be lost by specifying that the first name and last name column should be created before the full name column is dropped and then we do the data movement so we split the string and put first name last name last name and then drop the full name column so it should be done in incremental steps and that obviously helps us get around there the problem of just going to look at the states and say well these things weren't here first so here they are and then just dropping the other stuff cool okay do you think that there is an argument to say that we're continuously altering our database by using that approach then absolutely instead of taking all of the scripts for example and basically bringing out a bunch of create statements and comparing those what all we need really is just a kind of alter this do that alter this do that and as we gradually alter then we're not having to worry about dropping things or any such activity and is there let's let's say I'm working with the state based approach already is there no way of handling that kind of complex change with the state based approach yeah I mean you can you can handle it in a state based approach but it does involve sort of bringing in migrations still into the fray you have to have these like occasional migrations essentially I mean there is something to be said in the sense for the hybrid approach so this is something we're seeing quite a lot recently there state based was the concept was kind of originally brought about migrations came to almost answer a few of the questions that state based can't I don't think that's unfair to say and then now in modern world we're introducing this idea of a hybrid approach if you wouldn't mind in kris could you please introduce us to the hybrid approach what does that consist of well it was a perfect segue there Chris thank you for that so welcome that's what I'm here you are a wonderful tool to bounce things off of I am a basket ball board is it yeah I didn't know that the backboard that's it oh I see well I'm not going to bounce anything off you particularly as the only thing I have to hand is a half-fold beer yeah ideally not today that would be good so the hybrid approach is well as you might expect a mixture of the two so we've got our migrations and we obviously need to be doing those for tables because that's where the data is but then we do have a bunch of other stuff in the database that isn't well doesn't have data in it things like stored procedures functions views right yeah and essentially what we want to do is treat the code objects like code I treat them in the state based way of working so that we could trace down through and see exactly who may what change when and why and all this great stuff but then where we do make a change that involves potentially a data move we have the flexibility of going with a migration script approach there okay and what if we want to add maybe extra steps as well I spoke to a few clients and they've said to me well we want to when we push in a change ensure that certain environments go down and then it brought back afterwards for example of those in groups how I see and what they've said to me is that they'd like to be able to push in extra modifications whenever a script is rough is that something we can work with I don't know great you tell me well we have the ability because it's a migration script obviously that's being run every time absolutely so we can apply modifications to that script and that modification can essentially be a piece of SQL code right yeah absolutely so if we need to type it out say with the help from our good friend SQL prompt shamer's plug then we can put our own additional bits in there or even write a post script if we wanted to run after the deployment you've got all of this flexibility there with I don't know some particular tool maybe called SQL change automation get another plug in there shame this but what we can do is obviously ensure that anything else we need to handle as part of these migrations is tackled another big thing that is on a lot of people's minds at the moment is of course automation and as I said we look to source controller database because we want to introduce the database into our DevOps processes so automation then when it comes to the state based and the migration space approach are there any differences between automating the delivery of those again I suppose it depends on what tools that you're using but I mean if we were again to take the red gate tooling as a specific example something like SQL change automation would be able to look at either repository whether you've got state based or migrations based repositories and decide what the most poignant way of deploying those is and it would pick up sort of how it's been generated okay I mean sounds simple right that's the idea almost ingeniously simple again another nice plug in there seamless completely seamless thank you thank you and then the other point that we see people raising a lot is around data obviously traditionally what you'd be pushing into source control would be your schema changes however with the static data world and using SQL source control from red gate we can look at pushing our static data into our source control repository what about for our migrations environment it's absolutely the same I mean you want to be able to push static data around your environment regardless of the approach this is one of the things that a lot of people you know don't always get first time or they don't have in their mind when they start considering a source controlling approach for the database they think well the thing that should go into source control is the schema because we might have say a production environment that's got all of our lovely production data and it may be some personally identifiable information again throw back to last time's podcast but we might also have a bunch of test databases development databases where we have obfuscated or rubbish data and ultimately we don't really want to source control any of that because it might you know accidentally overwrite our production data as part of the deployment but then we do have a bunch of data that's going to be consistent throughout that reference data that maybe our database or our application relies on things like maybe country codes or something so typos of people yeah so mr mrs kernal doctor lieutenant that kind of thing reverend p hd grand high admiral less of emperor controller of the fleet yep so on h i h h r h what's h r h his or her royal highness that shows how much of a royal less i am now okay we have multiple ways that we can handle source controlling database and we can use either way depending on what's working best for us at the time i guess if we have lots of complex changes then we probably recommend using migrations correct yeah i would say so i mean as things wildly get out of control we need to be able to deploy these in a nice controllable fashion something where we know the same things going to happen each time these days we're also saying that the hybrid approach is a great strategy for people we can use the migrations for our data containing objects or tables and we can then use the static based approach or sorry state based approach for our program ability and you know our sort of procedures like yeah absolutely and the good thing about that as well is that it doesn't mean that for every tiny store procedure change so for example you suddenly to add one extra column you don't want to generate a full migration for that bad boy you just want it to be nice and simple and oh i made this change let's let's get it out great i guess if you want to learn more then we have a fantastic white paper that details this on the red gate website this is true i actually have a coffee right here with me and it's definitely not been helping fuel this discussion but it's actually called state or migrations based database development and it's very easy to get to it you just have to go to the home page httc codelon slash www red hyphen gate dot com i can't believe you just did that but slash products slash sequel dash change dash automation and then go on the resources tab i was kind of hoping you link to the exact site of the page okay state data migrations dot pdf or something i don't know but it's on google it in summary or other good search engines are available google it brilliant well i just realized the time chris and actually i think we're getting a bit on i think we've reached a realistic conclusion which is that you should source control your database regardless of which approach you take to it yeah we set this out of state based or migration space what's the difference between the two the conclusion we kind of come to is that there's also hybrid so use that good conclusion yeah so chris introduce the section properly please welcome to the news that was beautiful thank you i've been practicing chris has all these adembers he will be going on the voice the voice yeah do you not watch the voice i don't need to go on the voice you just have the voice i'm not amateur oh controversial so let's change tact then from the voice to another type of television program the news both really hot at the moment the news always getting good ratings never goes out of fashion but we we've had a look at various news stories that are particularly pertinent to those who are working with databases at the moment mainly because they're to do with data privacy data privacy news seems to be a common thing last week we were talking about a big data breach will we we were oh from dixon's car phone yes i do remember this yes sorry that was half a bottle of beer ago there was a pride and joy last time that's what that's what did me and this week we've got a data breach to talk about we've got a fine to talk about and we've got a the first gdpr rule in to talk about oh we should start i think with the gpr ruling i think that's a really good chance so you've definitely looked into this particular one just a bit more than i have i definitely didn't spend my afternoon playing gravity master so can you give me a rundown of exactly what it is you've heard grab it never mind the gdpr ruling has come up jummy essentially a german court has ruled that's a american organization called i can or i see a double n they are well known within the it world for providing a who is service as well so we're looking up the main they can they can how strong is this beer well they they as part of their service the who is service they require anyone who's purchasing the main yep the main name to register their information and that's available then through the who is service for look up now the there was a particular organization based in japanese who essentially said that this part of the service is non-avoided they said this because it was in breach of the gdpr they didn't have to provide that personal information if they didn't want to and the japan court came down in favor of that organization now i can have appealed this and the appeal i don't believe it's actually come through yet but gone up to the the next highest cause government bond to kun it's looking like it's not going to so the board from the edp or the euro data protection board have published a letter that they have released to icon not a letter a letter for a strongly worded letter which essentially states this that the information that i can are looking for is classified as sensitive information and so i can can't expect people to just give it to them and then to publish it i can't easily amused i think it's the phrase uh simple things anything more you wish to add here which fits a bit of an interesting point that i can also request it was that the rule would be handled differently for corporations rather than individuals you can kind of see the logic there my corporation as a website maybe have a generic email and again though the edpv came out saying that no because that again could be an individual's email even if it is a corporate email it could be tied to an individual so that still doesn't mean that that can be captured quite right have you anything more on this story no i've got to be honest i think we're in a situation now where the gdpr is definitely something that people are taking notice of in a very different way it's kind of been a shift in mentality so to begin with it was it was one of fear and reaction and everyone was saying ah this gdpr thing we need to to protect all of our stuff and certainly something that we covered in the last podcast was uh encrypted update and patch all your stuff but yeah right now we're moving forwards people are now actually asking themselves the question of well hang a second how do we actually enforce this thing what where is the where does the gray line end and i think a really good one for that is again another example that you've pulled out recently which is the ticket master breach obviously that was quite a big one and there was a lot of hoo-hires to whether or not it fell into the realms of gdpr ultimately it still does have sensitive information though right absolutely i mean it was it was credit cut skimming was it not yeah it's quite interesting but it's a credit card skimming effectively there was a group that as of now is still unknown they've been dubbed as i've lived as mage car mage car yeah and essentially what they've done is managed to put in a malicious piece of js onto a well a very popular yeah and they got in through this third party plugin the third party plugin comes from a company called iventor i'm not 100% sure if that's how you pronounce it but i bent it i mean to i mean to i don't know but i bent to provide a chatbot service to ticket master the chatbot service it's it's very common these days for it to be the first line of customer support as it you know a lot of these organizations that love to cut costs how do we reduce our customer support costs what we put in a robot to effectively handle that first line so it's kind of yeah essentially the machine overlords gradually taking over our lives fantastic and it's an emerging piece of technology as a result you're getting a lot of different companies around emerging and coming up with this and being implemented all across the board anyway they have this particular product this chatbot product that they sell to quite a lot of organizations taking master being one of them and taking master implemented that chatbot in the point of sale page which was according to i bent it not recommended that's true and do we know when do we know when the actual the actual breach occurred when it was installed or when it started so it was a few months ago that initial fraud was detected i believe you've looked into the timeline of this yeah absolutely so i think the first detections were in april i think was april 2017 or maybe slightly later maybe april 2018 well it depends what we're talking about here because the mage cart breach or the mage cart process i should say of using this javascript code to skim credit cards has been around since around about 2016 oh wow it's just they've suddenly got a bit smarter about how they're implementing this code by instead of targeting individual websites but actually putting on these third parties that are then installed on a lot of websites so they just extend their reach very easily so i think one of my favorite thing about a date breach is done by i think one of my favorite things about this breach and when we were when we were looking into it was actually the the internet banking company is it monzo yep that's brilliant so monzo had a really clever way of spotting this breach before it even was declared publicly so basically what happened was monzo had a bunch of their customers they suffered fraud and essentially what happened was they had 50 customers come to them one day having all having all of them had their cards defrauded i see yeah and they went oh that's a bit odd we've had a fairly large number of people having their details defrauded and having their credit card information stolen so is there anything weird about these customers and the thing they spotted was that actually 70 percent of these 50 people had all been on ticket master and bought something in the last couple of months okay so that would lead them to suspect ticket master was the course exactly so they put in place some really clever cyber security that started blocking like fraudulent transactions for people in similar situations and they started picking up all of these issues and actually once they had some more evidence that this indeed was the case they were able to report it to master cards directly and indeed they stopped the cards of everyone who had bought a ticket master once it had been reported and they issued brand new cards for those customers who had all bought from ticket master emailing them in the process saying hey we stopped your card was sending you a new one uh be aware this is because of fraud and they were just really proactive about it and that's something i really like i think that's that's really that's data analytics in its true form really benefiting the people so bear shout out to monzo yeah i think they like a lot of financial organizations especially credit card companies will have a big focus on fraud as that's an area that kind of eats away their bottom line i what is more interesting from that for i think is that they then went to ticket master themselves and then the reaction from ticket master was was there a reaction well ticket master looked into it in air quotes and decided that there was they weren't the cause of it and to an extent they were kind of right because it was this i bent a code that was the cause however it took them a couple of months to finally realize this and then finally publish and say no it was us indirectly but it was us this is always trying to shift the blame though isn't it i mean it's that we saw the same in um Cambridge Analytica you know facebook said they weren't uh they weren't to blame for the whole Cambridge Analytica scandal um but of course they were the ones who kind of allowed the access to this data and they allowed this app to be a thing in the personality quiz and then all of that data was gathered but it was only 260 thousand people who had access to actually took the quiz but they got something like 50 million facebook users data just because not only could they see the data with the people taking the quiz but all of their friends and what is facebook about that's right spying on your ex so they managed to spy on 90 million x's exactly 50 million x's i believe it was closer to 90 really yeah 87 million approximately okay that's a bit bigger than isole that's even more x's than you have crist big almost almost almost yeah the facebook breach again very highly publicized because of Cambridge Analytica so very highly publicized and of course being a company that lives or works in Cambridge you get a lot of people associating Cambridge with Cambridge Analytica and sorry for the record Cambridge Analytica has never been working in Cambridge itself always in London and other leaving New York based company yeah Cambridge is just the name nothing to do with us at all but yeah they they it's pretty insidious though they effectively gave people a quiz and the quiz's name was something like this is your digital life yeah and through that quiz gained access to not only that individuals data but all their friends they apparently they went to facebook pretending to be an academic and saying i would like this information they kind of got it under nefarious means granted but at the same time facebook really shouldn't be giving anyone this information academic or otherwise well that's true but they've recently i think it's fairly recently they were actually fined by the uk's ico 500 000 pounds altijum or facebook who were making 500 000 pounds every five and a half minutes that's also 18 minutes in terms of profit five minutes in terms of reading for those being counters out there sorry not that we're being nerdy or anything but let's just say it's not a lot of money for the facebook but a lot of money for me no don't be uh don't be modest Chris yeah that's like an afternoon's work but essentially you know facebook earn a lot of money they make a lot of money out of people and of course 500 000 is because that's the maximum find that the ico can publish that's true under the old data protection act because if this had been under the gpr that would have been how much four percent of global revenue which could well up to four percent of the global revenue which would have been eighteen point nine billion dollars you're out by five for ten one point eight billion oh it's close it's still substantially more that's that's more along the lines of what Chris earns in our domain well i don't have a social life yeah and we we can maybe forgive facebook a little bit here they were first all they were deceived and also they did request that the data be deleted to which Cambridge Analytica effectively lied they provided deletions to us yeah we've deleted it yeah and as you know i'm not going to defend facebook a whole lot here ultimately they are still the big evil corporation but they did take some steps it wasn't like they were completely blind at the same time however as previously said i think especially these days handing out personal information to anyone and expecting them to delete it yeah it's a little bit naive so we probably not forgive them quite so readily you you had a nice analogy for this so the analogy that i had for this was actually taken from uh my good friend robinson mayor uh hang on back i got it right here Robbie may i rob him but uh robinson mayor at the at the atlantic the uh the online newspaper he summed it up quite nicely where they said it's basically like if you are a library handing out hard drives full of illegal illegal pirated music and you're giving them out to people that saying well don't don't install them locally don't don't download the music onto your own computer we would never do that that would be very irresponsible exactly i always pay for my music especially if i like it um as you should as you should um but the way he turned it was if you were giving out these hard drives and then suddenly someone did download that music onto their computer and then got penalized for it it's almost like you saying well hey hang on a second i told them not to put it on their own computer but realistically they probably shouldn't have had that in the first place yeah i think that this raises another point as well we found out about this breach the whole kamri-chan was going to scandal in the first place because there's a whistleblower someone came forth and said this is what's happened and so the story erupted now it kind of makes us or begs the question how many times has this happened where there hasn't been always a blur how many times has a similar process being used to access people's data to facebook or other social media platform there's another particularly insidious case that is throwing up quite a lot from a site or an application i'm not sure which it is i think it's called emma's story where they provide effectively free services to expecting mothers and then the mother sign up provide their information and then that company has been selling their data to political parties like i said fairly insidious stuff these days i said these days this didn't happen all that long ago but facebook is now feeling the heat i think not maybe in terms of money they've got this 500k fine and they had a 95 million dollar fine about a year or so ago but definitely in terms of pr and you can see that a lot through that tv advertising and all that kind of stuff really trying to push their new privacy policies and all that kind of thing whether you believe it or not that's up to you but these days at least we have the gpr that we can rely on a little bit to work these companies absolutely and indeed and this is something that's going to persist as well even since the last podcast we made there has been the introduction of the new california data privacy legislation which of course is uh well it's very very similar indeed to the uh to the gpr and new york has something in the works we've obviously got poppy and pipper and all of these other regulations that we spoke about last time but uh you know we just we need something there to protect us as individuals and it really highlights how mistreating data can really go a long way to damaging not only yourself as an individual but also your your company both financially and in terms of reputation it turns out that big corporations won't self-regulate who would have guessed who would have funk it but i think obviously looking at the at the time i think it's probably wise that we you know wrap things up for this week obviously we still have lots of material that we can cover i think in a future session we'd love to cover the future u.s legislation coming up absolutely this is why i just can't stop talking about recently sir i know i know too well so hopefully we'll get a chance to talk about this next time as well but for now thank you very much for joining us obviously if you want any additional information you're more than welcome to come along to the red gate website that's uh chris what was that again htttco.com slash ww.red-gates.com forward slash and feel free to drop us a line as well that's chris.unwin or.curswell at red-gate.com once again he does not have my approval to publish my email address in this podcast don't know why he keeps bringing it up but for now thank you very much for joining us have a fantastic week and hopefully we'll be seeing you again next time but for now chris cheers cheers