Episode 98: The First Change

In a recent SQLSaturday conference, I walked into the speakers room and asked the question–What is the first thing you change after you install SQL Server?  It was interesting to get their take on the server setup and I think you will enjoy the conversation.  There are various answers on this one but some of the speakers have mentioned stuffs like set auto grow files, SQL Prompt, SQL parameter class and max memory among others.  I would like to thank Kevin Feasel, Jonathan Stewart, Eugene Meidinger, Raymond Kim, Tracy Boggiano, Mindy Curnutt, Thomas Grohser, and Vladimir Oselsky for their suggestions.

 Episode Quote

“I would say that now I’m basically a broken person without SQL Prompt.”

“One of the things that I recommend all of our customers… is an administrative failed logging attempt alert system.”

Listen to Learn

What people say is the first thing to change about a SQL Server installation.

It should be noted that the suggestion on the auto boost was said in jest.  🙂

Transcript: The First Change

Brian Carrig: 226 which is the first thing I would change from the default. Second thing I would change from the default is the set auto-grow all files for user databases. And SQL 2016, the default behavior is that single file growth rather than auto-grow all files. Previously, everybody would have enable trace flag for that behavior that’s ignored now so you have to set a setting that says auto-grow all files.

Mindy: I’m staying away for the totally obvious ones so… let’s do come up with.

Mindy: My name is Mindy Curnutt.  One of the things that I recommend all of our customers put on their servers and that I’ve scripted up and I give to them is like a complimentary script is an administrative failed logging attempt alert system. So it basically goes out in it is querying the error log, event log to look for the 18456… What is it? 18… No, it’s 18, failed log-in attempts basically. I think it’s 18456, event class, something severity, whatever. I used to know it at the top of my head but I’ve gotten too administrative. So anyway and then I have it set where if there is like if someone is trying to get in with an account that has administrative level rights, like SA for example, and someone has try to get in with that account with the wrong password more than x amount of times and it’s configurable within a configurable time window. It will send an email to the administrator so you know someone is trying to guess your admin password and you got their IP address and you immediately know that’s happening.

Carlos: What’s a little scary there is I’m constantly surprise that the number of error, SQL server logs that I see where I’m just constantly seeing like failed SA log-ins. Like back to back to back to back… And normally it’s like some machine that just forgot to change their password but it just constantly banging on the server and you’re like, “Hmm, that seems a little weird.” Anybody else, here we go.

Alex: I would recommend customers take closer look what a developer is using on their code to dynamically execute string. The problem what I fixed and forcing C# developers to be more specific especially with SQL parameter class that they are using default and the default is nvarchar. And most our databases we have a varchar which is a trigger convert implicit. Nobody can see it but it cost lots of problem in their performance. So number one I forcing developers if they don’t want to use, it’s called SqlDbType, implementing have to be varchar because by default it’s nvarchar. So to avoid such problem better to use store procedure which is much more manageable or just to keep an eye on your ideal code that’s much more for C# developers and be specific with data types.

Carlos: So that’s kind of an interesting take, right, so like setting a policy before you get going?

Alex: An interesting part they say when I’m done in presentations about. They say it’s[00:05:00] the best practice to specify data type in request code. I have a code, I wrote a code for them, and guys, that’s what you should do, that’s have to be. So sometimes we’re teaching .NET developers something else.

Carlos: Sure. Here we go.

Jeremy Fry: My name is Jeremy Fry and I agree with the whole room that these changes that people are indicating that are best practice to change or reasons why they would change it. Mine is max memory and the reason why it’s because I am a BI guy, and so other tools need to run on a server analysis services, SSIS. Although I would like to in real world scenario and in a best use case scenario break those components apart where I’m segregating if I have a warehouse my analysis services on its own server. An instance versus on the production server where your transactional information is held and your day to day business is occurring. But in a perfect world, that’s doesn’t always happen so with that being said sometimes I see memory the analysis services utilizes memory is it starts to cut things off at about 80% utilization. And so if you have high level of things happening on your transaction system that’s utilizing a lot of memory then you start to see a bottleneck down the line in other tools as well.

Woman: Ok, nobody said it so I was leaving it there. When I install a new instance of SQL server and it depends upon the resources that are in the actual operating system for the SQL server and of course also what other instances of SQL might be on that server so there is no tried and true setting that I use to put it at but I always go in and look at the cost threshold for parallelism and the max degree of parallelism because the settings that they are currently set at I don’t think are realistic for today’s hardware. And Microsoft, of course, likes to be always backward compatible so those values are kind of sitting at levels that are no longer appropriate for either today’s hardware or the platform anymore. Now what you set them at can vary but you need to look at that I think. Too much parallelism could not, you know, it’s like too much cookies, right?

Carlos: There you go. That’s a knowledge that I haven’t heard before – cookies to parallelism.

Alex: [inaudible – 7:58] package but you’re just killing parallelism. It’s not right for every single, there should be balance. I can’t find formula what the number of CPU and.

Woman: It depends on the code.

Man: Yeah, I start with 25 and I go up as far as a hundred.

Alex: They start it from 80 go through to 300. I told them, guys, I’m only one who is fighting a whole team including manager. I’m opposition. I hardly convince manager to jump over 2014. Skip upgrade 2014. Go over to 2016 directly because 2014 doesn’t do for application. Absolutely, CPU utilization, that’s true. Other than that, 0, just jump. I was like this to be fired because our manager he doesn’t like any oppositions. Somehow I got another back from my Pennsylvania team, he was my manager in Ireland. And the Pennsylvania team backed me up that’s why I still in here. He doesn’t like any opposition.
Tracy: Yeah, definitely query store then. Change the size. It only keeps 100MB of data. Change it up to like 2GB or something and tell it how many days you want to keep because it keeps up to a year. And get it off of the primary file group. There is a connect item for that. I twitted it out this morning so you can find it and upload it.

Woman: Ok, here is one. How about optimized for adhoc workloads? I’ve read that here has never been one negative, somebody is going to like respond, but I’ve read no one has ever ever ever seen anything negative or bad ever ever happened turning that on, ever. There you go, one more.

Carlos: So I do know that [word unclear – 12:29] experienced it once that when he turned it on he actually saw a CPU spike that he couldn’t explain. So you could toggle it, right? Turn it back off, right, CPU would. I can’t remember what the spike was but I think it was may have been negligible. But basically he could flip it and then he would see CPU differences. Now, I don’t know if you eventually tribute that to something else and it was like luck that somebody just happen to be doing something else while he was toggling that. That was the one freaky thing which I need to follow up back with him. But I don’t how he resolved that. I know that’s what [name unclear – 13:09] was complaining about.

Man: The one application that ran the same query plan only twice for every single. The one really efficient. If it comes exactly two times.

Woman: It was a query that checks to see if there were records were there before it actually runs the query. It seems like …

Man: It could be.

Woman: Really, [inaudible – 13:31] You’re like, go to the grocery store to see if they are open and you go back home and you get your car.

Man: I see you’ve met my developers.

Man: This is like one really [term unclear – 13:54] kind of thing. But for SSRS I would advice there are logs for when stuff gets used, and the default, I forgot, I think it’s like a month, maybe two or three but you can set it all the way to like 6 months. And that logs are really useful when you need to go back and say is anyone still using this report, because usually the answer is no. I mean, I know we’ve got a bunch of reports that are just aren’t being used and at some point we need to go back through, and it’s really nice to have 6 months of data that says the only time it was ran was when you ran to if it is still working. So that’s a nice change for SSRS is just get that log because at some point you’re going have to do cleanup because you just get report blown. It’s a problem.

Mandy: This is Mandy again. So that makes me think of having a SQL agent job that once a week or something cycles your error logs so you don’t end up when some things actually not going well and you want to go in the error log and take a look and your server has been up for a year. Yeah, good luck with that, right? You’re[00:15:00]like waiting, and waiting, and waiting, and like two hours later the thing might come up, right, so awful.

Tracy: Hi, Tracy Boggiano, again. Can we make the Hackathon noise go away and error logs please?

Man: Oh my god, error logs, gigabytes.

Tracy: Gigabytes in a day. System [inaudible – 15:30] points getting full [inaudible – 15:31] gigabyte log file so we only keep 30 days.

Man: All the messages are really scary. Couldn’t identify this file, couldn’t do a check point. All these horrible stuffs here that’s Microsoft, they are like…
Woman: Oh, your favorite settings. First is SSMS, if you like to have it, do certain things, and you want to see sp_helpindex, and you want to see sp whatever, right?

Woman: Don’t you go and setup hot keys. You don’t have to sit there and do that. You have a certain way of working.

Man: I’m not smart enough to do that. I don’t have any ITs that… I know what I like to do, that’s why.

Woman: I know there was one version of SSMS were they got rid of control [inaudible – 16:28] I couldn’t even work because I don’t hit that [inaudible – 16:30] some kind of road thing. You put it back, I think there are so many people like, “Arhaha”.

Kevin: This is Kevin Feasel. I must confess I’ve been lying the whole time. The first thing I do is install/configure PolyBase. The second thing I do is install R. The third thing I do is change the log growth.

Brian: This is Brian again, while we’re doing confessions, I would say that now I’m basically a broken person without SQL Prompt.

Carlos: We got a couple of concurrence over here. Yeah, SQL Confessions, oh boy!

Man: Just consider it the [inaudible – 17:24] DBA you got to buy SQL Prompt, just get it over with, alright?

Carlos: Yeah, I guess I’m interested when they are going to. Is there a competitor for the SQL Prompt. I know that Compare.

Man: I don’t use SQL Prompts.

Carlos: Oh, what are you using?

Man: I use DevArt SQL Complete.

Carlos: Oh, DevArt, ok. Yes, such true, they are there. I feel like I see a lot more publicity on the other tools maybe because of a compare and all, you know.

Man: [term unclear – 17:54] has also a SQL completion tool. I think it is also named SQL Complete.

Episode 97: SQL Server Replication

If you have ever been responsible for a replication environment, you know what a pain that can be.  Many of the guests of the show will point out their frustration with replication and many are loathe to use it–myself included; however, there are a few brave souls out in the world who do use replication regularly–and even use it well.  Chuck Lathrope is our guest today and he talks us through some of the components of replication and why we might not need to be so skittish about this long time feature.

We cover all the basics including an overview of Transactional Replication, Merge Replication, Peer-to-Peer replication and Snapshot Replication, the concept of publisher, distributor and subscriber in relation to replication.  The most important piece is Chuck gives us some good business cases for why we might use replication and I think you will find the discussion interesting.  Thanks for joining us on the SQL trail!

 Episode Quote

“I like to talk about it to waylay fears and give out some best practice knowledge”

“Stick to people who know what they are doing”

“The real ultimate goal of replication is to offload work from your main source of truth database server”

Listen to Learn

– Why you might use SQL server replication
– Different types of replication
– Use cases of transactional replication
– Replication and trace token
– Troubleshooting tips
– Where to get good information

https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Transactional-e34ed1e8/

https://www.brentozar.com/archive/2014/07/performance-tuning-sql-server-transactional-replication-checklist/

https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

http://github.com/SQLGuyChuck/

http://tribalsql.com/

http://download.red-gate.com/ebooks/SQL/fundamentals-of-sql-server-2012-replication.pdf/

About Chuck Lathrope

Chuck Lathrope is a Data Architect for Limeade, a SaaS health and wellness platform. He is a two-time SQL Saturday event speaker on replication, was a Top 5 nominee of Red-Gate Exceptional DBA Award in 2009. With over 20 years of experience in IT, he have used different operating systems as well as worked with different applications. He was also a Windows System Administrator for 12 years.

Transcription: Transactional Replication

Carlos: Chuck, welcome to the show.

Chuck: Thank you very much guys, glad to be here.

Steve: Yeah, it’s great to have you on the show. I know we kind of been talking about this for a few weeks trying to get together, and happy to have you here as a guest.

Chuck: Yeah, I know. Glad to be here guys.

Carlos: Yeah, even if you are a west coaster you’re still accepted here.

Chuck: Oh, great!

Steve: Anyhow, Carlos, there are more west coasters than there are east coasters right now.

Carlos: Oh men.

Chuck: Yeah, we’re winning.

Carlos: Well, what I lack in numbers I make up for ego apparently.

Steve: Alright, so today’s topic is on SQL server replication. I know this is a topic that I first heard you talked about at SQL Saturday in 2012 and I found your presentation there really interesting I didn’t picked it up very quick very soon after that. But then when it came around to a time that I needed to use replication I lean back to some of those things that I learned from you back then, so it’s great to have you on the show to be able to talk about replication today.

Chuck: Yeah, great, thank you! Yeah, I love talking about SQL server replication at SQL Saturday events. I try to do it as often as I can because I have all the battle wounds from having a very large environment with the transactional replication and so. Whenever I can I like to talk about it to waylay fears and give out some best practice knowledge.

Carlos: Well that’s interesting because I think there is a lot of fear around replication. We’ve brought it up in Episode 56 and yeah nobody wants to touch it with a 10-foot pole and here you said you have a large environment with replication, so I know I’m interested to kind of get in here and talk about some of your experience and why maybe we have so much fear around it.

Chuck: Yeah. I think the difficult part about replication is there’s a great job of keep retrying even though there is a failure. So if you’re not monitoring that correctly it will go sideways and if you’re not monitoring appropriately then it can get all kinds of bad and then you get to stuck situations like, now I’ve got this gigantic log file or distribution database. How I’m going to fix it? And then you go on the net and google stuff and there is so much bad advice out there that would basically destroy your entire environment and you will get basically a lot of wounds from trying to follow some of the advice out there. It seems to me like most of the advice out there is for people doing it like a dev environment not a real production environment so it’s not great. I usually stick to people like Hillary. Anyways, there are some great people out there that do from the Microsoft side and from consultant side that give great recommendation for replication. I mean, stick to people who know what they are doing, not random posts on the internet.

Carlos: Well, so I guess shall we go ahead and kind of let’s tee up some of the components around replication and let’s talk about some of the moving pieces.

Chuck: So there are three types of replication. There is actually four but one has been deprecated, that updated subscriptions. There is Transactional Replication which is always running, always sending out data to your subscribers. And then there is Merge Replication which allows you to do bidirectional updates, and then there is Peer-to-Peer Replication, and then there is Snapshot Replication. The snapshot really is you do it on less frequent basis than all the time like transactional. So like in a data warehouse scenario I could do like every 6 hours, or every 12, or every 24 hours and get that over to my data warehouse, so that’s what snapshot does. And my expertise happens to be in transaction replication.

Steve: Ok, so then what are some of the business cases or real use cases around transactional replication then?

Chuck: Yeah, so the real ultimate goal of replication is to offload work from your main source of truth database server. So you got multiple databases and they can all
be a part of different publications then send out to subscribers which are your clients that receive that data. The main goal there is just to offload work on off of that main production server and that’s kind of goal in life.

Steve: So occasionally in high availability or disaster recovery conversations somebody will bring up replication as a possible solution and I’ve never had a good feeling about that and I’m just curious what your thoughts are.

Chuck: Yeah, well just like you guys were talking about in Episode 59 and higher availability solution. Replication wasn’t really designed to be a higher availability solution. It was really designed to replicate some data to give off to other servers so they can do processing of that same data for whatever purpose you may need it for. Because what you can do with replication is published out tables or if you have Enterprise you can publish out a view and you can even publish out to your store procedures and functions and whatnot and your indexes. But you don’t have the security that kind of goes along with that and then there is still automatic failover anything like that. Your best option there is to stick with always on availability group or log shipping or database mirroring to do that high availability.

Carlos: Ok, so if I really want to just move some data off to another server for reporting purposes that’s where transactional replication or snapshot replication might be a good solution but not for the HADR.

Chuck: Yes, and a nice thing about the replication is it’s available on all editions and versions of SQL server so I can have Standard Edition of SQL out there and use that. I can even use that in my distributor server. So kind of give the bigger picture of that transactional replication. You have the publication server and that’s your main source of truth data, and then you have the distribution server that holds the distribution database that then sends that information to the subscribers so it’s like there is always a 3-way kind of path goes from publisher to the distributor to the subscriber, and you can have many subscribers and then you have one distribution server that could actually be on the publisher or the subscriber side. You don’t have to have a dedicated one. But when you start getting to bigger environments you definitely want a dedicated distribution server.

Steve: Ok. Now, I know you said it works with all of the latest versions of SQL server, when was replication first introduced?

Chuck: It’s been forever. I think it even came back in Sybase days. It’s been around that forever. It basically just pulling data from your log files looking to see which of those transactions are flagged as mark for replication and then sends that on down the path, so it’s been around for ages.

Steve: Yup, so with the replication going from a publisher to the distributor to the subscriber does the subscriber have to be on the same version of SQL server as the publisher?

Chuck: No, it doesn’t. You can have a mix. What you typically have as your distributor just needs to be at a level of your publisher and so you could have 2008 R2 publisher and 2014 distributor and a 2016 subscriber so the distributor just needs to be at a level according to your publication so you can have high level on your subscriber but you can’t just go lower on your distribution server than your publication server.

Carlos: So we’re talking about some of these terms, right, publisher, distributor, subscriber. What the original documentation release that I saw mentions kind of this newspaper hierarchy which some of our millennials may not even know
what that is. And so I feel like, and one of the things that was brought up is just some of the overhead and maintaining some of these. Where has some of the love been? Because it’s been around so long where has some of the love from Microsoft been with replication?

Chuck: Yeah, good question. So the latest kind of feature set just with replication is the support for always on availability groups such that if your main active server, primary.

Chuck: So you’re primary server can move to any of the other secondary servers and replication will go along with it, so that’s kind of the main reason support. And also that same team also does change it to the capture and change tracking so some of those features have been receiving a lot of love in the past few editions of SQL server. But SQL replication has been around for such a long period of time. It’s not a lot more features they can add on there that I could really think of other than maybe helping out with support ability of replication, so most of the love has gone that route.

Carlos: Got you. And I think that’s where I would have to agree is that in a setup like you mentioned like all the kind of the bad advice from fixing or troubleshooting. I guess I would like to see a little bit more love there because I feel like a lot of the fixes would just start over again. You know, and so being able to tap into that a little bit easier and feel better about what’s going on would be super helpful.

Chuck: Yeah, it would especially for people just getting into it. It could be overwhelming to do. I mean I was so passionate about in the past days to help author a chapter of a book SQL book from Redgate just on replication and kind of hear this. Here is how you can kind of monitor and here are some best practices for it just to kind of get more information out there because it’s been relatively few books out there. I can give a link to that. And then also Sebastian Mein which I think you guys had on a recent podcast. He created fundamentals of SQL server replication. It’s a free download from Redgate’s books too is a great starting point in kind of getting into replication.

Steve: Yup. Now, on the front of troubleshooting replication don’t you have some script available that you built that are available maybe on GitHub that can help with that? I know I’ve used them a couple of times I just forgot where exactly they were.

Chuck: Yes, so my SQL Guide Track on GitHub has some replication monitoring script for live use. And It kind of what I’ve lessons learned in replication it gives you the ability to create what are called tracer tokens. And it’s basically you insert this tracer command into your log file and it’s basically stuck at the very end and it watches it go through the entire process to your subscribers to see that latency that involved in there. You can do that with the replication monitor tool which is a GUI tool that you would use to kind of monitor and maintain replication. But the trouble with the tracer token is that it’s not really, you know, if you’ve got a busy environment that’s kind of overwhelmed currently, that tracer token might take ions of time to get through and it’s you mainly do it or you can automatically script it. The Microsoft IT guys actually have upon one of the free sites for script center where you can monitor replication. And you’ve automatically created this tool that would just continuously insert tracer tokens and you could see kind of health with it. But I found that a little bit limiting so my trick to tracking replication is look at the distribution database and figure out how many commands are waiting to be distributed out to the subscribers, and then I monitor that on a frequent basis like every 15 minutes or every half an
hour and it sends me an email. And it’s kind of gone above some sort of threshold, and that threshold is a variable amount that I can put in as a parameter. Because in some environments, you know, 5,000 records behind it is a big deal. On the other environment it’s be hundreds of thousands of records behind if you have a really gigantic installation. So that’s what my monitoring kind of does. It just monitor that queue that’s basically up there in distributor and then I also monitor for kind of pain points that replication has.

Carlos: I apologize. Before you get into that, I want to like I guess stop, one and make sure that everybody knows so all the scripts and things we talk about today will be available at sqldatapartners.com/replication so we’ll make sure to put all those things up there. But you had talked about tracer token, so I guess I’m all of a sudden thinking, it’s like I’m thinking of a tracer and that’s not profile, all of a sudden there was something that came to mind that was like that but. This is new to me, right, so it almost sounds like this is an object that I want to create in SQL server that’s going to start capturing commands which again kind of sounds like profiler or extended events. Is it just something that is specific to replication? I guess help me define what the tracer token is.

Chuck: Yeah, it’s very specific to replication. You basically you are sticking in this special command. You can use T-SQL that create this little tracer token or you can use the replication monitor tool to insert that tracer token and just watch it in the GUI. And it just shows you time from your publication to distributor and then the time from distributor to subscriber. Basically this goes through that path. You don’t see it just happens kind of behind the scenes kind of little hidden thing that happens but you can programmatically watch that information and see the results of it.

Carlos: Ok, so I don’t need to create it or modify it. It’s already there. It’s just what I’m using to collect the information from.

Chuck: Yeah, you have to basically interject it. It’s like putting a dye into a stream. You put this dye, a little drop in, and you can just do like one at a time. So you have to manually put that dye drop in the stream and you watch that dye drop go the entire path to the ocean I guess.

Steve: But in this example, that dye drop as you refer to it is really a record being injected into the transaction log. And then when it’s complete that means that it’s been through the part that catches the transaction log and moves it to the distributor and then actually ends up on a subscriber side. Conceptually it would be like almost like if you have a table and you just inserted a row to it and then you knew on the other side that that row actually arrived.

Carlos: Because that’s the one you’re looking at.

Steve: Yup.

Chuck: Some people even use what I call a canary table, so this create a generic table and then it will update the date on it. So it will be just a table of date of one column and this is just the current date and so replicate that up and use it kind of like a canary table so they can look at the subscriber and see how basically latent they are because it should be as close to the current date as possible. That’s another method you could use for monitoring.

Carlos: Interesting. Yeah, I haven’t heard of that one.

Steve: So I know with the tracer token in my experience of using the replication monitor to go through and inject tracer tokens on a few dozen different publishers. It can be painful clicking through all the screens and it sounds like you can do the same thing with T-SQL to just inject the tracer token. To me that’s one that I wasn’t aware of and it seems like that I could really speed up the process of troubleshooting, what’s working and what’s not working with replication.

Chuck: Yeah, I mean the scripts that I have are pretty useful. It doesn’t go down typically to the subscriber level though so I have the typical monitoring of jobs and failures. The standard best practices that all DBAs do for all production databases you need to do that for all the components in the replication including your distribution database and your subscribers so just the best practices known on jobs fail because all the replication jobs are basically kicked off with SQL agent
jobs so you need a monitor for failures of those and be watching that.

Steve: So you mentioned these jobs as SQL agent jobs and it seems like there are several of these jobs that are created when you initialized replication. What are all the jobs that are created in and what specifically do they do?

Chuck: So there is the log reader agent job that’s basically sitting on you master database you’re doing the publication on that is reading the log file. Then there is the distribution agent. It’s sitting in either one of two locations at your subscriber or at your distributor. The reason you have that is what we call is there is a push and pull scenario when you get the data to subscriber. And when you do a push all the work that is happening on your distributor server and when you’re in a pull it’s done on the subscriber side. So if you have a really gigantic environment and you have this dedicated distribution server you want to even offload some of the work off of it so you actually have what’s called the pull subscriptions in it. Basically you think of it, “I’m the subscriber. I’m going to the pull the data from the distribution server so I’m expanding all the work to figure out what data that I need to grab and I’ll go to distributor and grab that information. And the push scenario is usually in your smaller environments, and smaller I mean like you’ve got 5-10 kind of subscribers. In that scenario the distributor is doing all that work and pushing that data down the subscribers. So this is kind of the performance tricks that you kind of switch going from push to pull.

Carlos: Right. Now, what’s interesting is we talk about these jobs. We kind of almost talk about, you mentioned reading it from the log, right? The log is a sequential operation. You know, the way that it writes and things. Ok, and then you’ve talked about workload. Now, obviously the answer is it depends but from a percentage, a generalization perspective, is the reason that replication gets out of whack is because it just gets overloaded and you just can’t catch up?

Chuck: That is one of the scenarios, and then which if you’re monitoring properly it should be good. I mean you could have not pick the proper server for your distribution server, and maybe it’s kind of poor performer, or doesn’t have enough RAM, or doesn’t have enough CPU. Typically it’s I/O problem. So you find out quickly that you have tons of transaction you’re trying to replicate and you’re overwhelming your distribution server. Because in smaller environments you can do it all setup in one server if you wanted to. It’s not really I don’t know why you would ever want to do that because the whole point is offload work off of your publication server but you could overwhelm any of those…

Carlos: So getting back in there and I think you brought up or made a major point at least in my mind and that is the reason that you would use replication and again the offloading component. I think a lot of times replication gets jumped to because you’re trying to keep to systems in sync or something like that. Again, some of these new features maybe a little bit better at that but you’re potentially using replication for the wrong reason. And so replication get’s installed on that main server and so it just has to do with more work to keep up with all of that.

Chuck: Yeah, that all the truth. I think people are trying to use this because it’s available on all editions. It is like, “Oh, I can use Standard edition and save a lot of money”, because you really don’t have replication. I think database mirroring can do in Standard edition with two nodes. I can’t remember now. But now in 2016 always on availability groups can do two nodes kind of cluster.

Carlos: Right, they give you the read-only, or you can’t read from it if it’s available.

Steve: So jumping back a minute to when we’re talking about the different jobs on the
publisher, distributor and subscriber. One of the tips that I heard recently and I haven’t actually try this myself but it was a suggestion that for those jobs, it was suggested you go in and add a second job schedule to it so if something goes wrong and that job fails. There will be a second schedule there that would restart it. Is that something that you’ve heard or is it a common best practice?

Chuck: No, I mean, replication does retry and retry so, and the distribution agent where you automatically retry when you hit a failure, like if you’re like automatically scheduling like a snapshot, and it fails, that could be a scenario we might want have these multiple things. But this like you’ve said before, proper care and feeding have your SQL server and monitoring those job failures. I mean, I could see it in a maybe a snapshot scenario but I wouldn’t see it in the transactional replication because it will just continue to retry. So basically what it does is it retries and fails. You know a minute later retry again and fail, and it will do that forever until you basically fix the problem. And usually the error message is it provides are pretty useful.

Steve:  Okay, great.

Carlos:  Well so I guess getting you, kind of keeping in theme with that, you know, again the sky is the limit but what are your, you know is there like a general rule percentage-wise other than just that process getting overloaded? Why does that stuff get out of sync?

Chuck:  Yeah. You know I think it’s kind of these random hiccups that kind of happen and when you have these bigger environments, you know there’s something happens. It like the common error that I see even in my environment is that the road doesn’t exist at the subscriber. Like well, why doesn’t it exist? So why don’t you just add it?

Carlos:  Right exactly. Like you’re subscribing, you should get that real. That’s part of the process, right?

Chuck:  What the heck happened to that bro? And so then you have to spend time to figure out because everything starts backing up once you have that one error. So you have to do tricks like you can skip that one row or maybe you figure out what row it is. There’s a sp_replic commands that can get you information about the transaction that was trying to apply to subscriber. And you could figure out what row it is and go manually add that row. There’s also a command line tool diff.exe I think. We’re basically at the command line, datadiff. It can do a datadiff between your subscriber and your publisher and tell you what rows kind of missing and kind of screwed up and actually fix them for you.

Carlos:  Interesting. So you’d be okay and I guess to that point so the row being missing in the subscriber, is the common one of that that I’ve seen, you’d be okay with us just basically you know scripting that out and inserting that row?

Chuck:  Yep. Just to make it go past that error but I usually find this, usually like a group of commands that somehow didn’t make it to subscriber. And there’s a, you could have the build that you two, let’s say is, there’s different agents that you can set up on basically all these jobs. But the ones I typically modify in a short term, if I just wanted to skip errors because I know that it’s trying to update a row that got deleted for some reason that doesn’t exist in the publication. So it really got and skip errors. And so you can setup just the SQL agent to use this new agent that’s kind of like an agent profile is what it’s called. So I could change this agent profile and say skip errors and so skip a bunch of errors but then you really want to use that Datadiff 2.0 to figure out what rows are kind of out of sync and you can just manually update subscriber. And sometimes it really comes in that somebody on subscriber actually pull in and deleted a row but in reality is I know that’s typically not the case because I’ll make that security read-only on the subscriber side. So it’s like there’s no way it got deleted but somehow it just, you know, row missing so you got to do every do to update subscriber and you have full rights to do. You can do whatever you want to subscriber side and replication won’t stop you. You can even make a setting that just says, I don’t want to delete any of the data. So when the delete happens on the publication I can turn the setting on replication say don’t delete this row, if you wanted to do that for your workload.

Steve:  Okay sometimes that’s something that we talked about I think in SQL Saturday recently was with that when if the stored procedure was updating a million rows. I just clarified to make sure they’re right. Yet, if you didn’t replicate the stored procedure call it would send all millions of those updates across through replication but you could just push that store procedure call so it gets called to neither side. And then it’s happening sort of independently on both sides rather than pushing it row by row. Is that right?

Chuck:  Yeah. Yep.

Carlos:  Okay so now help me kind of connect the dots there right because in my mind I’m thinking replication, that’s by object right. I go in and I replicate this column with this table and whatnot. How do I setup or how do I visualize the process of saying when this store procedure gets executed that’s what I want you to take down instead of updates and deletes to this table?

Chuck:  Yeah. So you basically configure replication and tell it that I want to use, to replicate these calls to these store procedures. And I honestly haven’t used that use case because usually in my environment it’s like I can’t just describe it as one transaction because it’s related to maybe some other data that’s within the system or the data comes from something else. I haven’t been able to ever do that but you basically just tell replication I want to use to replicate these commands with some stored procedures and you just tell it and it will. If everything’s right it will use that store procedure call and replicate that rather than all the individual changes that happen to your table.

Carlos:  Right. Well I think that’s a pain point for a lot of people. And going back to the whole subscriber thing and like checking when the subscribers have issues, is that when they have, to say more than a couple, even then let’s just say you have three. When that’s near you had, I’m still how to troubleshoot one, so the source system. Two, my distributor which in you said it should also be another system, and then each of the three subscribers. So that’s five servers that I have to go and kind of do something with. And I think that’s maybe one of the pain points around replication. Doesn’t it give you a great way to centrally do all of that?

Chuck:  Right. Well the replication monitor allows you to see all those three servers and help you with troubleshooting. You know, I was in an environment where we had like 8 publications and like 30 to 40 subscribers in a dedicated distributor. And we use to have replication monitor up on our knock window and people would, you know look for errors and stuff happening there but that was just unrealistic and that’s why I created those monitoring tools to look for those kind of errors or replication latency where you’ve got tens of thousands of commands that haven’t been pushed to the subscriber. And it’s like semi-alert and then every 15 minutes I see the trend, if the trend is going down and everything’s good, if the trend is going up something’s wrong because that
replication will continuously retry even though it might’ve complete error. And if you don’t monitor that well enough you come to this point of it’s been for 48 hours how come? You don’t want to be in that scenario.

Steve:  So with the transactional replication on the subscriber side, if I’m using that as a reporting mechanism to offload some of the work from the publisher, if I need specific indexes on the subscriber to help with whatever the reporting is doing on that side, do I need to add those on the publisher and replicate them over or can they just exist on the subscriber?

Chuck:  Yeah, that’s a great question it’s because that’s when the most powerful features of the replication is I can say either replicate the indexes that exist on the source publication or don’t replicate them. And the great thing about not replicating them is your publication server has this specific workload and use case and your subscriber’s typically always different. And what you can do is you can setup in the replication setup is it, what’s called the post-replication script. It’s basically one file that can have any SQL code in there that will then create anything you want on the subscriber side. And it is ran after the publication, when you initialize the publication, so initialization is just the method you go to publish all your data and get it to subscriber. And so in that command there, there’s actually two that you can do, you can do a pre and a post after you do this initialization. So what I typically do is that I come up and figure out my tuning on my subscriber side what indexes I need. I just add it to that script. And that script is you know, check if it exist already, if not you know, add this index to this table. And that gets applied after your initialization has happened so your table’s been populated with PCP. You know, that’s the use of PCP behind the scenes and then it creates indexes if you told it to. And if you told it to not do the indexes then it’ll basically go to your script if you have one. You don’t have to have one. And then you just add them and I think that’s the super hidden benefit right there for replication.

Steve:  Oh yeah. It really seems like that could eliminate a lot of the replication bandwidth by not having to have those indexes in two places and synchronize over.

Chuck:  Yeah. I mean it’s not going to replicate over all your index changes. I think that’s kind of, it’s just whatever happens in the data tables themselves.

Steve:  Right. But on the initialization of it it’s going to have to bring, if you’re replicating the indexes, it’s going to have to bring over the whole index right?

Chuck:  No, it won’t transfer that data. It replicates out the script. Here’s what the index is and it will create that after the fact.

Steve: Okay. So then you’d use this post-script only if you needed, or if the indexes were only needed on the subscriber side.

Chuck:  Yeah.

Steve:  Wow. Lot of great information here. This is good stuff.

Chuck:  Yeah we only scratched the surface.

Steve:  Oh yeah.

Carlos:  Oh yeah, that’s right.

Chuck:  There’s so much. It’s crazy and the amount that you have to get into and just carry on that earlier thought of having those three subscribers. So you typically would have those behind the load balancer. And then so I have this like common name, like this is my order processing system. So my applications stack would just point to this when load balance name and then it would get the information that it will need behind the load balancer. And then when I have maintenance, I can say load balancer take this one server out. And then it allows me to create a new snapshot and then I’ll pause all three of them. So all three of them will become stale but once I do a new snapshot to a new initialization everybody tries to grab it and pull down so you cause an outage if you don’t pause the distribution agent. So then I just, then I work on that one server, get the snapshot applied and then make sure the indexes are then applied. And then add it back to the load balancer and I take my next server out and then so it’s up to date. And I may
decide that I’m going to take all of the servers and just use this one that’s now up to date or I may have one stale and one up to date and one maintaining the other. And so it’s kind of what, in a bigger environment, what it looks like.

Steve:  Okay, great. Well shall we wrap it up with the SQL Family questions?

Chuck:  Sure sounds good.

Steve:  So Chuck how did you first get started with SQL server?

Chuck:  Yeah. It’s how it was. And for that, you know, I didn’t really pursued databases as a career until that kind of dotcom crash in the early 2000’s when unemployment paid for my MCDBA certification classes. And so that like I was completely scared to write in SQL when I was doing Access, like I avoided it like.

Carlos:  Sure.

Chuck:  And some are tipped classes like oh this isn’t so bad. So I did some consulting work in SQL 7 in 2000. Then I became a contractor at Microsoft supporting SharePoint for just the worldwide internal SharePoint farm and work with some guys and Mike Watson They are kind of well-known players in that space and they kind of look to me as the go-to SQL guy. I’m like, “Holy crap I’ve never done SQL clustering before, and load balancing stuff.” I’ve always, you know, just done this MCDBA course before there and that kind of gave me the bug so I become a developer after that. And then I had a boss who said that, “No you can’t be the DBA because I don’t think you got the skills to do that, so just keep doing your SQL development stuff.” So I basically quit and found a new job doing that SQL developer and DBA and that were, yeah that’s a hidden lesson out there for managers out there. It’s pretty easy to piss off a developer and say no or, you know an ambitious person you know looking to learn new things. And my next gig, I work for 7 years as a DBA and a DBA manager and supported just a gigantic replication farm.

Steve:  So, doing all the things your previous boss told you you weren’t qualified to do?

Chuck:  Yes. And I even got picked for a top 5 finalist for the Redgate’s DBA of the Year Award. You know it sounds like ultimate response to no you can’t do that. Okay. Sure I can totally do this. It’s not a problem. So now I’m a data architect so I’ve kind of gone the spectrum from developer to data architect but I’m still a DBA as well. It’s tough to find good DBAs.

Steve:  It’s really interesting how telling someone they can’t do it will motivate them to just be wildly successful at doing it somewhere else.

Chuck:  Yeah. And I’m really thankful he did that because I thought I wouldn’t be where I am today. Yeah, I love being a DBA. It’s a great job.

Carlos:  Now as great as SQL server is, if there was one thing you could change about SQL server, what would it be?

Chuck:  Only one?

Carlos:  We’re going to take your top one today. Maybe the next time you can, we’ll dig deeper.

Chuck:  Yeah right. I think that we need to get licensing in more in tune with modern hardware specs because yeah you know SQL express cost 1 gigabyte a RAM and a 10 gigabyte database. I mean you’re kidding me, and you might just as well say say, “Go to MySQL because we don’t really care about you.”

Steve:  Yes. There are very few environments that SQL express actually is a good thing to you.

Chuck:  It’s useful for right. I mean they can use it even the replication environment which is kind of cool. But I mean my phone’s got more power and capability than SQL express does. And then Standard edition’s kind of the same way. I mean you’re limited to, what’s it, 64 gigs of RAM and you know they say that you can use more with the heap space now with the later editions. But still I mean it is like designed by untechnical marketing people and no reason where these numbers come from. So if we want to limit to one thing that change, I think we,
we’ve got to fix that.

Carlos:  Yes.

Steve:  Okay.

Episode 96: Building Community Tools

Have you ever created something and wanted to share it with others with the hope they will contribute and make it better? Sites like the new defunct codeplex and GitHub are full of functionality people want to share and work with others on; however, many projects remain dormant.  What are the characteristics that create an environment where people want to contribute to your project?  Steve and I discuss a few ideas and we interview two very successful community project leaders in Brent Ozar and Chrissy LeMarie about how they got started and what it takes to put together a good community project.

 Episode Quote

“We don’t know that building community tools is for everyone. . . , but if you have a problem that you’ve solved and you are committed to it, you liked the idea and I’ll say go for it.”

SQL Server in the News

https://azure.microsoft.com/en-us/services/analysis-services/

” open=”off” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]

Episode 95: Polybase

Big data is term we have been hearing frequently as of late and this might cause some concern for us in the SQL Server space.  Microsoft has introduced some new functionality to help connect different data stores with PolyBase.  We are happy to have Kevin Feasel from ChannelAdvisor back with us and  Kevin will discuss some of the basics around what PolyBase does. We’ll be discussing a lot about integrations using PolyBase specifically on Hadoop and Azure SQL Blob Storage. We also touch on some of the technologies that will be supported in the future.

For those looking at implementing both structured and unstructured data stores, PolyBase will be a way to help bring these environments together. Kevin gives us a great overview and we think you will enjoy this episode.

PolyBase

 Episode Quote

“PolyBase gives you this integration and it’s opening this door to possibly getting rid of link servers.”

“PolyBase simplifies that a lot for us by making an assumption that there is a consistent definition for each row.”

“Learn something new… You learn something the first time, you can learn something again.”

Listen to Learn

– What is PolyBase?
– Technologies supported by PolyBase
– PolyBase integration with different data sources
– Some thoughts around which teams are going to own which pieces of the project
– How Hadoop integrators are responding to PolyBase

Kevin on Twitter
Polybase Guide

About Kevin Feasel

Kevin is a database administrator for ChannelAdvisor and the leader of the PASS Chapter in the Raleigh NC area.  Since he was last on the podcast, Kevin has been awarded the Microsoft MVP and will be a speaker at the Compañero Conference.  He also enjoys old German films.

Transcription: Polybase

Kevin: My name is Kevin Feasel, I am a Data platform MVP. I’m also a manager of a predictive analytics team here in Durham North Carolina. I’m extremely pleased to be able to speak at Campanero Con, even though I can’t pronounce it. I’m going to be speaking on a couple of topics one of them is Security and really getting an understanding of that network security, getting an understanding of what a database administrator can do to help secure a SQL server instance. I’m also really looking forward to talk about a big data solution basically how do I get started in that. I’m a database administrator, I’m the only database administrator at this company and somebody is coming to me talking about big data, where do I start? What do I start looking at? What actually is the benefit? What kinds of workloads would work well under this and which ones don’t? And getting some of the ideas of what’s happening in the industry and seeing how this different technologies are evolving and turning into a full ecosystem. Finally, showing how that ecosystem some integrates with SQL server.

Carlos: Kevin, our all-time podcast episode extraordinaire. Welcome back for another episode.

Kevin: Thank you! It’s good to defend the title.

Carlos: Yes, thank you for coming and talking with us. One of the things and one of the reasons we continue to have you on is you’re doing lots of different interesting things, and as database administrators we’ve been hearing this for a little while this idea of big data and it’s kind of been at the door. Lots of people even from a past perspective, they’ve open the doors to analytics to kind of join those two worlds. But for a lot of us it’s still kind of an unknown entity, it’s different technology and we think that we have something here that will kind of save the day if you will in the sense. And so our topic today is PolyBase and we wanted to talk with you about it, you’ve been talking about it, and presenting on it and working with it, so why don’t you give us the tour of PolyBase? What is it and why would we be interested in it?

Kevin: Sure, here’s the nickel tour version. PolyBase initially came about, I believe it was actually first introduced in 2010, so it was part of SQL server parallel data warehouse edition which later became APS otherwise known as Extremely Expensive edition. Enterprise is expensive, PDWAPS extremely expensive, in SQL server 2016 this was brought down to the masses or at least the masses who could afford Enterprise edition. It’s been around for few years but 2016 feels like first version for the rest of us who didn’t have a chance to play with really expensive hardware. The concept of a PolyBase does at a really high level is it allows you to integrate with other data sources, so before people start thinking, “Oh no, it is link servers all over again.”It’s not links servers. It’s not that bad. So as of today PolyBase supports a few different links where you can connect to a Hadoop cluster. We can connect to Azure blob storage and we can use PolyBase to migrate data from Azure blob storage into Azure SQL data warehouse. At PASS Summit 2016 there were a couple of interesting keynotes where they talked about expanding PolyBase beyond Hadoop and Azure blob storage looking into Elasticsearch, MongoDB, Teradata, Oracle and other sources as well.

Carlos: Wow, so basically we’re going to have the ability through a SQL server Management Studio to be able to interact with, move data to and from all of these different systems that you have mentioned?

Kevin: Yes, and be able to query it using just regular T-SQL. So when you create this table you create what’s called an external table. It’s a concept that lives on the source server like the Hadoop cluster. The data is over in Hadoop but when you query that table select star from my external table it’s going to go over, request data from Hadoop cluster, and pull that data back in the SQL server where you can treat it like it just came from a local table.

Carlos: Got you, so now is it going to store that like a time bases so that you know, I run my select star and then 10 minutes later Steve runs his. Is it going to pull that data back over again or there’s some management now that we have to think about because the data is now on my SQL server?

Kevin: So the data doesn’t really get persisted to SQL server. It’s an external table meaning that it will live in the blob storage or on your Hadoop cluster. The mechanic that PolyBase uses to allow this to work is it will pull the data into SQL server into a temp table but it’s not a temp table that you should know about as a developer of a T-SQL query. It’s kind of like behind the scenes temp table that then acts as the table that you’re going to query against, so you query DBO.myexternal table. Behind the scenes there is a secret temp table and it has the form and structure of that external table, data gets pulled in, collected and then processed as though it were local. But once it’s done it’s gone.

Steve: So then that process sounds very similar to sort of underlying workings behind when you run a query over a link server where it issues a command on the other side it brings the results back, it’s basically stores them in a hidden format so you can use them and the rest of the query, locally. And I guess, I mean I’m just trying to understand the correlation there. Is there a big difference on how that’s been done versus the link server?

Kevin: So there is one major difference and that is the concept of predicate push down. So the idea here is let’s say that I have a petabyte of data in this Hadoop cluster and petabyte of data in this folder, I want to be able to query, I’m sending a query that maybe I just want a few thousand rows or I want to aggregate the data in such ways that I don’t get a petabyte back, I just get the few thousand rows I need.

Carlos: Hopefully, because if you’re turning a petabyte of data you’re going to be in trouble.

Kevin: Yeah. I don’t have a petabyte of data on my SQL server instances. So I write this query in my WHERE clause, maybe I do summations, GROUP BY’s, HAVING’s. All of that predicate will get sent to the Hadoop cluster and on the Hadoop cluster side, PolyBase instigates a MapReduce job or set of MapReduce jobs to perform the operations that you wrote in T-SQL. It generates all of the jobs. It creates the data set that comes back and gets pulled into SQL server. So the link server if I were doing a link server to an another SQL instance, well another SQL instance is a special case, but if I were doing it to Oracle, I have to pull the whole data set back or from querying out to Hive I have to pull the whole data set back and then any filters get applied. So predicate push down is what lets you get back the rows that you need, only the rows that you need and gets around that whole links
server problem where, oh yeah I’m querying a billion rows I’ll see you tomorrow.

Steve: Sure, very interesting. I’ve heard some people speculate that link servers are dead or will be going away because of what we can do with PolyBase. Do you think that that’s a fair assessment?

Kevin: I am crossing my fingers hoping that this is so. As soon as they announced at 2016 PASS Summit what PolyBase is going to do in the future, I got really excited because I thought about, “Wait, what if I could connect to another SQL Server instance.” And there is one extra bit of PolyBase that I haven’t talked about yet. That is the concept of head nodes versus compute nodes. This concept in massive parallel processing that you have a head node, this is the orchestrator, this is the server that knows what queries are supposed to come in and out, and then it passes off details to different compute nodes. In Hadoop you have a name node and you have a bunch of data nodes. Over in PolyBase there is actually a similar infrastructure, so there is a head node, that is your SQL Server instance, must be Enterprise edition, and it controls the jobs. But you can to different compute nodes. They call it scale up cluster. These are Standard edition SQL Server instances that I can have sitting there doing work connecting to this Hadoop cluster to the different data nodes on the Hadoop cluster, pulling data back and getting aggregated data back to my head node. So unlike a link server where I have to pull all the data over to my one instance I can now have several PolyBase servers getting data, aggregating it locally, sending it up to the head node, sending up that aggregated as fine as they could data back to the head node where the head node finishes aggregation and presents to the end user the result.

Steve: Very interesting.

Carlos: Yeah, kind of scale out approach. Now I guess at this point it might be worth kind of going back and talking about some of the things that I need to put in place. Now you mentioned kind of this architecture perspective, I have Enterprise version I can have Standard versions but let’s just scale it down a little bit. I just have one node and I want to start using PolyBase. What are some of the things that I need to create or steps that I would take to in order to set that up?

Kevin: Ok, so let’s take the easiest example, that’s connecting to Azure blob storage. On my SQL server instance, I have to install PolyBase. That’s part of the setup; there is a little checkbox you can select. But in order to install PolyBase you must install the Oracle Java Runtime Environment.

Carlos: Yes, I cheated, and I was looking at the docs here and I saw that and I thought, “What in the world!” It’s like sleeping with the enemy, right?

Steve: So just to recap then, if I want to query against Azure blob storage with PolyBase when I install SQL server I need to also install, and again you get this as part of the install, but Oracle components for the Oracle Java Runtime.

Kevin: Correct. So you install those. There is a couple of configuration steps that are involved like there is a setting in SP configure that allows for external queries. Turn all the stuff on. There are configuration guides that can help you with that. Once you’ve got that done what you do is you create three things. The first thing that you want to create is an external data source. So the external data source says, this is what I’m connecting to, this is the resource location. If I’m connecting to Azure blob storage there is a type, actually I think for Azure blob storage you just use a type of Hadoop. If you use a Hadoop cluster you just use a type of Hadoop. If you’re writing in Azure elastic scaling query, there is a different data source type for that. But that’s little bit beyond my kin I haven’t written those yet. Ok, so you create this data source. This data source says, over here is the WASB address, the Azure blob storage location of the folder or file I’m interested in. So, actually let me, I may have to rephrase that because I’m now looking at the, opps. Ok, let me, sorry Julien, going to have to cut this part just a little bit because I just said something wrong. Now, I could just keep going and make it
sound like I’m an idiot. That wouldn’t be the first time admittedly, but.

Carlos: No, that’s fine. We’ll make it right.

Kevin: Ok, so let me start over. So the next thing that you do after you’ve configured PolyBase is you want to create an external data source. For Azure blob storage we create this external data source that points to the WASB address of your blob storage container location. So you’ll point to the container and the account name.

Steve: URL right?

Kevin: Yeah, that is WASB or WASB[s] address. It’s an Azure blob storage location. You’ll include your credentials because if it’s a secure blob you’ll need to pass in credentials. So you create this data source. The next thing you want to do is you create a file, so an external file format. That file format says, any files that are on a source that I specify, any files are going to follow this format. There are a few different formats. One of them is delimited text, so just text maybe colon delimited or semi-colon delimited or however you’ve delimited your text. You can use other formats as well. I would recommend starting out just use delimited text that is easiest to understand. You can grab a file and look at it. But when you start asking about better performance, one of the better formats is ORC, which is row columnar format that high views the store data. So it’s much more efficient for querying especially aggregating data but you can just use flat files.

Carlos: So knuckle-dragging Neanderthal that I am like how am I supposed to choose what kind of file that I need to use. Is there like a, if I’m going to be, I don’t know anything about Hadoop, how would I choose that?

Kevin: Yeah, absolutely, so knuckle-dragger, delimited file. Keep it easy for yourself. Once you get passed that, once you kind of get passed the doorway and you say, ok now how do I get do better? You have to think about whether your data is more of aggregation like what you would find in a warehouse table. In that case, I would use ORC. If I’m storing the data and it’s more of a row store style data I would use Parquet. There are a couple of other formats as well but those are the main two that really supported within PolyBase.

Carlos: Well now, so in that determination, so again I’m going to use the delimited file. I start, I don’t know, three months in, right, I start writing queries. There are processes that I now have in place and I decided, “Hey, I think I can do better. I want to change the format.” Am I going to have to like start redoing my queries or what’s all involved if I wanted to change that format down the line.

Kevin: Great question. What you would have to do? Let’s say you have delimited files now. You’ve created an external file format that’s of delimited type. Later on you say, well I am actually storing this as Parquet, so you create an external file format that’s Parquet. And now we get to the last portion of a PolyBase external table so the table has a two part name. It looks like any other table when you query it dbo. something or maybe external.mytable. You have the column definitions so all of the attributes in your table and at the bottom, the part that’s little a different is there is a WITH clause and inside that WITH clause you specify the location of your data, so those would be the specific file or folder that you want to point to. The data source and the file format.

Carlos: Got it. So when I wanted to do a new, if I wanted to change file formats I’m creating a new external table.

Kevin: Yeah or you just drop and recreate the one that’s there. The external table doesn’t have any data. It just has some metadata around it. So if you have a few second downtime you can drop that table, recreate the table, use the new format, maybe point to a new folder that has the data in a different format. All the nasty work of converting those files getting them into the other format, yeah you still have to do that stuff, but you can do that as a back fill process or you can do that kind of off to the side and just switch when you’re done. That way you don’t have to update any of your procedures or any calling code.

Carlos: Got you, ok, so that’s nice.

Steve: So then when you say the external file doesn’t really have anything more than just for definition there. That’s the definition that sits on your SQL server that’s defining where it’s going to go and get that data for instance out of Azure blob storage. So it’s really just a pointer off to that data and you’re switching it around and if you point it to a different format file you have to give it a format type appropriately.

Kevin: Yeah, so the external table, yeah it’s just metadata. It’s just some basic information.

Steve: Ok, so then with that it’s pointing to a file in Azure blob storage and can you just start out with an empty file and then start filling in with data from there or does that file in Azure blob storage have to have been created somewhere else to meet those formats?

Kevin: That’s a really good question. So you have the ability to insert data into blob storage or into Hadoop. There is another configuration option you have to turn on to allow for inserting and once you do each insert operation you do will create some number of files in blob storage or in Hadoop. So you have to have a folder as your right location. But every time you insert maybe you’re inserting once a month, you’re taking last month’s financial data, all the individual transactions and you’re writing it over to blob storage for long term storage. That insert generates 8 files over in Azure blob storage and then the data is there. You can query it just like it was always there. But you cannot update that data from PolyBase, you cannot delete that data from PolyBase.

Carlos: Interesting, so now obviously it’s going to vary from place to place but for me a setup perspective let’s say, right, so again I’m the only database administrator in my organization or I’m not familiar with Hadoop or these other. Well, I guess when the other databases get on boarded then there will be more access, right? But when I think from a big data perspective generally there’s going to be another team, maybe a vendor comes in, installs Hadoop, starts loading data, things like that. What are we as database administrators, were going to create all of those components that you just talked about, are the Hadoop vendors familiar with PolyBase? Are we talking the same language here or is this still something kind of a very SQL server centric idea? Does that make sense?

Kevin: I would say that vendors, they’re not really going to know a lot of the PolyBase details. They’re probably not going to be familiar enough with PolyBase itself to do it. I’ve had some discussion with people who worked at Hadoop vendors and they’re very interested in the concept but there is not a lot of internalized information around there. These are typically not people who spend a lot of time in SQL server, with SQL server so they don’t necessarily know how it works, how to set it up, what the positive and negative aspect are, how you can shoot yourself in the
foot.

Carlos: Well, so speaking of that so what are the ways we can shoot ourselves in the foot?

Kevin: Oh, you have to go and ask that. There are some assumptions that are built into the way that PolyBase works today. This is not a critique of the PolyBase team, of the developers, of the PMs. This is not at all a critique aimed at them. I like you guys still, don’t worry. One issue that you can run into is let’s say you have just text data and your file has new lines in it but the new lines don’t represent new lines of data. Maybe it’s a free form text field where a person typed in new lines to symbolize a new paragraph. Well, PolyBase doesn’t understand this idea of ignore new lines unless I told you that it’s a new line. It will just pick up that new line and say, oh yeah you got a new line here.

Carlos: A new record basically.

Kevin: Right. There are some assumptions that are built in. You can also burn yourself by defining your result set so you create that external table and maybe you define a value as an integer. Well, if the value comes back as a string because some of the data is malformed coming in then those rows will be rejected as they should. So you’re going from a non-structured or a semi-structured system into a very structured system in SQL server. That semi-structured system is ok with you throwing whatever garbage you want into this file but you have to define structure when you pull this out. Historically, on the Hadoop side that structure was defined in the mapping in the reduction phases, so MapReduce. It was defined by the developer putting together the data in such a way that the developer understood what this data point signifies. PolyBase simplifies that a lot for us by making an assumption that there is a consistent definition for each row, so we say an integer age is the first value. Well, it’s going to assume that there is an integer value there and it’s going to populate age with that. If maybe every 20th row we have something that’s totally different. Maybe instead of age it is eye color because something weird happened with our data. Well, every 20th row gets rejected. The way you can shoot yourself in the foot, let’s go back to you have a few billion rows of data that you want to pull over. Maybe you want to get just everywhere were the person is exactly 14 years of age. So you’re scanning through this data and every 20th row instead of it being integer age it’s actually a string. Every one of those rows gets rejected. There is a cutoff for the number of records that you are allowed to reject before just failing a query. That cutoff can be 0 or it can be as many as you want. It can be percentage or a numeric value. So let’s say 1 billion rows and you have a cutoff of 5,000. You’re going to go through quite a few records to get 5,000 rejected rows. Once it’s done, once rejection happens, once failure occurs the entire transactions roll back and you don’t get to see the data that was there already. It’s roll back. There was an error.

Carlos: Oh, got you, that’s right, yeah.

Kevin: So you may be sitting there for an hour waiting for this data to process and it comes back and it fails.

Carlos: Yes, so you might almost think about in a sense, again not try to discount Hadoop. At least in my mind a knuckle-dragger that I am, I think about that almost like an Excel file, right. I want to load that into something that it can accept it and then let me take care of finalizing any of that and look rejected rows and things like that. Almost like an ETL process, right?

Kevin: Sure. This is a fairly common pattern in the Hadoop ecosystem as well where; ok we have a raw data coming in. It’s there we put it into the data lake. So ideally the data lake has a lot of nice clean data in reality it’s more like a data swamp. It’s where you throw in a bunch of old stuff. You got mattresses in there, just all kinds of dirtiness.

Carlos: Fish with three eyes.

Kevin: Yeah, exactly. And so you pull that stuff out and you try to clean it up in some
process. Usually it’s going to be a Hadoop process. Maybe that’s a spark job, MapRecuce job that scrubs this data, tries to give it some symbol of sense and then writes it out to another directory where it’s more of a structured format. In that way you can read it in Hive which is SQL for Hadoop. You can read it with SparkSQL, SQL for Spark, or you could read it with PolyBase, SQL for SQL.

Carlos:  Got you, so that kind of almost goes back or takes me back to that idea again of, kind of that who’s working with who type idea, and it almost sounds like if we wanted to we could push some of that to like hey guys can we work on this MapReduce. Is that a fair question to say, hey can we work on this that when the data comes back it gets cleansed before I see it? Or is that still kind of, you know, I need to as a SQL server person assume all responsibility for that kind of thing?

Kevin:  I think that depends on your environment. It depends on relative levels of familiarity. But personally my expectation would be that if you are say using SQL server as the engine to see final results, then I believe that it makes perfect sense to ask the people on the Hadoop side, “Hey guys give me the data in a format that I can pull it easily.” So for example, maybe we are reading a lot of data coming in from IoT devices. We have Kafka setup. Kafka’s a big distributed message broker. It’s a really fascinating thing and we’re getting tremendous numbers of messages that are streaming in to our Hadoop cluster. We’re cleaning up those results, we’re storing the results and maybe we have some aggregations that we’re doing to show hourly results by device type. And then load that data in to a file that PolyBase can read. As part of an ETL process you may pull that data over the SQL server, Persistent SQL server. So query like SELECT FROM your table INSERT into the real SQL server table, and you’re keeping a smaller streamlined data set that you can use to populate a PowerBI Grid or that you can use to populate web application. In that scenario, personally I’d argue that yeah the Hadoop side people probably should be doing most of the cleanup work. If you are both sides, it becomes more a question of well what am I more comfortable doing, like sometimes if the data’s relatively clean to begin with, or if we’re willing to accept a certain level of failure, take it, bring it to the SQL server, I can do really cool things in SQL server.

Carlos:  So it kind of goes back right to the adage of knowing your data, right?

Kevin:  Absolutely.

Carlos:  Being familiar with it and then making a decision based on that.

Steve:  So then back to that example with the age and putting that into integer column in the table definition, do you see that, I mean, there’s lots of things that could be valid for ages in there. You could have 6 mo. to represent someone who’s six months old but then obviously when that gets pulled down and try to go into integer, it’s got text data in there and it’s not going to work. So do you find that people sort of shy away from those restrictive types in their table definitions or maybe just leave it as something that’s more open like a varchar max or something like that? Or do find that people go through the battle of cleaning it up or filtering it ahead of time?

Kevin:  Unfortunately, probably more the former. It’s more of, well it’s a string, every string works so we will pull that in as a string and then we’ll clean it up here. That is a downside where with a lot of ETL, through ETL tools I can take a data element, I can make decisions based off of what that element looks like, like 6 mo. I can do a substring, I can parse out, is there a MO or YR or some known value here, and use conditional logic to convert that into something that is consistent across the board. PolyBase isn’t going to give you that. It’s going to give you the easy way of pulling data but yeah that, it means, it doesn’t do the transformations for you.

Steve:  Okay. So another area that I’ve thought a little bit about is that and I know sort of jumping back to the whole link server example is that when you’re running a query in sort of old school link server, whatever’s going on in the other side really gets hidden from execution plans. It’s just blindly calling something on the other side across the link server and your execution plan doesn’t give you any details other than it was waiting on something on the other side. Now, is there an option for seeing execution plans when you’re using PolyBase to get a better understanding of if a query’s taking some time, maybe where that’s time is being taken on when it’s connecting out to Hadoop for Azure blob storage?

Kevin:  Yeah. The short answer is yes. The long answer is yes if you look at the XML. So you look at the query plan XML, it will give you some details including there’s a remote query which is XML inside of the XML. So you have to deserialize the XML, decode the XML, and you’ll be able to see what the remote operation looks like. So it gives you a few indicators of what’s happening. It’ll show you the individual steps. Also, there are several dynamic management views that are exposed for PolyBase. And those DMVs will show you a lot of the same information. They’ll show you the individual steps that occur for this MapReduce process or for the data retrieval process.

Carlos:  So I think very interesting topic and we’ll let you give last thoughts here but one of the things that I feel, that I’m confident about or happy about is that while there’s still some unknowns here, right? Having the Hadoop, you know, in my environment or being able to connect to it, Azure blob storage, all these other things that are coming down  the pipe, at least it’s going to be, I have a tool that I can do or integrate with some of these things on my own turf. And it’s not completely foreign that I have to go and, you know, pickup new technologies right away.

Kevin:  Yes. That’s how I’m thinking of it. This is why I like it so much. This is why, honest I think this was the best feature in SQL Server 2016. A lot of people are going to say query store is the best feature. Query Store is an awesome feature but PolyBase gives you this integration and it’s opening this door to possibly getting rid of link servers. It’s opening a door to distributing queries, distributing those really expensive SQL server queries. Kind of like what you do in Azure SQL data warehouse, hoping that maybe we get something like that locally.

Steve:  So I know you talked about how PolyBase is perhaps one of the best features in SQL server 2016. I know that SQL Server 2017 community technology preview too I believe just came out recently. And is there anything that’s in there new with PolyBase that you know about?

Kevin:  Nothing new with PolyBase.

Carlos: Got you.

Steve:  Okay.

Kevin:  There’s a whole bunch of really cool stuff I’m excited about but.

Carlos: The fair question to think or assume but it will be supported in Linux version as well.

Carlos:  Because it’s a core feature if you will, I know they’ve been working and talking with Travis, the PM over there for the Linux migration. That’s what they’ve been trying to accommodate. Again, listening to the AMP conference or event or whatever it was called. They did mention some additional
functionality that would be in the Linux version. I don’t remember them specifically calling up PolyBase but, you know, I had to imagine that it will be there even if it’s not there on day one.

Kevin:  The answer that I think is safe to give is in today’s CTP, CTP 2 for SQL on Linux, there is not PolyBase support but there is no reason that PolyBase cannot be there.

Carlos:  Got you. There you go. But again well we did mention that this ultimately Enterprise only feature, right?

Kevin:  Yeah, for the head node it has to be Enterprise edition. I think even with the SQL Server 2016 SP1, I think it still required to be Enterprise edition for the head node.

Carlos:  Okay, got you. Yeah, I feel like that PolyBase was in the list of things that they made available in the lower editions but I’m not sure if that includes the head node or not.

Kevin:  Yeah, I know that the compute node was available in Standard edition but I’m not sure.

Steve:  Yep. So given that it’s been a little while since 2016 came out, around a year roughly, and with PolyBase sort of been mainstream available since then, do you see that a lot of people are actually adopting this and using it in production environments or do you see more people just sort of experimenting and trying things out at this point?

Kevin:  It’s more of experimentation. I don’t know of many companies that are doing it. The way that I would put it is okay well you have to have SQL server 2016 which already cuts out large slice with companies. You have to have Enterprise edition and you have to have Hadoop cluster or you could use Azure Blob Storage and get value out of that way, but this is going to be a fairly narrow segment of the population even today.

Carlos:  Got you. Yeah, make sense.

Steve:  Well perhaps after this podcast more people will give it a check.

Kevin:  Yeah, I hope so.

Carlos:  That’s right. Compañeros if you are using PolyBase after what you’ve heard here today, I want to know about it. We’re going to report that to Microsoft. Let them know you heard it here first folks. Okay, so I know you’ve been on the show here before, Kevin, but we’re going to still go through SQL family.

Kevin:  Excellent.

Carlos:  Can we do it?

Kevin:  I think so. I may make up new answers.

Carlos:  Well would you have a couple of new questions that I think that have changed since last time you’re an individual guest so.

Carlos:  Okay. So the first question is how did you get started with SQL server?

Kevin:  I got started as a Web Developer. It was about a decade ago and I was an ASP.NET web forms developer. It was my first real job, so I was the person who was least afraid of databases. I’ve written SQL queries before and we had a need for database administration so I.

Carlos:  How hard could it be?

Kevin:  Yeah, pretty much. Like hey why is the server failing? Oh it’s ’cause it’s not a disk space.

Steve: Alright.

Carlos:  There you go, and now you know the rest of the story.

Steve:  So if you could change one thing about SQL server, what would it be?

Kevin:  That’s a good question because everything that I think of tends to happen which is really cool, I like that. So last time around I said I want PolyBase to support Spark, and I’d like to see that happen still. I’ve wanted Python support for machine learning within R services which is now machine learning services. And we just got that so that’s really cool. The thing that I want most right now is a really good client for Linux. So I want Management Studio for Linux or something Management Studio ask for Linux that does maybe like 70% of what SSMS does.

Carlos:  Interesting. In all flavors of Linux or do you have a particular flavor that you’re interested in?

Kevin:  I’m kind of okay with pretty much any flavor. I mean you can get it to work. Nowadays, I use Ubuntu or Elementary a lot. Previously I’ve done a lot of Redhat. I go back to Mandrake for people in the know.

Steve:  Right. Yeah, I know recently we heard that, what was it SQL command, was going to be available on the Mac and that was a big move. And I think we’re a long ways off from Management Studio being on other platforms. But who knows, I
could be wrong there.

Kevin:  Yeah. I’m looking forward to whatever they are able to provide.

Steve:  No, I know that’d be certainly cool.

Carlos:  Although, and we do have request into the PM for SQL Server Management Studio. We haven’t quite been able to get them on the show just yet, but when we do we’ll ask them that question.

Kevin:  Put them on the spot.

Carlos:  That’s right. Okay, so best piece of career advice you’ve received.

Kevin:  I’m going to flip this on its head, best career advice I can give.

Carlos:  Well, here we go.

Kevin: Learn something new. Especially if you’re in a shop where you’re on SQL server 2005, take some more of your own time. Learn something new. It doesn’t matter that much what it is but expand out just a little bit. It could be features, it could be new versions of SQL server, it could be let’s learn a new language, let’s learn a new part of the stack. But don’t get caught in this one little part that you find out someday oh look your job has been animated away and you lost all of those skills to learn. You learn something the first time, you can learn something again. So that would be my advice.

Carlos:  And that is why we’re going to have you as a speaker at the Companero Conference. So folks if you want to hang out more with Kevin and learn all of his wisdom, you can come to the conference and hang out with us.

Kevin: Wisdom and $5 gets you a cup of coffee.

Steve:  And on to our last SQL family question, if you could have one superhero power, what would it be and why would you want it?

Kevin:  We’re getting close to episode 100. Nobody else has ever answered that this way. I want phase walking. I want the shadow cat kitty pride be able to phase through walls, phase through objects. Nobody else has answered that so either I’m completely insane and picking the wrong power or I’m the head of the curve. I’ll let the audience decide.

Steve:  Or it could be you’ve just answered the question several times before as well and you’ve had more time to think about it too.

Kevin:  That is also possible.

Steve:  Alright, very good.

Carlos:  Awesome, Kevin. Thanks again for stopping by. We always enjoy it.

Kevin:  I’m glad to come here.

Episode 94: Qlik Technologies

There are lots of reporting options and I watch SQL Server move up the Gartner magic quantrant, I saw another reporting tool moving up as well–Qlik.  In this episode we will start by sharing information about Qlik, what it is and some background in the event it gets adopted at your company. Today we are delighted to have Michael Armentrout as our guest. Michael is a Microsoft SQL Server DBA as well as the developer of QlikView and we discuss the fundamentals of QlikView such as the associative model, in-memory, compression and sharing among others. Also we will hear from Michael the difference between QlikView and QlikSense, and some of the “competitors”.

Michael share some of his thoughts on using the technology and how it is different from the traditional Microsoft stack.  We think you will find it interesting.

Episode Quote

“I think a lot of others are starting to inch towards that in-memory model…it’s a new silver bullet.”

“The biggest thing is understanding of the data obviously.”

“‘No one will care about your career more than you, so it’s up to you to advance your career to whatever level you want it to be.”

Listen to Learn

– Qlik technologies/products
– QlikView functionalities
– Qlik products and their associative engine
– Differences between QlikView and QlikSense
– How they differ from the Microsoft Stack

Michael on LinkedIn
Michael on Twitter

SQL Server in the News

Azure total cost of ownership calculator – http://tco.microsoft.com/
Testing on SQL Server 2017 CTP2

About Michael Armentrout

Michael S. Armentrout is a 17 year Sybase/Microsoft SQLServer DBA working in the legal, healthcare and financial industries including versions SQLServer 6.5 – 2016. The past few years I have focused, primarily, on Qlik development, while providing DBA support for a healthcare provider. Currently I on contract with Summit Healthcare, in Chambersburg, PA, providing QlikView services as well as DBA services. In my downtime I enjoy my wife, my four kids and playing guitar.

Transcription: Qlik Technologies

Carlos: Michael, welcome to the program!

Michael: Thank you! It’s an honour to be here.

Carlos: Thanks for coming on the show today and we’re hoping that you can give us some insight into this new technology that I’ve been seeing a lot about and that I’m actually curious about its name. Steven and I, we’re kind of talking about this and we were like quilk, click, click, clock. Tell us how you pronounce it.

Michael: It’s pronounced “click”.

Steve: Wow! That’s so easy to say that way compared to what we’re trying to do.

Michael: Actually I’ve seen it spelled many ways so…

Carlos: So ultimately the interest and I think one of the reasons we wanted to have you on, we’ve been talking a little bit about reporting options and we like to try to get around the edges if you will of the SQL server community from time to time. Just to talk about what others are seeing and what they might be experiencing. Last year as Microsoft, Eclipse, Oracle, on the Gartner Magic Quadrant, you could see where other technologies were in relation to that and so they continue to publish this out and one that I saw that was moving up the ranks was this Qlik Technology. And of course been talking with some folks, you being among them, that are listeners of the show but and then also users of this product, I thought, oh, ok, we should probably have the conversation because if people were being exposed to it, it’d be nice to at least talk about the what’s and the why’s and how that it might play into their environments. So I guess give us the nickel tour of what is Qlik and QlikView, give us the nickel tour.

Michael: Qlik, the company, was founded in 1993 in Sweden and the creators wanted to create software that mimics the way humans intuitively think which is through associations. So in 1996 they released the first version of QlikView.

Carlos: Is this a reporting software or…

Michael: It is a. So it’s ETL along with storage or EW and then producing UI/UX on the backend. So you don’t need an SSIS although you can leverage them but it’s self-contained.

Carlos: Interesting. So then it would connect directly to your SQL server box and then just kind of take care of the rest?

Michael: Correct. I mean there’s a way to load data, and it’s pretty much load column names from SQL and then a select statements. And it’s basic level.

Carlos: So tell us how you got started with it. I mean you were using some SQL server tools if you will. We talked a little about SSRS and whatnot. You mentioned SSIS. So how does an organization or maybe take us a little bit of history, how did you decide or what was the thought process like, hey, we should be using Qlik.

Michael: Originally, the company I’m at wanted to go the Microsoft BI Stack route. And then were very rural area so they learned pretty quickly that we’re not going to be able to staff up to leverage Microsoft. So we worked with another tender. We’ve had another product that worked with Meditech, our EMR, and we’ve developed an app for about a year and a half and it just didn’t pan out, it wasn’t robust enough. So they went back to drawn board and brought Qlik View back in or brought it in, excuse me. They were considered originally but…

Carlos: Didn’t make a cut, another, getting a second look.

Michael: Correct.

Steve: So then, what type of things are you doing with QlikView or where’re you doing with QlikView in an environment?

Michael: Ton of clinical reporting but I guess now might be a time to sort of, pull back to covers, and maybe explain why the Qlik Technologies, what their advantage is. So they have an associative engine. So as it calls data in, it compresses it with
cardinality so once you like 47500 zip codes, 43000 zip codes, I believe, in the US. So if you have five million customers, you don’t store five million zip codes. You store for 43000 zip codes. Now if you have male and female, you store two values for that. Just got an example, we have an app that calls in the last 30 days of nursing orders. So in SQL Server that’s 557 MB. But when we call that in in Qlik View, it’s 27 MB. That’s same information.

Carlos: Interesting. There’s some compression going on.

Michael: Massive compression. And then both Qlik products, QlikView and QlikSense, we can get on the differences later, are all in memory.

Carlos: Got you. Interesting. We’ve seen some of that played out here as some features in the SQL Server realm. So this is interesting, QlikView may be one of those helping pushing Microsoft in that direction. Almost sounds like.

Michael: Correct. And I think a lot of others are starting to inch towards that in-memory model.

Carlos: Sure. Sure. It’s just so sexy, right. It’s in-memory, right.

Michael: It’s a new silver bullet.

Carlos: That’s right. That’s right.

Steve: So then with that, once you’ve got all that data in memory, and then earlier you mentioned building UI/UX, are you then using that to build just the reporting UI/UX or you actually building applications that can change and manipulate data?

Michael: Good question. So, the QlikView, the Qlik Products are sort of read-only. So you pull from your source and then ultimately the biggest, the thing that makes everything else easier is your data model which you shoot forward as a start schema. So once you bring that data in through your ETL and you manipulate it, you can do it in the frontend or on the ETL loads and there’s places to do that, and you bring it into a start schema. And so Qlik’s model is everything, is associated. Each data piece is associated with every other piece of data. So your tables, you have one column that they join on your primary key and it could only be one column. So then through the visual you will be able to see associations. So Qlik is known for their green, white, gray color scheme. So if you select a value in say, in the UI, and it’s green, that’s the slack value. So you might choose all males. And so you’ll have, on your other screens, you’ll have values that are white which means they are associated, and you’ll have values that are gray which means they are not associated. So it’s an associative data model.

Carlos: Just take an example a little bit further just to make sure that I’m understanding. Again, we’re talking about a medical facility here. So I’m kind of querying the data and I’m looking for patterns basically, right? So I want to know the males that have broken legs, just use as an example, or what other things might those people experience, and then it’s going to show that to me without having to build the report or build all the logic to show that data?

Michael: Correct. Absolute correct. So I just, I mean, I love SQL Server so what I’m about to say is not necessary a knock on it, it’s just a comparison. So query-based tools like, for example, Cubes, you sort to have a, you have to pre-build everything. You have to know what questions users going to ask, and you have to build answers to it. If they change something, you have to rebuild the cube, they have to wait a day, two days, whatever might be. So with query-based tools, you have pre-defined joins, pre-aggregated hierarchies, and its only part of the story. If you forget to add a column into your query or into your results or your cube or whatnot, and you’ve lost it until someone recognizes that and you go back.

Carlos: Right. You know, I think you bring up a very interesting point. And one that actually people have asked us to talked about and that is how the relational, the database landscape, if you will, in general, is just changing. And you have these
other database technologies that are coming up to help solve some of those exact problems. Because of the team of people, not generally, that have data warehousing experience, they put that all together, so to have tools that make it easier like I don’t need to be a data warehouse expert to get in there and start playing around my data is very attractive.

Michael: Yes. And that’s sort of the selling point and then whenever we get in the differences between the QlikView and QlikSense, what audience each of those serves.

Carlos: Sure. Yeah. So let’s get into that now.

Michael: Ok. Sure. So, QlikView is what they call, is what they term guided analytics. So you sort of, you build the UI and that could be scatter plots, bar charts, pie charts, pivot tables, there’s lots of objects within QlikView that you can build which is sort of guide the user. We kind of nudge them along and then tell them what you expose, they could go, “Oh, I didn’t know that males with broken arms, they all get some certain medicine or most of them get a certain medicine.” So unless you’re asking that question beforehand, you won’t know that in SQL.

Carlos: Right, got you.

Michael: So Qlik exposes those, QlikView exposes those through the UI.

Carlos: And then QlikSense?

Michael: QlikSense is what’s called, it’s their newest product, they’re morphing they’re web-based, cloud, it is what’s called self-service analytics.

Carlos: Aha, there’s another buzz term.

Michael: Yes. So in QlikSense, I mean, the syntax is, you could take QlikView syntax, drop it into a QlikSense application and it’ll work the same. You have a different sort of lay-out and constraints in your UI but ultimately what people would do, would build, is what’s called a master library of items. And these are, this’s when data governance comes in to. If the organization decides this is the definition of length of stay of point X to point B, or A to B, then they might build that and then users on the back end could theoretically drop that unto a chart that they want as a measure or as a dimension or whatever or how they want to use it. And then it ultimately ends up being self-service.

Carlos: Got you. Now, you’ve thrown out terms: dimensions, columns and joins and setting kind of some of the stuff, I guess take us through some of that and well, it’s been a while since you’ve used SSRS, you mentioned it in our, as we were getting ready to go live here. What are some of the hurdles potentially, or maybe some of the differences that you’ve seen in, what’s ramp up is more my question, to start using a tool like this?

Michael: The ramp up is, the biggest thing is understanding of the data obviously.

Carlos: So that doesn’t go away?

Michael: No, not at all. You have to understand the data and then for DBAs, for SQL people what was really hard for me was I wanted because it loads columns from SQL statement. And I wanted to write this big SQL statements and they could tell me just suck it all in and work it within QlikView, it’s much easier. And I’ve learned, they’re actually correct, it’s much easier. So just bring in all the tables you need and then you have to model it, you might do some clean up. Ultimately, I’m thinking appear some less. Everything is in one environment. You don’t have to go to multiple environments or use sort of multiple tools. I can have an SSIS package write on some table and your data warehouse that SSRS pulls from. You could theoretically do it all in one product. A lot of companies I guess are using this as sort of replacement for data warehouse. You could use Qlik as a replacement for data warehouse.

Carlos: Right, again, that self service model, just suck the data in let the tool figure it out and do some of those hard things for you; totally understandable.

Steve: So then with that if you’ve got, I mean there’s a listener out there who’s a DBA, or BI developer, or someone who wants to try it out for the first time. Is there a developer or trial edition or something like that that they could try out as a proof of concept?

Michael: Yes. Everyone can go to qlik.com and can download QlikView or QlikSense and it’s a fully functional, no limitations product. What you can’t do is I can’t create an application and send it to you Steve or Carlos and you guys open it up on yours and have it to be working, so that the limitation. But on your desktop it’s fully functional.

Steve: Ok, interesting. So then let’s just explore that comment you made about if you create an application and send it. So if you build an application in the paid version or the full featured version, you create an application that something you want to share with Carlos and I for instance, and would that application then contain a copy of all the data at the point you built it and you ship it off to us or would it be something where we would have to be on the same network with access to the SQL server and just be querying the data as it’s needed?

Michael: Good question. Again, we get back to both products, so I’m not aware of if I create a QlikView application I can share it with my organization because I put on the publisher server and then we have security and then it can be shared, or it can be accessed to any groups whatever your permissions are. I’m not aware of being able to share that with you. There might be some external phasing option but I’m not aware of it.

Carlos: So you think that should be a kind of shared repository, right, because even though the data is “in-memory” it could probably just bundled that up.

Steve: So when you talk about sharing then it’s really internal sharing inside of your organization?

Michael: With QlikView it is internal. With QlikSense they have QlikSense cloud and they have a business model which is more from enterprise side. So if I create an app in QlikSense I can upload it to my QlikSense cloud and I could invite you and Carlos to view it or to utilize it and you could, if you have QlikSense on your desktops or had an account on QlikSense cloud you could use it.

Steve: Ok, so then do you see Power BI being a direct competitor of this sort of they do different things?

Michael: They are competitor. They end up at a similar point just in a different way and I’m not super versed in Power BI. That’s one of the things on my list sort of like Tableau which is the other competitor. So I need to spend some time and just understand the differences so I’m not super versed in the Power BI stuff.

Carlos: Well, I think that answers your question. If Tableau is a competitor then Power BI is definitely the mix.

Michael: Absolutely, oh yes.

Carlos:I think there are different comparisons from Tableau to Power BI, probably with that strength, the association strength and again also just coming from Power BI guru but while it definitely suck in that data and you can play with it. We’ve talk with some of the folks about doing like mobile reporting you definitely want to limit some of that data it sounds like QlikView is a little but more robust in that sense. But yeah, I mean I would think that it’s kind of what tool other people want to get comfortable with and does kind of suit their needs.

Michael: Right, and it ultimately boils down to a culture is what I found if you have a, do I say, younger culture that is open to different delivery methods then it’s more accepted I think. I think when you have an older culture they go, “Wow, that’s really cool. Can you email me that every morning?” “Yes, we can.” But that’s not the point of it. The point is for you to go and you discover on your own to click around, no pun intended, but click around and make the associations and discover things.

Carlos: Got you. So that’s an interesting little tidbit you brought up. So you can schedule reports and kind of send snapshots if you will of, “Hey, here’s your data.”

Michael: Yes. They have a product called Inprinting and they purchased it probably about a
year ago and then having incorporated it. So that’s where you schedule, so you might have an entire application that may have 50 objects in it and you might schedule everyday at 8:00 AM at least five people get these three objects emailed to them. So there’s that functionality that’s built in to the products.

Steve: Okay, so on your comparison to Tableau you mentioned earlier, I know one of the things I’ve seen with Tableau is it’s often driven from the sort of a business side of an organization rather than the IT side of the organization, and do you see the same thing happening with Qlik where it would be somebody purchase it to analyze your data and then it sort of ends up in IT’s lap later?

Michael: In my n equals one experience, we say hey we have this tool that can do all these great things here are all the bells and muscles, and I go yes we want that and we want to do an application around, say sepsis. So then it comes back to IT, so in the Qlik view model, it all comes back I guess to IT, for your developers to create the solution. whereas they’re moving towards the QlikSense model, which is self-service. So still IT’s involved and we’re creating the master items that, you know there might be 50, could be 200, but users can then go in and drag and drop to the various bar charts or pie charts, scatter plots, whatever it might be and then we have predefined, agreed upon governed measures. So it’s an iterative process and it takes time to build a library like that, but that’s the ultimate goal.

Carlos: Give us a quick overview of the architecture. We’ve talked about it a little bit, right? Just from our components perspective, does it, do I have a Qlik view server and I connect to my SQL server and then it does the rest of it so like the matching of the data and the UI components. Give us an overview of that architecture.

Michael: Ok, so what I would do on my local PC, I would connect my data sources and I would bring the data in locally. I would develop a full application so the UI and that would be the modelling, and the ETL, the modelling, the cleanup, the UI, everything. And then what I would do is I would then pour that application to the publishing server and that exposes it out on our internal network and then those folks that have the correct AD credentials can see it, so that’s sort of security model, one of the security models with it. In QlikSense we have not purchased on enterprise level and I imagine it’s a similar concept. One of the things with QlikView is if I expose a QlikView application the end users can only consume it but that’s only they can do. Now in QlikSense there is the ability to basically download my application and they could create their own tabs or dashboards or what not. Sort of two products, the old school and the new school and they’re sort of morphing towards each other.

Carlos: Last question here, from a job perspective or a demand perspective, you mentioned you actually transitioning as we record, as well as your transitioning but it comes out you will be at your new location, you’ve put in a couple of years into learning this and you mentioned that you’re small mid-western town, are there opportunities or you see, when you decided to make the transition here, are you seeing opportunities you think this is still kind of a growth area?

Michael: It’s still a growth area but there are a lot of opportunities and I only said that based on the emails I received and it’s a lot of contract opportunities and then there, right exactly that’s where people reach out, so there is based on my emails there’s demand or normally QlikSense because that’s kind of what folks are pushing nowadays because it’s a new product. And one of the difference is where I mentioned earlier was QlikView you kind of build it and you design it for a resolution and it can be consumed on a mobile device but QlikSense is all HTML 5 and so I build an app and it renders in sizes whether on my laptop or on a iPhone,
on a iPad, doesn’t matter it’s all that.

Carlos: Yeah, it’s responsive?

Michael: Yes and so what Qlik has done is they build a base product which I keep brooding upon but they expose their API’s and so now there is a market growing for folks creating extensions. There’s certain kind of a chart that maybe QlikSense doesn’t come without a box, there’s a big market, boutique market if you will springing at creating different extensions.

Carlos: Interesting, now we should know and again, obviously you’re moving to a new place we can’t speak to that necessarily just yet but at least in your current experience and some of the discussions you’ve had as a Qlik developer it sounds like very similar to the SQL side while you can have some reporting experience there is still some administration components associated with it or I guess I should say in essence the database administration and the reporting are still kind of under the same umbrella or responsibility. Is that fair?

Michael: In my case, yes, because we’re a small shop so there are multiple hats. But I can’t say if that’s the norm. I’ve said there are larger shops that obviously might have a more definitive separation of duties. We’re like, “Okay, here, you know we love you and then SQL server here, DB data reader there, suck it all in and you want that into your app because you store those QVDs or data files compress on your sever not mine.

Steve: So from the perspective of the DBA who’s responsible for that server that all the data that is being pulled from are there any gotchas or anything that they should really be aware of as they’re allowing access to that data to be used through Qlik?

Michael: Yes and we learned this the hard way. So being both the DBA and the QlikView developer, we had a lot of projects we’re doing Agile methodology and so we’re relatively quickly cranking our applications, so everyone or the two of us in a vacuum created our own queries back to database. So what DBA’s should be aware of is the same queries coming back, so both of us might be pulling the last 30 days of orders which is bad in the sense of rather than doing a full sort of data warehouse model where every night there is a separate job that pulls in the changes in the last 24 hours and then we’re hitting that to pull our information. So that’s one thing I would sort of caution or with DBAs is to get that sort of governance and make sure it’s only pulling once.

Steve: So does that means that the different developers just have to sort out the data that’s being pulled in using the same data that’s there rather than writing their own queries to get it? Or is it more complex for the Qlik developer than that?

Michael: it really depends, so there’s nothing stopping each developer from pulling their own set of data in a perfect world you would have a governed set of data. So for example you might all agree that, okay here are the 30 fields that constitute patient data of any patient and so every night we’re going to go out and pull the changes into this one QVD which is our QlikView data file and then, “Hey, developers you’re going to write your applications against these QVD’s. You’re not going to hit the server, the SQL server 24 times, 20 times you’re all going to pull from this agreed upon governed set of data.” Didn’t go either way like currently we’re all just pulling what we need when we need it and it took contention at times.

Steve: Alright good stuff there.

Carlos: Alright, cool, I guess last words on Qlik.

Michael: I would say just like to give an example sort of that DBA’s might able to relate to. So you can create an app that goes out and pulls backup file and back up set, let say you look at the average size of the back up so what not. Well, back to our modelling so when you bring those two tables in they have about six or seven columns in common so as a QlikView developer what I would do is I would go in and rename the columns in one table so there’s one column that they have in common that’s sort of a key to this all, so like your back up set ID. So we do that and then I have unique data that’s joined by one column and then I start creating my UI pieces, so for example I have a bar chart, so what’s the average back up size for every database and creates a bar chart. Now, I can create what are called list boxes which is just a unique list of values, so for example database, gives me all the unique database names and then in our case there might be different compatibility modes. So I’ll have a list box for compatibility modes and so through the GUI or through the application I might say okay list all the databases descending and the upper size but how many are 100 compatibility level, so once I click 100 I can visually see all two of my database on this server or three of them are at compatibility level 100. So the other ones which are grey which means they are not associated, well they are not at that level, so even I may not have known all that got different compatibly levels but through a tool like everything is associated, again green, associated white and then grey is not associated. I can visually see that might lead me down to another path to ask more questions. That’s just one example that I tried created the sequel space that DBAs might be familiar with.

Steve: Yeah, I think that’s a great example on something that I’m familiar with and it makes total sense to that point. But do you find that DBA actually end up using Qlik to do that type of work or is that just more of a theoretical type example?

Michael: It’s probably more theoretical, I again n equals one unfortunately is my sample pull, so I use it for things like that just to kind of okay I’m going to quickly throw together these 2,3,4,5 tables, and I’m going to look for something, and then nine times out of ten I’m looking for something, I discover something else. So in the example I gave you another list box could be what type of backup was it, was it date or a log and I may click a database to see what it’s average backup size is and the L may turn gray and that tells me there’s been no log backups on this. Now there are other methods to figure that out but just you know you can leverage visually and go, “Oh wow I didn’t know that” then you take some action.

Carlos: I think what’s cool about that is and again we all have our preferred ways to go about that but if you have that skill and the tools there it will be very easy to go around and start like, “Let me start asking some of these questions that maybe I wouldn’t be asking because I have to go figure out what a column name is”, or whatever. I could just go in and start picking at it without having to read a lot of documentation or figure that out afterwards once I start seeing some correlations or whatever.

Michael: Right, so there’s definitely a learning curve in the syntax which is not difficult but you know there’ll be a learning curve but I want you to be comfortable with that, you can just bring data in. So for example, I created a, so Meditech is a old school 60’s, 70’s programming application but they put all the data every night to a SQL server which is the Meditech Data Repository. So a lot of the times the analyst who know Meditech inside and out don’t know SQL server. Now, I know SQL server but I don’t know Meditech inside and out, so there are tables within Meditech that map columns to tables within Meditech and can give you hints about where it came from. So I build an application off of two tables that brings in and maps them so if I want to search for a module like I want to see all the lab modules. Well in my application just click lab and it shows me all the lab tables and then if someone says it has to do with admissions or admitting time, well okay I can search admit and it brings columns or tables that share that name and then I can use that to sort of narrow down where to find data on the SQL server that they’re seeing in the application. It’s kind of a data dictionary if you will. It’s probably better with visuals.

Steve: Alright, any last thoughts as we wrap it up?

Michael: No, I just SQL server DBA, side base DBA for many years and stumbled across this and saw the power and once you get past couple months of the learning curve, being able to leverage the Qlik either View or Sense solutions to visualize data even just one person, a lot longer with entire organization that is all ordering up the same menu if you will and see the data the same way and gives them the ability to ask their own questions in QlikSense.

Carlos: Awesome, should we do SQL family?

Michael: Absolutely.

Steve: So Michael how did you first get started using SQL server?

Michael: I was the typical DBA but I started in the side base world and did that for a couple of years and notice my other partners all had many more years of experience than me. So I had an opportunity at a company that was on Microsoft SQL Server 7.0 before it went public so we were sort of a beta site and thought, “Alright, that’s it easy transfer skills that’s relatively the same just with the GUI on top”, and made the move and been in SQL Server ever since 1999.

Carlos: Now if you could change one thing about SQL Server what would it be?

Michael: This was the toughest one. Nothing really major I guess like some minor things that I would probably change. Sometimes it irritates me when the different either size or time increments are in different values milliseconds, seconds, gigabytes, megabytes, kilobytes and just hadn’t do all the conversions gets to be annoying at times.

Carlos: Oh, got you. You’re being a little bit standardizing the some of the reporting data.

Michael: Correct, here’s the formula I have to use on every size field because they’re all in kilobytes. And then the one today we had an issue at work today with some deadlocks and keep going into the SQL Server through the GUI, went into the log and soon as you expand the error log it pops up and defaults to the SQL Server agent log, so you have to uncheck that and check the current SQL Server log, so just a little you know, no shows stoppers, but these kind of annoying little things.

Steve: What is the best piece of career advice that you’ve ever received?

Michael: I had a CIO at a company years ago he was Marine Corps reservist who since gone to retire as a General but point being is that he said that, “No one will care about your career more than you”. So it’s up to you to advance your career to whatever level you want it to be.

Steve: I like that, so true.

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

Michael: I’ll probably go vanilla here and say to fly.

Steve: Don’t be ashamed. Flying is a great superhero power.

Michael: Predicting the future and all that stuff like already cool but I would say the flying. I do a fair amount of driving and so it would be nice to get to places I grow up.
Carlos: There you go.

Steve: Alright, very good.

Carlos: Well awesome, Michael. Thank you so much for being on the show today.

Michael: Thank you for having me. I appreciate it and hope I have provided value to the companeros out there.

Carlos: Hey, companeros. That’s great! Well, if you didn’t comapneros, you let us know, I will let Michael know. That would be very nice.

Michael: Absolutely.

Steve: I know I learned something along the way, thanks, Michael.

Michael: Thank you guys! I appreciate it.

Episode 93: Making Mistakes

We all make them and sometimes it can be painful.  Making and sharing mistakes is one of the most powerful tools for learning; However, most of us do all they can to avoid and hide those mistakes.  In this episode, Steve and I talk about making mistakes and share some thoughts around how we might improve how to respond when we don’t do something quite right.

Listen to Learn

  • Two rather public mistakes and how the companies addressed them.
  • Why we are so quick to point the finger.
  • What you should do when you make a mistake.
  • Thoughts on ways to avoid mistakes

” open=”off” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]

Episode 92: SSDT

At one time there were two different products named SSDT and that was confusing for lots of folks. and we’re pleased to have Kevin Cunnane, the program manager for SSDT, as our guest.  We wanted to ask him about some of the new database features in Visual Studio and what kind of problems they are looking to solve.

SSDTWe think it is awesome the Microsoft folks will take some time our there their schedules to talk with us and we think you will this episode an interesting discussion on the future of database tooling.

 

 

 Episode Quote

“If you’re doing any kind of adhoc development, Visual Studio is very complementary and it will work well.”

“Visual Studio components are much more focused on kind of the developer scenarios and light weight DBA scenarios.”

Listen to Learn

  • SQL Server Data Tools
  • The BIDS and Visual Studio components of SSDT
  • Visual Studio source control and schema compare
  • Differences of SQL Server Management Studio and Visual Studio

Kevin on Twitter
Kevin on LinkedIn
Kevin on Channel9

About Kevin Cunnane

Kevin Cunnane is a Senior Software Engineer working on SQL Server client tooling. He has been creating developer tools in the NLP space and for the last five years in the SQL Server team at Microsoft. He is also involved in client tools (mostly Visual Studio), command line tools, SQL Server Data Tools, APIs for DacFx and the Microsoft Azure SQL Database Management Portal.

 

Transcription: SSDT

Carlos: So Kevin welcome to the program.

Kevin: Thanks very much for having me on.

Carlos: Yes it’s great to able to talk with the Microsoft folks and so we appreciate you take a little bit of time to chat with us today. Why don’t you first go ahead and introduce yourself and tell us a little bit about your role at Microsoft.

Kevin: Sure. So I’ve been working for the SQL server team for about five and a half six years now. I’m an engineer working directly on the SQL server tools and over my time I worked on a bunch of different areas but I’ve kept coming back towards developer tools specifically the SQL server data tools that shipped into Visual Studio. So, well sometimes I work on other things that’s all is kind of something that keep coming back to you. And that’s what I’m hoping to be here to talk about today.

Carlos: Yes actually that is our, our topic is the SQL server data tools. Now as we get in to this and if this is something new to you, you can maybe fast forward for just a minute or two but we want to make a clarification potentially. Because at one point it was a bit confusing as to the names SQL server data tools and what they meant. So there was kind of two veins. One of which we were familiar with so there was a, the biz, the BIDS for the Business Intelligence Development Tools which have been renamed SQL server data tools. And then there was a Visual Studio components that if you wanted to do some BI things on the visual studio – there was that. So I guess help us understand what are we when we say SSDT now, what is it that we’re talking about?

Kevin: Sure so, the reasoning now behind having two SSDT brands was a little bit accidental. There was a grand plan back in 2012 to unify all of these SQL tools and business intelligence and database relational into one product. Ultimately it didn’t quite land in terms of timing so it ended up with two products. The core SSDT which was the relational projects, and SSDT BI for business intelligence which a lot of people thought of just as SSDT. So with SQL server 2016, we actually took a big step and back and actually did that work to align the two together. So what you get now is as you install Visual Studio, you still got the database project and relational tool built-in since it’s really used by everyone. But if you go to the web and you search for SSDT what you’ll get is a package containing all of the Business Intelligence tools along with the Relational Database tools in one package. So it finally gets packed to that thing up. All of the SQL tools you might want to use in Visual Studio are SSDT. So and when you install them you get the choice of which ones that you want to actually use.

Steve: So then for instance, I mean if somebody was previously using the Business Intelligence Development Studio and they were working on reports. Is that what installs now as part of Visual Studio or is that for instance part of the SSDT add-on component?

Kevin: So for all of the BI tools, they are just part of the SSDT add-on. So they’re not shipped with Visual Studio just because they are a little bit more optional. They’re heavily used but they have always shipped that outside of Visual Studio. I would note though that in Visual Studio 2017, they actually have the reporting services and analysis services project types as extensions on the Visual Studio gallery. So if you don’t want to go outside of Visual Studio and find it on the web, you can actually search in extensions and updates and look for the online updates and find it them right there. So that’s another improvement going forward to make it a little bit more lightweight and easy to get your tools.

Carlos: So in this new environment, you mentioned right the, that idea that the tools are going to join forces or they’re going to become one product. It’s caused a little bit of heartburn if you will from the data perspective which is most of our listeners come from. And they’re used to using SQL Server Management Studio and now we’re kind of being led into the Visual Studio side. I guess, thoughts on how we can, I guess as you backup a little, that pill seems to be a little bit harder to swallow than maybe originally, you know I thought I think you know we as IT people sometimes we are very stubborn in the way that we go about things with this much as things changed. So I guess I’m curious to get some of your thoughts on what can we, yeah what are we doing or how can we make that adoption a little bit easier for those who have been so traditionally used to just work in SQL Server Management Studio.

Kevin: Sure, we’ll say that SQL Server Management Studio is still hugely popular and really will continue to be the flagship of all of our tools. And if you are serious about SQL server there’s, just the odds are that you’re going to use that. That said some of the more modern practices such as DevOps continuous integration making it easy to automatically deploy, those aren’t covered in SQL server management studio so you, you do need to step outside your comfort zone a little bit if you want to truly embrace those. But there’s a nice sliding scale of where you want to go on that. If you look at the, the database projects systems we have lots of functionality for producing these deployable packages that can be used in continuous integration but you can also develop the scripts and have them ready to publish and hand off to a DBA or more data focused developer on your team. So that they can comfortably deploy using SSMS or using sqlcmd from the command line and do that in a more controlled fashion. So that’s the first sliding step is you partner with other people on your team. They hand you off the assets and you go there and then moving on towards you know getting Git integration or TSF integration into your workflow and being getting more comfortable with that. It can definitely be a little bit, little bit more of clickstop on the way. So you don’t have to jump right in, you can partner with others and find ways to do this, and there are a lot of you know third party tools and also with the new Visual Studio team services, Visual, TFS 2015 and 2017, they’re making it really easy to set up these processes so that you don’t have to be a coder or somebody deep in doing these things just to get a continuous integration or continuous deployment process going. And that you’d hopefully keep it all pretty comfortable without actually getting deep into Visual Studio coding all of those things.

Carlos: Well I think it’s more, you’re seeing more and more opportunities jobs right in the all these SAS companies or organizations that are doing development that need the DevOps environment. The data folks are going to have to suck it up in the sense and you know and get familiar with that because it does make that process just so much easier and I think of getting into, using the SQL server projects even as an example. It gives you another reference outside of just the database so as you wanted to you know move those objects like you mentioned being able to hand-off the script. If that’s even a maybe another variation of that is I can then go to source control and use that as a comparison before I actually ended up deploying those changes. So it gives me another way to review what’s happening or the changes that are going inside of the database. And ultimately for that reason I think it’s a good thing to know and to become familiar with.

Kevin: That’s for sure and there, people build up all sorts of different solutions for this. Like you mentioned just having changes being compared against different versions of your source controlled data and produce scripts from that is something that a number of more serious users of our system actually do develop really nice systems are end up because it gives them that control that they want, assurance for data folks and but also makes it very easy to rapidly apply changes. And you know one of the big things with the DevOps movement especially for data is just being able to deploy your data changes at the same time as your application has huge benefits. It really helps speed things up but application developers are really not going always going to do the right thing. You need the help from data folks to make sure that you’re designing things correctly. And so getting involved earlier on that DevOps process, reviewing the changes and code review etc can really help make the whole team more productive.

Steve: Okay, so speaking of the source control side of it, so then is it with Visual Studio or with SSDT that you get the option to be able to like commit your database in the source control.

Kevin: Sure, so it comes with Visual Studio, Visual Studio Community and up for 2015 has source control. If you just install SSDT on its own, we put in a minimal version of Visual Studio just with the bits that we need. And unfortunate for 2015 that did not include source control packages, our source, support for source control. Now with 2017 we’re working on a similar, you know like external updater that will install just the pieces we need. And we are hoping to get some basic source controls support in there so that would come back to where we were in 2013 and earlier versions. But that is a little bit to be determined but ideally if you can use either one of the Express Editions of Visual Studio 2015, which includes the SQL server data tools for relational projects or the community or up. Those all have source control.

Steve: Okay. So I haven’t used that source control through Visual Studio in the past but I have used for instance the Redgate SQL Source Control through SQL server management studio. And that was one area that you could go and just point at the database, it would scan the entire database, put the whole thing in the source control, broken out by object types, tables, stored procedures, functions, views whatnot. Is that similar? I mean is that what the source control does in the SSDT in Visual Studio side of it as well or does it do something different there?

Kevin: It’s quite similar but it is a little bit of a change from the Redgate Source Control treats the databases as the source of truth still so it manages things in source control but you keep going back to the database for reference. With database projects in Visual Studio, you, if you want to start with the database you can import the contents into a project. And that just pulls all of the files locally into your machine where you can see them just like source control organize and then you will check those into Source Control. But those files we have a build process to validate all of the things that are happening in all of the contents. So often when you first import you’ll actually find all of the things that are broken in your database. Most commonly stored procedures and views that refer to tables or columns that are no longer there and somehow they’re still working but if you ever try to deploy it to a different database those will fail. So that’s pretty common. So yeah with SSDT it’s a much more project-centric view of the world where you start with the project and then you deploy from there. That’s the key change compared to Redgate Source Control.

Carlos: And then because I’ve worked with it a little bit you know, deciding what you’re going to do with your security as well because you know you’re going to import those users and the owners of objects get imported. And so what’re the things you’re going to find out pretty quickly is do you have a good structure for who owns all those objects and in your schemas and things like that. Because as you know, as we’ve mentioned kind of moving around from environment to environment, you’re going to see issues if that’s not setup well.

Kevin: That’s correct. One thing that we have improved on is we have much better controls when deploying on what to ignore or leave be when actually publishing. So you can pull in your user’s permissions, etcetera for reference but you can actually ignore them during deployments so that they don’t interfere and if you do have different systems for different deployment environments. It’s a little bit easier to configure that now.

Steve: Okay. So then beyond the Source control side of it. I mean what are the most common things that people use in the SQL server data tools to do beyond what you would get in Management Studio or in Visual Studio?

Steve: I guess what I’m thinking there is that if you, if there’s a DBA out there who’s been using SQL Server Management Studio for a long time and they’re comfortable with it and they do most what they needed to do today with that mechanism, what are the things that you really need to use SSDT to be able to do that you can’t do in Management Studio?

Kevin: Sure. Especially for DBAs there’s a couple of hidden features that are worth the price of admission we’d like to say. Particularly Schema Compare is built-in to Visual Studio and it’s free. And that lets you compare two databases contents to each other, database to project system or a DACPAC which is the compiled version of that. And so there’s great benefits there and if you want to be able to see hey what’s the difference between staging and production in terms of my database contents. That will give you that view and make it super easy to understand that. Similarly we have data compare for if you do want to compare for example hey what’s the reference data looking like in these two tables that are in different databases. And making sure they’re up to date or identifying issues. Those two are really huge features that a DBA might not be aware of but give then a massive amount of benefit.

Steve: Okay, excellent. I know those are two pieces that I have in the past looked at like third party add-ons trying to achieve and if that’s something, I mean I know the Schema Compare has been in there with the Visual Studio but didn’t realize that Data Compare was in there as well. Nice.

Kevin: Yeah. So both of those are there, I mean there’s a lot of the other functionality is around lightweight you know project system development but those two are useful for everybody. And schema compare is actually now supported in our command line tool which is called SQLPackage that lets you deploy all of your database project information up to a database where you can also do a number of other actions. And one of those is you can get a saved schema compare file that you’ve developed in Visual Studio and run it through this process or indeed using APIs you can actually build up entire workflows on top of the schema compare engine since we now just have a very simple API for it. So if you want to do some very simple logic and if you’re comfortable with a little bit of C# coding, very little that kind that you would do in a Power Shell script, you can do amazing things with that.

Steve: Very nice. So with that I mean if someone want to just build a script that would tell them what’s different between two servers and two SQL servers and it would run daily and send out an email. I mean is that the kind of thing you’re talking about or it’ll be really easy to do with that SQLPackage and a little bit of scripting?

Kevin: Exactly. You can do it directly with SQLPackage where you just send that straight to report and pre-configure what type of things to exclude; or if you needed to be more dynamic and respond to the result before formatting it for a result. That’s where you might write a little script that iterate it over the results and did it in a more controlled fashion.

Carlos: Yeah, and this an area that I think for still, even though it’s been in the couple of versions of Visual Studio is well, still appears to not be getting a lot of love and you know as we go to different conferences or talk with others that are using the projects. You ask like who’s using it and it’s kind of crickets. Now maybe I’m not in the right circles admittedly but I think there is a lot of value to moving to the SQL server projects and you know integrating with the source control you know components. And then of course you know you talked about the data, the data comparison components. Now one of the things, I guess maybe, I don’t know for some of the developer side but what gets included with Visual Studio now is actually a LocalDB copy. Do we need to be concerned about database sprawl as we look to be using Visual Studio?

Kevin: That’s a good question. The nice thing with LocalDB is that when you’re not using it for a long time it will stop running. It spins down and just keeps a very lightweight listener that will let it spin off again. So it’s not going to be using resources on your machine, that’s for sure. In terms of database sprawl and all of the databases you’re going to connect to there, it’s only if you got a local project develop, in development that will create a temporary database under LocalDB for you.

Steve: So then with the LocalDB there is that intended for like desktop applications that are going to be deployed and they would just use a local database or is it really more intended just for development purposes to.

Kevin: It can be used for either. We have people building, using LocalDB for like that desktop applications lightweight as a replacement for SQL CE or other lightweight database options. So that’s one of the scenarios for it. The reason it’s shipped inside Visual Studio is it really helps with your local development and debug loop. So every time you make a minor change in a project in SSDT, if you had it will deploy it up to LocalDB. So it can verify the deployment works, you can you know edit the data in there and compare it, verify everything works. And you can even run unit test against it to make sure that everything’s working okay. So that’s one of the most common scenarios. Similarly, for people who don’t want to use database projects, the web application development inside Visual Studio uses LocalDB too. It uses that as like a reference database. And when it’s publishing it gets a snapshot of that using our technology and publishes it up. So there are a lot of different ways to develop for SQL inside Visual Studio and it just depends on your workflow. Those web apps tend to be very simple it gets you bootstrap, you’re not worrying about a DBA for those and then often if you scale them up, that’s when you might want to move over to a dedicated SQL project or set a migration scripts which are much more controlled approach to go in through things either of those. But for rapid development LocalDB is great for these kind of scenarios.

Steve: I mean this sort of interesting is, sounds like again kind of that rapid development and I can’t help but think of kind of the open source maybe mentality or like, “Hey, I want to try something out and see that it’s working.” I know that again on a database side SQL server has made some changes to I’ll say I use the word compete with the MySQL because it is open source right from the data science perspective, analytics components. MySQL was being adopted much faster than SQL servers then they made some changes here. I almost feel like there’s a similar component there to Visual Studio’s, you know let’s put the tools in here so that people can quickly kind of see this is what they want to get bigger and then they can use something, something else that they need. Is that a fair,assessment there?

Kevin: Yeah I think that’s fair. And we are also by the way working to be more transparent and open source in our tools in general primarily with our new multi OS tools. So we have a visual studio code extension where it’s being develop fully in the open with SSMS and SSDT people have actually asked us about can we just open source this and they have pretty complex build system there’s some legacy stuff in there that would be hard to make that possible but we are trying to embrace that in any way we can in terms of extensibility points, making it easy for you to contribute, making it easier to raise issues and like that as well, just making it easier to adopt whatever tool that you use to deploy we want to be there and make it easy for you to develop SQL server.

Carlos: Now, you’ve mentioned you said before right SQL Server Management Studio is not going away. It will be there for those who want to continue to use it, one of the things that we also know I noticed in the Visual Studio component is that the SQL Server Object Explorer is now in Visual Studio. So I guess to reverse the roles a little bit, maybe let’s say I’m new, I’m a new DBA, I’m kind of coming up could Visual Studio be the tool that I learn in and grow in to manage my database?

Kevin: I think being honest it would be a little hard. We’ve had people asking why don’t you merge this two? But there’s a huge amount of functionality in SQL Server Management Studio around backing up, around different types of profiling, etc., There’s so many different piece of functionality there that you might need to use as a DBA that I think if you are actively managing databases, SQL Server Management Studio is still the tool that you should use. But if you’re doing any kind of adhoc development or want to explore that kind of side of things Visual Studio is very complementary and it will work well.

Carlos: It sounds like a tool, so the wizarding components that it gives you that’s what’s going to continue to be in SQL Server Management Studio, from the ability to get to the database and kind of specific to T-SQL statements and things like that, maybe there’s more options available.

Kevin: That’s correct, it’s just a little bit richer in that, the Visual Studio components are much more focused on kind of the developer scenarios they have all the common things that you might need to use if you’re a developer or a light weight DBA but once you get into managing, dozens or hundreds of databases and doing them at scale, there is just certain things that SQL Server Management Studio will do that I think people are going to want to install it and use it.

Steve: Sounds like there’s a hand full of different versions out there than with a SSDT and Visual Studio 2017, 2015, 2016 which ever.

Kevin: 2015 for that one, 2013, 2012, 2010 we’ve been in them all. Yeah, that’s correct. So one of the things that it is important to note is that all we actively develop on and ship into are the most recent two versions of Visual Studio, so that means that what’s Visual Studio 2017 at, we’re going to keep shipping updates into that with new features similarly Visual Studio 2015 will keep shipping features into that until there’s a new version of Visual Studio and for Visual Studio 2013 and older we will do security patching and maintenance and all of those things if needed but it won’t get any new features. So that means in particular if you want to use the latest versions of SQL Server, SQL Server 2016 or above and SQL Server VNext which our latest RCs are letting you use or if you use Azure SQL DB which tends to keep iterating and moving on and moving forward you should be using one of Visual Studio 2015 or Visual Studio 2017.

Steve: Okay, I know that’s an area you’ve mentioned the Azure databases, that scenario that I ran into using the Azure parallel data warehousing where I needed to get a specific version of the Visual Studio that was different than what I was normally using to be able to use the tools to connect to that location.

Kevin: That’s correct, so for that, if you want to use Azure SQL Data Warehouse you should be using Visual Studio 2015 or above and you should just update the SQL server data tools to the latest version. We’ve had support for quite a number of versions now but obviously with visual studio, you know 2015 came out 2 years ago, SQL Data Warehouse came in 2016, so naturally you just need to update to get support for that. And one other thing to just call on this is that with the SSDT for VS 2015 release and onwards, we do now have backwards compatibility support for previous SQL Server versions not just in the database projects where they always were but the all of BI project types, so the database project type analysis services and reporting services all have support officially for SQL Server 2008 and up and for integration services it’s SQL server 2012 and up, so if you do want to use this with older versions in the past you needed to be tied to a specific Visual Studio version, now our recommendation is just get the latest and will support your SQL Server.

Steve: For people who need to work on multiple versions of SQL Server that’s certainly a big win there, I know for instance working with SSRS a few times I’ve had to have several different installs to be able to work on different SQL Servers at different client sites.

Kevin: Well one thing that I like to bring up is ignore column order support which is a major feature people have asked for and just to get advertising out there that this is now in SQL Server Data Tools, if that’s cool? Great I’d like to talk about that briefly, we added in our most recent version of our SSDT or release candidate support for ignore column order so people have been asking for a long time and a little background is that if your deploying from a database project we try to be very accurate about making what’s in your project system be what’s in the database. The challenge with that is that people often add in your columns in the list kind of alphabetically or they just add one in the middle which they don’t realize will cause a data movement operation because you can’t do that via T-SQL syntax, you need to always add to the end. So we finally brought back support for ignoring column order when deploying which means that if all the columns are there it doesn’t matter what order they are in we will leave them be and we’ll add new columns to the end rather than doing a much longer running operation. So we’d encourage people to try the release candidate and we do have the full version will be coming to Visual Studio 2015 as a GA within the next month or two so keep your eye out for that it will go the regular update channels.

Carlos: Okay and that makes me feel a little bit better, so I think previously the 2015 version there was this option in the project which made my heart stop a little bit where it would go through the process of creating a new table, moving the data over, dropping the old table and all the constraints and everything that was needed. And I was like, “Ooshh”.

Kevin: Yeah, and just to point out we do have so, one thing that it’s not built in to detect those kind of changes and warn you about it. We do have people who built that though on top of our extensibilities, so if you do want to do it, it’s there but it helps as well as we say trust but verify where before deploying to production in particular just make sure that somebody does review the deployment script. And if you see those kind of things it’s unlikely to be what you intended so go back, check this option to ignore column order or redesign it and realize that this is a breaking change. So we have extensibility where you can actually do that programmatically but that’s a little bit more cumbersome than some automatic processes, so just people can just build those on top.

Steve: So then if you’re doing the ignore column order rather than the old process of renaming the table and then creating a new table and throwing it up and swapping them around, it’s just going to drop the new column at the end of the table if you add one?

Kevin: Correct, it will just do an alter table add column and your good to go.

Steve: Very nice, that will be much faster, assuming that it support the applications using it are fine with that layout.

Carlos: So I did want to circle back around for a moment because you have talked about the different versions that got pushed out even we talked about 2012 or 2010, 2012, 2013, 2015 and so there’s which is great and everybody is excited to see the “New Microsoft” and the speed of what you guys moving and its mind boggling sometimes. But that does beg the question a little bit you mentioned that the new features get into the last two versions of visual studio. It can go pretty quickly there, I guess thoughts on how or the organization would attack the idea of keeping up with the newest versions?

Kevin: That’s a good question, well one of the things that’s been happening which will hopefully make it a little easier is that with Visual Studio 2017 in particular, the installation has become a lot more light weight and installs a lot less global components, so you’re less likely to run into problems if you do try out the new version while still having the older version on your machine. What we suggest is even with this you’re still getting about a 3, 4 year process were you need to update at some point during that if you’re going up to the lasts SQL Server version. When it comes to build processes etc, one thing is that we now have a new get package with all of our MS build components to make is easier to have kind of unknown version when you’re doing your proper CICD so that it builds standard version that can avoid some of the trouble there that you get into with these where at least your build process will always be consistent no matters who’s developing on which version. The other one is that probably what we’re seeing is that usually because it’s backwards compatible you can have a handful of people in the team try at 2017 even if you’re still in 2013. Stuff still works certainly on the database project side. I think some of the other projects types you may need to be prepared to do a one way upgrade so it needs to be more blunt. For the database project type you should in the vast majority of cases be fine trying it out getting maybe a mini team to do it on a certain project and then adopting it once you realized there are no issues. And also, a lot of things have gone through more of a subscription based models so if you have Visual Studio 2013 it should be pretty easy to update without any pain straight up to 2017 and use it there.

Carlos: Got you. Well, awesome. Kevin, thanks so much for being with us today. Any last thoughts before we move to SQL Family?

Kevin: You know, I’d encourage people if you haven’t tried it already, try out the RC. I mean it has support for SQL Server vNext and one of the most fun parts of that is that we now have docker support for containers so you can install SQL vNext in a few seconds without it squashing anything on your machine which is the best part of it. So just try it out there. You’ve got no risk of damaging anything and pretty exciting to do that and give us feedback on it. We’re going towards GA so we’re focusing just on the quality of the tools that will support this even though obviously it will be a while yet still SQL vNext is released. We want to be there with the tools ahead of time. So everybody please write that out. Please write Visual Studio 2017 and the tools in there as well. And we’re looking forward to hearing you feedback on it.

Steve: Well, let’s move on to SQL Family then. So Kevin, how did you get started with SQL Server?

Kevin: That’s a great question. So I have been a tools guy for a while. Before I join Microsoft I’ve been working in different company developing tools as part of a larger set of things but I really enjoy that process of kind of getting the complex requirements on deep things. And when I came to join Microsoft I had a few different areas. But this one really descended by far the most interesting. So I joined and I knew nothing about SQL. I barely done any database development so it was a little bit of a trash course learning all about it from the schema point of view. And luckily on the team that I worked on, you have to learn all breadth and depth of how you can configure SQL server in terms of schema changes. That was really fun and I just love that process and I really like that kind of people we worked with in the user base. So that’s been very rewarding for me.

Carlos: Now, if you could change one thing about SQL Server what would it be?

Kevin: That’s a good question. From my side I wish it was easier sometimes to add new features into the tools that support the latest features, so we have some really amazing things that makes stuff super simple for people such as temporal tables. Unfortunately it can take a lot of effort to make the tool support that so people don’t realize that’s why sometimes we don’t always have as many new features in the tools as we’d like because we’re supporting these new SQL Server versions. And with SQL Server 2016 in particular, there were just so many features which people should try at which we supported, OS encrypted, temporal, just a huge number. So I wish it was a little cheaper to do that just so that we could add more value for people. But on the plus side people get to try out of all these new things and it should all work pretty smoothly for them. So I do upgrade and do try these things because they’re pretty awesome.

Carlos: Very cool.

Steve: What is the best piece of career advice that you’ve received?

Kevin: That one has, I mean, my first manager here, his best piece of life advice was buy a house because the market will be crazy and I regret not following that one. In terms of career of advice, I think the biggest one really was don’t be afraid to just on quality and always good feedback in terms of code reviews, and that’s just have been a huge benefit whenever I’ve done it. Just always be willing to ship a week later but have it be good and I think that’s done for pretty much everything.

Steve: Ok.

Carlos: Especially for somebody who is working in the tools, I’m sure you can appreciate that as well.

Kevin: Yeah, you know, we don’t like to do hot fixes. The good thing is when we have to do them occasionally and we can turn things around in a day now which is amazing. So we have shipped and gone, “Oopps”, something is wrong and by the next morning we have a new version that’s been patched which is great but we really don’t like doing that. It hurts our customers and it hurts us.

Carlos: Sure. Last question for you Kevin is, if you could have one superhero power, what would it be and why do you want it?

Kevin: I think I would like to have the power to turn back time sometimes, and just redo those decisions we’re like, “Oh, this is definitely what we need to do and going to it.” Specifically sometimes we get a very principled on we must support a certain scenario and then we spend weeks trying to do it and then realizing, “Ok, we could have done this much quicker at different way or avoided it all doing together.” I think gotten more than in my career if I could turn back time and change those decisions a little bit.

Steve: You could even turn back time and have bought a house when it was recommended by your manager.

Kevin: Yeah, I should have listened to him. He was a very smart manager and that’s the piece of advice for everyone if you could back to 2011, that would be good time to do.

Steve: Alright.

Carlos: Well, Kevin, thank you so much for being on the show with us today.

Kevin: It has been a real pleasure. Thanks for having me on.                                         

Steve: Yeah. Thanks a lot, Kevin.

Episode 91: DBA Tools

We all want an easy button.  It is human nature.  We heard quite a bit about how easy PowerShell will make everything, but for those of us who aren’t programmers, it can be a bit intimidating to get started.  Luckily for us, we have what is shaping up to be the closest thing to an easy button for DBAs I have found for PowerShell tools from the folks at dbatools.io.  We invite some of the team to chat with us about their tool, how they got started and the types of problems they are looking to solve.

DBA ToolsIn this episode, Chrissy will be talking about how DBA Tools started from just being a simple migration tool until it became a useful tool solving various SQL related issues.  The team will discuss the other various situations where DBA Tools can really be helpful and how they manage contributions from over 30 people which leads us to talk about GitHub, the open source community, and their experiences in updating versions of DBA Tools and how they encourage contributors in the project.

Chrissy, Rob, Constantine, and Aaron were super excited to talk with us and we loved their energy and think you will enjoy this episode.

 Episode Quote

“The features that are now inside of DBA tools, honestly, I would describe them as really awesome.” – Constantine
“I promised you this is the best code ever used and that you will ever have.” – Aaron
“It is important to us that people do feel welcomed and that their codes gets merged in.” – Chrissy

Listen to Learn

  • The tools the dbatools team has put together
  • How the team goes about deciding what gets into the code
  • Examples of how this makes PowerShell more accessible
  • The challenges of having a community tool
Chrissy LeMaire

on Twitter
LinkedIn
GitHub
MVP Profile
Blog

Rob Sewell

on Twitter
LinkedIn
GitHub
SQL Server Central

Constantine Kokkinos

Twitter
LinkedIn
Blog

Aaron Nelson

LinkedIn
Twitter
GitHub
Channel 9
blog

About Chrissy LeMaire

Chrissy describes herself as a Cajun living in Belgium and a SQL Server DBA and PowerShell MVP with nearly 20 years of experience.  She is also the colead for the SQL PASS PowerShell Virtual Chapter and lead for the Belgian PowerShell User Group.  A fan of Linux and Open Source since I was first introduced them back in the 90’s, right around the time I moved to California to work in tech and has worked with PowerShell since 2005.

Transcription: DBA Tools

Carlos: Awesome, well today we are in for a real treat. We have the DBA tools team or “the brain child” of that project here in the studio with us today. So, we want to say, “Hello to you all!” This is probably the biggest group we ever had on the podcast so we are excited about that.

Chrissy: You are welcome.

Carlos: The more the merrier and we are going to prove that today. And so I guess I’ll kick it off with you Chrissy. Why don’t you tell us a little about these projects, how it got started and then we can kind of go around and well go from there.

Chrissy: Alright, so DBA tools actually didn’t start out how it is now. Initially way back in 2014 I created a bunch of migration commands and I made a little project on Github called SQL Migration, and then I started adding a couple of things like get SQL Server key or dug into the registry and it grab that information, and then I just kept adding more and overtime I realized that it can be more than just a migration tool so I called it DBA tools and then I just started marketing it that way as a general Power Shell tool for DBA’s and it really took off from there.

Carlos: All of you, maybe Rob you want to jump in? When you got involved with the project were you looking at it from a migration prospective?

Rob: Absolutely not. I’ve always been a SQL and Power Shell person so Chrissy and I got together and created this SQL collaborative Github collection which is where DBA tools lives on Github and obviously I knew her from partial conference and from SQL conferences and so we got together and start working from there on.

Steve: So Rob, how long after the original work that Chrissy had done was it that you got involve with the project?

Rob: So Chrissy, when did it change from being migrations to being DBA tools? I’m not sure about the exact date of that.

Chrissy: So the official, I think that I saw its birthday was sometime around September 2015 as when I changed the name of the module and then really everything in April of 2016 I registered the domain. In doing that I wanted to make it more approachable for DBA’s and for non developers because Github is not the place for people who are kind of apprehensive about Power Shell and so from there we started Slack. And I would have to say that that was about the time that it really started to take off whenever we started the SQL community Slack channel. So the first command that was introduce by the community was Claudio Silvia he is from Portugal and he gave us expense SQL T Log responsibly and then the command after that was whenever Rob contributed remove SQL database safely and that was around April or May of 2016.

Carlos: Ok. Well, I think it would be worth kind of noting here because Rob kind of mentioned that he was a Power Shell person so really quickly let’s go through and kind of talk about what were your expertise, how kind of you got involve with the project and kind of where you were coming from? So Constantine, you actually want to start it for us?

Constantine: Yeah sure actually I had almost no Power Shell experience. I came from a DBA’s stand point and I work as a production DBA. And honestly, the reason why I saw the kind of interest in DBA tools and why I was interested in it was because I didn’t have that experience and the migration videos, the intros that Chrissy had put together were so good in such a small package that I said “I need to learn this!” This absolutely calls me to learn Power Shell more than anything I have ever before really. So I had a lot of T-SQL experience. I had programmed silly things in pure SQL and I saw this and went, “I want to throw that all on the trash and replace it with this.”

Carlos: Ok that’s interesting. So you’ve talked about migration and kind of how it got started. You still feel like this is primarily a migration tool?

Constantine: I don’t actually and I think that’s part of why it is increasing adoption is because you said earlier with the whole, “Thanks but no thanks.” In reality for a large portion of the DBA tools existence, Chrissy has been extremely accepting of what those DBA tools do. We’ve had a lot of conversations about what are we really trying to solve here. What problems are we solving? Do we go beyond that? Where does that lay? I think one of the benefits of DBA tools, as we’ve said as long as it‘s really is solving a DBA problem it probably belongs in DBA tools and that why a lot of people who don’t have all of the experience to contribute their individual pieces and make it better.

Rob: Is that anything that will help make DBA life easier and that isn’t something like to be provided by Microsoft through the partial SQL Server module. So we won’t build at database user or these who generic commands will do more complex useful things in that.

Carlos: Ok. How would you describe some of the features that are now inside of DBA tools then?

Constantine: Well honestly, I would describe them as really awesome. I mean, that’s a very basic thing. But the reality is, one of the things we started to deal with and kind of struggle with but also find good solutions for, is the idea that we have a ton of Power Shell commands here doing a ton of different things. And so some of our big contributors and Slack have actually put together some in browser things and also some Power Shell base help things if you were more into that to help you narrow down on keywords or related pieces. You know Power Shell has some pieces to help you with that but again we are not really targeting the heavy amazing Power Shell users. We’re targeting people who are just starting and who want to build something great and their environment that not always someone who’s going to know how to use the help command even.

Chrissy: Absolutely

Carlos: I think you’ve described to me perfectly. What are the reasons I was so interested on having you guys on and talking a little bit about this. So if someone doesn’t need to know too much about Power Shell obviously, you said, SQL Server they can download your stuff. How do we get it started?

Chrissy: Ok, I’ll jump and answer this one. If you have a dbatools.io there’s going to be a download section that you’ll see at the top and what I did was I created the site thinking of my own favorite open source projects. So you have Notepad++ for example, when you go there what do you want to see? You want to see download, so I put the download there and I also didn’t want to burden people. What I like about Notepad++ is that I’ll just download a zip or an msi. I don’t want to mess with anything and so we have like 4 different methods for downloading DBA tools. The first one is like really direct. We have it in the Power Shell gallery for systems that do support the Power Shell gallery so just be install-module.dbatools. But if you don’t have a modern system then you can also just copy and paste a single command and as long as you have either SQL Server Management Studio 2008 and above installed or the SQL Management Objects (SMO), it’s just going to download, put it where it needs to go, extract it into the proper directory and then you’ll have it. So it’s a really straightforward download and that enables you to have over a 150 commands with a single command.

Carlos: Very cool and just to clarify because I’m not familiar with the Power Shell gallery this must require additional setup before you could use that route?

Chrissy: So the Power Shell gallery its built into Windows 10 and it allows you to kind  of like if you are familiar with Linux and apt-get and all of their package managers. This is Power Shells package manager and there’s a gallery where a whole bunch of software publishers have placed all of their stuff and it makes it easy to download. It’s kind of like a command line windows store so instead of being in the Windows store we’re in the Power Shell gallery. And if you have older systems then we have links on our website that you can just click to and you install a couple of things and then it will work that way. And the install is something that is really important to is because it is migration and we are expecting this to work on legacy systems. And so it works on Power Shell version 3 back all the way to Windows 7, so it works all the way you know from Windows 7 and above. And anytime that we have the next that comes out we test our commands on that and fortunately Microsoft has been really standardize in a way that they support SMO that our commands really just works with the next version of SQL Server.

Carlos: Right I think to their credit as much grief as we sometimes give to Microsoft that whole idea of backwards compatibility, it really does work nicely

Chrissy: I loved it! Totally!

Steve: So, on those legacy systems, one of the things you mentioned was the SQL Server Management Studio and the SMO objects, with that if you don’t have the right ones there and you trying to do the install is it going to tell you that? Or are you going to end up in a bad situation?

Chrissy: You know I actually, I can’t remember why I decided on SQL Server Management Studio 2008. I might have tried it with 2005 but basically it just going to throw the same errors that you have whenever you try to use SQL Server Management Studio because really DBA tools is kind of like SQL Server Management Studio at a command line.

Steve: Ok got it.

Carlos: And if you’re starting using 2005, do yourself a favor. Upgrade!

Chrissy: But you know which really awesome is that a lot of times people may not want to upgrade because they’re like, “Oh my God, this migration process is so daunting and I have this hundred point checklist.” And then if you just use Power Shell if it is possible in your environment then it really simplifies that entire process.

Rob: It certainly does and the important part of all of that is that that’s only in the client machine. The machine that’s running tools but you can have older versions as your servers but the client machine the one that you look onto to do your DBA work that’s one that needs to have the right version partial and the right version of Management Studio.

Chrissy: Absolutely. I actually did a test, if you go to dbatools.io/install, there’s a video there where I migrated, I backup a bunch of databases on Windows 2000 server that cannot have Power Shell installed on there and that was really to emphasize what Rob was saying which is Power Shell doesn’t even need to exist on the server. What’s really important is the client so that makes it a lot more simplified.

Constantine: Yeah, that’s more basically just running queries. It’s just abstracting a lot of SQL.

Chrissy: Yep

Rob: And if you look, if you run Profiler against the SQL Server when you are using Power Shell SMO to connect to it, all you see is T-SQL. It’s not doing anything magic.

Steve: So that video that you mentioned about migrating from SQL Server 2000, I watched that and I was just amaze on how easy it was to do, or how easy you made it look to run through all of that. You just run in the command and it move things over. So if someone’s out there and they are stuck with a SQL Server 2000 box and they want to move to something newer. How big of a jump can you make with that using the DBA tools to do that migration?

Chrissy: By default, we kind of go as max as possible I have a lab that setup that has 15, maybe 20, different kind of instances. So what they would have to do first, Microsoft does not even allow the path from 2000 to anything above 2008. So if you want to go 2016, that’s actually would be super simple. You would first migrate to 2008 and then migrate to 2016 from there, and of course you have to test your applications and everything like that. But we do make it possible in that way.

Steve: And have you seen that type of a jump, from 2000 to 2008 then to 2016? Have you seen that go off smoothly? Or do people usually have a lot of code migrations they have to deal with?

Chrissy: I would have to imagine that there would probably be a lot of code migrations. Unfortunately, I don’t get a ton of direct feedback about those legacy systems. It’s mostly just like, “Hey, thanks! That was super awesome.” But there wasn’t details like, “Uh, but we really had issues in our applications side”, because really no matter how you do it we kind of just automate the way that you’re going to do it anyway. You’re going to make a backup and then you’re going to restore it and then you’re going to make a back up and then you’re going to restore it. It’s just kind of makes that a lot more straightforward. Also what I really like is that we do introduce some protections. So, you know, if Microsoft doesn’t really recommend you do detach and attach from 2000 to 2008 then you have to use dash force command to say, “No, that’s really what I want to do”, even though Microsoft doesn’t really want me to do it.

Constantine: Yeah, and then a lot of cases there are pieces completely missing from database roles or server-level roles in SQL 2000 compared to SQL 2016. And so most of the times we do best-effort, what the equivalents are or we need to prompt the user and basically say we had an inflection point where you need to make a decision.

Carlos: Right, okay. So part of the process is as I watched one of those videos and go in through it. I just want to go try it out myself here but was the, you do the migration from like your 2012 to the vNext video I think I watched. And it just showed you run it, it does the backup, it does the restore, it moves the users, moves the jobs, moves the database mail configuration. But in doing that, one of the things that I was thinking through there is, in the demo of course it’s smaller databases and it goes really fast. But if you’re in the position where you have larger databases that are going to take a while to copy, are there any options or is there anything that would like do some transaction log backups and get it caught up to a point in time? Or is there an option to?

Chrissy: I loved that question. We’re working on it and that was something that I actually thought that I had to have for the 1.0 update. I was like I don’t even want to go 1.0 until we can go really Enterprise. And we actually decide that we had so many commands in this really awesome framework that’s going to come in 1.1 or 1.2. We are currently testing a mirror. I think it’s like invoke DBA Database Mirroring. You know, to make it easy to, I’m sorry it was the log shipping, to make it easier to do that, that is something that is on the agenda but currently we don’t support.

Steve: Okay. I think, well even without that it’s incredibly valuable.

Chrissy: Right because what you can also do is just, is do the -norecovery and that’ll really help simplify your process as well. And while we’re talking about this I did test instance sure. I have personally migrated 500GB databases but people have reported back that they’ve had 2TB databases that have worked with no issue so we do go really as high as possible.

Steve: Okay. Great I look forward to trying that out.

Carlos: So you’ve talked quite a bit about the migration piece and I guess maybe, I don’t know, Aaron if you’re still there we’ll invite you to join the conversation here. Give me an example of how you’re using the DBA tools aside from the migration component.

Aaron: So one of the things I loved about the DBA tools that I was able to get in just under the deadline for the cutoff for 1.0 was actually stole some code out of SMMS and it’s the code that goes in, if you right click on a database that do the report and you want to expand all option there to show off their were any auto growth or auto shrink events. I stole the code out there to find the auto growth events and the reason I did that was it’s great information but I want to be able to see the entire instance all at once. So what I did was just wrote a quick Power Shell function, and you know, started up my pull request to the DBA tools project, got it merged in and now everyone’s able to get this kind of information across the entire instance with a single command. And that only took me about an hour and a half of my time you know from start to finish. So if you want to take something that I needed, that I thought was useful for lots of people, and you know, add it to the project and now thousands upon thousands of people can use it.

Chrissy: Aaron brings up a really important part that I really love. My favorite thing about DBA tools, there’s a couple things. The first thing is that we see an awesome blog post, “Oh yeah that person make such a good point” and we take that T-SQL and we stick it into a command and now just across the board everyone can use it as soon as they update DBA tools. And the other thing is I, what Constantine had talked about earlier is that you know here’s a bunch of DBAs who have issues or problems and they solved it using Power Shell and then they can put it in DBA tools and now every DBA across the world can easily access the thing that they did. And that was something that Aaron shared with us so he had an issue, he solved it, and then he contributed it to the project.

Aaron: Well, other things I loved about DBA tools is when the SSRS team was putting out Power Shell commands for SSRS. They were doing it just kind of like script-based. And the fact that Chrissy going to all this effort and put together this public project gave us the standing to be able to say, “Hey look, we don’t think you’re doing it right and we think you should do it more like this.” And then they had a look at what Chrissy was recommending. And it took them a, you know day or two of deliberating but they finally came back and said, “You know, yep, that sounds right.” We’re going to do it like you suggest. And I had an article published in and SQL tips just last week on how to deploy the SQL Server, SQL performance dashboard from the Tiger team using only Power Shell. It will download the reports for you with the module, download the module for the SSRS commands, download the SQL file, deploy the reports, deploy the SQL file that you need to be able to get all these information on this performance matrix. And it’ll run across your entire Enterprise for you. One script then it does it all, and very excited to make it that much easier for people to use this free tools. Like the Tiger team spent this time getting it out there so that people could use it. And then we’ve just share, greatly upped the number of people who were able to download and used it. They actually told me the number I don’t think I’m supposed to share it but it’s a big number.

Carlos: So this is kind of where again you know my mind, the knuckle-dragging Neanderthal that I am. You know, I hear, “Power Shell bla bla bla. Oh one script it will do all of these.” “Oh okay. Now, all of a sudden I’m very very interested.” And you know, ultimately for me it’s lot about that value right instead of having to do these ten things right that I have to do before, yeah ability to have one script, one way to do it and execute it that, that’s really cool.

Constantine: Sure, so yeah what I basically wanted to bring up was talking a little bit more about that value because, and that reusability, because I have you know run many scripts within the community within the SQL Server community there’s the blitz scripts, there’s all these scripts, there’s all sort of pieces that are reproducible and reusable but there’s still a lot of code going on. And there’s always the question of what is that code quality? And so a lot of people use some sort of social currency to determine, “Oh everyone’s says all these scripts are good I can use that. It won’t destroy my environment. But I’m not going to necessarily review every line of code.” And when you see a project like this moving your old SQL server, it is really important to have that kind of currency because if everyone is doing it the same way, if everyone’s using that option SMMS, you can too. It is okay. And yeah that’s really why one of the big reasons I like DBA tool become popular is because we can kind of encode the best practices for the community and if they don’t think those are the best they can come back and help us fix them.

Chrissy: Absolutely.

Steve: So then with that process if someone decides to jump in and help you fix it and then they do the work, they do a pull request, do most of those type of things make it in to the project? Or do you end up weeding out a lot of those along the way based off of the code that you process?

Chirssy: It is very important to our team that people feel welcomed to DBA tools so if you go to dbatools.io/team it actually says “The team is me. The team is you. We really encourage pull requests.” So if somebody has, if they submit a pull request that doesn’t quite meet our standards what we will do is kindly mentor them and say and you know, I really emphasize like hey be nice to new people. And people, you know the team really jumps in and they are super nice and they’re like hey you could’ve done it this way. What about thinking about it from this perspective and you can go through any of our pull request and see that. So in addition to them being able to you know like submit the pull request they also get to learn along the way. And Constantine, it was so amazing working with them because like he had said, he hadn’t had a lot of experience with Power Shell and I did his code review for him. And he said that in one code review he had learned about two years’ worth of Power Shell knowledge.

Constantine: That’s not a joke either. I had spent you know, not directed effort but 2 years picking up random pieces of Power Shell and putting them into production even in some cases, and then sat down and Chrissy basically, in the most polite way, set me straight. And said “Here’s things you want to do and here’s why.” And I spent 2 weeks just reading the code she had given me versus the code I had submitted. And learned so much, it’s why I’m here today.

Carlos: Wow, that’s really spectacular because that’s not always the norm in the open source community or even in GitHub. And I think that the way, the way you’re doing that is spectacular.

Aaron: I promised you this is the best code ever used and that you will ever have. You come out and feeling good even though somebody like of ripped your code to bits, it would’ve been done in a way that says “Okay, that’s not how we do things we’ve learned along the way that there’s a better way of doing things or this is more perform well if you look at this. And we put examples of those up on, on the website as well but we’re very much about team and then community and family. It’s supposed to be fun. We’re doing this in our spare time so you don’t want to feel like you’re under a boss who is glaring at you with all this highness. It’s something that we pride ourselves on.

Chrissy: Yeah it’s very important that people feel welcome then any time that we have to close a pull request, if I had to guess, I think that we’re up to over 500 pull requests. And maybe 5 at most have been closed without being merged. That’s the part that hurts and it does you know like “Hey we actually have this command. It’s this one”. And so it is important to us that people do feel welcomed and that their codes gets merged in. We actually, anytime that we make a release, you’ll see it we added 4 new contributors to the repository. We want people to understand GitHub that we want people to understand Power Shell and we want them to feel like a part of the community and to not be you know a just because they don’t necessarily know Power Shell right away.

Chrissy: Oh nice. Very nice.

Aaron: And part of that as a team. Yeah we’ve got some people who know good Power Shell and we’ve got some amazing T-SQL and DBA people. But we also need people who can write documentation. We need people who can help with testing and continuous integration and all those other parts of it. So we we’re not just about writing some code, we’re about writing the good code for you. We were making sure that when it goes out there this is good as it can possibly be or from a community in our spare time project.

Steve: So as you mentioned the community and the spare time side of it. One thing that I see with the lot of projects like that is that they tend to wander a bit. But I’m curious with the DBA tools. Do you have a vision or a goal of where you see this going? Like a year from now or 2 years from now?

Chrissy: Yeah, I do. I would like it to be the standard in everyone’s toolkit. I would like hundreds of commands. We are currently at I think a 175. We’ve only announced about 150 because the process to make the webpages and everything like that it takes a little bit. But we are already setting the platform for being able to explore hundreds of commands. So with exchange, their module that came from Microsoft and SharePoint, their module that came from Microsoft, they have 700 commands. And currently I think the SQL Server module has a little less than 100. We are making it so that they’re very easy to find. There’s going to be a lot of tagging with categories through their website. But I do expect that this will be part of and I’ve been told but really kind people who are emailing me saying I’ve seen this become, it’s becoming a standard for DBAs. And that’s what I would really like because it does, it simplifies database administration and for me it makes it super exciting. It’s fun. I don’t have to like with test DBAs last backup. It’s no longer a burden to have to test my backups. It’s so fun to sit there and watch it work. It, you know it’ll perform a restore and then it will perform a verify and then it’ll make sure or sorry, checkDB or check table. It’ll make sure that everything works and then it’ll drop it and it just does all the work for you. And you sit there and you like man I enjoyed testing my backups.

Aaron: I’ve got a nice fuzzy feeling that everything is okay.

Chrissy: I can sleep at night. It’s true, it’s so nice.

Carlos: Without adding to my workload right and then I have to stay late at all.

Chrissy: Yeah you can automate this. I automate my SQL server migrations. I set that on a scheduled task and then I check it in the morning. It’s amazing.

Steve: Very interesting.

Carlos: I think I’m going to have to watch the first one.

Chrissy: Yeah you watch the first one, you watch the second one, the third one, you grab on a beer you like whatever. I’ll comeback. It worked to, a few times I’m cool with it. That’s where I’m at now.

Steve: Yep. It’s really interesting because you always hear about how the role of the SQL server DBA is changing over time. And about how people always need to learn more and keep up one the latest and build more into their toolset to be able to be more productive and not fall along way as things evolve. And it seems like the DBA tool set here would be a really good way of keeping up and keeping up your skills and continuing to grow as a DBA.

Chrissy: Absolutely and you, not only because of Power Shell but what’s really, what I really love about this is that it’s the whole DevOps process. For a while we were most of the DevOps process but we didn’t have testing and then Rob came in and like put in like a whole weekends worth of work and now we even have pester test running across the board on every single one of our commands. And so if you want to learn PowerShell, GitHub, and DevOps this is really an ideal project to work on.

Steve: Very nice.

Carlos: Very cool. Well we do appreciate the conversation. I know that we want to continue with some of the community things but should we go ahead and do SQL family now?

Chrissy: Sure.

Carlos: So Chrissy, tell us how you first got started with SQL server.

Chrissy: Alright. I actually wrote this out because I have a terrible memory and I was thinking back, it was actually a good time. So back in 1999, I worked as a startup as a Linux and Windows administrator but our DBA wasn’t super helpful. He got let go and the Dev team asked me to fill in and honestly I have always been curious about SQL server. You know when you go to the text section of the bookstore was always my favorite, and I’d always see SQL books. And I was like man oh, you know Oracle and then I installed Oracle I was like nah. But then I installed SQL server and that was really awesome. I loved that I have always been primarily administrator but I’ve also like development and that really SQL server serves both of those needs. So I picked it up I loved it and I became a web developer and DBA for the startup and then I went from there.

Steve: Great. So if you could change one thing about SQL server, what would it be?

Chrissy: What would your guess that I would like to see more about with SQL server?

Carlos: I’d say Power Shell command.

Chrissy: Yeah, absolutely. I would actually love to see more Power Shell commands from Microsoft. I would like to see the module opensourced and put up on both GitHub and the Power Shell gallery. And in addition I would also like the SQL Server Management Studio, their grid little thing, it’s not nearly as powerful as PowerShell’s Out Grid view which allows not only sorting but filtering in so many ways. I would really like to see that brought into SQL Server Management Studio.

Carlos: Interesting. We’re actually going to have a conversation with the program manager of the SQL Server Management Studio so have to make sure he pass that along.

Steve: And I would agree that whole grid results set in the Management Studio is very limited.

Aaron: If you can imagine the question, do we have x in this table and your table is 30 columns wide. You, you do that query and put that entire grid here. You can type in and you’ll get your answer in seconds.

Chrissy: Not just the column but the yeah the data as well. It’s incredible.

Aaron: So within a 30 column wide or 50 column wide table and you need to you know, you’re asked it’s is this data released, you know. I’ve got email addresses in for example. You could search, find hundreds or search hundreds or thousands of rows in a single table just for the data in it in Out Grid view because it’s so quick.

Carlos: What’s the best piece of career advice you’ve ever received?

Chrissy: Back in the dot com. At that startup that I had previously mentioned, our CEO, he had 2 doctorates in engineering. I think it was like Mechanical Engineering and Computer Engineering from an Ivy League school. He didn’t mind us call him doctor, he was super awesome. He told me to drop out of college because he said this is the dot com and this will never happen again. So if you drop out now and you gained experience whenever this busts and it will, you will be able to get a job. And so I talked to my parents about it they weren’t excited and then I talked to him about it and I was like I don’t know. And he said, “How much do you think that you would make if you drop out, sorry, but once you graduated?” And so I told him and he said, “Okay, I’ll give you that.” And I was like, “Alright, bye.” And so I am, so I dropped out of school and then I went back years later when I could afford it. I got my Bachelors at the University of San Francisco and then after that I still because I knew that I could hold a full time job and go to school. I enrolled in my Masters program and I’m currently 3 classes away from getting my Masters. So that was actually my best career advice ever was to drop out and then go back whenever really whenever school suited me.

Carlos: Very cool.

Steve: Okay. So if you could have one superhero power, what would it be and why would you want it?

Chrissy: Alright, so my superhero power would be regeneration. And the reason I want it is because I’m a hypochondriac and I’m always worrying about getting some disease or getting to an accident. And if I had regeneration powers then I wouldn’t have to worry about that anymore because it would just fix itself.

Steve: Nice.

Episode 90: DBCC CheckDB

DBCC CheckDB.  It is a simple command, but one that can cause database contention, dba stress, confusing results, dba elation, and everything in between.  At one time we will all have to face the results of having a corruption issue and we’re delighted to have Sean McCown with us again to discuss the ins and outs of the issues of this command. If you can remember, Sean was our guest in Episode 41. He is an SQL Server MVP with more than 15 years experience.

We talk with Sean about how frequent issues we have with maintenance like we often we should run CheckDB on our databases or what happens when I can only check one database during my window. While we don’t end up discussing the command as much, we definitely review all aspects of making sure it can run in ALL environments.

 Episode Quote

“I would say that the bigger issue is probably going to be something like snapshot space for your disk”

“When the rubber hits the road I’m still running CheckDB and CheckTable but it’s all about the process behind it”

Listen to Learn

  • Pros and cons of DBCC CheckDB
  • Scenarios to do CheckDB on databases
  • Issues with CheckDB
  • Running CheckTable
  • Minion Tools

Sean on Twitter
Minion CheckDB

SQL Server in the News
Data Access Libraries can affect performance

About Sean McCown

Sean is an SQL Server MVP and the founder of MidnightDBA.com. He is into SQL Server since 1995 and has been working with various databases such as DB2, Oracle, Sybase among others. He is also an editor and the sole DBA expert for InfoWorld Magazine. He spoke in various engagements talking about backups, SSIS and PowerShell. Sean is also a co-owner and principal consultant of MidnightSQL Consulting and the author of the world famous Minion tools.

Transcription: Check DBCC

Carlos: Sean, welcome to the program.

Sean: Thanks, guys.

Carlos: Yes, nice to have you back. Episode 41, you were with us and since then you made a couple of changes, added Steve to the program, and it’s nice of you to come back and chat with us.

Sean: Is this my second or third time on?

Carlos: Oh, I thought it was just the second time.

Sean: It’s possible.

Carlos: Yeah, you’re just a popular guy. You’re just everywhere, here and there, everywhere.

Sean: Oh, God, I wish.

Carlos: Well, so today, we’re going to go backwards a little bit and we’re going to be talking about a command and then we’re going to back into the why we want to be using it and some of the issues around it, so our conversation is going to focus on DBCC CheckDB.

Sean: Oh, one of my favorites.

Carlos: There you go.

Sean: An oldie birdie.

Carlos: That’s right, and the infamous or what made, maybe not what made him but definitely what added to what Paul Randall is today. He likes to frequently give credit for building the CheckDB command, at least, the original one, I think it has gone through some iterations since then.

Sean: I mean he rebuilt it on 05. He didn’t build the original one that I’m aware of. He just did the major overhaul in SQL 2005, right?

Carlos: Oh, the way I remember him telling the story was that he and his team built it originally.

Steve: I think what I’d heard was closer to what Sean was saying, that he redo of it in 2005.

Sean: Yeah, because he was always talking about how it was really messy and how bunch of the error messages were kind of fubarred and they didn’t really give much info on how he went through and straighten all that stuff out in 2005.

Carlos: Got you. Ok, well then, there you go. So I stand corrected. So he made it a little bit better and so ultimately the question is why would we be wanting to run the DBCC CheckDB command?

Sean: Wow, that’s a big question. The general answer, the lightest answer is because stuff happens when you’re dealing with media and typically magnetic media. All kinds of things can go wrong in those rights whether those things that go wrong come from the magnetic media themselves or some kind of memory corruption, sometimes CPU errors can cause things since you can’t physically see it, right? It’s not like sending out a tray of food where you can see that the order served is in disarray, right? You need something to tell you when thing aren’t right or you’re going to be making very bad decisions and you need something that helps you fix on to. I mean, people take that kind of thing for granted but it’s just as important now as it ever was.

Steve: So then with the CheckDB process when you say when things aren’t right, I mean, what type of things are going to pop up that aren’t right that you are going to know about because of CheckDB?

Sean: Did you say what are some of the problems that you wouldn’t know about because of it?

Steve: No, no, what are the things that you’re going to find out about when you run CheckDB if you had problems that you maybe hadn’t run it in the past?

Sean: Right, so you can have problems like out of range errors, so you get data that is out of range for a specific column. That used to be really common in the lower version of SQL. I haven’t seen that kind of thing as much, so the data purity. So that used to be a really big thing when I was doing upgrades from like 70 to 2000, and from 2000 to 2005, and maybe even a little bit on 2005 but it was a lot better then. Where you got an upgrade and you start getting all these PK violations because they didn’t do such a good job in checking primary keys and there are things just flat out like page linking problems. Same thing happens, right, two pages don’t get linked properly and therefore when you go to run a query it can’t find the data it needs and you get an error because the link that it has on the page doesn’t point to anywhere. You know, and of course they are the same problems with index pages as well. Those are clearly the easier ones to find. And then of course then your disastrous ones where you’ve got problems and pages for, and the system pages for the database itself. There are different classes of those problems. I mean, Steve, you’re a big CheckDB guy so you know all about that kind of stuff.

Steve: Oh yeah. So I guess one of the things, I mean, when I see that oftentimes people are either not running CheckDB at all or they’re not running it frequent enough. And I guess frequent enough is one of those sort of variable terms. But, I mean, how often should people, and from your perspective, should people be checking their databases with CheckDB?

Sean: Well, you know, there’s actually a third one there. People, they don’t run it at all, they don’t run it often enough, or they run it and they don’t check the results. That’s a really big one. Well, I’m sure we’ll talk about that more later. But as far as how often should they be running it? Man, that varies so much. I mean, what does often enough mean? I would say if you’ve got a small database where you’ve got plenty of resources and really you’re not in danger of affecting a workload, run it two or three times a day. I mean, you’re not going to hurt anything, right? It’s not like re-indexing where it’s actually going to, or you could actually mess stuff up.

Steve: So then, like let’s say, though you’ve got a much larger database and it’s really not feasible to run it that frequent.

Sean: As often as you can.

Steve: I had conversations sometimes where people say, “Well, we can only run it, we can only afford to run it once a month on each database because things are just too big.” One of the things I commonly see there is that well they’re maybe running it once a month but they only have three weeks of full back ups. If they’re doing something like that they might not have back up history far enough to go back to where that last CheckDB was. How would you come back, I don’t know, how would you address that?

Sean: Are you baiting me?

Steve: No, no, those just come up in conversations. I was just saying what would you take beyond that when somebody can’t run it. I mean, it’s so infrequent that they don’t have a back up period long enough to go back in the database.

Sean: I know that. I’m just saying, I’m just asking if you’re baiting me because that’s one of the big situations that I was adamant about handling in Minion CheckDB. I wondered if you’re baiting me and to see how long I could go without saying Minion CheckDB.

Carlos: So let me jump in here I guess just for a second as the knuckle dragger Neanderthal of the panel here. We’ve talked about some of these scenarios. I don’t think we explicitly called out corruption although I think all of the scenarios that you listed could be classified as a corruption type issue. But the constraint or the reason people don’t want to run it, you talked about impact, because it has to read everything don’t we kind of get into a memory constraint issue? Because generally our databases are going to be larger than the memory that we have available so we’re going to be causing pressure because we’re going to be flushing things out of the buffer cache.

Sean: You know, usually the pressure I’ve seen for CheckDB isn’t as much memory as it is disk and some CPU running there as well depending on how much CheckDB is going to multithread of you’re on Enterprise. But I would say that the bigger issue is probably going to be something like snapshot space for your disk. I haven’t personally ever run out of memory when running a CheckDB.

Carlos: Well then what I mean by that is that the reason I can’t run it I’m going to affect my applications because now I’m flushing things out of the buffer cache that I need for my application because I’m running CheckDB.

Sean: No, because it’s going to take an internal snapshot and it really runs it against that internal snapshot. So really when we say it’s going to affect external application it’s going to be through CPU and disk. Let’s say that, one of the big scenarios especially on a really busy system with a really big database is that it’s going to run for quite some time. Let’s say it takes 5 or 6 hours to run a CheckDB which not only isn’t all that uncommon, it’s really not all that long for a really big database. And if you don’t have enough disk space then you’re going to expand that snapshot. It’s going to keep on expanding until it fills up the disk and then either it’s going to crash or it’s going to keep your database from being able to expand when it needs to and then you’re going to halt your processes and start rolling stuff back so that and some CPU area. Maybe Steve know something about this that I don’t but I just don’t see flushing the buffer as big of a deal because it’s running against that snapshot.

Carlos: Ok, well so then, I guess I need to ask another question because obviously there’s something about this process that I did not understand. When I hear snapshot, right, I generally think about I’m making, not necessarily a copy but as things change I’m going to keep the history of that, and then the database continues, and then my snapshot grows larger as more changes happen to the database.

Sean: Correct.

Carlos: This is what happens when I’m running DBCC CheckDB?

Sean: Yes. Only it’s internal snapshot that SQL itself runs. It creates a snapshot behind the scenes for you.

Carlos: So depending on the frequency of change in my database then that snapshot is going to increase and that is what’s going to cause me the disk outage that you mentioned.

Sean: Correct, and Steve was trying to say something in the background that I’m really interested on what he’s going to say.

Steve: Right, so what I was going to get at there was around the whole squeeze on memory like Carlos is talking about. The thing I’ve seen is when you’ve got a system where the database is significantly larger than what can fit in memory and queries generally being run that are doing the best they can to deal with the page life expectancy and keep, the right things are staying in the memory for them to run. Oftentimes, what I see with that is sort of if you chart the page life expectancy you’re sort of see the chart pattern where it grows and grows and grows until that CheckDB runs. And then that pushes everything out because everything is needed to be loaded in to be scanned, and the page life expectancy drops to zero when that CheckDB runs. I look at that usually as an indication to be good to add more memory to the system. However, that’s not always feasible in all environments.

Carlos: Ok. So it could affect the three biggies, right? It could affect memory, CPU and disk. But disk obviously because we’re pulling that whole entire database off of the disk and be the snapshotting process I’m getting reads and writes so that’s probably the biggest concern, the first concern potentially.

Sean: I really don’t think it pulls in the entire database at once. It probably goes on time.

Carlos: Oh sure.

Steve: No, it will bring it in page by page just as pages would be normally used. But as it’s doing that it’s pushing out something that hasn’t been used for a little bit through the normal buffer pool process. And you end up pushing a lot of pages out in that process that may have been reused had CheckDb not been run.

Sean: Sure, sure but that doesn’t necessarily mean it’s going to lead to memory pressure.

Steve: No, and I guess, I wasn’t saying that it’s going to push it to the point you’re running out of memory. I’m just saying it’s pushing to the point that you’re causing a lot more I/O because things have been pushed out of memory to make room for what’s being brought in when CheckDB gets run.

Sean: Sure.

Carlos: Yeah, exactly. I mean, so in a certain scenario so would it be fair to say in a sense you could compare that similar to almost like a restart? Because all that stuff just gets pushed out. Maybe not that extreme.

Sean: Sure, let’s go with that.

Steve: Ok, so then one of the things, I mean that comes up is a lot of people say, I can’t run DBCC CheckDB in my whole database. It’s not just good enough there. It’s not going to work. I mean, what are the main reasons you see that people say that or people are looking at they just can’t do it.

Sean: Right, so I’d say there are two big reasons I would say that I see for that. One of them is that that disk filling up that we talked about. Right, Because their databases is just too big and too busy and it won’t run in significant time that the snapshot doesn’t fill up. And that’s a really big problem. I’ve talked to lots of people who just flat out don’t run CheckDBs anymore because of that alone. And another one is that it just takes so long for it to run even if they can withstand the disk, it still just takes so long to run that you know, that’s a drain on resources, right, I mean, ultimately that is a drain on resources. And they just can’t have that resources drain for that long, or you know, they have a thing where they need to reboot now and then or something, something, something, right? They just can’t have a process that runs for literally two days.

Steve: Yeah, you got to exceed your maintenance window, right?  The shops talk about, okay, you can do all your maintenance kind of in this window, and then, always extra things, you’re taking away from the system, therefore, can’t do it.

Sean: Exactly.

Carlos: Okay, so earlier, one of the things you mentioned was that one of the problems is that people who are running CheckDB and they’re not checking the results. I know that if you click off CheckDB and then SQL Server Management Studio Window, you’re going to get results, are all going to show up red on your screen if you’re interactively running it. However, if you’ve got it running as a job what are your best options for knowing the failure or success of that CheckDB process?

Sean: Right, so you’ve got to look at the job itself, right, the job could fail itself and all this depends on how you’ve written your CheckDB routine, right? If you’re like on built in maintenance plans doesn’t it push it to the error log? I think you see those errors in the error log.

Carlos: That’s normally what I see, you run it as a job and that’s how I’m going to know that it failed, is in my job failed.

Sean: Right.

Steve: Right, and I know that whether it’s a maintenance plan or a job or just interactively running it, whenever a CheckDB hits a problem it does right to the error log.

Sean: Right, but.

Steve: There’s also going to be things that show up in your job history as a failure if it’s running as a job or a maintenance plan there.

Sean: Right, and now, I don’t know about you guys but I come from a lot of big environments where we’ve had, you know, a thousand servers, 2 thousand servers and tens and tens of thousands of databases. I don’t know about you guys but I really really want to search the log for something vaguely CheckDB related every week or every day or every something. And then not only that but then go inside of there and parse the results out and try to figure out exactly what the problem was, right? So that’s what I see most, right? The guys who have a little bit more on the ball will run it with table results flag which is better because now you can at least get the table results but you have to build on a whole process for that thing too because that whole table results thing has to be managed and it didn’t help. For the longest time table results was undocumented. Like, somebody was going to bring down the server with table result or something. I never did, I still don’t know why it was undocumented for so long. But the ones that are really on the ball we will run with table results and at least get in a table but like I said, you know, you have to build the whole process around that and it’s not just as easy as, “I’m going to run this with the table results flag and that’s it because I’ve got tens of thousands of databases now.” And to me, any process that I develop has to work against one and two thousand servers with tens of thousands of databases or it’s not going to do me any good for the five databases I have. Right, I mean, we’re being asked as DBA’s to do more, and more, and more and, you know, to spend my time going around and checking all of these things and then parsing the results. Who’s got time for that crap? You know?

Carlos: Yeah, and that’s really an interesting point there because the thing, I mean, I look at the DB, people talk about, “What’s the DBA job going to be in the future?” And I think that it’s something that’s always changing where we’re going with it. But I look at the people who maybe haven’t change in the last 15 years of SQL Server and they’re the kind of people who their job consist of everyday looking at error logs. And mainly scanning those error logs and determining are there any problems that you have to deal with. Whereas the DBAs who were evolving and going forward in the future are the ones who were building the automated processes so you don’t have to manually do that. That you can focus on real issues rather than reading error logs.

Sean: Yeah, because you know, there are two aspects. There’s discovering the problem, reading the problem, solving the problem. And so you don’t want to have to go out. You want this stuff to be as easy as possible especially with corruption. You want to know about it as soon as you possibly can and you want to know the nature of the issues as soon as you possibly can as well. I think that sending CheckDB results to the log is ridiculous. I think sending deadlock information to the log is ridiculous. Am I going to do with deadlock information in the log. I mean, I’ve got this XML graph kind of thing and taking up 37 lines in my log. What am I going to do with that?

Carlos: Sure. You know, it will be a little bit more proactive.

Sean: Just proactive but Microsoft needs to do a better job at making the stuff that we need to monitor accessible. I mean, one of the worst things they did in x events was make the whole thing XML driven. I was one of the worst they have done because the average DBA doesn’t know really anything at all about XML. And so it’s great that I can take that stuff and save it to a file. Now, I’ve got all this XML in a file what am I suppose to do with it. Now, DBAs have to go to a 2-week course just to be able to query the XML data out of there. Thanks for that. I could at least import server side tracing profile data into a table and query it. But here you got to go through some major hoops to get it in the same format. They haven’t done a good job about taking the stuff that we absolutely have to have and making it really accessible to people that are managing on a large scale.

Carlos: So Steve, I’m sensing that we should have asked our SQL Family question little sooner.

Steve: Yeah, that’s good point. We’ll come back to that at the end then.

Carlos: That’s right.

Steve: Yup, so then I guess back to the question around, “Are we baiting you?”, and that’s the time we might start baiting you on. I mean, now that we’ve talked about CheckDB and some of the pros and cons and difficulties and challenges. You’ve come out something here with your Minion CheckDB.

Carlos: Is there something better that we can run?

Sean: Yeah, I like to think it something better. I targeted Minion CheckDB for those large shops with DBAs that are super busy they don’t have time for searching through the logs or for building their own process with table results, and also for the super large databases where you don’t have time to run CheckDB, you can’t run CheckDB because your database is too big. So it was specifically designed with those guys in mind.

Carlos: Well, so now is it different from DBCC CheckDB? Or is it using the same command just kind of slicing it up and giving us more options.

Sean: Yeah, no, it’s all about the process. When the rubber hits the road I’m still running CheckDB and CheckTable but it’s all about the process behind it. For instance, let’s talk about one of the things that I’m particularly proud of process was. We’ll talk about the large database that Steve mentioned where they just don’t run CheckDB because their database is too big. So one of the things you can do with Minion is you can set it up to do a rotating CheckTable instead. So you can say, I want to do 5 CheckTables a night or 500 CheckTables a night until all the tables are done and then start over again. So you’re doing all of the objects. Because what is a CheckDB really? Is it a CheckDB or is it the equivalent operations?

Carlos: Right, all the pieces underneath.

Steve: Although it’s more than just CheckTable and all the tables.

Sean: Of course, it is.

Steve: I think CheckTable and all the tables get’s you started on a big amount of the areas where you’re most likely to have issues.

Sean: Yeah, but you can also run the other smaller operations like as well. But if you want to check your objects and you just can’t because it takes three days then do a hundred at a time, 500 at a time.

Carlos: So how do you get around that? I’ll use smaller numbers so I can wrap my head around it. So I have 10 tables and I can only do 5 at a time. So my maintenance window let’s just say is I can do 5 tables once a week. So it’s going to take me two weekends to get through all ten tables.

Sean: Yes.

Carlos: So when the change happens, so do 1 through 5, weekend number #1. Weekend #2 I’m doing 6 through 10 but corruption happens in Table #1. I’m not going to find that until the next day. I guess going back to Steve’s point, I just take that into consideration and make sure that my backups exceed the time that I can get the total database done.

Sean: Correct.

Steve: Go ahead Sean.

Sean: Another scenario that I do with that is I allow you to automatic remote CheckDBs. So you can set it up to every single night. It will take the latest backup and restore it to a remote box and run a CheckDB on it and send the results back to the prod box.

Carlos: Got you, which is will be awesome particularly maybe not if you have thousands but if you’re in the 20s, 50s, maybe of databases would be a nice way to do it.

Sean: Well, you don’t do that for every database. I mean, every database isn’t that big. But you do it on the ones that can’t do and the other ones you do local. And, you know, so I give you three or four options. Three options I think for restoring your backup remotely and I give you a number of options for the type of remote restore or the type of remote job you want to do. I mean, it’s incredibly configurable with just three, or four, or five columns worth of data in a settings table and then it just goes. I’ve got people doing it and they set it up and once they got the permissions work out and everything that was the biggest thing, right, it was the permissions between the two servers. Once they got that worked out they found that the process itself just runs.

Steve: So then Carlos, going back to your example of small 10 tables but you can only do half of them each week. And I think around that scenario if you’re only able to do half of them per week because that’s your maintenance window. If you were doing full CheckDB previously I think the likelihood of be you never going to actually do CheckDB so even if you’re only finding out a week behind or two weeks behind that something has been encountered that’s a problem, corruption for instance. It’s better to know about it within two weeks rather than never knowing about it.

Carlos: Or when it usually tells you, “Hey, I’m getting this error.”

Steve: Right, right, and you found out you had corruption 18 months ago and it just sort of crept through that amount of time.

Sean: Well, the cool thing is you can even set it up to run your important tables every night. So you could say, I want to run these guys on a schedule but I also want to run my really important tables every single day because these are the guys that I have to have, right? And it’s really easy to exclude tables or exclude schemas or something like that so you can really target it. And really importantly you can order the databases and order the tables as well. So in your maintenance window even if you’re doing all of your tables at once which is another scenario, right? We’ve got the concept of what I call, what do I call it, dynamic snapshots, so getting around that problem of your snapshot file filling up and just killing your CheckDB operation. Well, you can put your on snapshot on there so can create your own disk and put your own snapshot in there but if you don’t have a disk laying around that is big enough, you can switch to CheckTables. But even if you’re doing your own snapshot and CheckTables you’re still going to fill that up. So with dynamic snapshots, what we do is we allow you to say, you know what, every 30 minutes I want you to drop the snapshot and create a new one, and then continue doing the CheckTables.  So if you’re going to do a thousand tables and it’s going to take you say 7 hours to get through it. Every half hour it’s going to drop that snapshot assuming that it’s after an operation or if one table takes an hour and a half there is nothing I can do about that. if it’s over that half our it will drop that snapshot, create a new one, and then carry on with the rest of the tables and that’s going to keep you from filling up that snapshot space, that disk drive. That’s another scenario that we threw in there for this big database problem, right.

Steve: So one of the scenarios that I’ve come across a couple of times that has been challenging has been when you have a larger database that takes a while that run CheckDB against it, and so you’re trying to do CheckTables or other options. But then that one large database, although it’s got a few tables in it there’s one table that really takes up like that 95% of the entire database. So then you run CheckTable against that one really big table and it ends up taking almost as long as CheckDB would have taken to begin with. So are there any options or anything that you see of how you would deal with that differently? And then perhaps run it on another server?

Sean: Well, in that case, I mean how important is that table?

Steve: Well, usually when it’s that size it’s something that’s pretty darn important and it’s just that big because some software along the way wasn’t architected to correctly warehouse things or break it out into another tables.

Sean: Right, well I mean it could just be an archive table that’s sitting there and it doesn’t get data but once a day, or once a week, or once a month, or something. It’s not really that important of a table functionally. The data just needs to be there so there are two different scenarios that you’re talking about. You’re talking about the archive table that’s just sitting there getting bigger and bloating your CheckDBs. And the one that is an actually active table that they just refused to archive and, you know, we’ve all seen it, right? So in that first scenario it’s really just to exclude that table from your CheckTable process and only run it let’s say once a month or once every after two months or something. I mean, that’s really easy to do. The second one is a little bit harder because that is an active table and so you’ve got a couple of things you can do there. You could put it in its own file group and run CheckFileGorup. I like that solution a lot less because I don’t think you really gain anything from that. But yeah, you would either run the CheckDB remotely or you could as well run CheckTable against all of the other tables every day and only save that one for like the weekend or one a month or something like that if it’s going to take a really long time then do that one by itself. Or since it is 95% then run a full CheckDB or full Checktable process on the weekend or once every two weeks or something. But for your dailies, or even better yet you can give it, I want to say a perfunctory. But you could run it with physical only more often than doing a full data check. Just make sure that physical only will just make sure that all the page structures and everything are proper. It won’t bother checking any of the other data types stuff. So it’s a much faster check because it’s a lesser check. But at least you’ll know that the tables themselves have integrity and not necessarily the data. And I’ve got somebody doing that and by the way that’s really easy to do. And Minion is to say on Monday through Friday or on Weekdays I want to run this with physical only, on Saturday I want to run a full CheckTable on it.

Steve: Yup, ok. So another area that’s come up in the bait here and there as I’ve done things with the database corruption and what not is around doing DBCC CheckDB on TempDB. And that’s one that I’ve seen a lot of really intense arguments or one or the other whether you should do it or whether you should not do it. And I’m really just curious of what your thoughts are on checking TempDB.

Sean: Of course you should do it. You know, TempDB is central to everything that you do. I mean so much stuff uses TempDB these days. And if you’re pushing all of your Temp tables through there and a lot of your spill results and you’ve got corruption in there or more importantly you’ve got it on a disk that is causing corruption in there. And you’re going to now rely on the results from these operations. You need to know if TempDB is corrupt. I mean, what you do about it is for debate depending on what’s going to happen. I mean, of course, easiest thing or the only thing that you can really do is delete the files and restart SQL. You’ve got no choice, you can’t really run a pair on TempDB. But yeah, I think because that the data isn’t persistent it doesn’t mean that it can’t be creating corrupt results and just your entire environment and results and what not that are coming through.

Steve: Perfect. And although I tried to ask that question in a very middle of the road state of trying to not waste my opinion one way or the other. I completely agree with your answer there.

Sean: You did a good job.

Steve: And I think that next time I’m in an argument over this with someone I’ll say refer back to Episode 90 of the sqldatapartners podcast and there’s a brief discussion on that.    

Sean: You can tell them I said so, that’s right.

Carlos: I of course agree with you too Steve if that matters but I’m not sure I’ll get a vote.

Sean: You will get a vote we just may not count it.

Steve: Alright, so then I guess back on sort of the topic of the Minion CheckDB side of things. I mean, there’s a lot of people running CheckDB, there’s a lot of people running their own custom CheckDB scripts, there’s people that are running the Ola Hallengren’s database integrity script. So really what I’m trying to understand is who out there is going to get the most value out of the Minion CheckDB product versus one of these other ways of doing it.          

Sean: Dude, I hate to sound like that way but absolutely everybody. The amount of logging that we’ve thrown into this product is obscene. And I don’t mean obscene in a figurative. I mean it is obscene.

Carlos: So you mentioned some of that, so kind of downplayed it a little bit the role or the usefulness of going to the error log. So when you talk about reporting what does that mean? I’m getting an email message, there’s a dashboard, what does that mean?

Sean: So, it means that we store everything in log tables instead of in files or in the log. So we make it really easy to get to.

Carlos: So you can then query it. I mean, is there built in processing that’s going to help notify me like, “Hey, your biggest table has corruption in it.”

Sean: So we don’t get into the alerting business at this level. We save that for our Enterprise product. Because I don’t know how you want to alert. But I’ve made it as simple as possible. I mean, there’s even a flag that says to fail the job because we handle all the errors the job won’t fail so you have to tell us specifically I want to fail the job. Some people don’t want the job to fail. Some people have a separate process that go out there so there are network monitors that can consume SQL queries. Or maybe you want to add a separate step to the job that queries the table and says, “Email me if any errors occurred.” But the point is that we give the errors in a very consumable state. We even tell you how many allocation and consistency errors. We tell you how long it took. We tell you what the last status was. We tell you whether it was a remote job or not. We tell you what server it ran on. We give you an obscene amount of information. There are probably 40 or 50 columns in an hour log details table, logs for absolutely everything. And I would say that’s the biggest advantage that we have over everybody else is again in that process, in the processing of the information and the jobs because we even allow you to do things like, let’s say, that you’ve got even, you know, that you’re using Ola and you’ve got 300 database on a server. And they’re all relatively medium size, right? You have to do all those serially. Or you have to create different jobs and then put them in, 10 or 20 at a time in the parameters.

Carlos: Do the leg work to carve it up yourself.

Sean: To split that up so that you can run those concurrently. You can just, I mean, Minion is already multithreaded so you just say run 10 at a time and it will split them up and it will run 10 at a time.

Carlos: That’s bags the question, when do you start seeing, were you introducing problems by doing so many at the same time. Have you even seen any?

Sean: Like resource wise?

Carlos: Yeah. When is too many too many?

Sean: That’s going to depend on your server and everything involved.

Carlos: That’s fair, too shady.

Sean: I gave you a fully automatic machine gun. What you choose to shoot with it is your problem. One thing I despise is tools that try to be smarter than me. I may not ever use a multithreaded process because this one big server won’t support it most of the time. But then there’s one time when we’re down for our quarterly maintenance and they say, “You’ve got 2 hours to do anything you need while we do all of these other stuffs in our app boxes that hit the SQL box.” And I’ll say, “Ok, I’m going to run 5 CheckDBs at once and get them all done in 2 hours.” You know, because I’ve got the box to myself. You may not use that normally but you may use it three or four times a year when something like that comes up. And you know there’s just so much other stuff in there, right? You can do the same thing with that same rotation we talked about with CheckTables. You can do a set of tables and then do another set of tables and then do another set of tables. You can do CheckDBs that way too so you could rotate those 300 databases; 10 a week or 10 a night. Or you could say you can also run it base off of time. You can run CheckDBs for an hour a night and then just pick up where you left off with the next database tomorrow so it will just roll through all those guys on a timed basis as well.

Carlos: In that scenario is it keeping a snapshot. I’m getting a new snapshot, right? If it has to break the database up so I guess one hour a night, my process takes two hours, which means I need two nights to get through the whole database. I’m getting a new snapshot, right?

Sean: It depends.

Carlos: Oh, ok.

Sean: If you’re running time to CheckDBs which only work off of a rotation which makes sense of think about it then it’s only going to run the CheckDBs that it can do in that time. So you can’t CheckDB half a database.

Carlos: Got you, ok got you.

Sean: But if you’re running CheckTables then it will run through as many of the tables in an hour as it can. Then in the snapshot settings if you’re creating your own snapshot that is, right. If it’s an internal snapshot you have no say there. But If you’re creating your own snapshot then you can tell it to leave the snapshot or to delete the snapshot when it’s done, and then you’ll just get another one tomorrow so you’ve got options there.

Carlos: Another question I had and this goes to something. I want to say it was Grant Fritchey said it but it’s giving me a little bit of heartburn and that is when you have corruption his comment was, “You want to find out why it happened.” And this kind of goes back into their TempDB discussion and you mentioned the disks. So my question is, how do you normally go about identifying why corruption happens.

Sean: I would say that depends. There are a number of things that can cause corruption. Almost in my experience, most of the time it’s going to be a spinning disk. But it can be cause by you copying a database over the wire and it gets corrupted and the bits get corrupted over the wire. Or you’ve got bad memory and the memory is starting to go out and that will do it. I would say disk and memory are the top 2 but I see disk pretty more often.

Carlos: I think I’ve also seen one scenario, I guess, in that a client called me up. They were having some corruption and they’re trying to do like a big bulk import. I don’t know exactly what happened but it sounded like somebody just pushed a button and powered down, you know, hard stop the machine. Something like that caused the problem as well.

Sean: Right, and that can usually be boiled down to probably something being written incorrectly to the disk.

Carlos: Right, so some kind of transaction error or something.

Sean: Right. So to answer your question directly, I kind of preface it there. So to answer your question directly usually if I find like a corrupt table I’ll go on and fix it, and just kind of move on but watch it. Because things happen, stuff happens. But if I start seeing another corruption within a reasonable time say another month, maybe two then I’ll say, “Ok, we need to figure out because we’ve gotten two corruptions inside of a few weeks. And this is just too often.” So I’ll have one of the guys run a disk check and then I’ll have one of them run a memory check. You need to specifically check the disk, and specifically check the memory to make sure that they’re ok. Because you’re right, you do need to know where it is coming from. And I want to say most of the time that’s going to be disk. The thing is I’ve had them come back before and say, “Well, the disk came back clean.” I’m like, “Well, that’s great but you’ve corrupted three tables this week.” I had this conversation with somebody six months ago. “But you’ve corrupted three tables this week. The disk is not ok.” We just ran a full disk check. Well, either run it again or run a different check because you’re not ok. And after going back a few rounds with them they would get the vendor who would either show them a different way to do it or give them a different tool, and bam, the bad sector show up.

Carlos: Sure. There are some flag or some option that they didn’t have before that.

Sean: Check the controller. It could be the controller as well. But something there in the disk process, in the disk write process is not right. And if you stick to your guns, I’m saying this to the DBAs who are going to be listening, right. In this case, you got to stick to your guns. Data doesn’t just corrupt on its own. It just doesn’t get tired so you need to stick to your guns and don’t let them come back and say, “Oh well, we didn’t find anything.” Because you know what, I have found quite often that when they say they do something. Like these guys are network guys, “Oh well, we didn’t find anything.” They actually didn’t look.

Steve: That brings up an interesting story. I mean, I work for a client years ago where. Yeah, there was no reason that they could that they were getting corruption and it just happened. And after looking into it a little bit more, well, it turned out that it just happened because the SQL Server was running a desk without any kind of UPS or back up or anything like that. And every time there was a lightning storm, or a power outage or any kind of problem, it would basically just completely power the whole thing off. And surprise, surprise, you have magically a corruption at that point.

Sean: I had that same problem. One of my first DBA gigs, true DBA gigs was at Pilgrim’s Pride and they had SQL boxes and these were 65 boxes, but still. They had SQL boxes in the kill rooms and so it was just a SQL box sitting on a milk crate with a piece of plastic over it covered in splatters of blood.

Carlos: What?

Sean: And I was just like you’ve got to be kidding me.

Carlos: When you’re talking about kill rooms you’re talking about they’re killing turkeys in this.

Sean: Pilgrim’s Pride, well they process chicken.      

Steve: Oh boy.

Sean: Enterprise wide they process back then about 600,000 chickens an hour, 24/7.

Steve: Oh my gosh! So the server was in the same room.

Sean: The server was in the same room because they have to weigh the chicken at every stage so they want to know what’s the weight of feathers is, and what’s the weight of the guts is, they shift all that stuff out, right? The weight of the feed is, they got to get all that stuff out of this so they got to get the weight and find out exactly what’s what. Yeah, you’ve got to have a SQL box right there by the scales.

Carlos: There you go. So because I feel like I’m going to be left out if I don’t tell a story. Now, I can’t tell you directly back to corruption however being from Richmond, Virginia. Philip Morris, this is the headquarters for tobacco industry and it used to be, I think they’ve finally changed this policy because they were pro tobacco people you could smoke anywhere even in the server rooms. They had pretty good ventilation systems supposedly but that was always the fun story that people would tell is that, “You could walk anywhere and smoke and whatever tobacco product you could use it anywhere in the building.”

Sean: How gross? So to go back to your question a little bit we kind of got sidetrack a little bit and it’s just the nature of how I talk. I would say back to who would benefit from using Minion. You know, it’s out of the gate when you install it. It installs what the job and it installs with schedule. So if you’re not a very good DBA, you just hit the installer and it does everything and it will be up and running and you’ll have CheckDBs. But it’s also configurable for like the world’s greatest masters to come in there and tweak all the bells and whistles and get absolutely everything out of it they want. And one of the big advantages you get especially with all the logging is you get a history at your fingertips of all of the corruption you’ve ever had on that server in that database on that table. And you can say, “Has this ever happen in this table before?” And you can back and look. It gives you a huge advantage of something that you don’t get elsewhere. If you run the query to look at the last time the database was CheckDBed, SQL stores that information in a page. But it doesn’t store anything about CheckTables. So if you’ve got a database where you’re even rotating CheckTables every month and it takes you like 2 weeks or 4 weeks to get through all the tables. That database will show that it has never been CheckDBed eventhough it has. So having all of these here you could easily query, “Oh well, these tables were done here.” And you know when all of the tables have been done you can count that as a CheckDB.  So you could write and SP that tells you the last time it had a full CheckDB which was did all the tables get done last month then this was the last time the tables got done. SQL won’t ever know it but Minion will and we’re going to do more for that as we go through the versions. I mean, this is just version 1 and it was a very ambitious version. I mean, we’ve got so many features in there. It’s ridiculous. One of the things I like about it the most is how, I’d have to say something as corny as we crossed the finished line on a lot of these features. But say you’re doing a remote CheckDB and you’re restoring last night’s backup. On the prod box, I’ll show you in the log, in the status column that it’s restoring to that remote server and I’ll even give you the preset complete of the restore. And as it’s doing the CheckDB on that remote server I’ll even give you the preset complete of that CheckDB on the remote server here. So if you have 5 databases on Prod 1 then they are all being remote CheckDBed to five different servers. You will see the status and get the results back on prod one. You don’t have to go around to all those different servers and check all that stuff manually. You get it back on Prod where it belongs because that’s not a CheckDB of Dev 1 or QA 1. It’s a CheckDB of the Prod 1 database. It’s just being run on over there. So the results belong back on Prod 1. And when I want to see status that status belongs back on Prod 1. I shouldn’t have to go all over BFE to find this information. I think we’ve crossed the finish line really well on some of that stuff like that. We really made it as user friendly and as complete as we possibly can.

Steve: Ok, I think that’s a very in-depth answer to the question I asked but definitely good stuff there. 

Sean: You know I never shut up about my product. You bought that. Think I was a snake when you picked me up?

Steve: I think that wraps it up for all the questions we have to cover. Anything else you want to hit on before we move in to the SQL Family questions?

Sean: No, I better not. I’ll just go and go and go. I’m still so incredibly in love with CheckDB. Well, I’m in love with two things. I’m in love with CheckDB as a product and I’m in love with not having to be developing it anymore. It took me so long to write this and I’m just thrilled to be working on something else.

Carlos: I thought it was available earlier, and you mentioned like shipping it out like last week or.

Sean: February 1.

Carlos: February 1, yeah, so was that actually the first release?

Sean: Yes.

Carlos: Oh, got you. Ok, I guess I’ve just been seeing the stickers or whatever previous to it.

Sean: Yeah, we tease it a little bit. We had the stickers printed a long time ago because we know it was going to be released. I just didn’t know. It took me like a year, a year and a half to finish it, something like that. I mean, you know when I first started it was like, “Hey, CheckDB. This will be easy because there’s not nearly enough in there. There isn’t really much in there as there was in backup. What could possibly go wrong?” But then when you start adding multithreading and mind you this is T-SQL multithreading. I mean, I’m not breaking out in the .NET or Power Shell or anything. This is all done in T-SQL. So we’re doing multithreading, and then the rotations, and then the remote stuff and it adds up. I way over complicate everything so.

Carlos: Well, I heard an interesting thought and talking with one of the Microsoft guys, and all this rapid release things they talked about. And his comment was, “Releasing is the feature.”

Sean: Yeah, I like that.

Carlos: I’m like, you know, yeah because you just get kind of a trap, “Oh, it’s not good enough, it’s not…” You know what, let’s just release and will fix it later.

Steve: Alright, so let’s go on to the SQL Family questions then.

Sean: Do it!

Steve: So how did you first get started with SQL Server?

Sean: Man, I’ll give you a shorter version of the story as I can. So I was a French chef for many years and followed a French chef all around the world, and the latest bistro that we were in closed.

Carlos: This is in France or this is in the States?          

Sean: This was in the States. We were back here at that point. And I was like, “Man, I don’t know what I’m going to do. I’m not really looking forward to moving again.” Because chefs have to move around if you want to get any good experience. And I was kind of tired of moving around and I didn’t want to end up in a subpar hotel or something opening cans on the line. So one of the waiters said, “Well, you know, my sister works for Stream. And I was like, “Ok, great, a field magazine. Great.” And he goes, “No, no, they do tech support.” I was like, “What do I know about tech support? I’m a chef.” And he says, “You know, they’ll train you.” And I was like, “Oh, really?” So I had my own computer and ended up knowing a little bit more that I thought. And being in French kitchens, and being who I am a linguist and all that, I ended up working for HP on the French line. So I was French tech support for Windows for HP. And so I stayed there for a while and then left there and went to this young start up called Health Web and it was a SQL based product for health care where you could put all your patient information up online. And they were like one of the first ones that did this and now it’s everywhere. But they were one of the first ones who did this and I was their tech support. Well, the application was written really well because the guy really knew what he was doing. And so most of the problems that we had ended up being SQL related and this was like SQL 60 and 65. We had one customer at that time. They were hiring me ahead of time and so I had nothing to do. And there was this whole wall of shelves in the back room with all of these computer parts on there. So I built 4 or 5 boxes and put them under my desk and network them and just started learning SQL, and IIS, and Windows. And I didn’t really know that much, right, but I had a guy there who was willing to take the time to show me some stuff. He really mentored me. So I just sat there all day long with the SQL training kits, you know, the official training kits they give for the cert exams back then, and just went through those things and work all the examples and did them different ways. And every day when I came in I would break all my boxes down. All the way down from Windows, and reinstall Windows, reinstall SQL, create databases, load them with data, do their exercises and do the same thing tomorrow because repetition is king. I worked there for like a year and a half or so and every problem we ever had was always SQL related. It was never the application. So they got sold and they offered me a DBA job in Denver. And I was like, “I just bought a house 2 weeks ago. I’m not moving in Denver. I just bought a house. You could have told me that you were selling the company 2 weeks ago.” But it did kind of thought in my head because I had then looking for, you know, when we find out about the sell we have been looking for help desk gigs because I was help desk guy. That’s what I was. But when they came a couple of weeks later and said, “You could move to Denver and be a DBA.” I can’t move but then I was like, “Hey, why don’t I start looking for DBA gigs here.” And I ended up getting hired on at Pilgrim’s and being a helpdesk guy with a lot of lab time under my belt and just a little bit of actual and no real production trouble shooting, no real production database running just running it as a help desk guy answering a question every now and then and I was really pretty green if you ask me. I mean, looking back on it I was a new.

Carlos: We are all were.

Sean: We were all dumb in the mid 90s, right? But he hired me on the spot; he said that I was the best candidate they had ever seen. And I was like, “Really, me?” I guess because I read the book. And the rest is history, I kind of fell into databases. I wasn’t going to be a DBA. I wasn’t even going to be in computers.  I was going to be a chef my whole life.

Steve: Very interesting, French chef to SQL DBA.

Sean: Right, so that’s my story.

Carlos: Now, we talked a little bit about this earlier and perhaps we can revisit it. If you could change one thing about SQL Server what would it be?

Sean: Oh jeez, man, I would say the thing that’s most important to me most of the time is they don’t make information as easy to get at as they think they do or as they’d like even when they do something to help somebody, like the new clone database thing. If you ask me that kind of thing, that thing was half asked. I don’t like the way they do most of the stuff. But anyway, I would say make the information easier to get at. Like there is no reason whatsoever why last CheckDB date shouldn’t be in sys databases. There is no reason why you can’t have table properties and say the last CheckTable date or something like that. Some of this information is just so incredibly hard to get at. Why is it so hard to get table size? Why is it so hard to find some of this information? Even the last time an SP was updated or the last time a table was written to. They don’t make the stuff easy. You got to jump through some major hoops to get some of this stuff. So why isn’t this stuff easier to get at? Why do we have to jump through so many hoops or basic information about the database, right? And I think CheckDB information being the theme here is the best example of that. Why do I have to do this ridiculous DBCC page thing and parse and all of that crap to find out when the last time a database was CheckDBed. You know when it was. Put it in the sys databases and move on. Another one is why is it so hard to find out the last time a database was used. The last time somebody used the database or use a table or something like that to find out if the database is even still in use. SQL should be automatically keeping the list of all the applications that hit it so that you could easily query which applications have hit it and from what server? That stuff shouldn’t be a process that I have to write, SQL should just monitor that stuff and keep a history of it for you.

Steve: Yeah, definitely agree on that one.

Sean: And it should automatically have a firewall. You should be automatically be able to say. I only want these applications to be able to query SQL. Be able to lock anybody who’s not DBA out of every single access except through the intended application. I shouldn’t have to buy a third party product or write something really thick to do that for me. You should just have a list of applications that have connected. Let’s say, click, click, click, add one, I want you to do this, I want you to add this one to it and then that’s it. For anybody who’s not a DBA, DBAs can do whatever they want, but don’t let anybody hit it with Excel. Don’t let anybody hit it with Crystal Reports. That’s what the other boxes for. This one is for this application and that’s it.

Steve: Alright, so we’re into our last SQL Family question.

Sean: So you get me riled up about things.

Steve: We’re not trying very hard either.

Sean: I know, right, it’s so easy.

Steve: Yup. If you could have one superhero power what would it be and why would you want it?

Sean: Oh, God, what did I say last time, Carlos? Do you remember?

Carlos: I’ve had to look it up.

Sean: You know, right now, I’m going to say I wish. It’s split between two. And I really can’t decide which one. I would either want to be able to read minds.

Carlos: Oh yes, I’m pretty sure that’s what you chose all the dirty laundry that comes along with it.

Sean: Or I would want to be able to see details by the way. Not just as vague BS that you see in movies but details into the reasonable future. I don’t want to see vague shapes and colors and find out what that means as I get there, that’s ridiculous. I want to be able to see a day or a weekend to the future and know what’s going to happen and be able to be prepared. Imagine in our field how cool that would be, to know that a problem is coming and have seen the fix already and to be able to do something about it. You’d be a superstar in every sense of the world.

Carlos: Awesome, very rich stock picker as well. 

Sean: Yes exactly, that hadn’t crossed my mind.

Carlos: Warren Buffet it your heart out.

Sean: I know right.

Carlos: Well, awesome. Sean thanks so much for coming on the program today. It has been a blast as always.

Steve: Definitely been fun. Thanks, Sean.

Sean: I love this topic. I’d like to see you guys again for re-index. I’m starting now. And we didn’t even get to talk about the new stuff in Minion back up but we’ll save that for another one.

Carlos: There you go. You guys got another 45 episodes or so.

Sean: Yeah, exactly. You can’t make this the Sean show. Thanks a lot guys!

Steve: Alright, thank you.

Carlos: Thanks again, Sean.

Episode 89: Lone DBA

One of the neat benefits of putting the podcast together is talking to a wide spectrum of folks with varied backgrounds.  As we get more feedback from listeners, the idea of the Lone DBA emerged.  The Lone DBA is someone who is the only person responsible for database administration at their company.  These tasks may have been assigned or they may have simply been required to help support an application.  Steve and I thought we should explore this a bit and to help give us some context, we invited Monica Rathbun to chat with us about some of the challenges she’s faced.

Besides the challenges, Monica shares tactics and techniques to juggle many tasks while being a lone DBA. This includes using social media as a way of building connections with different experts, getting the right tools for the organization’s needs and making your boss your supporter. Have your own perspective on this?  Let us know in the show notes for the episode.

Episode Quotes

“To be a lone DBA, you’ve got to have 48 arms attached to you, doing 20 things at a time”
“It is important for the leaders in the community to mentor and pull people in”
“Making network connections gears you towards your future career”

Listen to Learn

  • Techniques, tactics and challenges of being a lone DBA
  • Using social media in building network connections
  • Managing huge amount of task as a lone DBA
  • Setting the right tools for the organization

US News DBA Job Outlook
Monica on Twitter
Monica’s Blog

SQL in the news
Near-real-time replication from SQL Server to cloud

About Monica Rathbun

Lone DBAMonica is currently a Sr. Database Administrator/BI Architect at Massimo Zanetti Beverage, USA in Norfolk Virginia. She has been a Lone DBA for 15 years, working with all aspects of SQL Server and Oracle. She is co-leader for the Hampton Roads SQL Server User Group and can be found on Twitter daily. When she’s not busy with work, you will find her playing taxi for her two daughters back and forth to dance classes.

Transcription: Lone DBA

Carlos: This is Carlos Chacon.

Steve: I’m Steve Stedman.

Sean: I’m Sean McCown.

Carlos: Sean, welcome to the program.

Sean: Thanks, guys.

Carlos: Yes, nice to have you back. Episode 41, you were with us and since then you made a couple of changes, added Steve to the program, and it’s nice of you to come back and chat with us.

Sean: Is this my second or third time on?

Carlos: Oh, I thought it was just the second time.

Sean: It’s possible.

Carlos: Yeah, you’re just a popular guy. You’re just everywhere, here and there, everywhere.

Sean: Oh, God, I wish.

Carlos: Well, so today, we’re going to go backwards a little bit and we’re going to be talking about a command and then we’re going to back into the why we want to be using it and some of the issues around it, so our conversation is going to focus on DBCC CheckDB.

Sean: Oh, one of my favorites.

Carlos: There you go.

Sean: An oldie birdie.

Carlos: That’s right, and the infamous or what made, maybe not what made him but definitely what added to what Paul Randall is today. He likes to frequently give credit for building the CheckDB command, at least, the original one, I think it has gone through some iterations since then.

Sean: I mean he rebuilt it on 05. He didn’t build the original one that I’m aware of. He just did the major overhaul in SQL 2005, right?

Carlos: Oh, the way I remember him telling the story was that he and his team built it originally.

Steve: I think what I’d heard was closer to what Sean was saying, that he redo of it in 2005.

Sean: Yeah, because he was always talking about how it was really messy and how bunch of the error messages were kind of fubarred and they didn’t really give much info on how he went through and straighten all that stuff out in 2005.

Carlos: Got you. Ok, well then, there you go. So I stand corrected. So he made it a little bit better and so ultimately the question is why would we be wanting to run the DBCC CheckDB command?

Sean: Wow, that’s a big question. The general answer, the lightest answer is because stuff happens when you’re dealing with media and typically magnetic media. All kinds of things can go wrong in those rights whether those things that go wrong come from the magnetic media themselves or some kind of memory corruption, sometimes CPU errors can cause things since you can’t physically see it, right? It’s not like sending out a tray of food where you can see that the order served is in disarray, right? You need something to tell you when thing aren’t right or you’re going to be making very bad decisions and you need something that helps you fix on to. I mean, people take that kind of thing for granted but it’s just as important now as it ever was.

Steve: So then with the CheckDB process when you say when things aren’t right, I mean, what type of things are going to pop up that aren’t right that you are going to know about because of CheckDB?

Sean: Did you say what are some of the problems that you wouldn’t know about because of it?

Steve: No, no, what are the things that you’re going to find out about when you run CheckDB if you had problems that you maybe hadn’t run it in the past?

Sean: Right, so you can have problems like out of range errors, so you get data that is out of range for a specific column. That used to be really common in the lower version of SQL. I haven’t seen that kind of thing as much, so the data purity. So that used to be a really big thing when I was doing upgrades from like 70 to 2000, and from 2000 to 2005, and maybe even a little bit on 2005 but it was a lot better then. Where you got an upgrade and you start getting all these PK violations because they didn’t do such a good job in checking primary keys and there are things just flat out like page linking problems. Same thing happens, right, two pages don’t get linked properly and therefore when you go to run a query it can’t find the data it needs and you get an error because the link that it has on the page doesn’t point to anywhere. You know, and of course they are the same problems with index pages as well. Those are clearly the easier ones to find. And then of course then your disastrous ones where you’ve got problems and pages for, and the system pages for the database itself. There are different classes of  those problems. I mean, Steve, you’re a big CheckDB guy so you know all about that kind of stuff.

Steve: Oh yeah. So I guess one of the things, I mean, when I see that oftentimes people are either not running CheckDB at all or they’re not running it frequent enough. And I guess frequent enough is one of those sort of variable terms. But, I mean, how often should people, and from your perspective, should people be checking their databases with CheckDB?

Sean: Well, you know, there’s actually a third one there. People, they don’t run it at all, they don’t run it often enough, or they run it and they don’t check the results. That’s a really big one. Well, I’m sure we’ll talk about that more later. But as far as how often should they be running it? Man, that varies so much. I mean, what does often enough mean? I would say if you’ve got a small database where you’ve got plenty of resources and really you’re not in danger of affecting a workload. Hell, run it two or three times a day. I mean, you’re not going to hurt anything, right? It’s not like re-indexing where it’s actually going to, or you could actually mess stuff up.

Steve: So then, like let’s say, though you’ve got a much larger database and it’s really not feasible to run it that frequent.

Sean: As often as you can.

Steve: I had conversations sometimes where people say, “Well, we can only run it, we can only afford to run it once a month on each database because things are just too big.” One of the things I commonly see there is that well they’re maybe running it once a month but they only have three weeks of full back ups. If they’re doing something like that they might not have back up history far enough to go back to where that last CheckDB was. How would you come back, I don’t know, how would you address that?

Sean: Are you baiting me?

Steve: No, no, those just come up in conversations. I was just saying what would you take beyond that when somebody can’t run it. I mean, it’s so infrequent that they don’t have a back up period long enough to go back in the database.

Sean: I know that. I’m just saying, I’m just asking if you’re baiting me because that’s one of the big situations that I was adamant about handling in Minion CheckDB. I wondered if you’re baiting me and to see how long I could go without saying Minion CheckDB.

Carlos: So let me jump in here I guess just for a second as the knuckle dragger Neanderthal of the panel here. We’ve talked about some of these scenarios. I don’t think we explicitly called out corruption although I think all of the scenarios that you listed could be classified as a corruption type issue. But the constraint or the reason people don’t want to run it, you talked about impact, because it has to read everything don’t we kind of get into a memory constraint issue? Because generally our databases are going to be larger than the memory that we have available so we’re going to be causing pressure because we’re going to be flushing things out of the buffer cache.

Sean: You know, usually the pressure I’ve seen for CheckDB isn’t as much memory as it is disk and some CPU running there as well depending on how much CheckDB is going to multithread of you’re on Enterprise. But I would say that the bigger issue is probably going to be something like snapshot space for your disk. I haven’t personally ever run out of memory when running a CheckDB.

Carlos: Well then what I mean by that is that the reason I can’t run it I’m going to affect my applications because now I’m flushing things out of the buffer cache that I need for my application because I’m running CheckDB.

Sean: No, because it’s going to take an internal snapshot and it really runs it against that internal snapshot. So really when we say it’s going to affect external application it’s going to be through CPU and disk. Let’s say that, one of the big scenarios especially on a really busy system with a really big database is that it’s going to run for quite some time. Let’s say it takes 5 or 6 hours to run a CheckDB which not only isn’t all that uncommon, it’s really not all that long for a really big database. And if you don’t have enough disk space then you’re going to expand that snapshot. It’s going to keep on expanding until it fills up the disk and then either it’s going to crash or it’s going to keep your database from being able to expand when it needs to and then you’re going to halt your processes and start rolling stuff back so that and some CPU area. Maybe Steve know something about this that I don’t but I just don’t see flushing the buffer as big of a deal because it’s running against that snapshot.

Carlos: Ok, well so then, I guess I need to ask another question because obviously there’s something about this process that I did not understand. When I hear snapshot, right, I generally think about I’m making, not necessarily a copy but as things change I’m going to keep the history of that, and then the database continues, and then my snapshot grows larger as more changes happen to the database.

Sean: Correct.

Carlos: This is what happens when I’m running DBCC CheckDB?

Sean: Yes. Only it’s internal snapshot that SQL itself runs. It creates a snapshot behind the scenes for you.

Carlos: So depending on the frequency of change in my database then that snapshot is going to increase and that is what’s going to cause me the disk outage that you mentioned.

Sean: Correct, and Steve was trying to say something in the background that I’m really interested on what he’s going to say.

Steve: Right, so what I was going to get at there was around the whole squeeze on memory like Carlos is talking about. The thing I’ve seen is when you’ve got a system where the database is significantly larger than what can fit in memory and queries generally being run that are doing the best they can to deal with the page life expectancy and keep, the right things are staying in the memory for them to run. Oftentimes, what I see with that is sort of if you chart the page life expectancy you’re sort of see the chart pattern where it grows and grows and grows until that CheckDB runs. And then that pushes everything out because everything is needed to be loaded in to be scanned, and the page life expectancy drops to zero when that CheckDB runs. I look at that usually as an indication to be good to add more memory to the system. However, that’s not always feasible in all environments.

Carlos: Ok. So it could affect the three biggies, right? It could affect memory, CPU and disk. But disk obviously because we’re pulling that whole entire database off of the disk and be the snapshotting process I’m getting reads and writes so that’s probably the biggest concern, the first concern potentially.

Sean: I really don’t think it pulls in the entire database at once. It probably goes on time.

Carlos: Oh sure.

Steve: No, it will bring it in page by page just as pages would be normally used. But as it’s doing that it’s pushing out something that hasn’t been used for a little bit through the normal buffer pool process. And you end up pushing a lot of pages out in that process that may have been reused had CheckDb not been run.

Sean: Sure, sure but that doesn’t necessarily mean it’s going to lead to memory pressure.

Steve: No, and I guess, I wasn’t saying that it’s going to push it to the point you’re running out of memory. I’m just saying it’s pushing to the point that you’re causing a lot more I/O because things have been pushed out of memory to make room for what’s being brought in when CheckDB gets run.

Sean: Sure.

Carlos: Yeah, exactly. I mean, so in a certain scenario so would it be fair to say in a sense you could compare that similar to almost like a restart? Because all that stuff just gets pushed out. Maybe not that extreme.

Sean: Sure, let’s go with that.

Steve: Ok, so then one of the things, I mean that comes up is a lot of people say, I can’t run DBCC CheckDB in my whole database. It’s not just good enough there. It’s not going to work. I mean, what are the main reasons you see that people say that or people are looking at they just can’t do it.

Sean: Right, so I’d say there are two big reasons I would say that I see for that. One of them is that that disk filling up that we talked about. Right, Because their databases is just too big and too busy and it won’t run in significant time that the snapshot doesn’t fill up. And that’s a really big problem. I’ve talked to lots of people who just flat out don’t run CheckDBs anymore because of that alone. And another one is that it just takes so long for it to run even if they can withstand the disk, it still just takes so long to run that you know, that’s a drain on resources, right, I mean, ultimately that is a drain on resources. And they just can’t have that resources drain for that long, or you know, they have a thing where they need to reboot now and then or something, something, something, right? They just can’t have a process that runs for literally two days.

Steve: Yeah, you got to exceed your maintenance window, right?  The shops talk about, okay, you can do all your maintenance kind of in this window, and then, always extra things, you’re taking away from the system, therefore, can’t do it.

Sean: Exactly.

Carlos: Okay, so earlier, one of the things you mentioned was that one of the problems is that people who are running CheckDB and they’re not checking the results. I know that if you click off CheckDB and then SQL Server Management Studio Window, you’re going to get results, are all going to show up red on your screen if you’re interactively running it. However, if you’ve got it running as a job what are your best options for knowing the failure or success of that CheckDB process?

Sean: Right, so you’ve got to look at the job itself, right, the job could fail itself and all this depends on how you’ve written your CheckDB routine, right? If you’re like on built in maintenance plans doesn’t it push it to the error log? I think you see those errors in the error log.

Carlos: That’s normally what I see, you run it as a job and that’s how I’m going to know that it failed, is in my job failed.

Sean: Right.

Steve: Right, and I know that whether it’s a maintenance plan or a job or just interactively running it, whenever a CheckDB hits a problem it does right to the error log.

Sean: Right, but.

Steve: There’s also going to be things that show up in your job history as a failure if it’s running as a job or a maintenance plan there.

Sean: Right, and now, I don’t know about you guys but I come from a lot of big environments where we’ve had, you know, a thousand servers, 2 thousand servers and tens and tens of thousands of databases. I don’t know about you guys but I really really want to search the log for something vaguely CheckDB related every week or every day or every something. And then not only that but then go inside of there and parse the results out and try to figure out exactly what the problem was, right? So that’s what I see most, right? The guys who have a little bit more on the ball will run it with table results flag which is better because now you can at least get the table results but you have to build on a whole process for that thing too because that whole table results thing has to be managed and it didn’t help. For the longest time table results was undocumented. Like, somebody was going to bring down the server with table result or something. I never did, I still don’t know why it was undocumented for so long. But the ones that are really on the ball we will run with table results and at least get in a table but like I said, you know, you have to build the whole process around that and it’s not just as easy as, “I’m going to run this with the table results flag and that’s it because I’ve got tens of thousands of databases now.” And to me, any process that I develop has to work against one and two thousand servers with tens of thousands of databases or it’s not going to do me any good for the five databases I have. Right, I mean, we’re being asked as DBA’s to do more, and more, and more and, you know, to spend my time going around and checking all of these things and then parsing the results. Who’s got time for that crap? You know?

Carlos: Yeah, and that’s really an interesting point there because the thing, I mean, I look at the DB, people talk about, “What’s the DBA job going to be in the future?” And I think that it’s something that’s always changing where we’re going with it. But I look at the people who maybe haven’t change in the last 15 years of SQL Server and they’re the kind of people who their job consist of everyday looking at error logs. And mainly scanning those error logs and determining are there any problems that you have to deal with. Whereas the DBAs who were evolving and going forward in the future are the ones who were building the automated processes so you don’t have to manually do that. That you can focus on real issues rather than reading error logs.

Sean: Yeah, because you know, there are two aspects. There’s discovering the problem, reading the problem, solving the problem. And so you don’t want to have to go out. You want this stuff to be as easy as possible especially with corruption. You want to know about it as soon as you possibly can and you want to know the nature of the issues as soon as you possibly can as well. I think that sending CheckDB results to the log is ridiculous. I think sending deadlock information to the log is ridiculous. The hell am I going to do with deadlock information in the log. I mean, I’ve got this XML graph kind of thing and taking up 37 lines in my log. What am I going to do with that?

Carlos: Sure. You know, it will be a little bit more proactive.

Sean: Just proactive but Microsoft needs to do a better job at making the stuff that we need to monitor accessible. I mean, one of the worst things they did in x events was make the whole thing XML driven. I was one of the worst they have done because the average DBA doesn’t know really anything at all about XML. And so it’s great that I can take that stuff and save it to a file. Now, I’ve got all this XML in a file what the hell am I suppose to do with it. Now, DBAs have to go to a 2-week course just to be able to query the XML data out of there. Thanks for that. I could at least import server side tracing profile data into a table and query it. But here you got to go through some major hoops to get it in the same format. They haven’t done a good job about taking the stuff that we absolutely have to have and making it really accessible to people that are managing on a large scale.

Carlos: So Steve, I’m sensing that we should have asked our SQL Family question little sooner.

Steve: Yeah, that’s good point. We’ll come back to that at the end then.

Carlos: That’s right.

Steve: Yup, so then I guess back to the question around, “Are we baiting you?”, and that’s the time we might start baiting you on. I mean, now that we’ve talked about CheckDB and some of the pros and cons and difficulties and challenges. You’ve come out something here with your Minion CheckDB.

Carlos: Is there something better that we can run?

Sean: Yeah, I like to think it something better. I targeted Minion CheckDB for those large shops with DBAs that are super busy they don’t have time for searching through the logs or for building their own process with table results, and also for the super large databases where you don’t have time to run CheckDB, you can’t run CheckDB because your database is too big. So it was specifically designed with those guys in mind.

Carlos: Well, so now is it different from DBCC CheckDB? Or is it using the same command just kind of slicing it up and giving us more options.

Sean: Yeah, no, it’s all about the process. When the rubber hits the road I’m still running CheckDB and CheckTable but it’s all about the process behind it. For instance, let’s talk about one of the things that I’m particularly proud of process was. We’ll talk about the large database that Steve mentioned where they just don’t run CheckDB because their database is too big. So one of the things you can do with Minion is you can set it up to do a rotating CheckTable instead. So you can say, I want to do 5 CheckTables a night or 500 CheckTables a night until all the tables are done and then start over again. So you’re doing all of the objects. Because what is a CheckDB really? Is it a CheckDB or is it the equivalent operations?

Carlos: Right, all the pieces underneath.

Steve: Although it’s more than just CheckTable and all the tables.

Sean: Of course, it is.

Steve: I think CheckTable and all the tables get’s you started on a big amount of the areas where you’re most likely to have issues.

Sean: Yeah, but you can also run the other smaller operations like as well. But if you want to check your objects and you just can’t because it takes three days then do a hundred at a time, 500 at a time.

Carlos: So how do you get around that? I’ll use smaller numbers so I can wrap my head around it. So I have 10 tables and I can only do 5 at a time. So my maintenance window let’s just say is I can do 5 tables once a week. So it’s going to take me two weekends to get through all ten tables.

Sean: Yes.

Carlos: So when the change happens, so do 1 through 5, weekend number #1. Weekend #2 I’m doing 6 through 10 but corruption happens in Table #1. I’m not going to find that until the next day. I guess going back to Steve’s point, I just take that into consideration and make sure that my backups exceed the time that I can get the total database done.

Sean: Correct.

Steve: Go ahead Sean.

Sean: Another scenario that I do with that is I allow you to automatic remote CheckDBs. So you can set it up to every single night. It will take the latest backup and restore it to a remote box and run a CheckDB on it and send the results back to the prod box.

Carlos: Got you, which is will be awesome particularly maybe not if you have thousands but if you’re in the 20s, 50s, maybe of databases would be a nice way to do it.

Sean: Well, you don’t do that for every database. I mean, every database isn’t that big. But you do it on the ones that can’t do and the other ones you do local. And, you know, so I give you three or four options. Three options I think for restoring your backup remotely and I give you a number of options for the type of remote restore or the type of remote job you want to do. I mean, it’s incredibly configurable with just three, or four, or five columns worth of data in a settings table and then it just goes. I’ve got people doing it and they set it up and once they got the permissions work out and everything that was the biggest thing, right, it was the permissions between the two servers. Once they got that worked out they found that the process itself just runs.

Steve: So then Carlos, going back to your example of small 10 tables but you can only do half of them each week. And I think around that scenario if you’re only able to do half of them per week because that’s your maintenance window. If you were doing full CheckDB previously I think the likelihood of be you never going to actually do CheckDB so even if you’re only finding out a week behind or two weeks behind that something has been encountered that’s a problem, corruption for instance. It’s better to know about it within two weeks rather than never knowing about it.

Carlos: Or when it usually tells you, “Hey, I’m getting this error.”

Steve: Right, right, and you found out you had corruption 18 months ago and it just sort of crept through that amount of time.

Sean: Well, the cool thing is you can even set it up to run your important tables every night. So you could say, I want to run these guys on a schedule but I also want to run my really important tables every single day because these are the guys that I have to have, right? And it’s really easy to exclude tables or exclude schemas or something like that so you can really target it. And really importantly you can order the databases and order the tables as well. So in your maintenance window even if you’re doing all of your tables at once which is another scenario, right? We’ve got the concept of what I call, what the hell do I call it, dynamic snapshots, so getting around that problem of your snapshot file filling up and just killing your CheckDB operation. Well, you can put your on snapshot on there so can create your own disk and put your own snapshot in there but if you don’t have a disk laying around that is big enough, you can switch to CheckTables. But even if you’re doing your own snapshot and CheckTables you’re still going to fill that up. So with dynamic snapshots, what we do is we allow you to say, you know what, every 30 minutes I want you to drop the snapshot and create a new one, and then continue doing the CheckTables.  So if you’re going to do a thousand tables and it’s going to take you say 7 hours to get through it. Every half hour it’s going to drop that snapshot assuming that it’s after an operation or if one table takes an hour and a half there is nothing I can do about that. if it’s over that half our it will drop that snapshot, create a new one, and then carry on with the rest of the tables and that’s going to keep you from filling up that snapshot space, that disk drive. That’s another scenario that we threw in there for this big database problem, right.

Steve: So one of the scenarios that I’ve come across a couple of times that has been challenging has been when you have a larger database that takes a while that run CheckDB against it, and so you’re trying to do CheckTables or other options. But then that one large database, although it’s got a few tables in it there’s one table that really takes up like that 95% of the entire database. So then you run CheckTable against that one really big table and it ends up taking almost as long as CheckDB would have taken to begin with. So are there any options or anything that you see of how you would deal with that differently? And then perhaps run it on another server?

Sean:  Well, in that case, I mean how important is that table?

Steve: Well, usually when it’s that size it’s something that’s pretty darn important and it’s just that big because some software along the way wasn’t architected to correctly warehouse things or break it out into another tables.

Sean: Right, well I mean it could just be an archive table that’s sitting there and it doesn’t get data but once a day, or once a week, or once a month, or something. It’s not really that important of a table functionally. The data just needs to be there so there are two different scenarios that you’re talking about. You’re talking about the archive table that’s just sitting there getting bigger and bloating your CheckDBs. And the one that is an actually active table that they just refused to archive and, you know, we’ve all seen it, right? So in that first scenario it’s really just to exclude that table from your CheckTable process and only run it let’s say once a month or once every after two months or something. I mean, that’s really easy to do. The second one is a little bit harder because that is an active table and so you’ve got a couple of things you can do there. You could put it in its own file group and run CheckFileGorup. I like that solution a lot less because I don’t think you really gain anything from that. But yeah, you would either run the CheckDB remotely or you could as well run CheckTable against all of the other tables every day and only save that one for like the weekend or one a month or something like that if it’s going to take a really long time then do that one by itself. Or since it is 95% then run a full CheckDB or full Checktable process on the weekend or once every two weeks or something. But for your dailies, or even better yet you can give it, I want to say a perfunctory. But you could run it with physical only more often than doing a full data check. Just make sure that physical only will just make sure that all the page structures and everything are proper. It won’t bother checking any of the other data types stuff. So it’s a much faster check because it’s a lesser check. But at least you’ll know that the tables themselves have integrity and not necessarily the data. And I’ve got somebody doing that and by the way that’s really easy to do. And Minion is to say on Monday through Friday or on Weekdays I want to run this with physical only, on Saturday I want to run a full CheckTable on it.

Steve: Yup, ok. So another area that’s come up in the bait here and there as I’ve done things with the database corruption and what not is around doing DBCC CheckDB on TempDB. And that’s one that I’ve seen a lot of really intense arguments or one or the other whether you should do it or whether you should not do it. And I’m really just curious of what your thoughts are on checking TempDB.

Sean: Of course you should do it. You know, TempDB is central to everything that you do. I mean so much stuff uses TempDB these days. And if you’re pushing all of your Temp tables through there and a lot of your spill results and you’ve got corruption in there or more importantly you’ve got it on a disk that is causing corruption in there. And you’re going to now rely on the results from these operations. You need to know if TempDB is corrupt. I mean, what you do about it is for debate depending on what’s going to happen. I mean, of course, easiest thing or the only thing that you can really do is delete the files and restart SQL. You’ve got no choice, you can’t really run a pair on TempDB. But yeah, I think because that the data isn’t persistent it doesn’t mean that it can’t be creating corrupt results and your entire environment and results and what not that are coming through.

Steve: Perfect. And although I tried to ask that question in a very middle of the road state of trying to not waste my opinion one way or the other. I completely agree with your answer there.

Sean: You did a good job.

Steve: And I think that next time I’m in an argument over this with someone I’ll say refer back to Episode 90 of the sqldatapartners podcast and there’s a brief discussion on that.    

Sean: You can tell them I said so, that’s right.

Carlos: I of course agree with you too Steve if that matters but I’m not sure I’ll get a vote.

Sean: You will get a vote we just may not count it.

Steve: Alright, so then I guess back on sort of the topic of the Minion CheckDB side of things. I mean, there’s a lot of people running CheckDB, there’s a lot of people running their own custom CheckDB scripts, there’s people that are running the Ola Hallengren’s database integrity script. So really what I’m trying to understand is who out there is going to get the most value out of the Minion CheckDB product versus one of these other ways of doing it.          

Sean: Dude, I hate to sound like that way but absolutely everybody. The amount of logging that we’ve thrown into this product is obscene. And I don’t mean obscene in a figurative. I mean it is obscene.

Carlos: So you mentioned some of that, so kind of downplayed it a little bit the role or the usefulness of going to the error log. So when you talk about reporting what does that mean? I’m getting an email message, there’s a dashboard, what does that mean?

Sean: So, it means that we store everything in log tables instead of in files or in the log. So we make it really easy to get to.

Carlos: So you can then query it. I mean, is there built in processing that’s going to help notify me like, “Hey, your biggest table has corruption in it.”

Sean: So we don’t get into the alerting business at this level. We save that for our Enterprise product. Because I don’t know how you want to alert. But I’ve made it as simple as possible. I mean, there’s even a flag that says to fail the job because we handle all the errors the job won’t fail so you have to tell us specifically I want to fail the job. Some people don’t want the job to fail. Some people have a separate process that go out there so there are network monitors that can consume SQL queries. Or maybe you want to add a separate step to the job that queries the table and says, “Email me if any errors occurred.” But the point is that we give the errors in a very consumable state. We even tell you how many allocation and consistency errors. We tell you how long it took. We tell you what the last status was. We tell you whether it was a remote job or not. We tell you what server it ran on. We give you an obscene amount of information. There are probably 40 or 50 columns in an hour log details table, logs for absolutely everything. And I would say that’s the biggest advantage that we have over everybody else is again in that process, in the processing of the information and the jobs because we even allow you to do things like, let’s say, that you’ve got even, you know, that you’re using Ola and you’ve got 300 database on a server. And they’re all relatively medium size, right? You have to do all those serially. Or you have to create different jobs and then put them in, 10 or 20 at a time in the parameters.

Carlos: Do the leg work to carve it up yourself.

Sean: To split that up so that you can run those concurrently. You can just, I mean, Minion is already multithreaded so you just say run 10 at a time and it will split them up and it will run 10 at a time.

Carlos: That’s bags the question, when do you start seeing, were you introducing problems by doing so many at the same time. Have you even seen any?

Sean: Like resource wise?

Carlos: Yeah. When is too many too many?

Sean: That’s going to depend on your server and everything involved.

Carlos: That’s fair, too shady.

Sean: I gave you a fully automatic machine gun. What you choose to shoot with it is your problem. One thing I despise is tools that try to be smarter than me. I may not ever use a multithreaded process because this one big server won’t support it most of the time. But then there’s one time when we’re down for our quarterly maintenance and they say, “You’ve got 2 hours to do anything you need while we do all of these other stuffs in our app boxes that hit the SQL box.” And I’ll say, “Ok, I’m going to run 5 CheckDBs at once and get them all done in 2 hours.” You know, because I’ve got the box to myself. You may not use that normally but you may use it three or four times a year when something like that comes up. And you know there’s just so much other stuff in there, right? You can do the same thing with that same rotation we talked about with CheckTables. You can do a set of tables and then do another set of tables and then do another set of tables. You can do CheckDBs that way too so you could rotate those 300 databases; 10 a week or 10 a night. Or you could say you can also run it base off of time. You can run CheckDBs for an hour a night and then just pick up where you left off with the next database tomorrow so it will just roll through all those guys on a timed basis as well.

Carlos: In that scenario is it keeping a snapshot. I’m getting a new snapshot, right? If it has to break the database up so I guess one hour a night, my process takes two hours, which means I need two nights to get through the whole database. I’m getting a new snapshot, right?

Sean: It depends.

Carlos: Oh, ok.

Sean: If you’re running time to CheckDBs which only work off of a rotation which makes sense of think about it then it’s only going to run the CheckDBs that it can do in that time. So you can’t CheckDB half a database.

Carlos: Got you, ok got you.

Sean: But if you’re running CheckTables then it will run through as many of the tables in an hour as it can. Then in the snapshot settings if you’re creating your own snapshot that is, right. If it’s an internal snapshot you have no say there. But If you’re creating your own snapshot then you can tell it to leave the snapshot or to delete the snapshot when it’s done, and then you’ll just get another one tomorrow so you’ve got options there.

Carlos: Another question I had and this goes to something. I want to say it was Grant Fritchey said it but it’s giving me a little bit of heartburn and that is when you have corruption his comment was, “You want to find out why it happened.” And this kind of goes back into their TempDB discussion and you mentioned the disks. So my question is, how do you normally go about identifying why corruption happens.

Sean: I would say that depends. There are a number of things that can cause corruption. Almost in my experience, most of the time it’s going to be a spinning disk. But it can be cause by you copying a database over the wire and it gets corrupted and the bits get corrupted over the wire. Or you’ve got bad memory and the memory is starting to go out and that will do it. I would say disk and memory are the top 2 but I see disk pretty more often.

Carlos: I think I’ve also seen one scenario, I guess, in that a client called me up. They were having some corruption and they’re trying to do like a big bulk import. I don’t know exactly what happened but it sounded like somebody just pushed a button and powered down, you know, hard stop the machine. Something like that caused the problem as well.

Sean: Right, and that can usually be boiled down to probably something being written incorrectly to the disk.

Carlos: Right, so some kind of transaction error or something.

Sean: Right. So to answer your question directly, I kind of preface it there. So to answer your question directly usually if I find like a corrupt table I’ll go on and fix it, and just kind of move on but watch it. Because things happen, stuff happens. But if I start seeing another corruption within a reasonable time say another month, maybe two then I’ll say, “Ok, we need to figure out because we’ve gotten two corruptions inside of a few weeks. And this is just too often.” So I’ll have one of the guys run a disk check and then I’ll have one of them run a memory check. You need to specifically check the disk, and specifically check the memory to make sure that they’re ok. Because you’re right, you do need to know where it is coming from. And I want to say most of the time that’s going to be disk. The thing is I’ve had them come back before and say, “Well, the disk came back clean.” I’m like, “Well, that’s great but you’ve corrupted three tables this week.” I had this conversation with somebody six months ago. “But you’ve corrupted three tables this week. The disk is not ok.” We just ran a full disk check. Well, either run it again or run a different check because you’re not ok. And after going back a few rounds with them they would get the vendor who would either show them a different way to do it or give them a different tool, and bam, the bad sector show up.

Carlos: Sure. There are some flag or some option that they didn’t have before that.

Sean: Check the controller. It could be the controller as well. But something there in the disk process, in the disk write process is not right. And if you stick to your guns, I’m saying this to the DBAs who are going to be listening, right. In this case, you got to stick to your guns. Data doesn’t just corrupt on its own. It just doesn’t get tired so you need to stick to your guns and don’t let them come back and say, “Oh well, we didn’t find anything.” Because you know what, I have found quite often that when they say they do something. Like these guys are network guys, “Oh well, we didn’t find anything.” They actually didn’t look.

Steve: That brings up an interesting story. I mean, I work for a client years ago where. Yeah, there was no reason that they could that they were getting corruption and it just happened. And after looking into it a little bit more, well, it turned out that it just happened because the SQL Server was running a desk without any kind of UPS or back up or anything like that. And every time there was a lightning storm, or a power outage or any kind of problem, it would basically just completely power the whole thing off. And surprise, surprise, you have magically a corruption at that point.

Sean: I had that same problem. One of my first DBA gigs, true DBA gigs was at Pilgrim’s Pride and they had SQL boxes and these were 65 boxes, but still. They had SQL boxes in the kill rooms and so it was just a SQL box sitting on a milk crate with a piece of plastic over it covered in splatters of blood.

Carlos: What?

Sean: And I was just like you’ve got to be kidding me.

Carlos: When you’re talking about kill rooms you’re talking about they’re killing turkeys in this.

Sean: Pilgrim’s Pride, well they process chicken.      

Carlos: Chicken, oh my God.

Steve: Oh boy.

Sean: Enterprise wide they process back then about 600,000 chickens an hour, 24/7.

Steve: Oh my gosh! So the server was in the same room.

Sean: The server was in the same room because they have to weigh the chicken at every stage so they want to know what’s the weight of feathers is, and what’s the weight of the guts is, they shift all that stuff out, right? The weight of the feed is, they got to get all that stuff out of this so they got to get the weight and find out exactly what’s what. Yeah, you’ve got to have a SQL box right there by the scales.

Carlos: There you go. So because I feel like I’m going to be left out if I don’t tell a story. Now, I can’t tell you directly back to corruption however being from Richmond, Virginia. Philip Morris, this is the headquarters for tobacco industry and it used to be, I think they’ve finally changed this policy because they were pro tobacco people you could smoke anywhere even in the server rooms. They had pretty good ventilation systems supposedly but that was always the fun story that people would tell is that, “You could walk anywhere and smoke and whatever tobacco product you could use it anywhere in the building.”

Sean: How gross? So to go back to your question a little bit we kind of got sidetrack a little bit and it’s just the nature of how I talk. I would say back to who would benefit from using Minion. You know, it’s out of the gate when you install it. It installs what the job and it installs with schedule. So if you’re not a very good DBA, you just hit the installer and it does everything and it will be up and running and you’ll have CheckDBs. But it’s also configurable for like the world’s greatest masters to come in there and tweak all the bells and whistles and get absolutely everything out of it they want. And one of the big advantages you get especially with all the logging is you get a history at your fingertips of all of the corruption you’ve ever had on that server in that database on that table. And you can say, “Has this ever happen in this table before?” And you can back and look. It gives you a huge advantage of something that you don’t get elsewhere. If you run the query to look at the last time the database was CheckDBed, SQL stores that information in a page. But it doesn’t store anything about CheckTables. So if you’ve got a database where you’re even rotating CheckTables every month and it takes you like 2 weeks or 4 weeks to get through all the tables. That database will show that it has never been CheckDBed eventhough it has. So having all of these here you could easily query, “Oh well, these tables were done here.” And you know when all of the tables have been done you can count that as a CheckDB.  So you could write and SP that tells you the last time it had a full CheckDB which was did all the tables get done last month then this was the last time the tables got done. SQL won’t ever know it but Minion will and we’re going to do more for that as we go through the versions. I mean, this is just version 1 and it was a very ambitious version. I mean, we’ve got so many features in there. It’s ridiculous. One of the things I like about it the most is how, I’d have to say something as corny as we crossed the finished line on a lot of these features. But say you’re doing a remote CheckDB and you’re restoring last night’s backup. On the prod box, I’ll show you in the log, in the status column that it’s restoring to that remote server and I’ll even give you the preset complete of the restore. And as it’s doing the CheckDB on that remote server I’ll even give you the preset complete of that CheckDB on the remote server here. So if you have 5 databases on Prod 1 then they are all being remote CheckDBed to five different servers. You will see the status and get the results back on prod one. You don’t have to go around to all those different servers and check all that stuff manually. You get it back on Prod where it belongs because that’s not a CheckDB of Dev 1 or QA 1. It’s a CheckDB of the Prod 1 database. It’s just being run on over there. So the results belong back on Prod 1. And when I want to see status that status belongs back on Prod 1. I shouldn’t have to go all over BFE to find this information. I think we’ve crossed the finish line really well on some of that stuff like that. We really made it as user friendly and as complete as we possibly can.

Steve: Ok, I think that’s a very in-depth answer to the question I asked but definitely good stuff there. 

Sean: You know I never shut up about my product. You bought that. Think I was a snake when you picked me up?

Steve: I think that wraps it up for all the questions we have to cover. Anything else you want to hit on before we move in to the SQL Family questions?

Sean: No, I better not. I’ll just go and go and go. I’m still so incredibly in love with CheckDB. Well, I’m in love with two things. I’m in love with CheckDB as a product and I’m in love with not having to be developing it anymore. It took me so long to write this and I’m just thrilled to be working on something else.

Carlos: I thought it was available earlier, and you mentioned like shipping it out like last week or.

Sean: February 1.

Carlos: February 1, yeah, so was that actually the first release?

Sean: Yes.

Carlos: Oh, got you. Ok, I guess I’ve just been seeing the stickers or whatever previous to it.

Sean: Yeah, we tease it a little bit. We had the stickers printed a long time ago because we know it was going to be released. I just didn’t know. It took me like a year, a year and a half to finish it, something like that. I mean, you know when I first started it was like, “Hey, CheckDB. This will be easy because there’s not nearly enough in there. There isn’t really much in there as there was in backup. What could possibly go wrong?” But then when you start adding multithreading and mind you this is T-SQL multithreading. I mean, I’m not breaking out in the .NET or Power Shell or anything. This is all done in T-SQL. So we’re doing multithreading, and then the rotations, and then the remote stuff and it adds up. I way over complicate everything so.

Carlos: Well, I heard an interesting thought and talking with one of the Microsoft guys, and all this rapid release things they talked about. And his comment was, “Releasing is the feature.”

Sean: Yeah, I like that.

Carlos: I’m like, you know, yeah because you just get kind of a trap, “Oh, it’s not good enough, it’s not…” You know what, let’s just release and will fix it later.

Steve: Alright, so let’s go on to the SQL Family questions then.

Sean: Do it!

Steve: So how did you first get started with SQL Server?

Sean: Man, I’ll give you a shorter version of the story as I can. So I was a French chef for many years and followed a French chef all around the world, and the latest bistro that we were in closed.

Carlos: This is in France or this is in the States?          

Sean: This was in the States. We were back here at that point. And I was like, “Man, I don’t know what I’m going to do. I’m not really looking forward to moving again.” Because chefs have to move around if you want to get any good experience. And I was kind of tired of moving around and I didn’t want to end up in a subpar hotel or something opening cans on the line. So one of the waiters said, “Well, you know, my sister works for Stream. And I was like, “Ok, great, a field magazine. Great.” And he goes, “No, no, they do tech support.” I was like, “What the hell do I know about tech support? I’m a chef.” And he says, “You know, they’ll train you.” And I was like, “Oh, really?” So I had my own computer and ended up knowing a little bit more that I thought. And being in French kitchens, and being who I am a linguist and all that, I ended up working for HP on the French line. So I was French tech support for Windows for HP. And so I stayed there for a while and then left there and went to this young start up called Health Web and it was a SQL based product for health care where you could put all your patient information up online. And they were like one of the first ones that did this and now it’s everywhere. But they were one of the first ones who did this and I was their tech support. Well, the application was written really well because the guy really knew what he was doing. And so most of the problems that we had ended up being SQL related and this was like SQL 60 and 65. We had one customer at that time. They were hiring me ahead of time and so I had nothing to do. And there was this whole wall of shelves in the back room with all of these computer parts on there. So I built 4 or 5 boxes and put them under my desk and network them and just started learning SQL, and IIS, and Windows. And I didn’t really know that much, right, but I had a guy there who was willing to take the time to show me some stuff. He really mentored me. So I just sat there all day long with the SQL training kits, you know, the official training kits they give for the cert exams back then, and just went through those things and work all the examples and did them different ways. And every day when I came in I would break all my boxes down. All the way down from Windows, and reinstall Windows, reinstall SQL, create databases, load them with data, do their exercises and do the same thing tomorrow because repetition is king. I worked there for like a year and a half or so and every problem we ever had was always SQL related. It was never the application. So they got sold and they offered me a DBA job in Denver. And I was like, “I just bought a house 2 weeks ago. I’m not moving in Denver. I just bought a house. You could have told me that you were selling the company 2 weeks ago.” But it did kind of thought in my head because I had then looking for, you know, when we find out about the sell we have been looking for help desk gigs because I was help desk guy. That’s what I was. But when they came a couple of weeks later and said, “You could move to Denver and be a DBA.” I can’t move but then I was like, “Hey, why don’t I start looking for DBA gigs here.” And I ended up getting hired on at Pilgrim’s and being a helpdesk guy with a lot of lab time under my belt and just a little bit of actual and no real production trouble shooting, no real production database running just running it as a help desk guy answering a question every now and then and I was really pretty green if you ask me. I mean, looking back on it I was a new.

Carlos: We are all were.

Sean: We were all dumb in the mid 90s, right? But he hired me on the spot; he said that I was the best candidate they had ever seen. And I was like, “Really, me?” I guess because I read the book. And the rest is history, I kind of fell into databases. I wasn’t going to be a DBA. I wasn’t even going to be in computers.  I was going to be a chef my whole life.

Steve: Very interesting, French chef to SQL DBA.

Sean: Right, so that’s my story.

Carlos: Now, we talked a little bit about this earlier and perhaps we can revisit it. If you could change one thing about SQL Server what would it be?

Sean: Oh jeez, man, I would say the thing that’s most important to me most of the time is they don’t make information as easy to get at as they think they do or as they’d like even when they do something to help somebody, like the new clone database thing. If you ask me that kind of thing, that thing was half asked. I don’t like the way they do most of the stuff. But anyway, I would say make the information easier to get at. Like there is no reason whatsoever why last CheckDB date shouldn’t be in sys databases. There is no reason why you can’t have table properties and say the last CheckTable date or something like that. Some of this information is just so incredibly hard to get at. Why is it so hard to get table size? Why is it so hard to find some of this information? Even the last time an SP was updated or the last time a table was written to. They don’t make the stuff easy. You got to jump through some major hoops to get some of this stuff. So why isn’t this stuff easier to get at? Why do we have to jump through so many hoops or basic information about the database, right? And I think CheckDB information being the theme here is the best example of that. Why do I have to do this ridiculous DBCC page thing and parse and all of that crap to find out when the last time a database was CheckDBed. You know when it was. Put it in the sys databases and move on. Another one is why is it so hard to find out the last time a database was used. The last time somebody used the database or use a table or something like that to find out if the database is even still in use. SQL should be automatically keeping the list of all the applications that hit it so that you could easily query which applications have hit it and from what server? That stuff shouldn’t be a process that I have to write, SQL should just monitor that stuff and keep a history of it for you.

Steve: Yeah, definitely agree on that one.

Sean: And it should automatically have a firewall. You should be automatically be able to say. I only want these applications to be able to query SQL. Be able to lock anybody who’s not DBA out of every single access except through the intended application. I shouldn’t have to buy a third party product or write something really thick to do that for me. You should just have a list of applications that have connected. Let’s say, click, click, click, add one, I want you to do this, I want you to add this one to it and then that’s it. For anybody who’s not a DBA, DBAs can do whatever they want, but don’t let anybody hit it with Excel. Don’t let anybody hit it with Crystal Reports. That’s what the other boxes for. This one is for this application and that’s it.

Steve: Alright, so we’re into our last SQL Family question.

Sean: So you get me riled up about things.

Steve: We’re not trying very hard either.

Sean: I know, right, it’s so easy.

Steve: Yup. If you could have one superhero power what would it be and why would you want it?

Sean: Oh, God, what did I say last time, Carlos? Do you remember?

Carlos: I’ve had to look it up.

Sean: You know, right now, I’m going to say I wish. It’s split between two. And I really can’t decide which one. I would either want to be able to read minds.

Carlos: Oh yes, I’m pretty sure that’s what you chose all the dirty laundry that comes along with it.

Sean: Or I would want to be able to see details by the way. Not just as vague BS that you see in movies but details into the reasonable future. I don’t want to see vague shapes and colors and find out what that means as I get there, that’s ridiculous. I want to be able to see a day or a weekend to the future and know what’s going to happen and be able to be prepared. Imagine in our field how cool that would be, to know that a problem is coming and have seen the fix already and to be able to do something about it. You’d be a superstar in every sense of the world.

Carlos: Awesome, very rich stock picker as well. 

Sean: Yes exactly, that hadn’t crossed my mind.

Carlos: Warren Buffet it your heart out.

Sean: I know right.

Carlos: Well, awesome. Sean thanks so much for coming on the program today. It has been a blast as always.

Steve: Definitely been fun. Thanks, Sean.

Sean: I love this topic. I’d like to see you guys again for re-index. I’m starting now. And we didn’t even get to talk about the new stuff in Minion back up but we’ll save that for another one.

Carlos: There you go. You guys got another 45 episodes or so.

Sean: Yeah, exactly. You can’t make this the Sean show. Thanks a lot guys!

Steve: Alright, thank you.

Carlos: Thanks again, Sean.

Episode 88: SharePoint

Why can’t we just get along? There can be a real love/hate relationship between those of us with data responsibilities and those who are responsible for SharePoint environments.  We want to provide good service to those environments, but there are SO MANY things we can’t do on a SharePoint database.  To see how the other half lives, we invited Todd Klindt onto the program to give us his thoughts on how we can all get along a bit better.

Because Todd suggests the “SQL and SharePoint communities should understand each other a little better”, we will try to tackle some of the obstacles the teams face so you can be better armed to support your SharePoint team.

 Episode Quotes

“SharePoint is kind of like that pretty girl in High School…”
“A SQL alias is like fire or gunpowder. I have the respect for it. It’s a good tool but don’t play with it.”
“Bad things happen, stuffs is unfair, and how you handle it is the key.”

Listen to Learn

  • Why the SQL Server and SharePoint teams can’t seem to get along
  • Dos and Don’ts with SharePoint Server
  • How you can still save the day
  • The vocabulary you might use to approach your SharePoint team
  • What an interesting sense of humor Todd has

Todd on Twitter
Todd’s Blog
SQL Server best practices for SharePoint
Todd’s SharePoint Netcast

SQL in the news
Mongo API for DocumentDB
Microsoft Data AMP Conference

About Todd Klindt

Todd KlindtTodd has been a professional computer nerd for over 20 years, specializing in SharePoint for the last 14 years.  His love affair with SharePoint began one slow day at the office when he discovered SharePoint Team Services 1.0 on the Office XP CD that was holding up his coffee cup, and decided to install it. The rest is history. In 2006 he was honored to be awarded the MVP award from Microsoft for Windows SharePoint Services. He has had the pleasure of working with SharePoint farms both large small. He has written several books and magazine articles on SharePoint. Todd has presented sessions on SharePoint at many major conferences both in the United States as well as Europe and Asia and does the user group circuit, SharePoint Saturday events, and the occasional children’s birthday party as well.

Transcription: SharePoint

Carlos: So Todd, welcome to the program.

Todd: Thanks for having me, Carlos.

Carlos: Yes, it’s a pleasure having you here and when we reached out and chatted with you I knew we were going to have very interesting conversation. Ultimately our conversation is about SharePoint today. But your response was, and we wanted to talk about the interactions between SharePoint and SQL Server. And your response was, “Yes, I’d be happy to talk with you about the database lucky enough to host the SharePoint application”.

Todd: Yes, there are many database platforms out there but SQL is fortunate to be the one that gets to support SharePoint. It’s an honor above all honor. I hope SQL appreciates it.

Steve: Now, one thing you’ve said is that you’ve taught some classes in the past around this and around SQL for SharePoint admins and around the opposite of SharePoint for SQL DBAs.

Todd: Yeah.

Steve: And I think that’s one that would be very exciting to our listeners in that a lot of DBAs may not have the SharePoint experience.

Todd: Yeah, so the way that works is we’ve all heard the phrase, “In the land of the blind the one eyed man is king.” And so when it comes to SharePoint DBAs, not SharePoint DBAs, SharePoint Administrators, they need to know a bunch of things. They need to know SharePoint obviously but they also need to know IIS, PowerShell, and they need to know DIS and SQL. And all of those things SQL is the most complicated, the least forgiving and just the most easiest to screw up. And so I came in with a little bit of SQL knowledge. Just a little bit more than the x guy and so I became one of this people. When speaking to TechEd and things like that I would speak about, you know, for SharePoint Administrators the SQL part. And I would cover just the dumb, the stuff that’s just embarrassing for SQL folks but the SharePoint folks didn’t know it. And so I had somebody, I can’t remember what the event was like, “You know, we’ve got some SQL folks they would like it the other way around.” And I’m like, “Absolutely, this is easy.” And so I kind of twisted it on its head and what really it ended up being was almost like a counseling session for SQL DBAs because I was saying all these things that SharePoint was doing that they hated. And you know, like the ever popular using as a primary key. SQL DBAs love that. So it end up being, here’s all the ways that SharePoint is going to try to destroy your SQL Server and all the things that it doesn’t go along. When I would teach the class to SharePoint Administrators I was almost like a god because, you know, because I just knew all these crazy things. Like I knew when a transaction log was and how to keep them from filling up my hard drives. To the SQL guys that was just bad news. I was just like, “Here is another thing that SharePoint is going to that you’re going to hate. And here is another thing.” I think I already did that one once. I couldn’t take it. 

Carlos: When they got the pitch forks out that’s when you knew.

Todd: Yeah, I knew, by halfway through the session they turned on me. That’s the moment of pitch forks. Yeah, it was horrible.

Carlos: So ultimately I think we’re going to get to a little bit of that, right? We want to talk about. I have some questions about why SharePoint does the way the things it does and maybe you can help shed some light there. I guess another opinion and then of course the flip side is we would like to get some feedback on what the SharePoint Admins wished we were doing a little bit better there.

Todd: I have a long list of grievances. I think I can represent all the SharePoint Administrators when I tell you DBAs these things that you’re doing wrong. So I hope somebody got some pen and paper, yeah. I’ve got all that. So I’m happy to obliged you.

Steve: Well, it’s great to know you can represent all of the SharePoint Admins from that perspective.

Todd: We are one large homogenous group, yes. They elected me as their spokesperson so very likely yeah.

Carlos: Ok, so the first thing I guess just to talk about, I mean is that, so we’re talking about SharePoint, we’re talking about a couple of different databases and logging is probably the one that is going to get the most attention only because it’s so easy for that thing to get so big so quickly.

Todd: Yeah, there are a lot of ways that SharePoint Administrators screw up SQL. I mean there is just — Like if you guys had a list of all the ways that you can screw SQL up, SharePoint Administrators have done them all and they’ve invented a couple of new ones, and the logging DB is one of them. And the logging DB came from a good place. But the underlying SQL structure for SharePoint is incredibly rigid and incredibly fragile. And so from Day 1 when SharePoint set on top of SQL. Microsoft said, “Keep your hands out of SQL. Don’t write to those databases. Don’t read from those databases. Don’t even look at those databases. Pretend they are not there.” And that was because there is not a lot of referential integrity and so SharePoint assumes the things in certain way inside of SQL. And so the administrators that’s great but there’s a really good information in there that I would like to have. It’s only place that exist and you yell at me if I read it from there so let’s figure something out. So Microsoft, I think in SharePoint 2010 introduce this logging database where they just for everything that anybody would ever want in that database and they said, “Go wild. Read from it, write to it, delete it, add columns, whatever horrible things you could think of. You can do it to this database and this database only.” So that thing can get huge and depending on what kind of recovery model you’re using, the database can get big, the logs can get big. And most SharePoint Administrators if they installed SQL once they do that they forget about it. And so they don’t think to model those drives and look at the logs and all that. That database has taken down many SharePoint forum because it fills up all the drives and just takes everything down. So you’re absolutely right, that’s one that the DBAs need to keep track of. That’s one point that SharePoint Administrator should keep track too but they don’t.

Carlos: Sure.

Steve: So are there like standards or best practices out there for how to go about cleaning that logging database up or purging all data out of it or anything?

Todd: I’m not sure if there are any official ones. Microsoft stands on it is you can do whatever you want. So you can delete it, rename it do whatever things that you want to do to it. You can because they don’t read for anything so they don’t care.

Carlos: Sure. Correct me if I’m wrong but I believe that the default is that there is no expiration or there is no kind of archiving there. So going in and setting that would be like a good first step. Alright.

Todd: Yeah and that’s one of those things that Microsoft, at least the SharePoint team has done is anytime, no I can’t think of an exception to this but I’m sure there are. But anytime there is a fork in the road and one answer means that something might get removed and the other answer is it won’t. They always go to the do no harm so things like, you know, it will be great if we archive after 30 days because that will save disk space and all that. But they’re like, “No I don’t want to delete anything.” You’ll never know when someone is going to need something 31 days ago. You know, when the most important spreadsheet ever got deleted or something. So they let that stuff run wild. So I think, yeah that’s an important first step to figuring out. You know, if you’ve got a data warehouse somewhere you’re going to keep these things in. Four of you you’re going to setup your policies the same you can’t look back any further than 60 days or 90 days. That’s definitely something that SharePoint administrators should figure out or handle that database.

Carlos: And shame on us, you know, I guess if we’re a database administrator there for having those drives fill up. When you think it’s part of the core of what the SQL Server database administrators responsibilities are beyond backups is kind of keeping the system up and knowing how much drive space you have I think is king of critical to that.

Todd: I couldn’t agree more, shame on you DBAs. This is not the SharePoint administrator’s fault whatsoever. We are the collateral damage. No, I think a part of that comes from, you know DBAs have an idea of what their roles and you cover some of the basic stuff back-up, and monitoring and thing like that. But optimization, you know, figuring out the way to get the best performance out of the databases and those kinds of things. That’s just something that every DBA does and they go in every situation expecting to provide that service and the SharePoint guys come along. It’s the longest time SharePoint was the sexy beast out there. The SharePoint guys walk in. They had a swagger about them. It was just obnoxious and I say that as a SharePoint guy that has that swagger about it. And so we come in we’re like, “SQL guys, that’s adorable do you think you could help but don’t touch anything. Don’t look at anything. Don’t change. I know you think you’re smart. But don’t touch anything.” And after that happens the DBAs couple of time they’re like, “Fine. You told us not to touch anything then we’re not going to touch anything.” Yeah, I want to go back to playing Solitaire in my desk. And so that’s how kind of this dynamic thing got worked out. And either side really understood the other side very well. And SQL guys are told to keep their hands of and a lot of them did. I do have a one funny story with a customer. They did have a SQL team, a SharePoint team and SharePoint was running fine and then all of a sudden things started breaking and I can’t remember exactly what the symptom was because this was 8-9 years ago. But after asking everybody, “Did you change anything?” “No, we didn’t change anything.” Talked to one SQL DBA who has kind of a stroke of honesty about him. He was like, “Yeah, I was playing at the public role a little bit and then I took a bunch of things out. Is that bad? Is that wrong?” Well, yeah it was. And in his defense that industry with a very secure industry and that was just his job was to make sure that nothing had permission that they didn’t need to have and he didn’t know for sure. And he was trying to do the right thing. And we are like, “Yeah, don’t touch that, that DBA.” But that kind of stuff happens all the time. So I agree the DBA should keep their eye on that kind of stuff but there are a lot of things going on and there is not always a DBA out there. You might have real SQL things to worry about and that SharePoint might got stored up in some place where you didn’t know about. Kind of off the record there and that kind of stuff happens.

Steve: So as the DBA oftentimes we are put in a position where we are responsible for that server but then we’re told, “Don’t touch it, don’t change it.” And then things happen like databases got big because a lot of login and what do we do? I mean, we can’t touch it, we can’t change it but we’re running out of disk space so we just throw unlimited amount of disk space at it or more memory.

Todd: All of the above. I keep hearing how cheap disc space is and I hear that from people all the time. When I was a server admin like, “Why does it cost so much for server space? I can go to best buy and buy.” So a couple of things so Microsoft has some documentation to kind of helps with that. And I can give you guys the links to that. The SharePoint product team understands the tough position DBAs are put in so they have hard guidance on here’s the things that you can do to secure SQL. Here are the things that you can and can’t touch in SQL to give you guys sort of a framework on things that you can do. And so I’ll get you the links for those listeners, those of you in the shownotes these guys have or I can blog, toddklindt.com, either way. So a couple of things about that, one of the things that I would bring up is that when you create a SharePoint farm and out of the box I forget where it is now but there is like 19 databases to get created with just a regular run of the mill SharePoint farm. SharePoint makes some really bad assumptions about how things should be configured for instance it doesn’t understand putting databases in different drives. It doesn’t do that automatically. It sets your recovery model to simple. All these kind of things so as a DBA you can’t assume the things were setup in a way that makes sense because SharePoint doesn’t always do that. One of the things that you can do is go in and look at the recovery model and say, “Ok, we don’t need. Sets with full recovery model.” You would probably don’t need that unless you’re Probably doing some other kinds of smart backups truncating your logs and doing all these things. SharePoint doesn’t always do that correctly. It’s going to put everything on the default data drive, there’s a default log drive, there’s obviously some databases that you can move around that makes sense. I’ll put them on different tiers of space and different sizes so there’s some of those things. Really looking at the documentation and finding out is the best way to do it. One of the things that I have talked about when I speak to SharePoint administrators is I don’t expect them to be SQL experts. There’s just not enough time to be a SharePoint expert and a SQL expert. And so they should know a few things, they should know some vocabulary, they should know some of the terms so that when it comes to you guys or when you guys come to them that you can kind of speak to them and saying the same things. So if you come to somebody and say, “Hey, these drives are getting full what can we do?” There’s situations where content databases can have huge amount of white space in them and so then you need to have a conversation, “Should we shrink this databases, should we not shrink this database”, things like that. So it’s kind of a two way deal.

Steve: So even in that default setting, Tod, you talked about like simple mode. But even back at least in, and this is dating me a little bit. We couldn’t backup the databases and use that as a restore mechanism, right? It had kind of go through the SharePoint console. Is that change at all?

Todd: So that is kind of, let me go back and say that by default SharePoint creates the databases of this full recovery model. Not simple because full is the one that fills up the hard drive and makes everything crash. But as you can treat your model settings and play with that. But if you don’t know enough, if you’re a SharePoint guy and don’t know enough that can bite you. As for databases with the exception of one database for the most part all of the databases inside of SharePoint. When it comes down to as known what kind of database is that and what you’re trying to do with it. So the one that you really can’t touch is the config database and that is the one that has all of these form specific and machine specific settings. You can’t move that thing. You can’t move it from farm to farm. You can’t really even move it from location to location side of SQL. It’s just a vey fuzzy thing. There’s a lot of hard coded things in there and that’s the one that really destructs all the SharePoint servers. So if something is wrong in that database SharePoint can’t start. Outside of that though like your content databases where all your documents, and your lists and all that but those things are completely affordable to a point where I tell folks, when I’m talking to SharePoint folks, if you do nothing inside of SQL. Get SQL level backups of your content databases because if everything burns down, if your SharePoint Server is exploding, your SQL Servers explode. If you got copies of those databases I can’t make sure you don’t get fired because that’s where all the stuffs at. I’ve got this great story where I used to work at a company and I left and like 10 years later the guy that was my boss calls me on a Friday night at 6 o’clock. I’m like, “Hey, Todd, how it’s going? Been a while.” And he’s like, “Well we got this big SharePoint environment and we’re moving it to a hosting company. And we get everything set up and we have all these VMs, all these Hypervisors and all these SharePoint farms and the hosting company did a reverse migration.” Now I’ve never heard that term reverse migration but I think I had a pretty good idea what had happened. When they were set to copy their local virtual machines and databases and all that to the hosting provider the hosting provider did it backwards and copied the blank environment over top of that production environment.

Carlos: Oh man.

Steve: Wow.

Todd: Yeah, so it gets worst, so he’s telling the stories like, ok. So we talked to our SQL team, they had databases, they had all the databases backed up the SQL team rocks. Everything is good to go. He’s like, “Go DBAs!” And I talked to the Windows team and I said, “Ok, we need to restore all the VMs.” And the Windows team said, “No, we don’t take care of that. The virtualization team does.” I said, “Ok”, so I went to the virtualization team and I said, “Hey, I need to restore all these Windows VMs.” And the virtualization team said, “We don’t do that, the Windows team does.” So this guy was in this situation where he had like I think 9 SharePoint farms and close to 30 SharePoint Servers and all the servers, all the Windows images are gone. All he had was the SQL images and databases. That’s all that he had. And so I was able to take nothing but the SQL images and SQL databases. And the SQL team rebuilt SQL Servers and monitor everything. And I brought all that up full fidelity everything that he had the week before because all the good stuff, all the important stuff is in SQL. All of the data is in SQL. And the fact that they had just SQL backups, SharePoint backups. The fact that they had good SQL backup so I was able to rebuild everything. Bolt it all back together and as far as I know we didn’t go far. So where does the SharePoint part comes in is there are a bunch of SharePoint piece that aren’t in SQL and so if you don’t do SharePoint backups those things font get back up. And there’s also unit of scale inside of SharePoint. And a content database again which has all your important documents and list items and calendars and all that. They can have one or more site collections which just kind of the unit of scale inside of SharePoint. So SQL can’t backup anything in the databases. But if you do SharePoint level backups you can backup individual site collections and give some plenty of business there. But the SQL backups are the very first thing I tell anybody to do.

Carlos: There you go so companeros if you’re getting pushed out of the SharePoint love and make sure you’re still taking your backups so that one day you too can be the hero.    

Todd: Yes, recover from that reverse migration.

Carlos: So one of the things that I was going to ask about in the database itself. There are some settings that kind of draw these bunkers, right? And one of the things that it doesn’t want us doing is creating statistics. And these are statistics, again, so you have these very wide tables, of all these list, right, column 1, column 2 and we look at we go crazy. And then it’s like now you’re not going to let me choose, you know, create statistics at the database itself is going to create. I’m not going to create any on my own. I’m going to let your database create it and SharePoint is like, “No, no, don’t do that.” Where you get in trouble particularly even if you decide to go and to do is then when you want to upgrade it will say, “You’ve touched my database. I’m not going to let you upgrade.” My question is creating automatic statistics, is that still a no no? Is that something that we can turn on and then again assuming that we know enough and then we just remove them before upgrade time? Or what’s your choice there? Which side do you coming down on.

Todd: So I think that is covered in the best practices document that I promise that I would link to you guys. So the upgrade of the database stuff I’m not sure what the last version of SharePoint trying to upgrade from or to us. So I’m not sure if that particular one is still gets caught or not. These days when upgrade SharePoint so all you can do is database attach so basically you build a new SharePoint farm as you got SharePoint 2013 database as you built your SharePoint 2016 farm. There is a command lock that you can use to test the database and it will tell you all the things that you’ve done wrong and all the things that it does right. But more importantly it will tell you whether that particular thing will block the upgrade or not. And there are precious few things that actually block an upgrade. Now, play with the database schema that might be one of those things. But I don’t remember if the statistics, one if it just yells at you or if it blocks the upgrade or not anymore. I know when going from SharePoint 2007 to 2010 it was super mean about those kinds of things. I don’t know if that’s still the case or not, best practices thing, I think it’s in there. But yeah, you’re right and that’s why I feel for the DBAs is because there are so many things that you guys do to keep things run then SharePoint comes along and says don’t do that. Now I’m looking to the best practices document right now and it says do not enable auto create statistics at SharePoint content database. We will find you, we will pull your fingernails, put cockroaches in your underwear that seems unnecessary. I don’t know why they didn’t do that. Yeah, so don’t do that particular thing now that is the SharePoint 2013 best practices document.

Carlos: So you would still follow that as a best practice then?

Todd: Yeah, I would. And I’m sure your listeners are used to talking about things in Microsoft Support. And there is support in a capital S and support in the lower case s. So depending on what you’re talking about. There are different levels of what you can and can’t do. I’ve absolutely done things that I know that Microsoft hates. But they can’t necessarily catch me up and I’ve got plausible deniability. If this is one, I don’t know enough SQL to know if there is a way you could turn that on and then you call Microsoft support and they notice that if you could turn it off and undo potential damage. I don’t know that one well enough to comment on.

Steve: Then when we’re looking at the SQL Server best practices or the normal things that we’re doing for performance tuning and then you compare that to what SharePoint is going to allow you to do. What are the kind of things that we should avoid or that we can get into trouble if we do. Or what are the things that we can do and get away with and work well?

Todd: Yeah, SharePoint is kind of like that pretty girl in High School. She’s really pretty and you want to demanding. SharePoint is kind of like that when it comes to SQL so again that best practices document that I’m going to link and that you’re going to link that will walk you through basically that question as a well seasoned DBA. You got a list of things in your head that you want to do and here’s the list of the things that you can do which is very short and here is the list of all the things that you cannot do and that really guides you. So a couple of things that we talked about you can’t mock with things like indexes and fill factor and those kinds of things. There are some harder things that can’t do. I know one of the ones we talked about that you can’t auto create statistics that’s a big no no. A gigantic no no is you can’t mess with the maximum degree of parallelization or maxed up to its friends that has to be set to one and only one or SharePoint 2013 and greater will just, depending on when it catches you changing that it will just fall down and throw up its hands and refuse to serve anything. So if you don’t have maxed up set to one on your instance when you create your farm SharePoint will refuse to create the farm. It just won’t do it. There are different maintenance processes and stuff that will check for that. So if you try to change it afterwards it will be very angry with you. While that’s obnoxious it is less obnoxious not checking and not getting angry with you and corrupting your data because it thinks everything is running and one threat that’s not. I send that to that best practices document to SQL DBAs all the time. it normally brings them into tears. Microsoft has done a pretty good job because this question comes up all the time. SQL DBAs are better way longer than SharePoint administrators have. So you guys honed your craft a lot better than we have so we have to provide you with this improvision.

Steve: Sure and we just don’t have that cool swagger that you mentioned earlier.

Todd: It’s true. I mean, I’ve met some of the coolest SQL folks and you got nothing on some of the coolest SharePoint folks. It’s true story.

Carlos: And here I was starting to warm up to you Todd.     

Todd: I’ll take care of that.

Carlos: A Chill just entered the conversation here.

Todd: So that’s plenty, many years ago. So I’m Microsoft MVP and I have that for 10 or 11 years and along one of this flight to the MVP Summit I was sitting next to Tom LaRock. I know you guys have deal with him. I do mean deal with him, not meet him, he is a special individual. So on that plane, you know, it’s all nerds. That plane right out to Seattle just have the extension nerds. I’m sitting next to Tom and he pulls out his laptop and he starts doing SQL-y thing. And so I was like, “Alright, he’s probably an MVP and I start talking to him.” And to this time, and this is probably 10 years ago, Tom and I still talk all the time because when that conversation started up I’m like, “Hey, you’re a SQL guy.” And he’s like, “I’m a SharePoint guy.” And he kind of looked at me and he gave me the stink eye. I’m like, “No, no, it’s cool. I’m on your side.” What I can do is SharePoint guy to make SQL better. Help me understand the performance things that I can do and we kind of bonded over that. So there’s some amount of, we can mix the chocolate and the peanut butter but it works out.

Carlos: Now getting back into the reverse because basically out hands are tied and don’t stick your hands into the cookie jar, right, get out don’t do anything. So on the reverse side there how are we driving our SharePoint admins crazy and how can we do a little bit more and less yelling.

Todd: Yeah, well I think there’s a bunch of stuffs you guys can and a bunch of stuff that SharePoint people don’t understand. SharePoint administrators everything in SharePoint is the can’t to them. It’s the word docs, it’s excel spreadsheets that they are laser focus on that that they can’t see anything else. And as a DBA you understand that by the time a piece of data end up in a content database has probably gone through TempDB. It sat in a lot of cloud for a while. There are a bunch of things that happened before it gets into that database. Whereas to a SharePoint admins, you know, it goes from my mom’s computer to the screen directly into that SQL database, you know, no stops between. So one of the things you guys can do is on the backend thing about where database go, which drives TempDB goes on, how many TemDBs have, where to put the log, where to put the databases and things like that to make SharePoint shine. That’s one of the things and educate those SharePoint folks on that and why you’re doing it, and help them understand the value of that. That’s makes so much difference. Making sure that your SQL Servers have enough memory, have enough RAM to do the things that they need to do. And there’s some guidelines out there but again you guys, again it’s almost more art than science at this point. You guys can feel those things out and you understand the different metrics to look at and see where SharePoint is come flowing down and help guide those folks out.

Steve: Yup, so on that point then around moving data and log files around. I mean generally we see with SQL databases that if you can have your log files on a different drive than your data files. And you’re having I/O contention you usually can improve performance with that. And one of the things I’ve run into with SharePoint or SharePoint admins that you make recommendations say this is what we’re going to do, we put in a new drive, we’re going to move the log files over here, we’re going to move the data files whatever and there’s a lot of push backs saying, “oh you can’t do that with SharePoint.”

Carlos: Yeah, you can’t touch it, you know.

Steve: That will invalidate the warranty and the entire system will blow up.

Todd: Yeah, I think that comes from two things. I think one of the things is SharePoint administrators are very sure of themselves and so they have heard that you shouldn’t touch things in SQL and so they repeat that with great authority and feeling. And the other thing is I think most SharePoint administrators are scared to death of SQL. I mean just terrified of it. And so they’re afraid and if you go back there and fiddle some bits that it might just kill SharePoint and SharePoint will never recover. And so I think it’s SQL parts in those two things. SharePoint absolutely is fine with the transaction logs and databases being on separate drives. It’s absolutely fine with if you’ve got a content database that has really fast filling logs and you need to move those. It’s fine of taking that database offline in SharePoint which is different than dropping it in SQL the way SharePoint uses offline for databases is maddening. But it actually understands that moving the logs or backing it up it actually covers all these things. You can actually do all those things.

Carlos: Just to clarify there. So I go to my admin and say, “We need to make a change here.” We’ve gotten to a point where they will trust me and I’m not going to break everything. Am I using SharePoint to take the database offline or can I do that through SQL Server Management Studio and take the database offline and move the files around?

Todd: Yes. It depends on the database and what you’re doing on the SQL backend. So the content databases and the content databases alone have this idea of being offline and detached and things like that. A content database in SharePoint if you set it to offline what does that not mean is it’s not accessible to SharePoint. You know, the way offline means in every other of the tech world every time ever except that one instance. And to make it even more confusing there are two screens that that’s on. In one place the actions are offline and ready for our database, the content database. In the other screen the actions are stopped and started.

Carlos: Oh, interesting. Okay.

Todd: And it is the same setting. It’s been that way for 10 years. So as a DBA you hear that the database is offline and you’re like, “Hey, the database is offline.” When SharePoint, what it means for SharePoint is I can’t create new site collections there. This is what that means. In content database you can detach them and then SharePoint doesn’t look for it anymore. And them what they’re doing in SQL doesn’t matter. You can do whatever. But you can have different file groups and things like that for databases and depending on what you’re doing on the backend you may be able to move those transaction logs and create another log on a different drive. You know, SQL does it thing it tries to balance things and you can move things that way as long as that database is accessible. SharePoint doesn’t care.

Steve: We don’t recommend creating additional logs and files but moving them would be a preference there.

Todd: Yeah, if you could do that. If you could say this content database because they’re the ones that are going to get really big. Say, detach this in SharePoint so it doesn’t show up. You are going to have little bit down time but that’s ok. And then take your transaction log moving at the database back in. Yeah, SharePoint doesn’t care.

Carlos: Well, they shouldn’t be getting that big anymore because now we have permission to go and take the backups to the databases and we’ll help manage that size from that perspective.     

Todd: Yeah, and that’s so I’ve got, I don’t know how many hours I’ve spent with the SharePoint admins when I was consulting and they call and say, “Hey, SharePoint is down everything is ruined.” Never would be because the transaction log file was My favorite one, and this is like in 2008, and we are talking almost 10 years ago and a guy calls up all freaked out, “SharePoint is down. SharePoint is down.” I imagine him running around his office waving his hands in the air, his hair is on fire that ordeal. And so I jump on there, sure enough it’s another one of those farms that gigantic transactions logs had taken down. And this transaction log was 350 GB something like that. I don’t know how big transaction logs I know you guys have seen this days. But back in 2008-2009 that was a pretty big file. So I walked with my typical SharePoint swagger and I’m like, “Don’t worry about this man. I got this. I clean this up all the time.” And I went in, changed it from full to simple, and I know in the background what the database engines are going through and marking transactions and all that. So I did a little chit chat with the guy and told the administrator that I’m going to try and shrink the transaction and I told him what causes it’s going to be. We shrunk it like 20GB. Which 20GB that’s a huge. This guy is seeing 350GB file and now let’s say 330GB file and he’s freaking out. But it just goes to show that he didn’t understand the back end of SQL. He understood SharePoint but he had to think through all that. And I’m like, “Good luck on your database engines working as fast as it can.” But that’s kind of stuff happen all the time. Another fun one that I had, I was working with a customer we’re doing test migration and we talked earlier in this podcast about database are being affordable and do SQL database level backups. So I jump on his SQL box and make backups of all his databases. And I look at the SQL box and it’s got the amount of space I need times 1.5 free. So him and I start to running the backups and all of a sudden it shuts down. And he’s like well is the backup still going? I’m like, “Well, that depends a lot on why I doesn’t it?” Because your connection to the server went away probably still going on because if the server burst into flames probably not. So he tries to connect and he can’t connect it and he’s like, “I don’t know what’s going on?” And then he gets a knock on his door and everything is down, email is down, the whole thing. And he’s like, “Oh, sounds like our hypervisor went down.” Okay, so he has investigation. They have been thin provisioning their drives. And I sort of this multi gigabyte backups and filled up the drive on the hypervisor everything paused. Ok, good enough so he goes some stuff. Couple of minutes later we’re back up and going. We jump back on the server of course the backups didn’t finish because the machine shut down. And set backups again. We did about 10% in. You need to figure out some space for this. I can’t keep your taking your old company down like this. I feel bad. And I remember back in those days when I would speak in SharePoint conferences I will tell people, “Don’t virtualized SQL. Just don’t do it.” There’s a performance penalty. Of course today in 2017 everybody virtualizes everything everywhere. But that’s one of those things back then. I’m like, “This is just another reason not to virtualize SQL, going to have that drive space, going to have good I/O.

Carlos: You know, that’s right. If you don’t have drive space you don’t have too much. Especially if you want to add something to it. And I guess if you want it to be read-only that’s one thing.

Todd: I keep thinking and I feel bad about it.

Steve: Alright, well so great information there I think for the SQL DBAs as well as the SharePoint people.

Todd: Yeah, one of those things that I want bring up to DBAs and this is the story of SQL aliases. And do you guys use SQL aliases much?

Steve: Not often.

Todd: Ok, that is a debate inside of the SharePoint community a lot. And the way that I feel and again it’s not my information on this is dated but I first started touching SQL aliases probably in 2008-2009. And it was like black magic to me. I mean it was just like. I don’t remember the first time I pulled the hard drive and everything kept working. I’m like, “This is black magic there is no way this works.” I felt the same way about the first time I use SQL aliases. And once word got out that those were there people were like, “We should use SQL aliases all the time.” But for the longest time when I would get up in front of a room speaking to SharePoint I would say, “Who here knows what a SQL aliases is?” And like 3 people out of 50 or 100 will raise their hand. And I would say, this is why you shouldn’t use SQL aliases. Because SQL aliases work so well that if you don’t know what you’re looking for you’ll never find it. You’ll never know why things don’t work. So my guidance to folks has always been not to use SQL alias unless you need it. Unless you can articulate the reason why you need to use a SQL alias don’t bother with it. Because you’re going to go on with your career hopefully and get big promotion or win the lottery whatever the next guy might not be as smart as you and he might not know about SQL aliases. You can drop one in anytime. There is no penalty for using that when you build you SharePoint farm. That’s one thing I told folks not to do unless they need it is use a SQL aliases.

Carlos: Yes, it’s one of those things because the people are migrating. They want to go from server A to server B and they’ll introduce that alias but I kind of feel like, you know, it just bite the bullet and get that SharePoint setting reset. Like change your applications unless it’s really going to be that difficult it’s worth not using the alias because you’re then kind of giving control if you will to who owns that. Whereas with like even DNS there’s an owner like you know where that is going from. You don’t have that quite with an alias so I think that’s kind of the tricky part with there.

Todd: Well there is one very specific reason to use aliases with SharePoint. Well there are two but it’s the same reason. We talk earlier about that config database. SharePoint does not support moving that config database, and so if you have to move servers or migrate or something there is no supported way to move where that config database is. So if you want to move it you have to use an alias. There is just no way around it. And the other thing is if you’re using any kind of non-standard ports because you can’t do anything with non standard ports with DNS because that’s just a hosting level thing. So that’s the two reasons that I tell people. And again it’s very valid. If you’re doing one of those things rock on use an alias. But the thing that I tell people is there is no benefit in using the alias when you build the farm. You can always drop it in. Because aliases I’m telling still today I’ve got half of the SharePoint administrators don’t know what an alias is or know where to find it, or know where to configure it or know that there is two of them. Know that they need to do the 32-bit one and the 64-bit one. So I tell people to be afraid. It’s like fire or gunpowder. I have the respect for it. It’s a good tool but don’t play with it.

Steve: Yup, very good advice. So shall we go on into the SQL Family questions now?

Todd: Sure.

Steve: Can you tell us how you first get started with SQL Server and or SharePoint?

Todd: Yeah, so I got my first SQL Server cert in December of 1997. I got my administrating SQL 65 certification back then. It was one of those things I worked for a couple of company that use a little piece of a kind software. Maybe you’ve heard of it. Great Plains Dynamics and this was way before Microsoft purchased them. And the company that I worked for is a consulting company and they wanted to use Dynamics that was really expensive to buy. But what they found was if you’re a Great Plains reseller you got to use it for free. And so they said, “Congratulations everybody we’re going to become a Great Plains reseller.” And to get that you have to have somebody who’s certified on the software and that person Microsoft because that stuff also run on You had to be an MCSE and you had to be certified with SQL and then you go to this class. And I was already an MCSE so they said, “Congratulations Tod, you’re about to become the SQL Administrator. Go pass this test because you’re going next month.” That’s how I got started with SQL 20 years ago now. And then SharePoint, my introduction to being a SharePoint administrator was very similar. I was a server admin at that time. This was like 2002 and Windows Exchange that kind of stuff and my boss told me to setup a webpage that had all of our servers worldwide and put it up on a webpage. And I can’t program, I can’t design webpage, I can’t mesh my clothes for god’s sake. And I look into this task and I’m like, “Well, huh, I got my resume so that’s good. I’m going to need that.” And then I was drinking some coffee and I had like what the Office XP CD and it had a trial or a thing of SharePoint Services 2001. And I’m like, “I never seen something like this in Mac.” And I installed it and put this database out there and held up there and I was hailed as a hero and it was all great and then the company decided that we’re going to write some software on top of SharePoint 2003 when it came out. And my boss said, “SharePoint we’ll I’ve got a SharePoint guy.” And that was the day I became the SharePoint guy.

Carlos: That’s when the enter swag, right?   

Todd: Exactly. I stood up a little straighter, shoulders are a little back.

Steve: Wow, nice.

Carlos: Now, if you could change one thing about SQL Server what would it be?

Todd: Boy, there’s a lot of it. This I will tell you that SQL has gotten better with a bunch of things. I haven’t played with the SQL 2016 PowerShell stuff as much but I know with the previous versions the PowerShell was shall we say odd. As a guy that uses PowerShell for Windows and for SharePoint and for Office 365. The SQL PowerShell seems to dance through its own beat a little bit so that’s one of the things. I think for the most part of SQL are really matured products. And they’ve made a lot of mistakes but they made them a long time ago. So some of the SharePoint stuff maybe around is database sizing, the recovery models and things like that. One thing that always gets me for whatever reason the way I think is security piece is backwards. In my head if I want to give a user permission to the database that should be a function of the database. But it’s not, it’s a function of the user. I have to go in this security find the user you give them an access to the database. In my head it should be database, the other way around. Does that make sense?

Carlos: Got you. Sure.

Todd: Little things like that because there is a bunch of places in SharePoint we have to do that. And then because of that security stuff is in, the master database and the database is running I can see that coming through with the backups. But just little things like that but for the most as a SharePoint admin SQL treated me fairly well. Again like fire and gunpowder I have a healthy respect for it. It has been pretty good to me.

Steve: Ok, so what is the best piece of career advice that you’ve received?

Todd: Professional football is not for you maybe you should be a computer nerd. I think that was probably pretty good.

Steve: Was professional football an actual option at some point?

Todd: No, not even a little.  I was a nerd from the day I was born. So back in High School wasn’t really cool. I spent a lot of time hiding in my locker. Spent a lot of times wondering why girls wouldn’t talk to me and things like that. Turns out being a nerd works out for me. As I look back now it’s cool to be a nerd. No, there’s never a real option for me at all. I think I had a couple of them. One of them something we talked about before we go into air was just it’s all about people. It’s all about who you know and not in an equitism sort of way. But it’s all about whom you have relationships with so never burn bridges. Always give the other person the benefit of the doubt. One thing that I found over the years is anytime I have this idea who’s the idiot who thought of that? The answer to that question is usually a well intentioned smart person who just sees things you haven’t thought about yet. It humanizes some of these down decisions that you see people make. They see other aspects of the problem that you haven’t seen. And so you give other people the benefit of the doubt. And another piece that I got was about 5 years ago when I start working for I was part of an acquisition and I was a remote worker. I wasn’t in San Antonio. I wasn’t with everybody else. And I was like my first review. I had my goals and I didn’t get them all done. And I talked to my boss and he’s like, “How come you didn’t get all the stuff done?” And I’m like, “Well, I’m not in the office and this guy wouldn’t call me back and I couldn’t get hold of this guy.” And he’s like, “Yeah, that’s the truth. Every bit of that is true. Figure it out.” I’m like, “What?” He’s just like, “Yeah, try harder. I don’t care about what they’re doing I care about what you’re doing. Yup all these obstacles are in your way figure it out. Make it happen.” And I was like, “That’s kind of…” But it was actually the advice that I needed. You just got to go through. Bad things happen, stuffs are unfair, how you handle is the key. And this guy wasn’t very empathetic he was kind of a jerk. But it was a good advice.

Steve: Alright.

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

Todd: So it’s funny I was asked this question a few years ago and for my superhero power I wanted to be a SharePoint administrator and I got it.

Steve: Wow, I think you’re the first person on the show that’s had that aspiration.

Todd: And so I think it’s a self explanatory as why I want it. It gives me out on shows like this. No, a superhero power, you know I watch a lot of Sci-Fi and I’ve found almost every superhero power comes with a really negative side effect. I don’t think I have the morals to have any of those powers and use them for good. So I think, maybe the superpower will be I’ll always know whether or not so that I would know when to put that down. Maybe something like that because I swear to God I would screw up any superhero power I had. I cannot be trusted with superhero powers.

Carlos: Not too well. Ok, so we can trust you with our SharePoint but we won’t trust you with powers.

Todd: Yeah, that is it.

Steve: Alright, well it has been great information, Todd. I’ve learned a lot during this episode.

Todd: I’m glad. I think there is not enough done to get our communities together. I think the SQL community and the SharePoint community they need to understand each other a little better. I think that would be good thing.

Carlos: Well, awesome. Thanks again for being with us today.

Todd: And if any of your SQL folks have questions or whatever I’ve got a blog toddklindt.com and I’m on Twitter @toddklindt. I’m not very imaginative with my names obviously. But yeah, tell your folks to reach out to me. I’m happy to hear from anybody.

 

Episode 87: Resource Governor

Why do some features get all the fun?  I would like to be a fly on the wall at some of the Microsoft feature development meetings. There are many very cool features that didn’t get quite enough love to get the adoption they deserve.  In this episode we are talking about Resource Governor and before you roll your eyes, you may be interested to know some of the side benefits of using the feature.  We are joined by Mark Wilkinson from channeladvisor to give us an overview of the feature and how they use it to manage SQL resources and workloads.

One interesting story is how their team won the query challenge last PASS summit using Resource Governor so it is pretty powerful. Mark will also share his experiences using Resource Governor in different working environments. He will also be discussing the basics and tips about building the classifier function of Resource Governor. Furthermore, we will go through setting up I/O caps, resource pools and workloads, and the NUMA node affinity.

 Episode Quote

“I mean, It’s just a really cool tool to use”
“Say what you mean and always ask for exactly what you want”
“Always make sure people know how you feel about stuff and always be honest and straightforward”

Listen to Learn

– Managing SQL Resources using Resource Governor
– Resource Governor Classifier function
– NUMA node affinity
– Setting up minimum and maximum limits using Resource Governor
– Resource pools and workload groups
– DMVs and I/O operations

Mark on Twitter
Mark on LinkedIn
Mark’s GitHub repository
Mark’s Posts on Resource Governor

About Mark Wilkinson

Mark is a Database Administrator at ChannelAdvisor solving interesting problems on large databases.

He is a father of 4, and lives in Raleigh North Carolina. In his spare time he likes to read, speak, and learn new things.

 

Transcription: Episode 87

Carlos: Ok, so Mark, welcome to the program.

Mark: Thanks for having me.

Carlos: Yes, it’s nice to have you back after a good amount of time being with us in Episode 14. Today, we’re going to be talking about Resource Governor, and always to me at the, you guys over ChannelAdvisor using all the cool toys it seems.

Mark: Well, the cool toys, it’s kind of, Resource Governor is a little bit old but yeah.

Carlos: Oh yeah, well you know, but I’m impressed by all the features that you’ve been able to implement in regarding to SQL Server. And Resource Governor is another one of those features and that’s kind of the topic of our discussion today.

Steve: Seems like with Resource Governor, it’s one of those things that people either know use it and love it or they don’t know an awful a lot about it and never tried it. There’s not an awful in between there and the people I’ve talked with.

Mark: Well, it’s funny actually we were in Redmond for a week in Microsoft Labs doing some testing. And part of the gig when you go there is that you have to talk to their SQL developers and telling them the things that you like, things that you don’t like and stuff like that. When we listed the features that we use, I mean there were some features on the list that we had to explain what they were because they were so like, yeah we use CMS. What’s a CMS? Central Management Server. People definitely knew what Resource Governor was but, yeah, we use a lot of features and there’s definitely some stuff in there that not everybody is aware of. Yeah, I like to tell people about Resource Governor because we got a lot of use out of it and some need to kind of things we didn’t expect that came up as well.

Carlos: Sure, well I think that classic scenario and ultimately I guess the definition of Resource Governor is it allows you to manage SQL Server resources and workloads by putting limits on what either logins, or users, applications can use and have access to, right?

Mark: Yeah, anything that’s basically related to the session. You know, so like you said the application name, the user. There are a lot of different things you can classify on but pretty much anything that’s related to the session itself and you can use to determine which group or something it’s classified until limits is put on it.

Steve: Now can you use that for just limiting or can you also use it for some type of monitoring. Yeah, that was when I’ve mentioned that there’s some kind of neat side effects we didn’t expect. The monitoring capabilities, not really capabilities but the metrics you can get out of Resource Governor are kind of amazing. It keeps track of anything that goes through each of the workload groups, from CPU time, to amount of reads and writes, I/O operation issued, total number of requests, all kind of things like that.

Carlos: And you don’t always have limits on those. It’s just keeping track of them because it’s part of the session.

Mark: Exactly.

Carlos: Well then why did you want to implement Resource Governor?

Mark: Yes. It kind of came up because we’re having issues where a certain, actually it might make sense to go kind a level higher and then talk very vaguely about our architecture at ChannelAdvisor. It’s not going to seem alien to most people probably have experienced something like this but we’ve got a set of SQL Servers and then we have got a set of application servers and basically any team can submit work to these application servers. And the work is kind and all those servers communicate with the database instances. That being said we don’t have a lot of control over how many like requests can be made from a given application server or set of application servers. So we were starting to run into situations where maybe one team is using all of the instances and there wasn’t really much left resources for anybody else to use. So we had a find a way to kind of limit that, reduce that a little bit and that’s when we start using Resource Governor.

Carlos: Ok, so these are actual teams then, so you kind of separated by team or I guess by application or these are by login?

Mark: We’re very strict in our application naming in the connection server when you connect to SQL. So any application that communicates with any of our instances should have, I say should, we would love to evolve into it but, you know, sometimes things They should all have an application naming associated with them and the team name is part of the application name so, you know, Team A, their application with the Team A, you know, file process or something like that. So we can use that application name then to push them into the Team A workload group.

Steve: Ok, so if somebody is listening and now they’re thinking, you know, I want to give Resource Governor a try. What do they need to know about SQL Server versions or editions or anything like that as far as trying it out?

Mark: So as long as you’re on a version 2008 and higher you can try it out. Now the features you can try out are going to depend on which version you’re o. Obviously, 2016 has all new bells and whistles which that’s kind of good to mention as well. It’s not one of these technologies that seems like it’s going away. I would imagine they probably use Resource Governor in Azure to kind of separate workloads if you’re using like the Azure database or something like that. But they’ve been adding features to Resource Governor as of SQL 2016 so it’s definitely something they’re continuing to work on. At a minimum you’re going to be able to put some limits on CPU memory and then the concurrent requests. Concurrent request is actually where we started. Like I said we don’t really have a lot of control over the amount of work that’s coming from our application server and hitting the database. A lot of it is customer driven. So the way we originally kind of went at it was that we would reduce the concurrent requests per team so that everybody had some resources to use there. But once you get into later versions you can start doing things like limiting I/O so you can set a minimum, maximum amount of I/O. Like in that operations per second so it’s not like, you can’t limit like throughput or anything like that just the number of I/O operations per second.

Carlos: Right, and start creating additional bottlenecks, right. You’re like, you can only return this many records.

Mark: Yeah, one really actually kind of neat thing you can do, were you guys can’t remember it was kind of a whirlwind but you guys were ate PASS, the summit the 2016, yeah?

Carlos: Steve was.

Steve: Yeah, I was there.

Mark: So Sandisk had a query challenge setup where you had to tune. You had to make this query run as fast as possible but you couldn’t actually touch the query itself. You can only do things to the instance. You only had I think 5 minutes for your changes to be done and then you had to run the query and see what your time was. So we actually won that competition by using Resource Governor. Yes. One of the things you had to do is you had to, one of the things that really helped the performance in that case was rebuilding indexes but there wasn’t really enough time to rebuild all of the indexes that you needed to. But using Resource Governor you can set NUMA node affinity so we actually just dedicated a few CPU cores per index for the index rebuilds. So there’s a lot of really weird and interesting things you can do with Resource Governor if you take the time to learn all. NUMA node affinity is definitely one of those kind of edge cases that helped us there. I don’t know where you might use it in production but it was kind of a neat application, the Resource Governor.

Carlos: Yeah, so next year on your PASS you can’t.

Mark: And we might pull out some new tricks next year.

Steve: Ok, so then given that it’s been around since 2008 is that available on all editions of SQL Server or is it an Enterprise only feature, or who’s going to be able to use it there?

Mark: Unfortunately, it’s Enterprise only. I’m lucky enough to work in a shop where it’s Enterprise so that’s not a problem for me but that is an Enterprise only feature.

Carlos: That was interesting. And chatting with Jessica Moss, they were talking about the 2016 Service Pack 1 and how was that kind of opening up additional features. And taking a quick peek here it doesn’t looked like that one got opened up with the Service Pack 1.

Steve: No, I think I can remember when that Service Pack 1 announcement came out there were some people griping and wishing that Resource Governor had been included in Standard Edition after that. But I think it’s not there yet but maybe one day, who knows?

Carlos: Yeah, we’ll see.

Mark: Yeah, I mean even if they could release the subset of the features I think it would be useful for a lot of people. One, actually, this is super important thing to mention about it. And another thing that I wasn’t aware of when we first implement it at bit it’s been kind of useful, when you implement Resource Governor you set up what are called resource pools and then within each pool you setup workload group. The workload group is where the actual request get categorized to. But for every pool that you create it gets its own plan cache. So you can actually use resource governor as a way to kind of partition your plan cache. So cases where that might be really cool is if you’ve got like we do. We got multiple teams executing procedures. They might be running the same procedures with completely different parameters and causing some weird parameter sniffing issues but as soon as you kind of partition the plan cache those type of issues can go away. So that was kind of another neat side effect of implementing this.

Steve: Interesting. Now, you say they can go away but it seems like it might also hide them a little bit where if you have one plan that’s having parameter sniffing issues that’s only for one group of users. That might be more challenging to track down.

Mark: Yeah, it all depends on your workload obviously. And if somebody messes up and changes their connection string or does something weird that stops their work from being classified properly they might get dumped into the default workload group or something like that and we can see some issues there as well. Yeah definitely, when you’re using Resource Governor make sure that whatever you’re using to classify your workloads is something fairly stable.

Carlos: Well then where did you guys start with? I guess take us down that journey and let’s talk about, you know, I guess you mentioned setting it up. What were the first steps and how would you recommend people going about setting up Resource Governor.

Mark: So like, I think I said anyways, you kind of mentioned. You don’t have to setup any limits when you’re using Resource Governor so I think it’s really good like first step you can take is to just setup your resource pools and your workload groups and create your classifier. So the classifier is a function that lives in master and it is executed for basically every request that comes into the box. So you do want to make sure that it’s pretty efficient function because it’s going to be ran a lot. So yeah, if you’re just getting started you want to see if this is something that you want to use you could just setup the pool and the group. Get a classifier that sorts classifying work into the various groups.

Carlos: Now, you mentioned it should run efficiently but I thought it’s going to be like something that I just chose from setup. How am I going to choose an efficient one?

Mark: Oh no, yeah, yeah, there’s definitely nothing built-in. You’re going to have to write it from scratch.

Carlos: Oh, got you.

Mark: Yes, so when I say efficiently just make sure you’re not doing any extra work. There are some limits on what you can in a classifier function like in schema bounds you can’t access objects outside of master.dbo. So I think that’s actually there just to stop you from hurting yourself.

Steve: So then, just a back of a second, was that a stored procedure or a function you said you put in there?

Mark: Actually, it’s a function.

Steve: Ok.

Carlos: Got you.

Mark: The classifier is a function that you define.

Carlos: I feel like we just, I just found the reason why more people aren’t using Resource Governor.

Mark: Yeah, it could be it.

Carlos: You know, if you have to be building that function to do that for you I feel like that’s going to be a big hurdle to jump through.

Mark: So it’s actually not that hard. The function has one job really. So when I knew request comes in the Resource Governor classifier function looks at that request, looks at some different parameters of that request. So for example there is a few like variables that are setup by default, well not variables sorry, they are system functions. So like app_name fro example that we would use. So for your classifier function you would just look at app_name and then set up a case statement. That’s what I like to do. You could do if else wherever you like to do there but really all you’re doing is writing code that will look at that application name, figure out what group it belongs to and then just return the name of that group. So that’s all the logic there really using your classifier function. It’s one job. It’s just to return the workload group that this work should go into.

Carlos: Ok, so when the session comes in it’s just passing the app_name and then I will tell you what group you belong in.

Mark: Yeah, exactly and like I said, application name is just one example. There is a lot of different things that are available to you. Like username for example is another good one so if you’ve got like maybe all the stuff coming from your application you do want to categorize into these groups and put limits on them and all that. But if something comes in from the web interface maybe it’s using a different username you could then classify that to be unlimited and not have limits at all.  

Steve: So then you’ve got your classifier function set up and it’s sitting there in the master database and then how do you go about using that then through the resource governor.

Mark: So when you’re configuring, configuring is a little bit too much of a big word to say when you’re dealing with resource governor. When you’re initially setting it up, to enable it you just run Alter Resource Governor Reconfigure. That’s all, it is running. It’s not classifying anything but it’s running. So then you run another alter statement where you just set the classifier function and that’s it. So once you specify the classifier function, anything that’s coming into the instance is going to run through that function.

Carlos: We still do have limits here?

Mark: No, there are no limits here. So what you have right now is basically the metrics on your various workload groups. So there is a few DMVs you can look at for this information but it will show you the total number of requests that are coming through for instance. So if you were just interested to see how much work all these various groups are doing all that is available to you.

Carlos: And I think that’s a great suggestion.

Mark: Oh yeah, definitely. It’s a good way to get some insight into what’s going on on your instance without really having to do tons of work. You could probably get this running on maybe, you know, 20-30 minutes.

Carlos: And it’s base lining, right. I want to baseline my system and then I’m going to start making some tweaks.

Mark: Exactly. One thing to mention too is those DMVs are accumulative so as requests come in the number just goes up right. So if you want to monitor all you’re going have to, like I do, you’re going to have to setup a table and then just store the deltas in there from last time that kind of thing.

Carlos: Alright, can you purge those or reset them.

Mark: You can, I can’t remember if you, so I know you can reset them obviously like most things when you reboot the instance or restart the services. I don’t know if there’s any like DBCC functions you could use to clear that out. One of the DMVs though which is kind of interesting because I don’t know if many DMVs have this, but one of the DMVs will actually give you a date of when things were last cleared. So you kind of you know how long it’s been gathering statistics.

Carlos: Right. Yeah, I can’t think of another DMV that does that for me.

Mark: Yeah.

Steve: No, I can’t think of one either.

Mark: It would be nice if they did, but yeah.

Steve: Right, right. Yeah, especially those index ones. They’re having problems like the index get rebuilt and their counts and what not will get cleared. You know like, “What!”

Mark: it would be very nice.

Steve: Yeah.

Carlos: Ok, so can you then maybe elaborate a little bit more on sort of the idea of resource pools versus workload groups, and sort of how those.

Mark: Yeah, so this is kind of a piece of over engineering in my opinion.

Carlos: Interesting.

Mark: Yeah, so I like to think of, I like to think of this as kind of hierarchy rights. You got your SQL Server, underneath that you got your resource pools, and then underneath that you’ve got workload groups. The theory being is that you could have a resource pool with multiple workload groups in it. I’ve never seen anybody implement it like that. I don’t implement it like that either. I usually just do for every, again well run with my example of teams, right. For every team, they’ll have a resource pool and a workload group. The difference between the two is just what kind of limits that you can set at which level. So at the resource pool level you can set limits on the minimum and maximum CPU that can be use, the amount of memory that each request can use, you can set your I/O caps and your NUMA node affinity. And then below that at the workload group is where you can set like max CPU time in seconds so not percentage but actual time. You can also setup the concurrent request limits at the workload group and then also maxed up at the workload group. So there are different things you can set in each level but like I said I’ve never set it up where I’m not doing a single pool with a single workload group in it, you know, for all those various type of work that I want to classify.

Steve: Yeah, ok. So then once you’ve done the classifier function and you have things assigned to the appropriate resource pool and workload group as the connections come in. Have you ever run to any issues if you limit something too much that it causes blocking to go bad or I mean something takes longer than it did before and now other things or bottleneck waiting on that?

Mark: Oh yeah, that’s always a big risk. We’ve had issues.

Carlos: And almost by definition you’re asking for that because you’re now saying in the example of this reporting services query or whatever that hey I don’t want you to be taken So that means you’re going to have to take a little bit longer, right?

Mark: Oh yeah, definitely. And if you’re in the kind of place where you’ve got a lot of different applications hitting your instance but none of them are really aware of each other and you slow one of them down and it starts holding locks or something for longer than it was before. Yeah, I mean it can cause havoc. So it’s always best in my opinion with Resource Governor to start with very high limits that you don’t think you’re really going to hit right away and then kind of walk it down a little bit until you find a nice comfortable place. But yeah, it’s always a risk when you’re implementing something like this that you could kind of rick havoc for other people trying to use your server.

Carlos: So we talked about implementing it in production and do you have this in lower environments as well?

Mark: Yes, we do. If anything is awesome on your development environment to implement this with no limits because of all the reporting you get. One example that I like to give people because it kind of helps it kind of sync in. At any given time I can tell you exactly how much CPU time. One of our application teams are using on any of our instances. And that’s all using, you know, just stuff built into SQL. It’s not some special monitoring. I’ve got the application layer just with Resource Governor I can tell you, you know, what percentage of CPU time was spent by what team in our development environment. And that’s really powerful because then you can go to that team and be like, “What you guys are doing here?”

Steve: Yeah, that’s really interesting because in my experience that development servers are usually a bit slower than production. And you end up with developer griping about why it’s so slow? And often times it’s because there is just not enough hardware and horsepower there basically to service everything the developers are doing. But if you could come back and say, “Well, it’s slow because this team over here is using 90% of the CPU. That might give some interesting insight into who should be paying for the upgrade on that SQL Server. Exactly, another kind of good thing you can do with Resource Governor is you can kind of slow down bursting workloads. So this is also come the development environments, right, your QA tests, automated tests running midnight everyday or whatever. You can set up limits on like concurrent requests for example so that when those tests are running at midnight it’s not going to take everything else down with it. It’s only going to be able to maybe execute three or four requests at a time and everything else will be able to run as it was. It might not run as fast as it was before but it will still be able to actually run.

Carlos: Right, and those normally are performance test anyway. Can this execute, will it complete successfully.

Mark: Yeah, does it work. So when you’re in those type of situations, yes, it’s really a great tool. And you can kind of get more out of your lower resource instances because things are restricted a little bit more that can hit it as hard.

Steve: Ok, so can you share with us maybe a little bit more around the monitoring of the stats or the DMVs to better understand like where someone would go to start learning that piece of it?

Mark: Yeah, sure. I mean, not to self promote too much here but actually I’ve got a blogpost not just this topic – m82labs.com/resource-governor. I’ve got a whole post on, even download the procedure you would setup together the statistics. But I can walk you guys through what I do. Might not be for everybody but what we do in our environment is every, I don’t want to say it’s every 5 minutes I do this, but I’ve got a table set up that stores, it’s going to come from all the different stats and things that I’m interested in. Every five minutes a procedure runs that grabs the latest data from the DMVs inserts it into the table and also uses the existing data in the table to calculate the delta, the change in all these different statistics since last time it ran. And then I push all that information into elastic search. And then from there we can graph it out in any number of tools and that’s our primary way of kind of visualizing how things are performing in our instances.

Carlos: Interesting strategy there.

Carlos: Any other links that we might reference here we’ll have available on the show notes page at sqldatapartners.com/resourcegovernor.

Mark: One thing to mention too and this was kind of surprising. Not to brag on Microsoft or anything but the documentation for Resource Governor at Microsoft’s page is amazing. They’ve got very in-depth documentation on really every aspect of configuration and what all the different DMVs are for, you know, what the different field means. This is definitely one of those cases where I head over there to take a look. It’s a wealth of information there. They even give you some kind of like example scenarios where you might use it so it’s very useful.

Carlos: Ok, so as far as then other scenarios that you might have seen where the reason the people are using Resource Governor, maybe a little bit of background story or more information on like why people are using it.

Mark: Honestly, I haven’t run into a whole lot of people that use it.

Steve: Ok.

Mark: Yeah, I mean for our use cases, I would imagine our use cases are pretty standard where you would see most people using it for outside the monitoring piece. But yeah, I haven’t really seen a lot of people that are using it. I’ll see the occasional talk like a SQL Saturday. But yeah, I haven’t really met anybody else that uses it that could also be because it’s an Enterprise feature and not everybody is in Enterprise.

Carlos: Right, now, you said that in a classifier function that you were using the login name which is how you identify who’s on which team.

Mark: Well, both actually. I use the app name and then in some cases we use the login name as well.

Steve: Ok, the app name and login name. Ok, so even if someone is using a shared login amongst multiple applications you can use that app name to be able to classify it appropriately base off of where that app is coming from.

Mark: Exactly.

Steve: Ok.

Mark: Yeah, and there is nothing special about the classifier function really how you’re writing it. So you can do things like, you know, where app name is like this or things like that so you’ve got pretty much your full range of TSQL that you can use within the function there. Make sure it’s not too expensive.

Steve: Right, right, so any that you’ve come across or anything that like you just tried it out and things went really bad and people might want to know about before giving it a try.

Mark: Yes, definitely so the kind of, you know, derby dragons here kind of scenarios with Resource Governor is you want to make sure that you’re really really read about the different limits you can set because they may not behave how you expect. Max CPU is a perfect example. Like what would you expect max CPU to do. If you set max CPU to 50% you would expect that whatever is in that group can only use 50% CPU, right? That’s not the case. How it’s defined is that in cases of CPU contention it can only use 50%. And how CPU contention is defined is kind of a mystery. I’m not sure. I’ve never actually found a good definition. But it sounds like if there’s other workload running on the box it will try to make sure that this can only use 50% but if this task comes through and that’s being governed. If it’s running in the middle of the night when nothing else is going on in the box it could still use 100% of the CPU even if you’ve got that limit set.

Carlos: So it’s almost like a tapering effect, right? So, you know, my process is running 100 miles an hour and then oh somebody else wants to come onto the track. Ok, well, you got to slow down and so this guy can speed up kind of a thing.

Mark: Exactly, so it can definitely still be useful but if you’re expecting it to just hit a hard ceiling of 50% that’s not going to happen. That being said, in the later version they did add a CPU cap that you could put on that would do just that. So there’s actually minimum CPU, max CPU and then a cap. So there are three different values you can set as far as the CPU is concerned.

Steve: Ok, so then if there’s minimum load on the server and my connection gets assigned to a resource pool with a max of 50% CPU but there is no other load O might be able to use of upwards of 100%. But then if I have a long running job on that connection that maybe runs for an hour, hopefully not, but let’s say it does. And someone else comes along and says, “I need 50% of the CPU”, but that connects and starts using CPU is it then going to take my existing connection that was using around 100% and squeeze it down to 50% or is that going to find a new connection?

Mark: That I’m not sure. I could definitely see where it would have the opportunity to limit it, right. But I’m not sure. I’m not sure what happens to that existing stuff that’s running. That’s another thing I would have to test.

Steve: Since like an interesting blog post there Mark.

Mark: There could be a whole blog post just around the various behaviors of these things. Another interesting one is you can set the minimum and maximum I/O. So it will limit the number of I/O operations that can have per second. But I have found in my testing that even if I set the max I/O to something that is impossible like 10 million to 20 million operations per second. Even when I set the limit that high there are still I/O operations that are being throttled based on what the DMVs are showing so that’s something I going to be looking further but that’s really why those metrics are important to look at. So even if you think you know what some of these limits are going to do I highly recommend people set it up in a test environment and even run some load test against it if you can if you’ve got that kind of setup. And just see if the DMVs are responding how you would expect because the I/O thing I didn’t expect at all. And the reason I start playing with it is because when you enable the I/O throttling even if your limits are completely nuts and something you never going to hit. It actually opens up another DMV that you can look at that will show you the I/O operations per volume, per workload group which is really cool because if you’ve got TempDB on a different volume than your data files you can actually look at TempDB utilization per workload group using that DMV. But once I start seeing that’s limiting the I/O even when it shouldn’t I can go of nervous about that one. It’s going to take a little more research.

Carlos: Yes, that sounds like caution flag there.

Mark: Yeah, definitely and that seems to be the case of the lot of these. There is just this little weird kind of scenarios where you might want to do a little more research. Fortunately though like I’ve mentioned before, the Microsoft’s documentation on this actually covers most of this scenarios. Max up would be another interesting one. You can set a max up for workload group that’s higher than the max up for instance and it will go ahead and it will honor that. But if there’s a request that’s coming through that manually defines a max up that’s higher than what the group is it will not honor that. It will only go up as high as what you have set at the group level. There’s a lot of, you know, like tweaks that you can do but, yeah, and see how it gets applied.

Mark: If you’re using like a Developer Edition, since obviously this is an Enterprise Feature it should be available on Developer Edition as well. So even if you’re not going to be using it on production because you’re running on Standard if you could get it on your development environment it could have a lot of use there or even just from the reporting aspects and also potentially from being able to kind of throttle your work a little bit so that maybe you can get a little bit more use out of those testing instances.

Steve: So shall we go ahead and do SQL Family then?

Mark: Yeah, let’s go ahead.

Carlos: So Mark, tell us how you get started with SQL Server?

Mark: I’m pretty much your standard accidental DBA. I was in a company and the DBA left. There was really nobody else left to fill the shoes so I kind of stepped up and started learning about SQL Server. I want to say we are running on SQL Server 2000 at that time. And I just kind of stuck with that ever since.

Steve: So if you could change just one thing about SQL Server, what would it be?

Mark: We actually, I’ve be interested to see what listeners have to say about this one because we would have a use for this feature at ChannelAdvisor but when we talk to Microsoft about it they weren’t. I don’t know if they were entirely sold on the idea. We would desperately love to have a query hint called notindex. So instead of forcing an index you can actually force a query to use any index but this index. We’ve got a lot of cases where we have an index that’s been created for a very specific purpose. But other queries will pick it up and start using it and whenever they do performance just tanks.

Carlos: Sure, because they’re grabbing on the locks on the index.

Mark: Well, not even that. It might just be the index might not just be that great for this other subset of queries.

Carlos: Oh, really but the optimizer is still picking it.

Mark: Yes. I don’t know why it’s picking it. But yeah, we do have cases where the optimizer will pick an index. We’ll hop on the box to see what the issue is and like, “Oh, this index again, great.” But you can’t get rid of it because there are a lot of queries that actually benefit from it, so a notindex hint would be fantastic.

Steve: So basically what whatever index you would normally pick like exclude this from your list.  

Mark: Exactly.

Steve: Yup. I would be interest in that.

Mark: Yeah, we would love that.

Carlos: What’s the best piece of career advice you’ve ever received.

Mark: So I thought about this here. And I honestly haven’t received a whole lot of career advice directly. But indirectly just from kind of absorbing a manager that I had previously. I think what I could take away from that experience with him was to always say what you mean and always ask for exactly what you want. So don’t get caught up in the kind of the games, the political games that happen in the office or whenever you have in a work. Just always make sure people know how you feel about stuff and always be honest and kind of straightforward. Even if it doesn’t always work I think in the long run it is definitely good advice to follow.

Steve: If you could have one superhero power what would it be and why would you want it?

Mark: I don’t remember which superhero this is but there might even be a few superheroes that have the ability to kind of duplicate themselves. Like I may have mentioned before I’ve got four kids so it’s usually pretty hectic inside the house. If I could duplicate myself and get some more stuffs around the house that would be fantastic. And then of course we’ve got SQL Saturday’s every weekend. All kinds of conferences and all that stuff so yeah duplicating myself would be very beneficial.

Carlos: How many SQL Saturdays could you go to if you could duplicate yourself several times?

Carlos: The mental strain I guess of concentrating on all of those different places. It’s also interesting as I think about duplication. It seems like and I guess this is just from my limited experience that those villains generally that have the duplication ability are always using it for nefarious purposes, right? So to have someone to actually be able to use it for noble purposes would be a change of pace there I think.

Mark: There we go.

Carlos: Yeah. Well, Marc thanks so much for being with us today.

Mark: Thanks for having me it’s been fun.

Steve: Thanks Mark, definitely some good information about resource governor today and thanks for being on the show.