Episode 71: BI Tools of the Trade

It is easy to feel like the odd man (or gal) out in the SQL Community.  We are constantly hearing about new features, products, and architectures and our companies may not always have the same enthusiasm when we describe what could be.

This is probably even more true in the Analytics space as much of the marketing has been pointed in that direction.  Even PASS tried to create a conference just for Analytics.  While there are some amazing new technologies out there–we might–GASP–not need them in every scenario.

Our guest today is Kevin Wilkie, a BI architect with Innovative Architects and he talks to us about some of the tools he uses and spoiler–they aren’t all that fancy.  He also talks with us about how he manages the tech and business users so each feels comfortable and each can get their work done.

 Episode Quote

“Excel’s that tool that’s sort of magically hiding in front of everyone.”

Listen to Learn

  • Some of the tools Kevin uses in his analytics role
  • The criteria you should use to determine whether you should use a new tool
  • Why using simple tools might hold an advantage over ‘better’ tools
  • The data profiling task in SSIS

Data Profiling Task
VLOOKUP
Excel Solver Tool
Excel Data Analysis
Master Data Services

About Kevin Wilkie

BI ToolsKevin Wilkie is BI architect with Innovative Architects.  He is a community participant and helps organize SQL events in the greater Atlanta area.  He is a graduate of Southern Polytechnic State University.  He also gives the SQL Data Partners podcast two thumbs WAY up.

Transcription: BI Tools

Carlos:                  Okay, well Kevin, welcome to the show.

Kevin:                   Thank you Carlos. Good to be here.

Carlos:                  Thanks for coming on with us today. So compañeros for those you on the east coast, you might have run into Kevin, he’s just out the Atlanta area. A bit of a big, I’ll say supporter of the show, particularly, kind of early on getting some supportive feedback from Kevin was helpful to me. I appreciate now, that we’re able to have him on and talk a little bit with him about some of the things that he’s doing. Ultimately, I think our idea today, or our focus is going to be that, the analytics environments, have started to take over more and more of the attention that the data world is getting. Lots of focus is being placed there, and I think as administrators we want to be able to start looking there, how do we potentially move over into the analytics world, and what tools are those analytics folks using. Ultimately, I think that’s where our focus is, tools, analytics, how and when to appropriately apply them.

We talk about these tools, and ultimately we’re talking about the Microsoft stack, right? These are going to be tools you’ve heard of and probably installed before. Let’s just go ahead and start that conversation, Kevin, why don’t you talk about some of these tools that you’re using for data analysis.

Kevin:                   Thank you Carlos. I think one of my favorite tools, since I’ve been doing SQL for years, is SQL itself. I just get into the actual SQL itself to play with the database and that simple queries just so that I can see the overall form of the database of many of the tables. Don’t get really into the nitty-gritty as much, I try not to in SQL Server itself, but I like to at least the overall form, we see things like the foreign keys, the primary keys, any constraints that the table will have. You know kind of where the data will lie. That’s where we’re going to try to stay, just at that level with SQL server itself, and for data analysis in the overall distinct values there are, how far the field exists in the table, like looking in the max, or things like that.

Carlos:                  I think maybe, let’s just step back just for a moment, and talk about, we’re talking about analytics, you mentioned you like to spend your time in SQL Server, I think that most people would say that is true. Ultimately, when we need to start looking at tools, is either because we have either questions that we want to ask the data, that we can’t get currently. Or we want to look at the data a different way, or present that to other people so that they can review it and ask questions of that data. Right?

It’s interesting, you went from the SQL Server, and then kind of into SSIS, but you used that as more of an ETL, it almost sounds like a data cleansing, or a data integrity operation, rather than moving it from one place to the other.

Kevin:                   In this case, you’re right. It is just, for this one SSIS is fairly good at just, like the task itself says, its profiling data. You’re just seeing the basic outlines of how far it can go from min to max, maybe a few averages and standard deviations, but not overly heavy on what you can do with it, and how much you can see with it.

Especially, the biggest thing I see is how many nulls were in the table, because if you have a four hundred thousand row table, and three hundred and ninety eight thousand are nulls, usually the business needs to know that, we need to rethink why that field exists in the table.

Carlos:                  Sure. There’s not enough data there to validate, or use that as a valid option.

Steve:                   Just a question around that Kevin, I know the data profiling task in SSIS that you mentioned, that’s not something that I have myself used in the past, but the things you’re describing there, are things that I would normally have done in SQL Server and TSQL. I’m just curious, what are the benefits of getting it that with the data profiling task, rather than just running queries to do it?

Kevin:                   Because with the data profiling task, I can do it a little bit faster, and I don’t have to do as much work … As much work to actually seek for each, let me back up … For example, if I wanted to do the min/max, standard deviations, and averages, I have to do a little bit of work on a number, for each field, I have to write different equations for SQL.

Steve:                   Okay.

Kevin:                   Whereas, data profiling task, I can do it in one shot.

Steve:                   Okay, that makes sense, sounds like something I need to go try (laughter).

Carlos:                  Now also in your role, you mentioned being able touch SQL server, and then also be able to use SSIS. Now generally, while that line is getting thinner and thinner, there has traditionally kind of been a border between, the administrators, or the ‘keepers of the data’, and then what we’re going to allow them users access to. Now, we think in analytics, we’re talk about end users and I guess there are the Power BI folks we see in the community, but generally, we’re think of our business users. Any heartache with giving them access to SSIS? Or is that something more purely for the administrator in kind of a prep move?

Kevin:                   It’s really a tool I use when I’m setting up, or doing the beginning phases of a warehouse. I don’t really like to go, I wouldn’t like to go a normal user access, because there obviously are multiple tools that the SSIS world can do. And even SQL sometimes will intimidate the normal user too much, that shows us run away.

Carlos:                  Sure.

Kevin:                   I like to keep these two tools are really the IT use. If you really know what you’re doing, these two tools are great, but for the normal everyday user, not so much.

Carlos:                  Okay.

Steve:                   Okay, so then, if you’re considering SSIS and then TSQL, and SQL server for the IT side of things, and then the normal users come along and they want to be able to go and do some analysis, what are you recommending, or how are you going about doing that with other tools?

Kevin:                   Well, there are two tools, well two branches of users, I like to call it; the more advanced users, I would have in Power BI, Tableau, something that there’s a lot of features, almost IT, but not quite. IT can still have security on it so they can make sure they’re not going crazy, and adding a bunch of stuff that they shouldn’t be, or even looking at data they shouldn’t be. Where in both those worlds, you can still do 98% of everything you’ll ever want, at least for the Power users, but, also, the second class of users, will mix in with the Power users because, I like to give them a tool that they really know rather well, it’s Excel.

Carlos:                  (Laughter). Oh boy, we’re going to have to wash your mouth out with soap now Kevin.

Steve:                   Excel’s that tool that’s sort of magically hiding in front of everyone.

Kevin:                   Yeah, but it has a lot of features that everybody’s gotten use to, everybody can play with, but the sad thing is everybody likes to think they’re a expert at it. With some of these tools, you really, really do have to know what you’re doing, but at least you’re in Excel … You can play around some other ways and still get the same data, but there are lot of easier ways. Even, VLOOKUP for some people at performance, automation, right? Repeatability. Now all of a sudden, we think about Excel, I don’t think about any of those things (laughter), in that term.

Can you give us some thoughts, or under what circumstances should we be using Excel, versus all these automated things we like to be using?

Kevin:                   I like to use Excel, for more of proof of concept. To actually see, “oh yeah, we can do this. This makes more sense this way. I have only like twenty-thousand rows.” Excel is great for that.

Carlos:                  Sure.

Kevin:                   Especially if it’s already in a spread, in less space, it’s 99.9% of our stuff is spreadsheets. Why did I take it out of Excel and put it in SQL Server, massage it a little bit, then run through with it? Let’s just go ahead and do what we need to in Excel, that’s what people want, let’s give it to them in Excel.

Carlos:                  You know, one area I do think that we could probably be a little, or we might leverage some of those Excel users, and that is, in that data validation parts. Because the business users are coming to us with these reports that that want written, they’re going to be showing us that data in Excel, and I think we could potentially help leverage that to say, “well look, this is the process that I went through, let’s validate that this is, I guess, maybe before I build it, let’s validate that process with them. Right? I take this data, I do this to it, I apply this, and then I add that, and this special sauce, and then bam! There’s my number.” I think that could potentially help accelerate that report development process.

Kevin:                   Right. Heck, even Excel itself can do a lot of what-if scenarios. It can do the T-test, it can do a lot of Nola tests that more of the data analysis sites will use. You can do all your tests there without having to go to ‘R’ or choice is.

Steve:                   When you’re talking about Excel for data analysis at that point, do you find that the Excel Solver tool is being used quite a bit for that?

Kevin:                   I’ve used Solver, not as much as I’ve used the data analysis itself.

Steve:                   Okay.

Kevin:                   For those who don’t know, both data analysis and Solver are just very simple add-ins that are added, are simply Microsoft Excel add-ons. They’re almost loaded straight out of, from … When you loaded Excel, but it’s like one or two clicks and they’re there.

Steve:                   Right, so for instance, I’ve seen the Solver used in data analysis to run the what-if scenarios, where it has to work through a number of variables and figure out, “well what if we adjusted this in this way, or in that way.” I’ve seen different times, trying to port that same kind of thing into SQL Server, once somebody’s figured that out in Excel and finding that that can be very challenging.

Kevin:                   Yeah, it’s not simple because Excel is made for statistics and numbers, it’s made, “if this variable changes, and this variable changes, and this variable changes, what’s the one, I can get the most I can get out of this other number?” Excel works great for that. I used to do that SQL, it would take a little while to figure that one out, because it changes for each scenario.

Steve:                   Yup. As we go through this, we’ve talked about different tools, and we’ve mentioned SSIS, and Excel, and SQL, what else do you see being used for this analysis side of things?

Kevin:                   We’ve started to use SSRS, especially the new SSRS 2016. It has, the great mapping capabilities that more and more of our clients are starting to really like, because they’re not just seeing the data that’s actually in their little area, these companies that want to do more and more stuff outside of Georgia, the south, United States, they want to do stuff all over the world, so it’s great so they can see, how is their product being bought, in Atlanta, in Richmond, in Seattle, wherever. They can do all kinds of analysis even down to the, at least the city level, I’m sure some of these people have done it even further down than that.

that you are in the architect role, at least that’s one of those hats that you wear, but how much of a learning curve is, building SSIS reports, particularly ones that come from stored procedures, I think are kind of the very, I won’t say basic, but that’s like, a lot of people cut their teeth on those kinds of scenarios. Right?

Now going to a report that you mentioned that includes the geography components, what kind of learning curve is involved there?

Kevin:                   For SSRS, at least for me, there wasn’t a huge amount, because for the most part, once you have the city, most of the data will have cities, states, zip, at worst it will have … Most of our stuff will have that data in there …

Carlos:                  Mm-hmm (affirmative)

Kevin:                   You’re good. Thankfully SSRS picks it up, runs it with it.

Carlos:                  Oh wow, okay, so it’s really just, I like this type of map, here are my columns and there you go, I can see the data.

Kevin:                   Yup, because it’s all built in and going for bangs, it’s great for that.

Carlos:                  Got you.

Steve:                   So it’s basically using Bing as your presentation engine, wrapped in SSRS?

Kevin:                   Yes, that’s exactly what it does. At least from what I’ve been able to tell, it is using Bing to just roll the data out there, and it figures out “oh this is where Atlanta this is, this is where Richmond is, this is where the counties around it are.” Figures it out rather quickly, much more quickly than I would expect.

Carlos:                  Now another tool that I’ve heard talked a lot about, and I’ve heard scenarios, but I haven’t, it seems like only in very niche situations, or with people that really know their stuff, have been able to get Master Data Services to work well. Am I off base there, or why is there option of Master Data Services.

Kevin:                   I wish I could tell you (laughter). I think there’s a couple things going on with Master Data Services, that makes it where people don’t want to use it as much. First part is, it can be highly taxing on the actual business user themselves.

Carlos:                  Mm-hmm (affirmative)-

Kevin:                   Because they have, somebody has to be on it every day making sure, “yes, this one is a real valid answer, let’s put it into our data warehouse. No, this one really means this.” These are the people that are the think we’re actually pushing it as much as we should be, because, especially well, let me back up, with the small ten, fifteen people shop, there’s really not a big use for this. I wouldn’t think, there are probably scenarios where yes it would be great, but for the big, major companies, and some of the groups that are within it, it can be useful, but I don’t think that the people that are in charge, see the value as much as it should be.

Carlos:                  Okay compañeros, I just want to ask a question to all of you, would you be interested in hearing more about Master Data Services? Again, I’ve talked with lots of people that are mostly product managers, so if you’re interested in us doing an episode on that, let me know. I’ve kind of shied away from it, just because, again, it kind of seems like you really have to spend quite a bit of time to get up to speed on it. But if it’s something you think we should cover, let us know, hashtag SQL podcasts.

Steve:                   It’s interesting on that one is that, with Master Data Services, it seems like there’s two categories of people I’ve seen. There’s the people who swear by it, and it’s the best thing they’ve ever used. And there’s the people who swear at it (laughter), and it’s the most painful thing they’ve ever used. Maybe it’s that there’s some misunderstanding there, but I think that there’s certainly a lot of value there that people could, if you have to do the data cleansing side of things, you could get with that.

Kevin:                   I agree. Especially since, it’s been out since 2008, R2, it’s been available for a rather long time, more and more people should be at least heard of it, like it enough to at least try it, but it’s just not happening, for whatever reason.

Carlos:                  But I think some of that, it needs to maybe get a little more love from Microsoft, or from an interface perspective, it still feels a little bit form-ish, and I think that may be a part of it as well.

Kevin:                   Now Carlos, you’re not talking about the extremely early versions of it, it was, I agree completely bad, 2008 R2 was horrible. Twenty, well was a little better, I haven’t seen the 2016 version of it, but I believe they’ve updated it some.

Carlos:                  Okay, I admit I haven’t looked at 2016, I’ve heard that they’ve been doing some updating, but I haven’t seen any screenshots.

Kevin:                   Yep, my wife is going to Leblanc, who’s also from the Atlanta area, on earlier, talking about Power BI, and of course that continues to change. I guess, thoughts about introducing that, or making that part of the repertoire of analytics.

Kevin:                   I think, just like everything else, you have to play with it, you have to work it in, once you see how, I don’t want to say life-shattering, but it can really help your world by using Power BI, or all the tools that are available, especially even MDS, just to see if it’s available. Test it, play with it, see if it works for you, some of these things just won’t …

Carlos:                  Mm-hmm (affirmative)

Kevin:                   But others will, and you’ll see, it’s much easier once you actually start playing with it, and know what you can do with each of these tools. That’s why I’m encouraging, just try some of these things, try different scenarios, see what this works, see what works, see what doesn’t.

Carlos:                  I feel like some of the rub with the tools as well, is that, they don’t … It takes a cross functional team, right, to make that happen. We talk about wearing different hats, and some people can wear all the hats, but even in smaller organizations, there is some divide, you’re going to have to share that workload, and I think where we run into some heartburn is that, not a single person is going to know all those tools, or able to do all of those steps for the analytics. I think maybe, that’s the way sometimes, at least on the administrator side, we tend to balk at it a bit.

Kevin:                   Agree. I think a lot of the DBA’s I’ve talked to, they want to, it’s not just DBA’s, it’s any programmer at all will try to say, “oh, I do this, I am good, I know what I’m doing, I can do it all.” Especially in the data analysis world, you really can’t, there is so much stuff out there, you may have a tool that can do the job, but it may not be the best tool. But you can do it, and you can get around to it, so you still like, just like SQL, you have to have friends out there who know different areas, who can help you with whatnot. That’s one of the things, sometimes you will get just try talking it out with them, sometimes that’s the best help.

Carlos:                  Right.

Steve:                   Yeah, that’s a good point. On the different tools, I kind of look at it as the tools that somebody might have in their toolbox. You got, maybe you got a hammer, and a drill, and a screwdriver, and Excel, and Power BI, and SQL are kind of the equivalent of those, where one of them might be the right tool to do one job, but maybe not the right tool to do another job. You use a screwdriver to screw in a screw, you could probably get that same screw screwed in with a hammer, or at least hammered in, but it might not be the best way of doing it. Sometimes with Excel versus Power BI, or the other tools, it’s kind of the same kind of comparison where you might be able to do something really quick in Excel, that might take you more time in Power BI or vice versa. already there, just do it there and be done with it.

Steve:                   Exactly, and I think there’s lot of the business users out there, who may not have that SQL Server experience, but if they can pull the data into Excel, then they can use it in an area that’s comfortable for them.

Kevin:                   Not even bother with the IT department which …

Steve:                   Yep.

Kevin:                   Somebody has thirty thousand things going on, one thing that’s easier to do will be helpful.

Steve:                   That’s a very good point.

Carlos:                  (Laughter) Okay, so last thoughts, or as we kind of wrap this up.

Kevin:                   I just want to encourage more people to actually play with different tools that are available. Don’t just, “let’s play with SQL Server, we can do it all on SQL Server.” Now you have thirty thousand tools out there, not just what is normally setup for the IT, but for, you have Excel, you have a lot of open source things out there that you can get and use, that use SQL Server, or Bing to get better. Great tools, lot of people have put a lot of time into some of this, and some of them may be quicker than your regular tools you normally go for. I mean, even at worst, you’ll learn something new about yourself, or about the tools, or what scenarios it can be used, and what it shouldn’t be used for.

Carlos:                  Some of that I think though, has to be, it comes from a business culture perspective, because I think, like you mentioned, the business users are going to bring us an Excel file, or they’re going to bring us some handmade report, if you will, that came from who knows where, and are going to be like, “I want this.” So I think part of that is, maybe a little interpersonal, or personal relationship skills, to say, “you know what, let’s talk about this, let’s see if we can’t play with this other tool, or look at this in a slightly different way to get where we need to go.”

Kevin:                   Right, in that case, you really have to work with your business team. If, sometimes I have work with teams to, this is always how it’s always been, and this is how it’ll always be, I need it copied over at Thesaurus. I want a report everyday, at such and such time, do not talk to me about any other things, this is what I want, and this is what I need. End of story.

Carlos:                  Right.

Kevin:                   And then there’s the one’s you can work with.

Carlos:                  And there is an advantage, at least, to having the users know what they want. Right?

Kevin:                   Yes, and know how to get there. That’s great help to all of us.

Carlos:                  Right. Okay, should we do SQL family?

Kevin:                   I like it.

Steve:                   Yes, let’s do SQL family.

Carlos:                  So Kevin, how do you go about keeping up with technology, and all the changes that are happening, especially, in the SQL Server area?

Kevin:                   I read lots of blogs. I have a old school, I have a RSS feed, lots of those, and listen to lots of webcasts. My favorite is just SQL Server user groups. I learn probably more through those than I will through any amount of reading, or anything I can think of to get all the information that I could ever want. Plus, it let’s me see friends that I haven’t seen in quite some time. User groups are the number one way of getting, my way of getting data. It’s everything.

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

Kevin:                   All right, this is where the DBA out of me comes out. The auto close feature, that is to me one of the dumbest things I have ever seen out there on SQL Server. So many of the brand new DBA’s will try to say, “oh, let’s just hit that, and we’ll be fine, we won’t ever have to use the database again.” Oh my God, it would drive me insane when I would start looking through the logs, and I start seeing “Closed, closed connection, closed connection” What? No, let’s just get rid of that feature once and for all. I don’t want to see it at all, I don’t, I’ve never even seen why it’s even there.

Steve:                   Good point. Okay, so can you tell us about the best piece of career advice you’ve ever received?

Kevin:                   This was from my wife, who listens to this podcast along with me every week. She actually said, early on, even when we were starting dating, she told me that, at night, how did she put it … No matter what happens, as long as the systems are still running, and you’re just doing performance tuning, it’ll all still be there in the morning. Because, like all SQL developer, well auto-developers period, we try to get two or three more seconds faster for all of our short procedures. It can wait, don’t just stay in the office until nine, or even your own work at home office, don’t stay down there until midnight, or two in the morning. See your kids, see your family, see somebody. Don’t just stay in the office and work yourself to death. Get out.

Carlos:                  Right.

Steve:                   That’s a really great point, and I think that, when people do get out, or have something else outside of just their work life, they end up doing a much better job in the time that they’re there doing their job.

Kevin:                   Amen.

Steve:                   Very good point.

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

Kevin:                   Teleportation, yup. I notice y’all have been lately talking about “I want to be able to fly.” Oh heck no, I’m going to be teleporting because I’m going to be there now (laughter). So flying towards there, heck with it, I want to teleport.

Steve:                   (Laughter) I like that.

Carlos:                  There you go. Well Kevin thanks so much for being on the show with us today.

Kevin:                   Oh, thank y’all, that was great. That was a great time.

Carlos:                  Yeah, we do appreciate it, you taking a little time with us.

Episode 70: SQL Server on Linux

SQL Server Loves LinuxIn the keynote of PASS summit 2016 Rohan Kumar, the program manager for SQL Server proudly displayed a database restore to a SQL Server instance installed on Linux.  What makes this all even more interesting is the database was sourced from a Windows server.  While it might not sound like much, the number of operating systems SQL Server runs on has now doubled.  This is no small feat and our guest today Travis Wright is the program manager for the SQL Server on Linux migration.  While I have to admit I was not super excited about the news when it came out last year, I am definitely more interested and think there is a huge opportunity for SQL Server administrators to get access to jobs they wouldn’t have before.  I know you will enjoy this interview with Travis.

Episode Quote

“Why are we doing this? . . .  It comes down to the customers have been asking us to do this for a long time.” Travis Wright

Listen to Learn

  • Why are they doing this to us? (come on, that is funny)
  • What features are going to be supported.
  • The version of Linux supported.
  • What type of integrations are now possible – think WordPress on SQL Server.
  • How do I kick the tires on this?

Travis on Twitter
SQL Server on Linux
Data Platform insider blog
SQL Server on Linux Preview

About Travis Wright

SQL Server on LinuxTravis Wright is the program manager for the SQL Server transition to SQL Server on Linux.  Travis is the father of 3 kids and is currently on his second tour with Microsoft.  He was heavily involved in the system center team and is now putting on his skills to us on the Linux team.  He describes himself this way–“In a room full of “suits” and “propeller heads”, you can find me in the middle fluently talking about business strategies, priorities, requirements, financing, and schedules as easily as software architecture, algorithms, and database schemas. My specialty is bringing together the business flint and software steel to ignite the spark of innovation that creates something meaningful.”

Transcription: SQL Server on Linux

Carlos L Chacon:               Travis, welcome to the program.

Travis Wright:                    Thank you. Glad to be here.

Carlos L Chacon:               Yes. Thanks for coming with us on this show … Thanks for coming on the show with us today. One of the very interesting things that happened in the key note at the SQL summit in Seattle was a demonstration of a restorer of a database that was a window sourced database and was restored to a SQL server running on a Linux machine. Lots of chatter about that. Ultimately, we wanted to have you on the program to talk a little bit about why this move to SQL server on Linux? I must admit I’m not a huge Linux person myself, what Microsoft sees as the future, I think there’s going to be some analytics discussion in here. Let’s go ahead and jump in and tell us a little bit about that history. Why the change forth to SQL server on Linux?

Travis Wright:                    Yeah, this is one of the first questions we always get is why is Microsoft doing this. After all these years you mentioned that you’ve been working on SQL for 26 years, why after all these years is Microsoft doing this. Really it comes down to the customers have been asking us to do this for a long time. For various reasons, it’s always been either a technical challenge that was seemed insurmountable or there was political challenges to do that within Microsoft obviously with Windows being a huge part of Microsoft’s culture.

Carlos L Chacon:               Sure.

Travis Wright:                    Everything. The star aligned recently though about a year and a half ago. I’d say we revisited the decision to do this, went on did a bunch of research talking to customers about what they’d like to see, talked to some developers. What we found is very interesting that Linux is very much a growing and vibrant ecosystem. We’re getting up to the point now where a SQL, Linux VM and azure accounts for about 25% of the total BMs and azure even. Lots of interesting things are happening on Linux. We got lots of big data things happening, a lot of CICD types of things are happening on Linux. It’s just a very interesting ecosystem. It’s growing. Even if you look at the IDC numbers for the database market you can see the Linux is growing faster than Windows as far as it being a database platform. Over time those markets will be roughly equivalent in size by about 2020-2021, somewhere in there.

Really it just is a matter of being responsive to our customer’s requests. Looking at the market opportunity ahead of us as well as for our partners, just the ecosystem in general. Just doing what customers want. What customers want is to have a choice. SQL Server is really become a very mature database at this point. It’s full featured. Recently we’ve surpassed Oracle on the Gartner MQ For the first time.

Carlos L Chacon:               Hooray.

Travis Wright:                    We have retained that crown if you will for another year here recently. Now it’s really just a matter of; okay, should we go and add yet another feature of the SQL server which would of course be great or should we maybe take a look at expanding the availability of SQL Server to other customers, developers, and just industries in areas that we haven’t been in before. Just take the goodness of SQL Servers to those other areas. There’s lots of other reasons too. We’ve talked to customers, sometimes for whatever reason, customers want to run their database on Linux. The reasons range from that’s what their database and systems administrators are experienced in. They have Linux people, they want to use SQL Server. “Okay let’s do it.”

In other cases you have companies that have maybe been acquired by another company that happens to run all their database platform on Linux. That acquiring company is now requiring the acquired company to map to that.

Carlos L Chacon:               Sure.

Travis Wright:                    The acquired company want to keep their SQL Servers. These types of scenarios come up where customers really just need this flexibility to be able to run SQL Server on other platforms besides just Windows.

Carlos L Chacon:               I’m I naive in saying that that’s more than just hosting companies because I could see the hosting companies doing that. Obviously as they consolidate and things like that. Are there … You’re still seeing other places, just takes some general verticals like even healthcare or engineering or things like that are also wanting to do those the same changes?

Travis Wright:                    Yeah, absolutely. Lots of enterprises that are running their structure, whether it’s on or private cloud, or a host of product or something along those lines where they’re in their own SQL Server instances are very interested in this. It’s interesting, I took a trip down to Silicon Valley and visited with several customers down there where Linux is very hyped. They were interested in how they could use SQL server in those situations as well. Definitely lots of enterprises. Then as you mentioned hosters are also very interested in this because typically hosting providers are based on Linux. We’ve had a number of hosting providers approach us about now operating SQL servers as a database platform in their portfolio because we have now support for SQL server running on Linux.

Carlos L Chacon:               Exactly.

Steve Stedman:                A question on that then, because I’ve heard rumors of Ubuntu support, or Red Hat support, is there anything that you can talk about with what Linux distributions that you will be supporting?

Travis Wright:                    We’re starting out with support for Red Hat Enterprise Linux. The version that we’re supporting there is 7.2. We also support Ubuntu 1604. We’re now working on support for you SUSE Linux Enterprise server version 12SPQ. Those will be the 3 main Linux distributions that will support the SQL Server on Linux. We’ve had requests from customers to support some other distributions like Sento-S or Oracle Enterprise Linux, Amazon Linux, so on. I think as we wrap up the work to support those first 3, we’ll start to look at other distributions that we may want to support and working out what that might look like.

Steve Stedman:                Okay, when you talk about the Red Hat Enterprise Linux for instance, to be running on, is that something that a developer or someone who’s just trying to a proof of concept to try out on their own machine using just regular Red hat or does it obviously have to be enterprise there for people to use it?

Travis Wright:                    Right, Red Hat has a developer program. People can go and get Red Hat licenses that are intended for use in a development environment. It doesn’t grant you the right to run things in production. You can still get all the documentation and the subscription access to be able to get the packages and that kind of thing as a developer.

Carlos L Chacon:               Okay. Great. I have to check that out. So then when we talk about the Linux edition I mean I’ve heard of this being like core engine features. I mean will this just be the SQL engine or will it have other things like SSAS or SSRS or integration services or anything like that?

Travis Wright:                    Right. The first release we have of SQL Server on Linux will be focused on the relational database engine. That includes all the features around data being stored in a relational quantity. It’s all the DDL, schema, then storing the data, that kind of thing. It also includes a lot of the advanced features that we’ve been introducing in the more recent version the SQL server. Like in memory LLTP, column store. Lots of the other goodness of SQL Server; like compression, partitioning, and all those things.

It includes all of the new security features we’ve been adding as well. Things like always encrypted to track and granted encryption, level of security, date of masking, auditing, all of those features as well are also included. Also things like SQL Server Agent, we can get in the high availability here as a specific topic here in minutes.

All those features around the core relational engine. What’s not included in the first release is things like Integration Services, analysis services, reporting services. Those are the big areas that we’re not going after yet. Then some other miscellaneous items or things like master data services, data quality services, some of those kinds of things. Over time, we’ll be responsive to customer feedback. We’ll a look at what it is that people want and go after those things that make sense. I think integration service is probably the one that pops out the most as something the customers are looking to be able to run on Linux and so we’ve started looking into that and working on that. We’ll see what the time frame will look like.

Steve Stedman:                Okay. It really sounds like it’s the core engine but it’s going all the way with the core engine.

Travis Wright:                    Right.

Steve Stedman:                As far as the core SQL functionality there.

Travis Wright:                    Exactly, you should expect nearly all of the features that you would see in the co-relational engine to be there.

Carlos L Chacon:               Yes, when you think about that SQL server service.

Steve Stedman:                Okay.

Carlos L Chacon:               Everything would be there. Lets go back, you mentioned you broke out the high availability solutions, should we dive into that for a second?

Travis Wright:                    Yeah let’s talk about that. It was different levels of high viability and disaster recovery. Lots of different features around that as well. We just finished up the work on the SQL server agents to enable a lot of shifting. That’s one option. You can log shift from a SQL Server running on Linux to another SQL Server running on Linux or a working SQL Server around Linux, you’re supposed to run on Windows, you kind of have some flexibility to go about how you log shipping. That’s one option. I’m just going through the basics to the more advanced.

Carlos L Chacon:               Sure.

Travis Wright:                    You also have support for backup and restore. You mentioned at the top of the session here the demo that Ro-On did at the past Summit where he showed taking a database that had been backed up on Windows and restoring it on SQL Server on Linux. At the other direction as well. You can go take a database back up on SQL Server on Linux and put it on Windows. You have old backup, restore, attach, detach capability because of the binary compatibility between the SQL servers regardless of where they’re running at.

That gives you a complex ability there. Then we also demod at one of the sessions we did at past. The fail over clustering running on Linux. We had 2 different notes in a cluster using pacemaker which is an open source clustering technology. I had to coordinate that cluster. Then we had to shared disc running on another VM. We showed how the SQL servers were using that shared storage to have all their data files, the master data as well as user data bases all right there. Then we would use the fail over command line utility for pacemaker to fail over from one node to the other.

I had a little application that was running there. It was written in node. It would just sit there and run a simple query against the database nonstop. I would kick off up that application, it would sit there and would output the name of the server that it was connected to. I just output that to the terminal just every second or so. I failed over and you could see how it’d lose the connection for a few seconds. It would pick up that connection again and output the name of the new server to the terminal. It was a cool demonstration of how fail over clustering just works. Very similar to how it does on industrial cluster services, in this case running on Linux using pacemaker.

Carlos L Chacon:               Sure. That’s very cool because, again, I think sometimes maybe we forget until we get into the thick of it. Particularly in availability groups even more so. That reliance on the Windows server cluster service, right? Through to manage some of that. It’s very cool that there are alternatives in the Linux world to do that.

Travis Wright:                    Yeah, absolutely. Going on to the best of all worlds is where you start to get into availability groups and having high viability that way with the replication based HA. That, we are also working on. That will also be based on pacemaker. We will have that available for people to test here pretty soon as well. That will be pretty exciting because then you going to have the replication based HA using always on availability groups with reader election and fail over detection, being able to detect a node being unavailable and automatically failing over a lot of listener. All those things that you’re used to in always on availability groups on Windows, those will also exist on Linux as well, just using a different technology.

One of the most interesting things about the way that we’re doing higher viability on Linux is that it will be a more open architecture. What I mean by that is that on Windows today Windows Server closer services is the main way of doing clustering in high availability with SQL server. On the Linux sides, there are lots of different technologies that are used for clustering in the Linux world. Pacemaker happens to be one of those and the most commonly used. There are others like zookeeper, there are some harbor B solutions. That we want to do is we want to make this a more open architecture where people can introduce other higher availability solutions that SQL server can work with. People can just write the scripts and talk over APIs, just introduce other solutions for high availability beyond what we provide as a reference in founding the recent pacemaker.

Carlos L Chacon:               Yeah. Wow.

Steve Stedman:                Interesting.

Carlos L Chacon:               You’re making it hard for your documentation folks there.

Travis Wright:                    Yeah, we just hired 10 more. It was starting to be a challenge to keep up with the documentation in general. You’ve got features that are going to work here and not there. You’re going to have what’s applicable to Linux, how you do things here versus there. There will be a ton of overlap. I’m expecting there to be just, if you had a chance to play with it, I don’t know yet or not. Most things just work, right?

Carlos L Chacon:               Sure.

Travis Wright:                    If you run an application, that just sits on top of TBS, it just works. All your drivers work. Anything that you’re doing through the front door, that all just works as you would expect it to. You got 2 SQL statements, most of those things just work. DMV’s just work. You can use SSNS, you can use SSPP, all those things just work. There will be some differences. Yeah, the documentation people have some good job security for the next couple years keeping up with all this. Yeah.

Steve Stedman:                Wow. Impressive for the initial release on Linux. I’ll tell you that. I know that for years Marilyn has been announced that it’s going to be deprecated. I would assume that Marilyn is probably not included in Linux. Is that correct?

Travis Wright:                    Right. Exactly. We’re not planning on bringing Marilyn over to SQL Server on Linux.

Steve Stedman:                Okay. I know you mentioned replication around availability groups. You probably weren’t talking about replication in a publisher or subscriber mode, were you?

Travis Wright:                    I wasn’t when I was talking about earlier. Let’s hit on that briefly.

Steve Stedman:                Okay.

Travis Wright:                    With SQL Server agent working now, we do have sub-subscriptions as well working. Most of that just works. I think the only thing that is left to do in that area is where you’re doing some publishing and subscribing between different database engines; like SQL Server and Oracle for example. We need to figure out how we host third party binaries in our platform on Linux before we can get some of those working.

Carlos L Chacon:               Sure.

Steve Stedman:                Okay.

Travis Wright:                    As long as the SQL is looking good. We’ve been doing some testing with that. That’s doing well.

Steve Stedman:                Great. Not that … I mean, it’s one of those that a lot of people are using it but the whole subscription piece is not … Publication piece not a piece that a lot of people get excited about. It’s being used in a lot of places.

Travis Wright:                    Yeah, it’s one of the things that are around for a long time. People depend on it. We’ve got to make sure that works.

Steve Stedman:                I know with the Windows version of SQL Server, there’s generally been the Express, a standard, and the enterprise edition. Although there’s been a few other little flavors over the years. Will the Linux version follow a similar licensing model or will it be something completely different.

Travis Wright:                    Yeah. The way we think about it is that SQL Server is the product. You license SQL server. Now you’ll just have an option of where you run SQL server. Could be on Windows Linux, could be on Docker containers. To that end, we plan on having the same edition lineup on Linux as we have on Windows. You’ll have a developer edition, you’ll have an enterprise edition, standard, express, Web Edition and so on. One thing that we don’t currently have a plan for right now is local DB on Linux.

Steve Stedman:                Okay.

Carlos L Chacon:               That’s your U-fold curve all of a sudden. You just said local DB.

Travis Wright:                    What?

Carlos L Chacon:               I guess I’m drawing a blank. What does that mean?

Travis Wright:                    Local DB is an optional component you can install as part of express. It runs as a process instead of a service. It’s just a lightweight database can be used in the context of developments. Typically few people use it like in Visual Studio. It’s a little lightweight DB that they use for development.

Carlos L Chacon:               Sure. Okay, I got you.

Travis Wright:                    Yeah.

Steve Stedman:                As far as the performance goes with this, one of the things that I’m wondering … I mean the initial version around the performance is always a big deal. Are there any metrics out there on how it will perform to comparing SQL Server running on Windows versus SQL server on Linux using the exact same hardware?

Travis Wright:                    Yeah, great. We haven’t published numbers on that yet. We’ve been spending the last couple of months really focused on making sure that SQL server on Linux performs in scales and that kind of thing. Of course SQL server on Windows is the most obvious benchmark that we can measure ourselves against because that’s really apples to apples. We also do use some bench-marking relative to other database engines and like that. At this point I would say that we are at about 90% or so of the performance of SQL Server on Windows on a machine that’s a 2 socket machine with somewhere around like 256Ghz of RAM, something like that right. We have lots of work still to do in this area. We need to add support for Numa. We have some other improvements we want to do around network IO and some other code path improvements.

There’s lots of work still to be done. Our goal is to get to the point where we have essentially the same performance and the scalability is what people are used to today with SQL Server on WIndows. We want to make sure that SQL Server on Linux can support those same mission critical to your one-type workloads that people use SQL Server for today.

Steve Stedman:                Okay, excellent.

Carlos L Chacon:               So is SQL Server going to be open sourced?

Travis Wright:                    Yeah, that’s maybe the next logical step, right? I think at this point, there’s no plans to do that. I think that would be a massive development effort on our part to get SQL Server ready to be open sourced even.

Carlos L Chacon:               Yeah.

Travis Wright:                    Let alone the will it would take to do something like. First steps first, we’ll get SQL Server on Linux out there, we’ll go from there.

Carlos L Chacon:               Very nice.

Steve Stedman:                Okay. One of the things today that I see is if somebody has a website they’re hosting with Word Press or something like that, the backend out there is generally a MySQL database.

Travis Wright:                    Yeah.

Steve Stedman:                Do you ever see SQL for Linux competing in that area with maybe an Express Edition or something?

Travis Wright:                    Yeah, absolutely. In fact, we’ve been working with some guys that started this project called Project Nami. Have you guys heard of this?

Steve Stedman:                I haven’t.

Carlos L Chacon:               I have not.

Travis Wright:                    Yeah. Project Nami, N-A-M-I, which funnily enough we’ve been talking to them for a couple months, then one day we’re finally like, “What is this Nami thing? What does this mean? They said it stands for ‘Not Another MySQL Instance’. What they’ve done is they’ve created a compact layer for MySQL … I’m sorry, for Word Press to run on SQL Server.

Carlos L Chacon:               Interesting.

Travis Wright:                    Very.

Steve Stedman:                Wow.

Travis Wright:                    Their original intention was to create this such that it could run on top of an azure SQL database which is where most of their customers today are using it. It also, because we have this compatibility between azure people database and SQL Server and windows and SQL Server on Linux, it happens to also work with SQL Server on Windows and SQL Server on Linux. They’ve taken it now a step further to where they have the full stack of Word Press and SQL Server running on Linux. A very interesting scenario. As we hit public review, we’re looking to how we can put that out there and make people aware of it. That certainly could be a very interesting scenario for people to run a Word Press instance on top of a SQL Server Express or standard. Depending upon what size they need to have their Word Press application or blog running on.

Carlos L Chacon:               Right now, I think that would be very attractive. I know, I was with a client that SQL Server shop, their marketing department wanted to move to Word Press. Which meant that all the sudden they inherited some MySQL databases that they now needed to care and feed, pure Microsoft’s shop if you will. That was just another item that they’re going to tackle.

Travis Wright:                    Sure.

Carlos L Chacon:               Being able to keep data all in the same family if you will, I think, would definitely be of benefit.

Travis Wright:                    Yeah, and definitely check out this Project Nami thing. It’s an open source project. I think it’s amazing actually. Very cool.

Steve Stedman:                Yeah. Definitely have to take a look at that. I know every time I try and use MySQL behind what I have in my blog, I just feel completely impaired trying to use it.

Travis Wright:                    Yeah. You want to know what you … You want to use what you know.

Steve Stedman:                Right, you know? Okay. Then for someone who wants to try it out; once you’re at that preview point, whenever that may be, how do you go about really someone getting started with that? Let’s say someone has a little bit of Linux experience. Are there going to be some ‘how to’ guides or anything like that that people could just walk through and say, “This is what it takes to get it going.”?

Travis Wright:                    Absolutely. It’s actually super simple to get going with SQL Server on Linux. I think we’ve done several demos now at conferences of the installation experience. It’s really quite refreshing for people that are used to installing SQL Server on Windows. On SQL Server on windows, you go through a wizard. It’s fairly simple and easy to do. It does take some time to click through everything and answer all these questions and everything. With SQL Server on Linux, we’re leveraging the package management systems in Linux. On Ubuntu, you have APTs, on Rel you have Yum, on Suso you have Zipper. They have all these packages. Installing SQL Server on Linux is really a simple command that you run at the terminal. Like on Rel, for example, you would say Yum install Ms SQL Server, it would go through the entire installation and download experience in about 40 seconds. That’s all it takes to get SQL Server installed. Then you run this setup script. It prompts you to accept the EULA and provide the SA password and you’re done. All in all, in less than a minute, typically you can have SQL Server downloaded, installed, and running.

A very cool experience with that. Then I’ve recently just been falling in love with Docker. I don’t know if you guys have had much experience with Docker. With Docker, again, it’s a very simple acquisition experience. You just go to the terminal, assuming you already have Docker installed and configured, you just go to the terminal, you type in Docker Pull and their SQL Server. It will pull down the latest version of SQL Server, that image. That takes a minute or 2, depending on how fast your network connection is. Then you just type a command Docker run and their SQL Server, you pass a couple of parameters to it to specify the SA password and accept the EULA. You’ve got SQL Server running in a container on your mac book or on your Linux development environment, or wherever you might be running Docker.

Then that also is just a beautiful thing when you start using some of these container management platforms like Red Hat Open Shift, For example. We just did a demo about a pass where people can publish that image up into their catalog and open shift. Then they can just provision containers just with basically a one-click experience in the open shift portal and push those containers out onto the container management platform.

Steve Stedman:                Very cool stuff.

Carlos L Chacon:               I can …

Steve Stedman:                I know … Go ahead Carlos.

Carlos L Chacon:               Travis, I think the folks who are familiar with Linux are going to be maybe a little bit earlier adopters with the docker stuff. That may not be quite a fair assessment, maybe in my lay of the land. I think that with this migration of SQL server, at least those in the data space will have to become a little bit more familiar with Docker or at least   get more familiar with it than we currently are.

Travis Wright:                    Yeah, I would agree. With Docker, to me feels like the VMs did, whatever 15 years ago or so, Right? It feels like we’re on the verge of a big transformation in how people isolate their workloads, how they provision their workloads. Docker, I think is going to be huge. We have Docker for Linux containers for SQL Server. That is from the very beginning of the SQL Server on Linux project, there has been an emphasis in making sure that we get that working really seamlessly. We’ve also been recently working on having SQL Server running in Windows containers.

With Windows Server 2016 being generally available now and having the option of running Windows based containers, we want to make sure the SQL Server runs well in a Windows based container. We’ve recently pushed out a SQL Server Express Edition in a Windows container to Docker hub. People can go grab that and use it. You could take that same Docker file that’s available there, just change the location of the bits and actually have a Docker container in which that you build for other additions of SQL Server.

What we need to do now as far as Docker goes is just do some more testing around having SQL Server running in a Docker container. Get to the point where we can have that be a first class supported scenario of having people running SQL Server on Windows containers as well. That’s something that we’re working on.

Carlos L Chacon:               Good deal.

Steve Stedman:                I know one of the things that I end up working on a lot is database corruption, having to fix it. Is the typical, or are the typical DBCC, check DB, check table, and those type of commands going to be included and available as well on Linux?

Travis Wright:                    Yeah, all of that works. So does tools like DMVs, those are also working. We’ve added a couple of additional DMV’s there. We have tweaked a couple of the DMVs that exposed system informational so that depending upon which system you’re running on, whether it’s Windows or Linux, we go and get the right information from the right place. Then we surface it up in the exact same results schemer.

Steve Stedman:                This is a tool that’ll only run on Linux for now. What it does is it creates a mounted file system using Fuse on Linux. It creates a bunch of virtual files, each of which represents the DMVs in SQL Server. What you can do is, you can just CD into this mounted directory basically, this fused directory. Then you can just LLS that directory, you can see all the DMVs listed there as these virtual files. Then you close as though they’re files. You can just cut them to get the upload of them or you can type to tools, like ‘cut’ if you want to use just one column or whatever.

What happens is when you open and read that virtual file that we’re actually initiating a call out to the SQL Server and executing that the DMV query and bringing back the results as though they were the content of that file. That sounds mind blowing. This is what Linux administrators expect; is that they can go in, they can look at system information in a file. We’ve created this virtualized experience using virtual files where they can go and get the DMV data and view it as though it was a file.

Carlos L Chacon:               Interesting.

Steve Stedman:                That’s great. Then using your favorite shell bash, whatever it may be, you can then use those files to type them into Set, or arc, or whatever you want to use there to work in your normal Linux type environment to get the output you want.

Travis Wright:                    Yeah, exactly. Because they’re just files there effectively, you could have that in a mounted directory even and share it out through a samba share, for example. People could remote into it. It’s just a file. Right? You can do all the things you could do on that.

The other thing about the DMV tool is you can actually have … You can set it up with a config file where you can have multiple SQL Servers in this config file. Then what you do is you CD into the mounted directory, you’ll see all of your SQL Servers there as directories essentially. Then you’ll see you can CD into which ever SQL Server you want to look at and look at all the DMVs for that SQL Server. Then you can do really interesting things because these are all just files. You could do Scripts over this. You could traverse over each of those SQL Servers and look at a particular DMV, compare data to each other, or whatever. It’s just files at that point. Very interesting possibilities there. I’m curious to see who will be the first person to build some monitoring tool based on this. That would an interesting thing to see.

Steve Stedman:                Yeah.

Travis Wright:                    The other thing that we’ve done is the flip side of that; where we will now have a DMV that will expose … Which will show you the process information from the proctor rectory. The proctor rectory if you go in a SQL Server … If you go into Linux is where all this system information is about all the processes running on that particular Linux host and will expose that proctor rectory as a DMV. People can just get access to the proctor rectory by running it to a SQL query. They don’t have the SS agent of the box, they can just run it to a SQL query if that’s what they prefer to do. That will be very interesting I think for any of the monitoring tool that moves out there to be able to access that information just through a T-SQL query.

Carlos L Chacon:               Exactly.

Steve Stedman:                That sounds great.

Travis Wright:                    The monitoring tools and monitoring tool vendors and that kind of thing out there don’t ever have to access each into the box to figure out what’s going on. Everything can be exposed through T-SQL. Obviously is systems go down for some reason then you’re black, dark. At least for the most part it should work.

Steve Stedman:                On the Windows version of SQL server, one of the more controversial commands that’s out there is the XP_command shell.

Travis Wright:                    Yes. Yeah

Steve Stedman:                Is there a Linux equivalent of that where you’ll be able to check out your shell and run local commands?

Travis Wright:                    Right. As of right now, we’re thinking that we’ll probably close that off for Linux. We’re always interested in hearing people’s feedback on that. That’s just a security vulnerability that we’re inclined to just close off for now. There are other options for executing those types of things; like having a SQL Server Agent job for example that can execute bash scripts that will probably enable.

Steve Stedman:                Okay.

Travis Wright:                    We’ll probably have that sort of thing through the agent.

Steve Stedman:                Then with the agent today, you could create agent jobs using different stored procedures. I assume a similar thing would just work. Can you be able to create agent jobs on the Linux version?

Travis Wright:                    Yeah.

Steve Stedman:                Okay. Excellent.

Carlos L Chacon:               Travis, I have to admit, I wasn’t all that excited, I’ll use that word, about SQL Server on Linux. Now after our conversation with you, I have to say I’m a little bit more enthused about it and interested to see where things go because of, I will say, the merger, right? If you will of the 2 operating systems and what you’re having to traverse there.

Travis Wright:                    Yeah. I think we ran into that sentiment a bit at SQL pass. I think at the past summit, you’d expect to find people there that are long time SQL Server users, they’re for the most part happy with what they have today. They will maybe just want us to add more stuff to SQL Server or what they would like us to do. Or maybe improve all of your performance on scale. Whatever it might be. They live in this world of SQL Server as they know it today. Right?

Steve Stedman:                Right.

Travis Wright:                    I think as people start to learn more about SQL Server on Linux, they see the new opportunities that this presents to everybody. Whether you’re a consultant, or you’re a DBA, or you’re a developer, if you think about it it’s really just expanding opportunities for everybody in the SQL Server ecosystem. Where we can go now and talk to our friends over in the other hallway about Linux. We can figure out what makes sense to do. It’s not maybe just a one-size-fits-all solution running on Windows. We need to run something on Linux. For whatever reason, we can now. We can have that conversation. I think it presents some interesting opportunities for all of our partners in this space. Whether they’re monitoring tool vendors, backup vendors, security vendors. Presents opportunities for all of our hardware partners, we have brand new friends now with our operating system vendor partners like Suso, Nautical and Red Hat.

It really just was opening up the ecosystem and presenting a lot of new opportunities to everybody. I think as we all look at the landscape and how Linux is just growing so much, it’s good for everybody that’s in the SQL Server ecosystem to take a look and see it as an opportunity for you to expand your own opportunity. Learn about Linux, expand your skill set, have more opportunity as you look out to what your next job might be. It might be to run SQL Server on Linux. There’s a … You can get huge demand for people that know how to do that.

Carlos L Chacon:               Exactly. Exactly.

Steve Stedman:                I know it’s been a couple of years since I’ve done really much of anything with Linux but I’m excited. I want to go spin up the Linux VM to get ready for whenever this is available to give it a try.

Travis Wright:                    Awesome.

Carlos L Chacon:               Should we do SQL family?

Travis Wright:                    All right, let’s SQL family.

Carlos L Chacon:               SQL family is just an opportunity for us to get to know you a little bit better. I guess I should have made sure that you had those questions we added in the invite.

Travis Wright:                    I think I saw that in the invite. I just didn’t realize it was called SQL Family.

Carlos L Chacon:               Yeah.

Travis Wright:                    Okay.

Carlos L Chacon:               It’s just our little segment that we call SQL family.

Steve Stedman:                Should we jump into it then?

Carlos L Chacon:               Yeah, let’s do it.

Steve Stedman:                All right. With technology changing so rapidly, how do you go about keeping up with technology and all the changes that are happening every day?

Travis Wright:                    Well I do a lot of reading. I read Tech Meme incessantly. I can’t stop reading it. I can’t stop reading Red It and all the cool stuff that’s going on there. I do a lot of reading. Then whatever I find that’s interesting, I go and try it, you know? I’ve never really been a developer on anything other than dot.net. Since I’ve been working on SQL Server on Linux, I’ve taught myself Ruby, Python, and Java. What else? One other language, Node. I’ve been working with node. I just go and try it. I’ve been using other database systems as well, to get what people like and don’t like about those. I’m a hands on guy. I like to actually get down and try to use this stuff and see what I can do with it.

One of the funnest things I’ve done recently is one that Microsoft announced, the Back Framework back at … Earlier this year. I just sat down, stayed up all night, built out this little box that sat on top of SQL Server on Linux and got that working. That was pretty fun.

Carlos L Chacon:               Pretty cool.

Steve Stedman:                All right.

Carlos L Chacon:               Now, we’ve talked about lots of things that are happening with SQL Server, right? If you could make one additional change to SQL Server, what would it be?

Travis Wright:                    That’s a good question. I don’t know.

Carlos L Chacon:               Okay. We can … We’ll strip that question out.

Steve Stedman:                Yeah.

Travis Wright:                    Let me think about it while we go on some of the other questions. Then your editor can pick it up I guess.

Carlos L Chacon:               Yeah, if you think of an answer, we’ll come back to it.

Travis Wright:                    Okay.

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

Travis Wright:                    I think probably the best career advice I ever got was from John Byce. He pulled me into his office one day, he said, “Do you know who Terrell Owens is?” I said, “I’ve heard the name. I don’t know who he is.” For those that don’t know, Terrell Owens was a wide receiver in the NFL. He was a flamboyant guy. He would pull his … I remember one time he caught a reception, ran it in the end zone, he pulled a Sharpie out of his sock and signed the football and threw it to the audience. His feedback to me was, “Don’t be like Terrell Owens. Look at it more like you’re part of the team. Make the people around you better. Don’t be such a showboat. Try to …” That that was some of the best advice I’ve ever gotten in my career. I’ve always looked back on that, appreciate it.

The other thing that John Byce always preached to me as I transitioned into being more of a manager was, “Look for people that are humble, hungry, and smart.” I’ve always tried to surround myself with people that are hungry, humble, and smart.

Carlos L Chacon:               Yeah, that’s a good combination. Travis, our last question for you today. If you could have one superhero power, what would it be? Why do you want it?

Travis Wright:                    Yeah, I’ve always dreamed of having a superpower of human computer interface. Where I can just sit down to a computer, without any other input device, the computer can just mind meld with my brain. I can make computers do whatever I want without any typing or any other input just rained a computer meld. That’s what I would love.

Carlos L Chacon:               There you go.

Steve Stedman:                That would be awesome. I’ll take that too.

Carlos L Chacon:               Yeah. Once you’re done with SQL Server on Linux migration, you get the other one, the mind meld figured out Travis, come back and let’s chat.

Travis Wright:                    Yeah. It’s interesting, if you look at all the superhero characters out there in Marvel, DC, and everything, they all have these physical abilities that they have or whatever. Rarely is it like a technology thing that they leverage, yeah? I thought that was interesting.

Carlos L Chacon:               That is an interesting…

Travis Wright:                    Other than that, yeah. Very interesting.

Carlos L Chacon:               Travis, thanks so much for being on the show with us today. It’s been a blast.

Travis Wright:                    Thanks guys.

Episode 69: SQL Server Install Checklist

We have all been there–next, next, next–let’s just get this install done.  While there are many ways to get SQL Server installed on a server, the real impact is in the way we administer the database and when we want to get good performance.  Listener James Youkhanis asked us about our install checklist and Steve and I realized we hadn’t made one public before.  In this episode of the SQL Data Partners podcast, we talk about some of the settings we are looking for when we set up a server.  We don’t have time to reference every single item, but hit some highlights.  Your list might differ slightly, but our list has been built from the experiences we have seen over time from various installations.  We love to other opinions, so give us your thoughts!

This page and the checklist available below will continue to evolve over time.  This is the version we have now.

Episode Quote

One of those [settings] that can be a life saver, but you have to have it enabled before you actually need life saving.” Thoughts on enabling the DAC in SQL Server.

Listen to Learn

  • The four areas of SQL Server installation – Preparation, Installation, Configuration, and Post Install
  • The trace flags now considered defaults on most systems
  • The cost threshold for parallelism setting we recommend
  • Azure specific settings to consider

Resources

Enable IFI
Setting Trace Flags
BgInfo
Enable
Setting Database Ownership
DBA Tools – Moving Logins

 

Episode 69: SQL Server Install Checklist

Carlos L Chacon:               Companeros, welcome to the SQL data partners pod cast. The pod cast dedicated to SQL server related topics which is designed to help you become familiar with what’s out there. How you might use those features, or ideas on how you might apply them in your environments. This is Episode 69, I am Carlos L Chacon-

Steve Stedman:                I am Steve Stedman and this week’s topic is on SQL server setup. This is something that was requested on Twitter by James for us to talk about things that we do and maybe compared to things he does on how to set up and configure your SQL server servers.

Carlos L Chacon:               Yes, exactly, I think this is something that we all have to do as administrators and we all kind-of have our own unique blend to it, and so as he proposed his idea, he actually sent us his checklist or kind-of a comparison of sorts, so we thought it would be interesting to kind-of put together our thoughts and experiences on it and then talk about some of the different sections.

Steve Stedman:                Yes, exactly and I think what’s really interesting about this is every time I see one of these from somebody else, I usually see that there are many things in common with what I normally do, but then I see that there are also one or two things that I hadn’t thought about that before or maybe I do it but it’s not actually on the checklist, so I think it’s good to look at the different lists.

Carlos L Chacon:               Sure, exactly, just how different people approach kind-of skinning that cat if you will.

Steve Stedman:                Yes. Then the phases we’re going to go through with this really the prep, the install, the config and then sort-of the post install or the things you’re going to do a little bit later.

Carlos L Chacon:               That’s right. We will actually have our checklist available. We haven’t really made it available to the public or on our blogs previous, and we will do that in this episode and that will be at sqldatapartners.com/serversetup. There we’ll have those downloads, you can get that access and kind-of, again, kind-of share and compare notes as to how you go about doing different things.

Steve Stedman:                Yes.

Carlos L Chacon:               I think we’ll go ahead and get into that conversation, Companeros, welcome to the show. Okay, so the first step as we get into how we want to set up our SQL servers, maybe the most obvious but there’s a couple of things I think we want to check off the box before we actually do the install of SQL server.

Steve Stedman:                Yes, and I think part of that is basically getting the server ready to go so that you’ll be able to get SQL server installed and its going to be happy to run after that. One of the very first things I always do is install the BG info application. If you haven’t used that, what it is, is it’s great when you’re dealing with virtual machines or when your often remote desk topping to different machines is that when you log in, it sets a bunch of information on your Windows desktop so that you can look and see immediately what server your on and what drive letters are there and how much memory is there and things like that. BG info is one of the very first things I’ll put on there just so I know I’m on the right server.

Carlos L Chacon:               Exactly, and I can tell you a little bit more information about that.

Steve Stedman:                Yes.

Carlos L Chacon:               One of the interesting things that we think about the configuration, and again we’ve had some conversations with Argenis and he’s going to be back on the show to talk about dis-configuration, but I think again, each individual environment is going to differ as to what kinds of disks they have and what’s made available to them. Whether we’re talking about local on board disks of a physical server, right? SAN, and then how that SAN is all made up.

Ultimately I think we generally want to try and separate some of the data, and we generally think about that in data and then log files and then Temp DB.

Steve Stedman:                Yes. Really the place to start with that is your C Drive first. No matter how you’re going to configure things with your different drive letters, what you want to know is that you should put nothing, really nothing but the operating system on your C Drive.

Carlos L Chacon:               Yes. Do we include, for example I know a lot of times the default SQL server stuff will get sent to, like even server logs and things, are we still sending that to a different drive as well?

Steve Stedman:                I know when you do the default install it usually installs to some stuff in program files and different sub directories under there. My preference is, I like to install that at a higher level outside of the program files directory, often times on a different drive letter so that you can call it out as here’s the install of SQL server 2016 for instance. Then that way, you’ve got it there, it’s not on your C Drive, but it’s on…that could, I guess the install could be on your C Drive, but I like to put it on a different drive letter than the C Drive itself.

Carlos L Chacon:               No it does make sense, I think particularly when start taking a peek at, if for whatever reason you wanted to keep around some system health extended event sessions or other logs you wanted to keep around for a longer time, again it just reduced the chance that you have coming in danger with filling up that C Drive for whatever reason.

Steve Stedman:                Right, yes. Then after you’ve got your C Drive and you know you’re going to put your data and logs and everything somewhere else, that’s where you really need to figure out how you’re going to split that up. Usually, we’ll have a drive that’s where SQL Server installs to, and that’s typically where I leave the system databases.

Lets say that could be your drive letter E, and then you want to set your user databases, the data files for those generally on a different drive, we can just call that F for now. Then you want to have your log files on a different drive, or yet another different drive from those E and F drives we’ve just set up. The reason for that is the logs have very different IO behaviors than what you need for your data files, and generally you can get better performance by having those on different types of storage.

Carlos L Chacon:               That’s right. Again, I think Flash is changing this dynamic slightly and so again ultimately its going to depend on your set of circumstances, these recommendations I think are towards the spinning rust if you will, so spinning disks and kind-of diversifying that, those IO differences.

Steve Stedman:                I think the other thing its not just if you’re using the spinning disks or spinning rust as Art Genis liked to call it, but if you have tiered storage, where you have some spinning disks and some SSDs, you can then, if they’re on different drive letters, you’ve got better opportunity to move those around or better promote them to some faster storage.

Carlos L Chacon:               That’s right, and that’s kind-of where, or this idea, or the suggestion of using mount points instead of drive letters comes into play, is that in the event that you need to move that data around, it can be done in the background and SQL server is none the wiser.

Steve Stedman:                Right, exactly.

Carlos L Chacon:               You don’t have to start moving around your data files like detach and re-attach because you need to go from a slower disk to a higher tier.

Steve Stedman:                Then going through this we’ve got our database files, the two sets of them, the system and the user, and we’ve got our database log files, then the next thing that we want to put on fast storage as well will be our Temp DB data and log files. Generally I like to start out with Temp DB data and log files on the same disk, but depending on how things perform you might need to split those off on a different disks or different mount points. Finally you need to have a place for where your backups are going to go.

Carlos L Chacon:               Yes, exactly.

Steve Stedman:                All right, so other things we want to do with that is we usually start out and format the disks to 50…usually we start out and format the disks to 64K allocation blocks for the databases and the log files. I know there’s a lot of different takes on SAN storage with that, but generally it’s a good practice to start with that. Then benchmark that storage before you install SQL server. There’s different benchmarking tools you can get out there to see what kind of performance you’re getting and what kinds of sequential reads and rights are you able to get on that storage.

I know working with Azure one of the things we discovered is that some of the Azure storage, the difference between the standard and the premium was basically when the SQL server goes from a usable disk speed to not usable or vice versus depending on which package you’re looking at there.

Carlos L Chacon:               Exactly, I think the other reason why you would want to consider taking these matrix before you go ahead and with SQL server is that at least you have a baseline. Should things change over time and you see that performance, for whatever reason, usually I guess it would go down, I guess if it goes faster nobody’s going to complain, but if it changes for whatever reason, at least you have those numbers to say well when I installed it, I was here and now I’m at this other number, can you help me understand what’s going on.

It could just be that there’s increase traffic on that SAN kind-of behinds the scenes there, and you can provide that information to those administrators to help you get back to where you were.

Steve Stedman:                Yes, that’s a really good point. Then the other thing you’re going to want to do to prepare the server before installing SQL server is to set up the service accounts and grant the right permissions there for the accounts that SQL server and the SQL server agent are going to run as.

Carlos L Chacon:               Yes, that’s right.

Steve Stedman:                You’re going to need those as you go through the install process.

Carlos L Chacon:               Yes, exactly. That kind-of rounds out, we may also want to think about configuring the port number that we’re going to be using. Of course the SQL server uses 1433 as a default, you’re environment may or may not require you to change that. If you do have to change that, you may also then need to think about opening up ports or whatever other security mechanisms you need to do in order for SQL server to be able to communicate with your applications.

Steve Stedman:                Yes, good point. All right, so jumping into the install then, there’s really a couple of different ways to look at the install. One is you’ve got a freshly provision machine and you’re popping in the install media or an ISO and you’re going to run through all the set up processes, or the steps and you’re going to choose what you want to install.

The other side of it is, you might be working in like an Azure VM environment where you just ask for VM to be permissioned with SQL server already installed on it. Some of the steps are going to be a little bit different depending on which one of those environments you’re in, but either way, at the end of the process, you’re going to end up with SQL server installed and hopefully you’ve got all the right components that you need installed at that point and not too many of them.

Carlos L Chacon:               Right. That’s probably the biggest point right, is that if you have somebody else install that, they’re just going to go through and check everything. Then that becomes a little bit complicated because if you don’t need SSAS or SSIS for that matter, don’t put those on there unless you’re going to be using them.

Steve Stedman:                Some people argue and say well, “I just might use them one day so why not install them.” My response to that usually is well, it’s just extra overhead. Then you might say, well but if I’m not using it, it’s not extra overhead, but where you get that extra overhead is when you get into doing service packs and updates in that if you’ve got SSRS or SSAS installed, and you’re installing that service packer update, it’s going to update it even if you’re not using it. That’s going to add a little bit of time when you do those server packs and updates. I like to not install the components that you’re not going to use.

Carlos L Chacon:               Exactly.

Steve Stedman:                Okay, so I think then once you’ve gone through and you’ve got it installed, now if you’re doing the install yourself, you will have picked all the disks locations that where data files are going to go, default locations for logs, things like that. If you’re using a pre-provision machine through Azure VM for instance, you might have to go through and manually set some of those when you’re done to set where the default location is if you just create database without specifying a path, where those databases are going to go. If you don’t do that, they’re going to end up in your default install location.

Carlos L Chacon:               I think this is probably one of the benefits of having a configuration or an install checklist, is keeping the environments consistent because one of the biggest pains is, we kind-of get into the post installation steps, is that when they’re not the same, it just makes administration that much more difficult because paths change and this server I have to do this, that server I have to do that. You have to start thinking about those differences and it could be difficult.

I think that’s why one of the reasons that the power shell installs have become much more popular. It just enables you to have that script. You maybe make a change for the network account or the log in information, and other than that everything kind-of stays the same. You know you’re going to be getting the same install no matter what.

Steve Stedman:                Yes, and that can save a lot of time if you know that things are in the same location and the same configuration on all of the SQL servers that you’re working with there.

Okay, so you finished the install. You’ve now go in sort-of the configuration mode and this is where you want to go through and start setting up the SQL server to, I don’t know, behave in the way you want it to behave.

Carlos L Chacon:               Exactly.

Steve Stedman:                Usually the first thing I’ll start with there once we know we’ve got all the right directories and things like that set up for the system is to start with the SQL server service and the SQL server agent accounts and making sure that they’re going to run as the desired either domain user or active directory user that we want to have them run as.

Often times you’re going through that, it will take a couple of re-starts and maybe a re-boot of the machine to make sure that they actually all come up and start correctly and they all have the right permissions to be able to run as a service there.

Carlos L Chacon:               Right, and I know that, again our security minded folks are going to have different recommendations there, however I must admit that the majority of the installs that I’ve done, the service account for both the…i’m sorry, what am I trying to say there? The active directory account that I’m using for the service account and the agent account are the same.

Now a lot of people are going to slap my hands for that because you don’t want your service account for the engine to have the same access that your agent might.

Steve Stedman:                Right. My experience on that usually has come down to your active directory administrator. How savvy are they on these specific permissions that you need for different services. Depending on what you can get from that active directory administrator, that may really control whether you have different user account there or not. I would prefer to have a different user account just from the security perspective.

Carlos L Chacon:               Sure. I think some of that is culture right? As you start requesting, so however you go about requesting those servers, it might be at that time that you start thinking about what those accounts are, if you have a culture requesting both accounts, obviously it makes it easier to do that.

Then you start kind-of getting up into your, you know, I need accounts and yadda, yadda, yadda, you can make that more difficult.

Steve Stedman:                Then once you’ve got the server running that way and with those users, you’re going to want to make sure that people can actually connect to it and use it remotely rather than just the local desktop. That usually involves coming back and checking the configuration on the firewall allowing ports 1433 and 1434, 1434 is for the dedicated admin connection, but allowing those ports through the firewall and allowing TCP connections.

There’s scenarios where you might not be using those, but generally that’s where you’re going to start. If you’re not using those, you probably know what ports you need to allow through.

Carlos L Chacon:               Yes, that’s another great point that I think we missed on the install step, the consideration is not installing SQL Server Management Studio on the server itself. This will help force you or limit the number of times you actually have to log into the server. As you’re doing that, you’re testing that remotely, you know that you have to administer it from your own PC like another thing.

Steve Stedman:                That’s one thing that comes up a lot. Why do we want to avoid like remote desktop into the SQL database and running the SQL Server Management Studio from there?

Carlos L Chacon:               No, exactly. It ultimately comes down to that memory reservation, because you are then part of the operating system, SQL server, or the server rather, is going to seed memory to you potentially if it needs to, for you to be able to get on there. That’s just resources that SQL server is not going to have access to.

Steve Stedman:                That reminds me of a database that I worked on a few months back where it had eight GBs of RAM so it wasn’t a gigantic amount of memory, but it should’ve been more than enough, but what happened was you had all kinds of people who were remote desktopping in. This remote desktop connection took up a GB and another one took up a GB and a half, and another one took up two GBs because they were running development environments and things like that. Next thing you know all these remote desktop connections were taking up all the memory and SQL server was squeezed down to just a few GBs of memory out of what it should’ve had up to about eight.

Anytime you can sort-of force that connection off the server but over to someone’s desktop, you’re just going to allow more memory for SQL server to run in rather than somebody’s desktop.

Carlos L Chacon:               That’s right.

Steve Stedman:                Once you’ve got to that point, we kind-of go through the rest of the configuration there and we’ll go through and usually I’ll enable instant file initialization, which is one of those that we talked about on a tuning review on a few pod casts back. Then we go through and if it’s a SQL server only dedicated box, we’re going to do things like enable lock pages and memory.

Carlos L Chacon:               That’s kind-of an important point, and people your mileage may vary, but if I know that if that SQL server is dedicated, or that server is dedicated only to SQL server, then I feel much more comfortable with that setting if it’s going to be used for different things, or you know you just have a cultural or people are going to be logging in to the server all the time, I might be a little bit more hesitant to pull that trigger.

Steve Stedman:                Then I’m going to go through and do a few more settings at this point where I’m going to do things like set up default fill factor if it hasn’t been specified. Set up the cost threshold for parallelism. Usually I’ll start that out at 50 on a new database and then kind-of tune it from there to see what it needs to be. Set up the max degree of parallelism. Turn on optimize for adhock workloads. Setup the backup compression default, this is one of those that will make it easier when you set up backups later, you don’t actually have to remember to turn on a compression, it will just use the server default. Then all your backups will be compressed.

Then of course, you turn on the dedicated remote admin connection, which allows you to get into server when you’re in specific scenarios that normal connections would not be allowed.

Carlos L Chacon:               Exactly, that’s one of those that can be a life saver, but you have to have it enabled before you actually need life saving.

Steve Stedman:                Then after that, we’ll go through and also set up multiple Temp DB files and there’s a lot of different takes on how much, or how big they should be and all that. I would usually start out with eight Temp DB files for the database and then modify from there as needed. I would also usually expand them to be large enough so that they don’t have to have growth very often.

Carlos L Chacon:               That’s another one of these areas where again, lots of different opinions there, but what I have seen is that if I’m setting up my SQL server, it has it’s own Temp DB drive, let’s just say, or mount point whatever, then they’re going to allocate, I’ve chosen an allocation size based on how big my data files I think they’re going to be. There’s usually some formula that I’m using to estimate that.

They’ve dedicated that space to me if again, whatever that number is, if you decide…like I want to take up all of that space, if I have more cores there and I decided that I want to use Temp ten DB files or for whatever reason, go ahead. I haven’t seen too many people arguing that if you have too many you kind-of get into trouble. I think in high end systems, you have a million cores kind-of a thing, yes you might come into some problems, but I think again, let me use that space it’s dedicated to me, I want to just nip that in the bud to begin with.

Steve Stedman:                That’s a really good point, I mean if you’ve got a dedicated drive that’s a fixed size that’s going to be used for Temp DB you may as well fill it up with, or almost fill it up, and I caution you, don’t fill it all the way to one hundred percent, leave a little bit of space at the end there, but almost fill it up with Temp DB because it will prevent the need to have auto growth on that or for the files to grow over time.

I think that one of the things you might experience when you do that is say, oh well I’ll fill it up to ninety-five percent, one of the things that happens is some of your SANS administrators panic a little bit saying, “Ah, the disk is full.” Well it’s as full as it’s ever going to be because we’ve just used up what we’re going to use for Temp DB at that point.

Carlos L Chacon:               Exactly. No, that’s right so I guess I would caveat that and say I would probably go up to normally eighty percent is the threshold number, so seventy-nine percent. That way you do know once you cross that eighty percent threshold that you are seeing some growth and maybe again, if your other monitoring situations haven’t alerted you that Temp DB needs some attention, that one would get my attention.

Steve Stedman:                Part of the configuration I’m going to do at this point is turning on some trace flags. The trace flags that I’m going to turn on will vary a little bit depending on whether is SQL server 2016 or 2012, 2014, 2008 or R2, but generally with everything up to 2016 or prior to 2016, I’ll turn on trace flag 1117 and 1118. I think those are now the default on 2016 so you don’t need to turn them on.

Eleven-seventeen, what that does is allows you to grow all the files in a file group equally. If you do have something like Temp DB, if you haven’t filled up that entire disk, it will make it so that when you grow that Temp DB, it will grow all the files at the same time.

Carlos L Chacon:               Right, so if you had four files and one needs to grow, all four will grow at the same time.

Steve Stedman:                Yes, exactly. That’s important because if they grow at different rates and different sizes, the bigger ones will end up taking more of the Temp DB load and therefore needing to grow more and more so you end up with some really oddball sized data files without this trace flag.

The other one was 1118 which is for full extents only. This can help with a little bit of the overall performance there when a Temp DB is growing and being allocated, but it can also impact other databases.

Then a couple others that I usually turn on are 3026 which is to enable check sums on backups. What this does is it makes it so when your backup files are written in check sum gets written to each of those chunks that gets written out, and when you’re doing the restore it will tell you if anything has changed or anything has gone bad in that file from what it had intended to be written to disk initially.

Then I’ll also turn on trace flag 3226. What 3226 does is it silences the backup messages going to the error log, SQL sever error log. It’s one of those things that I find it frustrating when you go in and look at your error log and all you see there is a bunch of backup succeeded messages. I don’t need to know that, I only need to know when something failed. Thirty-two twenty-six turns off all those backup succeeded messages.

Then depending on the specific SQL server or SQL server version or the environment, there may be a couple of other trace flags that would be considered at this point as well.

Carlos L Chacon:               Exactly right. Again, your mileage may vary there with some of those other trace flags.

Steve Stedman:                Whatever you’re doing, I would make sure that you test those trace flags before using them in a production environment.

Carlos L Chacon:               Yes. There’s one that we haven’t talked about and that is optimize for adhock workloads. I admit that’s the one I’m flipping kind-of automatically now, is the default standard as well.

Steve Stedman:                That’s one that I will, on a new server configuration I will almost always turn that on and I have never seen it cause any type of negative performance issues. It does help improve performance from what I’ve seen, but I’ve never seen it hurt any of the environment.

Then at this point, once we’ve gone through a few of those settings, this is the point that I’ll usually install database health monitor and point it at this newly built database and then use the quick scan report and database health monitor to go through and catch any other settings that I think may need to be set.

I think there’s a hand full of them that we’ve already covered here, but database health monitor and quick scan report will catch a few more things that we’ve might of overlooked somewhere along the way.

Carlos L Chacon:               What’s interesting is James kind-of approached us with this topic, I thought, ‘hmm, why haven’t we put out some of that information?’ I think some of it is because its kind-of baked in to the database health monitor tool now and so while it’s not necessarily scripted out, because the script says point the tool at your server and then go fix anything that needs to be fixed there.

Steve Stedman:                Precisely. Once we’ve done that, one of the things that it will catch right off is that DBCC check DB has never been run on your databases. I have a job that I installed that’s generally a customer job that goes through and does DBCC check DB and the one that I generally use just checks for a certain amount of time and then if that time passes, and I usually set it to like five or ten minutes, and if that amount of time passes, when it finishes the next check, it doesn’t go on to check anymore databases.

Then I usually adjust that so that it doesn’t run check DB for twenty-four hours a day, it will only run it for a certain amount of time, and the next day it will catch the oldest databases that haven’t been checked recently. The other option there is the halogran scripts for doing the checks as well.

Carlos L Chacon:               Now we’re really kind-of in the post-installation process. You’ve changed all of the SQL server’s specific type settings, now we’re going to start installing things that are, kind-of in the sense unique to each environment and the way they like to administrate as those SQL servers.

We’ve kind-of talked about the health monitor, we’ve mentioned scripts, there are a hand full of other community scripts that are out there.

Steve Stedman:                Yes, SP_ who is active-

Carlos L Chacon:               Exactly, they’re kind-of like bunched in as…I like to have it on all my servers just because if that’s the way that I then begin working or troubleshooting when I need to come back to that server.

Steve Stedman:                I think that part of the, like when we talked about those when we mentioned database health monitor, part of it is you don’t have to actually install the database health monitor application on the server itself, you can put that on your desktop and connect it to the server then have it create the monitoring database on that server or on another database if you want to.

Carlos L Chacon:               Exactly. The other things that again, I think will be environment specific might be some certain auditing that you want to enable or that you need to enable. Ultimately extended events also come into play here. Do you have some default extended event templates that you want to have installed?

Steve Stedman:                Definitely good points. Then the other thing, I think maybe this is one that we missed on configuring the database, but creating a database owner user that you can use to switch the owner of most of your databases so that the owner of the database is not SA. That’s one of those things that you can get into some security risks if your database is owned by SA and there’s a couple of examples out there of how people can get escalated security by running scripts as the database owner. It’s good practice to set the default database owner to be a user that does not have connect-ability to the database. They’re denied connect to SQL.

Carlos L Chacon:               Sure, and I guess depending on your security method, I think we’re assuming that most people still run the, what’s the name of it, mixed mode? Yes, mixed mode.

Steve Stedman:                Yes.

Carlos L Chacon:               You can still create a local log in should you need to do that.

Steve Stedman:                Right. In this case you could, if you were running in the active directory mode only, you could create a domain user called DB Owner that’s been denied the ability to connect and set the database owner to be that user as well. It doesn’t have to be an actual SQL user.

Okay, I think that kind-of hits on most of the normal set up scripts that I go through. Oh, enabling the dedicated admin connection, did we mention that?

Carlos L Chacon:               We did talk about that.

Steve Stedman:                That’s an important one. Okay, so yes we’ve got that. I think that’s it for my usual to do list on setting up a server.

Carlos L Chacon:               Another consideration that you might have is you might potentially be migrating from one server to the next, this could be in the case of a version upgrade. You’ve gone and you’ve installed all these other things and now you need to bring over the user information from the previous environment.

I admit that I had been using SP was it helprev log in for the longest time, you go to the old one, you have to install two procedures as a hashing algorithm, then you run that and it will script out with a password the information to put onto the new one.

Found a post by Chrissy LeMear, power show, who would’ve thought, MVP, and her argument is it’s 2016 why are you still using that stuff that was created for SQL 2000? I thought, ‘Hmm. Chrissy, you’ve got a great point there.’ Through the databasetools.io program, they have a way, and she actually points out some limitations which I hadn’t considered, but there is a tool set there now that will allow you to actually create, again using power show, you can connect to the old instance, connect to the new instance, specify some parameters of how you want that to be set, filter, all those kinds of things, then it will go ahead and take care of migrating those options over.

One of the bigger areas I found are compelling reasons to do that, is it will take care of the individual groups or roles. If you’ve assigned a role to a user, the SP helprev login won’t grab that information for you, it will just create the account. This will allow you to bring over those roles as well.

Steve Stedman:                That looks like a great option because I know that’s one of those things that can be very painful if you don’t have a script or a tool in place to help move all those users and permissions over.

Carlos L Chacon:               Exactly. I guess that’s kind-of our checklist. The way we kind-of go about doing things. Thanks to James we will kind-of put this organized out there on the web and you’re welcome to download it. If you do these things differently, we’d love to get your feed back there as well. We know that everybody has a unique environment, unique considerations that maybe we haven’t addressed.

I know we brought up a couple of things Steve, but I wonder are there any other changes that we need to point out for the Azure installs?

Steve Stedman:                Oh, yes good point. On the Azure installs there’s a couple of components that need to be installed so that the Azure administrative interface can, or the Azure management end of things can connect and know what’s going on with the health of the database. There’s a couple of processes that usually get installed there.

Then, just…if it’s running as an Azure virtual machine, the rest of the settings are very similar to what you’re going to be running with normal SQL server. I guess one difference there is with backups. You’ve got the ability to do your backups to Azure blob storage rather than just normal disks.

Carlos L Chacon:               Right.

Steve Stedman:                That’s something that you want to consider and take a look at too. There’s also some automated backups with Azure where just sort-of out of the box it does your SQL backups for you and that’s one of them that I have never been entirely comfortable with that, so I usually turn that off and then make sure that the backups are managed by whatever process I would normally use.

Carlos L Chacon:               Yes, exactly and I think that those automated processes sometimes it can get tricky with point in time restorers and things like that. I can do that for you, but there are some limitations as well.

Steve Stedman:                Just think of your Azure VMs in this case as just another VM running SQL server with just a few additional settings you have to add to it.

Carlos L Chacon:               One more point that we should make there in with the Azure VMs and that is the D Drive.

Steve Stedman:                Oh, good point.

Carlos L Chacon:               It can be very tempting, and we’ve done different things in the past, so the D Drive, and have a little note there that says, ‘this is a flash storage ray’, or ‘this is flash storage’. Don’t put anything here because when you bounce your box, those files are going to go away. Then you think to yourself, hmm, that sounds like a pretty interesting place to put Temp DB.

Steve Stedman:                Yes, and there’s a lot of posts out there that say give it a shot.

Carlos L Chacon:               Sure.

Steve Stedman:                That’s one of those that I’ve give it a shot, I’ve tried it, what I found is that the D Drive that you get on your Azure VM is flash storage, however it doesn’t seem to perform as well as the flash storage through the premium storage that you get through Azure. There’s a few steps that you have to go through because with Temp DB you can put your files there, and you just have to make sure that it’s set up so that when your system re-starts, and if that a brand new formatted disk that the permissions get set right so that Temp DB can recreate your files.

Carlos L Chacon:               Exactly. That could be kind-of a stain your pants kind-of movement when, particularly when it re-boots and you’re not aware for whatever reason, that D Drive, there is a possibility that you could get a new D Drive basically. As Steve mentioned, the security won’t be set there and your SQL server is going to stop.

Steve Stedman:                Yes, because what its going to attempt to do is it’s going to create those Temp DB files, when that fails, SQL server says, I can’t go any further. We can’t start the system at that point.

Carlos L Chacon:               That will be just one of those that you’ll have to make sure that those who have access to the SQL server, your admins and whatnot, that if for whatever reason, they decided they want to bounce the box, patching, depending on how you’re patching goes, that they understand that’s something that they’ll have to check afterwards. It’s not every time that will happen, but it’s just one of those that you need to be aware of it.

Steve Stedman:                You may test this, you may re-boot your SQL server twenty-five times and never have a problem, then Azure decides that machines going to be moved somewhere different and re-provision somewhere and it comes up and that D Drive is empty and doesn’t’ have the right permissions. I’ve learned that one from the school of hard knocks. It seemed like a good idea at the time, but I know better now.

Carlos L Chacon:               I guess I will say, again you use with caution, but there was a situation where the drive…we had a classic model VM and we had allocated all of the disks that were allowed, the eight disks, stripped them and we were still…we needed premium disks and we couldn’t get them because we were in classic model.

Steve Stedman:                Oh sure.

Carlos L Chacon:               By moving Temp DB over there, at least we got some breathing room because we had additional disk in play until we were able to get premium disks in there.

Steve Stedman:                I guess the way I would say it is in a perfect world, like in typical physics examples, in a perfect world I would not recommend using the D Drive for Temp DB if you have other options. However, if you’re in a pinch, read about it, understand what happens and make sure you get the permissions set right and you can use it.

Carlos L Chacon:               That’s right. Okay again, James thank you for sharing your notes with us. Hopefully this is of some benefit to you. We will be posting this out on the show notes for today’s episode at sqldatapartners.com/serversetup and we’d love to get your feedback. Did we miss something, do you not like it, do you disagree with us, in some shape or fashion, you can let us know on Twitter at #sqlpodcast or reach out to us on Twitter. I’m at carloslchacon-

Steve Stedman:                I’m at sqlemt.

Carlos L Chacon:               We’d love to get that feedback and so I guess anything else before we end Steve?

Steve Stedman:                I think that wraps it up for this week.

Carlos L Chacon:               Awesome. Okay Companeros, we’ll see you on the SQL trail.