Episode 113: Standing on the shoulders of giants

“If I have seen further, it is by standing on the shoulders of giants.”  Those words are attributed to Issac Newton and they are fitting for the way I was feeling recently as I thought back to those who have helped me in my career.  While we might always want things to be better, as technologies we have it pretty good.  There are unnumbered people who have dedicated untold amounts of time so we can have the tech available to us.  We take a moment to think about those who have paved the way for us to be where we are now.

Whose shoulders are you standing on?  Let us know in the comments below.

 Episode Quote

“This idea that the way he was able to look at the world is based on all the previous work that has been done”

“I guess “gratitude” is the right word of those who have gone before us and enable us to have those who are in technology we have a pretty nice career.”

“There are just a lot of people out there who just want to help and just want to better things with what they do every day.”

Listen to Learn

00:38 Companero shout out to David Stoke
02:11 SQL Server in the News – SQL Server 2017 is out
02:35 Interleave execution for multi-statement table value functions
05:34 Artificial intelligence, analytics, machine learning
07:58 Today’s Topic: Standing on the Shoulders of Giants – having that gratitude to the people who have gone before us
11:27 Carlos shares a documentary he watched about bitcoin
14:50 Stop complaining
19:56 Who are “giants” in the life of Carlos?

Transcription: How do you start consulting?

Carlos: Companeros, welcome to Episode 113. It’s good to have you on the SQL trail again.

Steve: Yes, it’s good to have everyone who’s listening. Good to see too, Carlos.

Carlos: Yes, always good to connect back with you, Steve. Today, we’re going to be talking about, kind of changing the subject up a little bit, and the idea is standing on the shoulders of giants. We’ll get it to more what we mean about that in a bit later but that’s the idea of our topic for today.

Steve: Ok, sounds good.

Carlos: So we do have a companero shout out. I want to give a shout out to David Stokes. Now as we record this we actually have not had the Companero Conference just yet. That will happen, actually as soon as I hang up I’m going head down there and have the conference. But I want to give a shout out to David. David is the leader of the Norfolk user group. He was actually the first person to buy a companero ticket and has been a great supporter of the podcast and of the conference. I want to give a shout out to him and thank him for what he’s done.

Steve: Yup, you know the interesting thing about David is that he is the very first user of Database Health Monitor that I have ever met in person.

Carlos: Oh ok, interesting

Steve: I met him at Charlotte, North Carolina for PASS Summit, was that 3 or 4 years ago?

Carlos: That’s right, 2013 I believe

Steve: 2013, ok, so 4 years ago. Yeah, it was really kind of cool to meet him and listen to what he had to say about Database Health Monitor back then and he’s been using it ever since. David is a friend of Database Health Monitor.

Carlos: Yes, very nice. Ok, as mentioned, I’m sure the conference went great. You already have that and we’re looking back to some feedback. We will probably have some kind of post mortem about that. But now I think it’s time for a little SQL Server in the News. We’ve been going back and forth a little bit about this and one of the things I wanted to talk with, of course we know that SQL Server 2017 is out and should be up, right? I’m sure you spent your weekend installing that. But one of the things that I wanted to discuss a little bit here which has been mentioned I think it’s expanded a little bit here and that is the interleaved execution for multi-statement table valued functions.

Steve: Oh yeah, this sounds pretty cool because there’s been so many concerns over the behavior of multi-statement table valued functions over time.

Carlos: That’s right. Yeah, so ultimately the bad news is that in previous versions of SQL Server, and I guess in 2017 as well. I mean, there is a fix for it but the optimizer can’t always determine how many rows the table valued function is going to affect. As a hard time just in for that fact, right, and so in previous versions basically it would guess a hundred and then it would really be 5,000 and so we have a bad plan, and that was kind of the issue. So what they are going to do here with 2017, they are adding some analytics and some machine learning basically into the optimizer, so that when the first time the plan comes in with the table valued function it’s going to guess again a hundred rows when it executes it’s going to have 5,000 rows. The difference now is that the second time it runs it’s going to take a peek at that previous plan and say, “Hey, how did I do?” And it says, “Oh, I did horrible. Let me adjust my numbers and take in the previous execution into account to see if I can come up with something better.”

Steve: You know, I wonder as I hear that if that is actually going to help or hurt because. I mean, if you table valued functions commonly have the same number of rows they are processing it might help you. But what happens if you’ve got such a wide variety of data in your database that every time that gets called the table valued function has a very different set of data that it’s processing depending on a client or customer or whatever it may be.

Carlos: Right. Yeah, and so obviously we have skewed data it makes it difficult to solve for the outliers. There still probably an outlier case. I think here what they are trying to get at basically is do I use a hash or do I use nested loops? Which way that I’m going to go about that? I think, again, after you execute it a hundred times you’re going to have some data there to then be able to say, “Ok, well, 80/20 rule. This is the way I’m going to go with.” I guess the implications here are not suggesting all of a sudden that everyone start using table valued functions and this is going to solve everyone’s problems there. I guess what was curious, or what was interesting to me is that I thought this was a very interesting way because we hear a lot about artificial intelligence, analytics, machine learning even. This is now a problem that we deal with all the time and now Microsoft is putting that analytics, that machine learning into the product that we love. And I thought, you know what, I meant that’s kind of a very specific scenario. I wonder if we couldn’t start taking some of the same thought and then applying it to other areas of our business so that our users or our customers might also benefit, so that was kind of my thinking there

Steve: Right, so it will really be interesting to see if somebody comes out with like an AI package for tuning SQL.

Carlos: Yeah, something like that, right. Or even that idea of, “Ok, I have this data. My customers keep asking me for whatever use cases. Can I apply some analytics here to give that better information? So it will be interesting to see how this continues to evolve. I think this idea is going to affect those of us who continue to be administrators or gatekeepers of data. These are going to be problems that we’re going to have to start solving, so it will be interesting to see how other tools crop up and try to address other problems.

Steve: Yup. Yeah, and I think it’s been easier with just sort of fix rule sets previously to be able to definitively say this is how something behaves. But with AI built in on how those things are being handled and how it’s tuning itself almost, it may be harder or maybe more work to figure out exactly what’s going on when there is a problem

Carlos: Sure, and I think that’s goes back to you are still going to have to know your system for those outlier cases. What’s going on? Why are you doing what you’re doing and is there a different way to potentially go about it?

Steve: Yup, interesting.

Carlos: Ok, so today’s episode, the show notes are available at sqldatapartners.com/giant

Steve: Or at sqldatapartners.com/113 for our episode number.

Carlos: Yeah, so ultimately this idea, and maybe a became a bit nostalgic which we can get into in a second but Sir Isaac Newton is quoted in 1675 as saying, “If I have seen further it is by standing on the shoulders of giants.” And ultimately this idea that the way he was able to look at the world is based on all the previous work that has been done, right? Helping him to arrive to where he is now. I got a bit, as I mentioned, nostalgic about this idea where I ran into to somebody who work with a former co-worker of mine. We started talking, and so this former co-worker is actually the person who convinced me to apply for my first database job.

Steve: Oh, I remember that story from one of our previous podcast.

Carlos: Yes, and I wasn’t actually completely qualified for it. But he convinced me and he coached me and said, “Hey, I think you do great.” And I think back to that, obviously, you know, there are thousand different I could have gone but I think about where I am now and ultimately kind of that first step of making that decision. And so I think about it, I guess “gratitude” is the right word of those who have gone before us and enable us to have those who are in technology we have a pretty nice career. Yes, it’s not all roses and sunshine all the time but the fact that those giants have gone before us, many of whom we’ll probably never know. We have to look up in Wikipedia, and the fact, even the first people who put together the rules for the transactional database. I know he was brought up in a keynote, unfortunately I can’t remember his name, obviously on top of my head. But, I mean, they put in place what we have today. And I think it’s easy, and not to discount. I don’t mean to discount them at all but it’s easy to look at the Bill Gates, and the Steve Jobs, and the, what is it? Oh gosh, Linux is going to kill me now

Steve: Linus Trovalds.

Carlos: There you go, thank you.

Steve: I don’t know if I pronounced that right

Carlos: If not, I’m sure you’ll get corrected. Again, not to discount their contributions but they are not the only ones. You think about all the teams behind them that helped them do what they wanted to do.

Steve: Yeah, I guess with that, I mean you look at a product like SQL Server, it’s really been like a Microsoft product since 1989/1990 but it was being worked on for years before that and then before anybody even started working on what became SQL Server, there were people in the 70’s that were theorizing on how these databases would work and the rules around it. I mean, 50 years worth of work, and foundation that has gone into what we have today in SQL Server 2017

Carlos: Right, and how amazing it is. It was interesting to kind of [term unclear – 11:26] here slightly. I was watching a documentary on bitcoin, and so bitcoin is the digital currency that they are trying to advance and I didn’t realize but, oh gosh, this Swedish guy who started Wikileaks. Whatever his name was, he was part of the original group of this eight people who put together sort of theorizing about digital currency, and they came up with a block chain idea and methodology. It was because of his situation and what Wikileaks was doing that really kind of put bitcoin on the map because when PayPal took their services away and said, “Hey we’re not going to let you use PayPal services.” That’s when he was like, “Ok, well, bitcoin, Internet can you help me,” and kind of launch that whole idea. In a way it was very interesting. But anyway, in the documentary they go and they talk about and they say, “The person through the door with a new idea get shot.” Anytime you have something revolutionary, the first person through get shot but somebody has to go through the door. So they talked about some of these first people like the first companies that started in New York City trying to exchange currency. For example, dollars to bitcoin and things like that. Well, he’s going to jail. Long story, but you can watch the documentary. Anyway, but I thought it was interesting. I guess going back to this idea that all of the ground work that was put in to enable us to enjoy what it is that we enjoyed today.

Steve: Right. But what was interesting, I mean, he was going to jail for Wikileaks side of things not for bitcoin side of it.

Carlos: Yeah, I’m sorry, so Julian Assange. That’s his name, the Wikileaks guy, and he is going to jail for something else. I don’t know it. But the guy in New York City who was doing the bitcoin exchange, so he was actually trying to get people on the bitcoin and basically sell them, bitcoins this idea. He is going to jail because one of the guys, unfortunately, illegal activity tends to push a lot of innovation. And so when you have something that is not monitored there was some illegal activity going on. He knew about it, and he was basically selling bitcoin to people who were doing illegal things and so that’s why he’s going into jail.

Steve: Oh, interesting. Ok

Carlos: It wasn’t that he was doing anything illegal but he was kind of aiding, and abetting if you will in a sense.

Steve: Wow! I hope I’m not considered aiding and abetting by investing in bitcoin. Or maybe the word is “gambling” not “investing”

Carlos: Yeah, that’s right. It is still a bit shaky, again to progress for a minute, the value of how that gone up it was pretty impressive but obviously it has come down I think in 2011 or 2013 something like that. And so where I was going a bit with this as well is this idea of it kind of grits on my nerves a bit, and we all like to complain. We all find ourselves in situations we don’t want to be. When people will say, “Gosh, I they had only done this right. Why they have to do it this way?” Basically, I’m inconvenience and I don’t really appreciate all the things that I have

Steve: Right, and sometimes that inconvenience comes from just not understanding what the people did before you and just complaining about it. I mean, I’ve done that but I’ve seen that happen plenty of times.

Carlos: Yeah, that’s right. You have that “Aha” moment where you’re like, “Oh, so that’s how it works.” Sure, you may have developed it differently; you may have architected it differently all of that jazz, great. But, you know, you work there, right? And now you’re now kind of taking advantage or taking over in some case. No I’m not saying it’s all always great but the decisions that were made there before you have you put you in a position where you can now either take over or ride that bus if you will. Yeah, it’s kind of interesting.

Steve: Yup, and you may look at something and say, “Wow, that’s stupid. Why would they have ever done that?” But then you look back 10 years ago at what was being done, or 15 years ago, and that was cutting edge technology and the fact that it even worked at all was completely brilliant.

Carlos: Exactly, exactly. Yeah, you know it is funny particularly as database people, like yourself Steve, there are developers among us or recovering developers or whatever you want to call them. But we like to complain quite a bit about you know, and the stupid developer if you’d only done that. Well, I can kind of find that we’re being a bit of a hypocrite because we use software all the time, on our phones and on our computers. I mean, we wouldn’t be able to do anything that we do now if it weren’t for software. We need to be mindful of where we are, what has been invested and give people a little bit of break when they are not upgrading to the latest thing. I guess in our last episode even was about patching. You know, just staying up and keeping with the latest and can kind of get a little tiring sometimes, and so you’ll forgive people a little bit if they are not quite there to where you are.

Steve: I think some of that comes back to what I learned in a class. It was called the Dale Carnegie class that I took several years ago. It was based on “How to Win Friends and Influence People” book as well as some other books, and it was a simple statement that said, “Don’t criticize, condemn, or complain.” It’s just that one of those things that just comes up and if I find myself criticizing, condemning or complaining, I try and stop myself. I try, so it doesn’t always happen but I try.

Carlos: Yeah, it’s one of those things, the reality in today’s world with social media everybody has a voice box or a [term unclear – 18:11] and they want to get up. It’s not to say that reform is not needed, that we can’t make progress. There are times when change needs to happen. There’s no question on my mind. I think it is just more about the way that we go about it and then recognizing what we have and how do we go from there.

Steve: Yup, and you know, no matter how bad the day may be we’ve got things pretty good right now. I think that’s so often overlooked in, well you mentioned social media, I mean, if you’re on social media and it’s almost like everyone is out there just to complain

Carlos: They are either having the best day of their life at the beach with the vacation photos or the world is against me. I think those for you companeros, those who are listening, going to be in technology, yes, you may not be where you want to be. And I’m not saying that you should settle. That’s not what we are saying either but I do thing that you if you take a minute to use the phrase from our culture if you will, is to capture many blessings and just to kind of appreciate where you are and know that there is a path ahead. If you will continue to work there, like we talk about in the beginning, all these artificial intelligence and analytics; yes, is it going to affect you, is it going to change what you do? Absolutely, but hopefully that will be to your advantage and not to your decrement.

Steve: Right, yup

Carlos: Anyway, we thought we change us up just a little bit and share some of our thoughts on that topic. I guess we’d like to, again, thanks to all those shoulders we stand on, so Robert Pollard, he was the guy who commits me to get to that job interview. I even think about Matan Yungman and Guy Glanster. The other guy is the SQL Server Radio. They are out there doing the podcast. Listening to them made me think, yeah, let me try this. Right, let me do this as well. And then of course all those in the community who have give freely of their time. I guess especially thinking about that to all of the speakers who came to the Companero Conference. They put a lot of time and energy and I’m super appreciative to them and what they’ve given.

Steve: And you know, it’s just interesting because one of the reasons that I enjoy what I do so much I think a big part of that is because of the SQL Community. You don’t see, at least I have never seen a tech community similar to what we have with the SQL Community. There are just a lot of people out there who just want to help and just want to better things with what they do every day. And I think that’s awesome

Carlos: Yeah, that’s one of those things. I think standing on the shoulders, again, I’m sure there will be a lot of people out but I kind of, if there’s one person I would point to, Steve Jones. So Steve Jones, probably one of the most humble guys you’ll ever know. He has done quite a bit in the community, kind of a very public figure but he is always looking to help and do so in a way that he’s not boastful. He is just trying to help you along and he has taken his breathe of experience and freely gives of that and all things to do this.

Steve: Yup, and what’s interesting with Steve Jones is that when he presents and gives back and shares with people. He does a great job in the tech side but he also does a great job on like the personal improvement side. Like, how do you get out there and improve the position you’re in – how do you improve your social media footprint, how do you improve your value, all those kind of things. I’ve seen him present a few times and definitely another giant to call out there.

Carlos: Yeah, it was funny, so we have him on the podcast that was one of those first ten episodes and looking to expand your game. And one of the things that I really liked about Steve, because again, he is one of these internet famous folks and he is also one of the few of that genre that will come up to you and say, “Hey, I’m Steve, what’s your name?” You’re like, “Ahhh. I forgot, I know who you are.” Of course he is in a Hawaiian shirt. He has a great brand but part of that brand I feel like is his contribution, and because of that he has made it the norm or almost the expectation of this is what the community does.

Steve: Right, yup.

Carlos: So awesome, so that’s our episode for today. Companeros, thanks as always for tuning in. Our music for SQL Server in the News is by Mansardian used under creative comments. If there is other topics you would like for us to talk about or ideas, you can hit us up on social media. I am carloslchacon.

Steve: You can connect with me on LinkedIn stevestedman, and we’ll see you on the SQL trail.

Episode 112: Keeping up with patching

When listener Mel Vargas first suggested the topic of patching, I was not sure this would work as a topic; however, the Equifax story had just developed and there are many other security related issues that could be prevented with patching.  Match that with our guest, Robert Davis, who just happened to publish some articles on patching and I decided we needed to do this episode.

I should never have doubted this as topic.  While we are a bit more protected in SQL Server than others, the security threat is still real and this is something everyone has to go through.  Robert presents us with some interesting details on how he goes about patching and we think you will find the episode compelling.

Episode Quotes

“You not being able to be patched for the new stuff, you are really opening yourself up to a variety of attacks.”

“We’re all on the same team ultimately and the success of the application really benefits all of us.”

“There isn’t a security reason to be diligent about patching SSMS.”

“The really good DBA, really proactive one, gets overlooked because everybody else in the company doesn’t see the fires that you’re preventing.”

“Learning to speak up for myself and to publicize the things I do definitely was one of the best things I had to learn how to do.”

Listen to Learn

01:08   Episode Topic: Patching
02:19   Why should we care about patching and what it helps to do?
06:26   Maintenance window, automated patching and system center
07:56   Octopus: Patching Automation Tool, automation tools
2:52   Deploying and rebooting, and restarting services
15:48   Do you really need to update SQL Server Management Studio for security reason?
19:44   Cumulative updates in Azure
22:34   Windows patches and SQL Server patches, failover cluster, availability groups
26:39   Patching testing
29:07   Scenarios and issues when trying to do install updates or patching
36:32   SQL Family questions

About Robert Davis

Robert is a SQL Server Certified Master, MVP, and has spent 17+ years honing his skills in security, performance tuning, SQL development, high availability, and disaster recovery. He served as PM for the SQL Server Certified Master Program at Microsoft Learning, and in various roles at Microsoft specializing in SQL Server administration, development, and architecture. He currently works as a Database Engineer at BlueMountain Capital Management where he spends a vast majority of his time tuning massively parallel queries. Robert feeds his passion for security by acting as co-leader of the PASS Security Virtual Chapter.

Episode 112: Keeping up with patching

Carlos: This is Carlos Chacon.

Steve: I am Steve Stedman.

Robert: I am Robert Davis.

Carlos: Robert, welcome to the program.

Robert: Thank you.

Carlos: Yeah, it’s good to have you. Thanks for taking a little time of your busy schedule all the way from New York City to come and chat with us today, so thanks for stopping by.

Robert: Thanks for having me, coming at you from the heart of Manhattan.

Carlos: Wow. Yes, deep downtown.

Steve: Well, I know over the years on different topics that I’ve googled on around SQL Server often times some article or post that you’ve written has come up and definitely appreciate that, it’s always been good content. I want to say thank you.

Robert: Wow, thank you for that feedback.

Carlos: Yeah, and it’s nice to have you on the program today. Ultimately our topic is going to revolve around patching. Maybe not the most sexy of topics but as administrators something that we all have to live with, and I think has only accelerated with the “new” Microsoft and not only we’re getting patches now, we’re actually getting versions and all these things. And so keeping up with all of these can be laborious at times. And so we wanted to start this conversation with talking a little bit about patching. And maybe let’s just start with the basics, right? You know, there’s an inevitable or at least in the past it has been I’m going to wait to upgrade until I get SP1, right? And then, we’ll go forward with the upgrade. And so I think a lot of people look at patching for lots of different reasons. I guess let’s just go ahead and hit on a couple of ideas of why should care about patching and what it helps to do.

Robert: I’d like to just point out the fact that we had a couple of major events very recently in the computing world where there were vast instances of people’s servers being encrypted and it was utilizing very very old vulnerabilities that have been patched years ago that the people affected by them were the people that weren’t patching their servers.

Carlos: Or even learning older versions of older operating systems and things like that and then just have them patched.

Robert: Right, exactly. Those were two major events that could have been stopped right away if people just have their systems patched up to date.

Steve: Interesting. So I mean I have come across situations where people have said, “Oh we’ve got SQL Server 2005”, running on whatever older version of Windows Server it is that they are happen to be running on, “It does everything we need for our business and we don’t need to spend money on patching or updating it because it’s getting the job done for us.” What would you day to someone with the comment like that.

Robert: I would say to those people that if for these really old versions that are no longer supported that means you’re not getting patches for these SQL instances anymore. When you see things like these encryption attacks or SQL-Injection attacks and a lot of these know vulnerabilities attacking people’s systems. In fact, just recently we had a reoccurrence of a mass Slammer attack. If you remember, Slammer hit SQL Server 2000 pretty hard, and suddenly it made a comeback earlier this year. And obviously the people that are going to be affected by that are the people running SQL 2000 that aren’t patched and aren’t getting patches anymore. So by not being on the current version and you not being able to be patched for the new stuff, you are really opening yourself up to a variety of attacks.

Steve: Yup.

Carlos: And so then this kind of gets in to the heart of I think some of our conversations so some of those vulnerabilities. So the worms that were introduced earlier this year there was the patches available 30 days prior. So if you would patch in those 30 days it would have been fine. Some were much older, then we get into that idea of how often or how frequently should we applying them and the testing that goes around with patching? And again I say this is not, I don’t know if anybody that really enjoys patching. I don’t think I’ve met a person that person, right? So I guess thoughts on how can we introduce a process or a culture even to get those patches in and get them tested and maybe what’s necessary to kick the tires, because we know that you as much as we try a lot of times our lower environments aren’t quite the same as our higher environments and that’s a problem for a lot of people.

Robert: Yeah, exactly. But I think really the key to getting people on all our kaitens of regular patching is to introduce automation to the process. Obviously if you’re up late at night installing patches manually waiting for them to finish and then rebooting is going to be a boring tedious task and nobody is going to want to do it. We use system center to push patches out to all of our servers, and with system center we have the ability to. If we have some exception like we don’t want it to be automatically patched and rebooted in the middle of the night we can exclude certain server so we can manually do some certain server but otherwise at a certain point our automation kicks in says, these servers aren’t patched, patch and then reboot them starting at 11PM on Saturday night.

Carlos: There you go, so kind of finding that maintenance window and then making that happen. So you mentioned the system center, right and obviously if you have that product and I’m sure it’s doing a lot more than patching there is some inventory kind of helping you with your server environments just in general. Have you played around with any of the other patching automation tools by chance?

Robert: Yeah, actually when I worked at Microsoft we used Octopus for our patching processes. We would have Octopus, it would push the patch files out to each of the servers and install them and do anything. Octopus is great because if you need to do anything like stop a service before you patch and then restart it after you patch, you can build that right into the Octopus script. So for example, say if we needed to stop a web service that’s getting the web, the database server before we patched and rebooted it we could do that into the Octopus deployment script. It would stop the web services, install the patches and reboot it and then restart the web services on the web servers. It could do the whole, anything that could be automated that could do and the really great thing about Octopus too is we could build into it. Some sort of some basic testing at the end of the deployment like it could go in and send a request to the web service and check to see if it gets a valid response back so that we know. Yeah, we know that the web service. We would setup like some dummy process on the web server that Octopus could hit it would make a database call and it will just return a value, and if it returns the expected value then we know that the database and the web servers are all functioning properly.

Steve: Very interesting. I mean, I have worked in environments were Octopus deploys are used but I’ve never really thought about using it for updates. Kind of makes me want to go back and take a look at it now.
Carlos: Right, well I think it’s a good point too that Robert makes in the sense that you want to automate those but then with automation comes with responsibility in the sense of then going and testing some of those things. I guess there’s a little bit of that fear particularly if we’re going to start restarting services. You want to make sure everything comes up, and so automating the patch almost like it’s step one, right? And then there’s building out the test afterwards to complete the process.

Steve: And that’s the interesting thing being on the DBA side of the world. Oftentimes you don’t always understand that entire application or web service or whatever it may be that’s using the database and know how to even tell if it’s working correctly. And I think that if you can have the tools that automated it that would really be great.

Robert: Absolutely and it definitely wasn’t something that we had done on our own. We had some amazing test people and developers for the application I worked on at Microsoft and they were really proactive about helping us build things like this and creating the web service call that we’ve made then do a dummy transaction in the database and make sure everything was working.

Carlos: It takes a village to do automated patching.

Robert: Absolutely. It often gets lost but we have to remember Dev Team, DBA Team, whoever we are we’re all on the same team ultimately and the success of the application really benefits all of us.

Carlos: Sure, it’s almost taking me back to the conversation that we have with Richard, Steve, about building trust our teams. This is kind of an interesting scenario where it gets to be a win win. One because we get to apply the patches that we want for security purposes but then application people can be assured that the application is still available when we’re done with it.

Steve: Right. And if you have that all automated then it’s less extra hours in the middle of the night for all of those people involved as well.

Carlos: Exactly, which I think everyone would agree is a good thing. I wanted to ask about deploying as well and restarting, you mentioned restarting the services. Is that across the board? I think with SQL Server we tend to it a little bit more just because SQL Server is running, we’re applying that so there’s file dependencies that sometimes get lock and things. Kind of by rule are you restarting your services after you patch them?

Robert: Typically, it’s easy to determine whether or not the automation process you can configure it to reboot it if reboot is recommended. You know, like some patches will say you must reboot to complete installing this patch. Other patches like say applying a CU to SQL Server won’t require you to reboot but then applying the new version of SSMS probably will require you to reboot. Most of the automation or the system center patching that we use gets that message back as to whether or not reboot as needed and then we’ll reboot as needed. You can also override that if you have systems that you don’t want to be rebooted automatically. For example, if you have a system that has a known issue where like maybe when it reboots all the drives don’t always come back online. I have seen cases like that where until they got configure out why some of the drives weren’t coming online they had configure it to not reboot automatically. But in the past without having that when we schedule for patching we to take into account that reboot maybe required and so we schedule Maintenance Window according to how long we think it will take if a reboot is going to be required. And so if you got the time, in the past we just install it automatically whether it says to be rebooted or not, reboot it. And there have been few updates from SQL Server, few CUs, or service packs where it gave me no message saying it needed to be rebooted, so we didn’t reboot. And then the next time I go in there to try and install an update, I then later go in to try and install the SSMS update and it says, can’t install there is a reboot pending. Like, why didn’t you tell me? So if you got the time scheduled and you don’t and you just want to play it safe. I’d say, yeah go ahead and schedule the reboot if you don’t foresee it causing you any problems.

Steve: Alright, so one of the things you mentioned there was the patching of SSMS or SQL Server Management Studio and one of the things that’s interesting around that is that, I mean it’s used to be the Management Studio was part of the SQL server install and if you updated SQL Server you would get your Management Studio patches at the same time. But now Management Studio is detached, running separately and with a completely different patch cycle and we’re getting much more frequent updates there but the other thing is it’s not a very smooth update process like a lot of the Microsoft products desktop applications for instance. You will get a message that says, there is an update required or come part of the Windows update process. But with Management Studio pretty much you got to always download it and then install that update. I’ve talked to a lot of people who, I don’t know, they kind of a little bit bothered at how annoying Management Studio is with monthly updates. And I’m just curious what your take is on updating Management Studio? Because some people I’ve talked to they come across to say, “Well, I’m not going to update Management Studio every month because I don’t need those new features.” But do people really need to update management studio often for security purposes?

Robert: With SSMS there’s really not a big security issue with the SSMS. Nobody is going to take away SSMS on your machine and be able to do bad things with it. Like they could took over your SQL Server instance. So there isn’t a security reason to be diligent about patching SSMS. But they are trying to work out a lot with the process and a lot of the recent versions of SSMS have had bugs in them. And there maybe things you don’t notice right away for specific features that you rarely use but you can definitely look at the update to see what they’ve done and determine if it’s something you need to do. But for myself every time an SSMS update comes up I update the SSMS I have on my desktop. I rarely ever use SSMS on the servers themselves. I like to have SSMS on the server in case there’s a critical issue and the only way that you can connect is via the dedicated admin connection. So I would like to have the SSMS on the server but I almost never use it so I don’t update the SSMS every time on the servers, every time an update for it comes up. But what I will do is when a new CU or SP comes out then I’m going to apply it to my instances. I’ll get the latest version of SSMS and I’ll install that as the same time as I do the CU and updates. I know that SSMS is on the servers themselves is at least as current as the last patch that was applied for the SQL Server itself.

Steve: Yup. Ok, great.

Carlos: Ok, that’s an interesting approach.

Steve: So I knew you mentioned accumulative updates there as well. It used to be like maybe 8 or 10 years ago that cumulative updates weren’t really as safe as they are today it seemed like, and that people would generally just stick with service packs when they are doing patching unless there was a specific reason that they needed cumulative update. But it seems like today cumulative updates are strongly recommended compared to where they were in the past. I’m just curious on what your feelings are on that or your experiences.

Robert: There’s used to be a big topic of debate amongst DBA’s whether or not to install CUs. A lot of people said, they always install the latest CUs. I was one of those that always said, “I only install a CU if it has something that specifically fixes an issue I’m having.” For the very reason that they didn’t get CUs used to not get full regression testing. When they were working on 2016 one of the things they change is that CUs not get the same full regression testing that service packs get.

Carlos: There you go, that’s the change right?

Robert: Yeah, so for me that really took that element of risk out of it. So now I will install a CU at my first opportunity to perform CU updates on the instances.

Carlos: I think that’s the effect that Azures had on the environment and that they need to be able to test those to support the Azure environments.

Robert: Absolutely. And everybody who owns Azure gets those updates automatically already so it was really one of the reasons say had the building the same level of trust for their CUs as they do for service packs because if you’re in Azure whether they’ve taken that choice out of your hand you’re getting the updates whether you want them or not. It’s really one of the selling points of Azures is that you don’t have to worry about the updates. It’s all taken care of for you and it happens automatically without you having to do anything.

Carlos: Right, also true. So before we go on with the other scenarios because I had a lot of questions on, so are you bundling your Windows patches and your SQL Server patches or do you like to do those separately?

Robert: We’re not bundling them, in that sense they are separate.

Carlos: Bundles is the wrong word. I guess what I mean to say, ok it’s patching weekend I want to do all my Windows updates and my SQL Server updates at the same time.

Robert: Yeah, so that’s really the kaitens we’ve gotten into now, and all of our production databases are on availability groups. The way we do it is we always patch the inactive nodes one weekend and do all the patching of the inactive nodes and then we failover our availability groups so that the patch instances is now the currently active node. And then we will run with that for a couple of weeks and then we’ll do the other CU. Unless there are some issue that we feel we need to update sooner we’ll do that another weekend a couple of weeks later and then throwback again.

Carlos: Ok, and then back into this next architecture if you will. I mean, so many people, they are stand alone where there’s clusters, now availability groups. And it sounds like if you’re familiar with the clustering idea that same concept, I can apply my service pack to one, flip it over, apply it to the other, and then either to flip it back or whatever your policy is there. That hasn’t change much with the availability groups.

Robert: Exactly. I mean with the availability groups you still want to patch your inactive node just like you would a failover cluster, failover and then do the nodes that are left that used to be active and then now inactive.

Steve: But it also sounds like either way with that whether it’s an availability group or a failover cluster that you’re delaying the time between when you patch one half of it versus when you come back and do the rest of them, so that you can have time to know if something went wrong and the ability to flip back to the old one if something did go wrong.

Robert: Absolutely, and because the way we do it where we break it up with the nodes to different weekends. If we do run into an issue like if there is a breaking change in the new version which is a lot likely to happen with the service pack than with the CU, but if there is a breaking change and the secondary can’t continue syncing data because there are some upgrade in the database that it’s trying to do but it can’t because it’s the secondary. The type of behavior you would see if you had a major mismatch between the nodes, you then have the option because the unpatch side is now the inactive one. Typically, you don’t even need to wait till the middle of the night. If you say, “Ok I’ve got to get this patch right now.” You’re not going to cause a major outage by now going in patching the secondary to make it match the active database.

Carlos: Yes, then you’re back in sync and hopefully that fixes the issue and they can continue to replicate.

Robert: Absolutely.

Carlos: Yeah, very cool. And then testing, right? We talk about automated deployments, right? Any specific test that you do around admittedly and I guess you know, maybe I shouldn’t admit this, but I think a lot of times my testing has been, “Oh, I install in a lower environments.” The proverbial, wait for something to blow up, and then if I don’t hear anything I move forward, right? Thoughts around patching testing?

Robert: That’s definitely one of the things I’ve always done is I start with dev and then move on to test and then move on to production. So like when the new CU or SP comes out I’ll install it on our dev machines the same day that we get it. And that gives us some time to see if any problems arise there before we go there. Also, usually if there’s a major problem with the CU or SP usually you hear about it within two or three days of the patch going public. Because a lot of people are start installing it on the devs environments right away and if there are problems they start finding it almost immediately. By the time we get around to patching the first set of production servers not only I’ve had it running on dev and test for awhile. A lot of other people have two and a lot of these people are very vocal when they find problems and really put it out on social media, which is one of the things I love about things like Twitter because you really, and some may post out, “Oh, this new CU is breaking my machines don’t install it.” Even if it’s somebody you don’t follow people will start retweeting it and you’ll see it pretty quickly on social media.

Steve: Yeah, that’s interesting because that really shows how valuable social media can be in that case where, I mean a lot of people look at social media in the workplace as a time snake in complete ways but when you’re using it for things like that it’s incredibly valuable. Ok, so one of the things I really want to ask about and it sounds you’ve done a lot of patching, do you have any horror stories either that you’ve seen happen or you’ve heard of happening where somebody tried to do a service pack or an update or a new version of SQL Server and something just went horribly wrong.

Robert: I have seen a few of those. There were some well known bugs that got hit in CUs and service packs where the upgrade would fail, the first part of the upgrade would fail but then not always but sometimes if there are system updates that have to be upgraded. You know, it will run a script to upgrade those objects and it puts the database in a state where if at that point it fails it puts it in a state where it won’t come unwind because the database is marked as in an upgrade. So if the CU or SP fails at that point it will uninstall the upgrade but it doesn’t revert that database and take it out of that upgrading state. It requires manual intervention. Obviously, one of the easiest ways to fix it is to, and we only know this because we’ve had to go through these issues where the servers won’t start.

Carlos: Life experience here.

Robert: Yeah, find the script, try to run, start the server with only master running and run the upgrade script and let it complete and maybe it will come late when you run it manually, if not it may give you a clear error, pin you exactly where it was. Last time I ran into this is because they made some changes to the job systems, the way they track jobs in MSDB. And part of their script was it deleted all of system categories by default and then inserted a bunch of new system categories, and had the IDs for those categories hard coded expecting that the likelihood that somebody has created a custom ID or custom category and that it’s using one of these IDs were going to use. They basically just assume that those category IDs wouldn’t be taken up, so we had custom categories that we had created and those IDs were in use and so the script failed because there is a unique constrain on the column. These were really kind of basic things we would yell at our developers if they didn’t check for if they’re going to insert hard coded values and not check to see if they exist already. But those get reported, they fixed those issues, and we haven’t seen that kind of thing…

Carlos: Click, yeah, record again. I apologize, Robert. If you’ll just go ahead and just pick up that story again talking about the change they made to the schedules or the categories then we can keep going.

Robert: Yes, so last time I encountered this issue myself was because they were upgrading the job system in MSDB which involved, their script would delete all the system categories from the categories table and then reinsert a bunch of new categories. The new categories was actually about as twice as many categories and they just made the assumption that the IDs, the hard coded IDs, they were inserting for the categories wouldn’t have been taken up by any user created category IDs. But we had created several categories of our own so when the script ran it had the unique constrain error because some of the IDs they were trying to insert were already in the table, and so it failed in the middle of the upgrade. So I found the script that they ran, ran it manually, got the error that it was generating. And I say, ok, so I went there. I dumped out the data from the table into something so I could fix it later, and then just delete, emptied out the table, and rerun the script and it completed successfully. These issues have all been reported to the team. I particularly gave them a hard time over that one because that’s the kind of stuff we would yell at our developers if they did. Like, why are you inserting hard coded values in not checking to see if they exist already?

Carlos: Or nothing else at least go to like 500 or something, right?

Robert: Right. But they took all that information in and they address those problems and these things never happened for a really long time. The only recent occurrences I’ve heard of these issues happen for people that are installing old updates. Like somebody is installing, “Oh look I just found out there’s Service Pack 3 for SQL 2005. Let me install that”, and opps it fails.

Steve: Interesting as you say that. I mean can we even get service packs for 2005 anymore? I remember recently somebody looking for one and we couldn’t find it anywhere on the Microsoft side.

Robert: Right, so you can’t get it through self service, you can’t find it yourself, but if you need to you can call my reserve support and they can get you old service packs.

Steve: Ok.

Robert: They are just going to ask you why you don’t upgrade. Obviously they are going to want to know why you don’t just upgrade to supported version. But they only keep the current versions of the updates online for you to download, so if you need really an old one you’re going to have to call support or I sometimes see people posting it for money. “Service Pack 3 for 2005, does somebody have a copy?” I see a lot of people are exchanging old updates that way.

Steve: Ok, interesting.

Carlos: Well, awesome, very good conversation, Robert. We do appreciate it and some good food for thought there. Shall we go ahead and do SQL Family?

Robert: Sounds good to me.

Steve: Ok, so Robert, how did you first get started with SQL Server?
Robert: You know, I’ve been in love with computers for as long as I can remember. But I didn’t go into IT straight away when I got out in the real world. I actually worked with the [term unclear – 36:58] disabled people for the first 10 years of my, that was my first career for 10 years. I worked with the [term unclear – 37:06] disabled people. But I was really longing for something different and still have that passion about computers. I’d started doing freelancing work, was doing Perl scripting and other types of web development. And eventually I decided, ok, it’s time to make the jump into the IT field for real. And so I started looking for position as a web developer. That’s the cool job that everybody wants though, right? Everybody come out of college wants to be a web developer. Competition was very stiff for web developers. I went to several interviews and ultimately I got offered a position as a database developer. And I thought, ok, I’ll do this for awhile and try to work make my way into web development world there. I worked for that company for 6 years and over the years, they were a very small company and their needs change and sometimes I would do web development. Sometimes I would do application developments, sometimes I would do database development. But we came to a point three years into my tenure there where our DBA had left the company 6 months earlier and they had never filled his role. And they were telling me that they thought they were at a point where they could make me a web developer full time. And at that point I realized I’d no longer want to be a web developer. I really enjoyed working with the databases. So I made a counter proposal, told them, at that time I was the head of the database development team. So I proposed that I take on the role of a DBA and still continue being the head of the database development team instead of doing web development full time. So that’s how I got my first DBA position. So that’s how I really got into it.

Carlos: Yeah, funny how things change, right, over time.

Steve: That point of enlightenment when you realized you don’t want to be the web developer. I can remember a similar point in my career as well. It’s an interesting time.

Robert: I like to tell people I’m a recovering web developer.

Steve: Oh, I like that.

Carlos: Now, of all the experience you’ve had in SQL Server and SQL Server is changing all the time. But if there’s one thing you could change about SQL Server what would it be?

Robert: What I would really like to do is there is some really horrendous features in there that I would love to see go away.
Carlos: Just take them out and shot.
Robert: Right. But that’s not likely to happen. If I had to narrow it down to just one thing, I would say get rid of all the features that have been deprecated for years.

Carlos: Pull the plug already.

Robert: I mean as much as I love database mirroring. I mean I wrote a book on database mirroring. Get rid of it. It has been deprecated for much longer than things are supposed to be deprecated. I mean there’s an extremely long list of things that have been deprecated since 2008, 2012. It is like get rid of these things already. We have people that are still using them and the logic they use is, they said that were going to get rid of it after two versions and they still haven’t, so it’s obvious that they are never going to. Why should I bother switching to the new stuff? People are still using the SQL 2000 system tables instead of the current catalog.

Steve: Right, right, so force deprecation. I like it. So what is the best piece of career advice that you’ve ever received?

Robert: I would say the best piece of career advice I ever received was that you have to be your own advocate. Early in my career this was something I really had a hard time with. I’ve never been one to go out and say, “Look what I did. Isn’t this great?” But one of the hard things of being a DBA is if you’re really good at your job nobody notices. People only notice the DBAs who are always fighting fires. If things are always breaking and you’re constantly in reactive mode trying to fix, people are like, “Oh, he is a great DBA because he is always fixing out problems.” And then there’s the guy who’s, his servers are not experiencing these problems because he is proactive and he prevents things from happening, so people don’t see all the work he does. And so the really good DBA, really proactive one, gets overlooked because everybody else in the company doesn’t see the fires that you’re preventing. And so they think you just sit back there and do nothing all day. Learning to speak up for myself and to publicize the things I do definitely was one of the best things I had to learn how to do.

Steve: Yeah, interesting. Yeah, I can see that how the firefighter reactionary DBA can say I fixed these ten crisis situations, where the proactive DBA it can be more challenging to say that I prevented these ten could be crisis situations, right? That’s really what it comes down to. That’s the difference between the reactionary and proactive. Ok, cool.

Carlos: Robert, our last question for you today. If you could have one superhero power what would it be and why do you want it?

Robert: If I could have one superhero power I want to be able to freeze time.

Carlos: Oh, freeze time, so almost like be able to walk through it where nobody else is moving type of thing.

Robert: Absolutely. Time is frozen for everybody else that I may be able to walk about and do things.

Carlos: Yeah, very handy when the boss is in your cube, right? When is the database is going to be up?

Robert: Exactly, so I think goes down and people bugging you, you could freeze time and get it fixed and then unfreeze and say, “It is fixed.” “What do you mean?” I think the challenge with that power though would be keeping it in the spectrum of superhero power as opposed to super villain power.

Steve: Yes, very good point. So many of those types of powers could be abused easily.

Carlos: That’s right. Well, awesome. Robert, thanks so much for coming on the program today we do appreciate it.

Robert: Thanks for having me guys.

Carlos: It’s been great.