Episode 56: Favorite SQL Server 2016 Features

1400

Welcome back to the SQL Trail! Have you ever wanted to stick a bunch of SQL Server professionals in a room and hear their honest take on things? I found myself in Columbus Ohio at the SQLSaturday and I wanted to do a ‘live’ podcast and this episode is the result.  I chat with a panel of SQL Server professionals including Jonathan Stewart, Kevin Feasel, Mike Fal, and Microsoft employee Scott Klein. The panel members chat about their favorite Microsoft features. We also discuss the “Microsoft paradigm shift” and how the hybrid model is likely to stick around for a long time.

We Talk About…

  • Direct seating, availability groups, and Windows Server 2016
  • Why SSRS Mobile is a must-have for any company with C-level executives
  • The benefits of mobile SSRS reports for database and network administrators
  • The paradigm shift that’s spreading across all Microsoft products
  • PowerShell changes coming in SQL Server 2016
  • The problem with the “cloud first” model and why hybrid is the future
  • The power of Query Store

About Mike Fal
Mike Fal is a SQL Server Consultant with Upsearch. He’s a frequent PASS speaker on PowerShell topics. Follow him on Twitter @Mike_Fal or on his blog

About Kevin Feasel
Kevin Feasel is a Database Engineer at ChannelAdvisor and a Data Platform MVP out of Durham, North Carolina. He was also a contributor to “Tribal SQL”, a DBA guide from Redgate. Follow him on Twitter @feaselkl.

About Jonathan Stewart
Jonathan Stewart is the Principal Consultant at SQLLocks LLC and regularly presents on BI topics. Follow him on Twitter @SQLLocks.

About Scott Klein
Scot Klein is a Senior Technical Evangelist at Microsoft and former Microsoft MVP. Follow him on Twitter @SQLScott.

Resources
MSDN: What’s New in SQL Server 2016
Windows Server 2016 Preview
MSDN: PolyBase Guide
PolyBase Explained
Intro to PolyBase in 2016: Part 1
PolyBase in SQL Server 2016 (by Scott Klein)
Video: Query Store in SQL Server 2016 (by Scott Klein)
SQL Server Query Store
The SQL Server 2016 Query Store: Overview and Architecture
Security and flexibility with SQL Server 2016’s hybrid cloud solutions

Transcript

SQL Data Partners – Transcript

Episode 56, SQL Server 2016 Panel| Air Date: 08/09/2016

Introduction

Carlos: So companeros, we have quite the panel today. We’re here in Columbus live in front of a live studio audience! We’re gonna be talking a little bit about some 2016 features, so because we are going to be doing a panel I’ll let each of them introduce themselves. There’s a couple of them that you’ll have heard before. In fact, you know, Kevin man, we’re going to have to start paying this guy to be on the show. But let’s let them introduce themselves and we’ll go from there.

Kevin: Hey, I’m Kevin Feasle. I’m a Data Platform MVP and a database engineer out of Durham, North Carolina.

Mike: Hi I’m Mike Fal. I’m a SQL Server consultant with UpSearch and yeah, I’m here and happy to be talking about SQL Server 2016.

Jonathan: Hi I’m Jonathan Stewart and I’m a Business Intelligence consultant and I am a guest of these guys and happy to be here

Carlos: Awesome, so one thing I did note that you added to your resume since we last spoke, Kevin, is the MVP. It’s nice, congratulations on that. So again, ultimately our topic today is SQL Server 2016. Who wants to start? What do we want to start talking about? Here we go.

PolyBase

Kevin: Okay, so there are a few features in 2016 that I love. But above all, number one feature? PolyBase. I love this concept that I have a Hadoop cluster, I want to be able to access this Hadoop cluster from SQL Server. Most people in this space are going to be a lot more familiar with writing T-SQL statements than if I throw a scala application at them. They’re not going to want to maintain that. They’re not going to want to maintain dozens of pages of Java code when really all they want to do is, do a SELECT statement, pull this data from the Hadoop cluster, and tie it, join it to data that’s in SQL Server and return a results set. I want them to see this as just another table as opposed to a whole different data system.

Carlos: Yeah, that brings up a great point and Polybase is something that we haven’t talked about here on this show. But that idea of as different data sources continue to change, the landscape is changing there. In fact, that was the last podcast episode that you were on. Just talking about some of those different technologies we’re now required to support those. So having something that can kind of tie them together a little bit easier so I can use my tried and true interface to be able to do that. That’s going to be exciting. So we’ll actually commit to having another episode on Polybase and what that will do for you and we’ll talk about that at a later time.

Availability Groups and Direct Seating

So Mike, do you have a favorite feature of 2016?

Mike: Gosh, 2016. Where to begin? Um, I will say that the thing I’d like to lead off with is, I’ve been working with SQL Server since 7.0 and the 2016 release is probably the most excited I’ve been about a SQL Server release in that time. There’s just so many great changes, so many new editions. Um, my favorite edition is probably something that’s a little obscure to people, particularly, it’s around availability groups. And Availability Groups are starting to get more adoption, especially since Microsoft is adding more and more to it. There’s a lot of improvements to availability groups in SQL Server 2016, especially in combination with Windows Server 2016, which I just got confirmation is going to get released during Ignite later this year. So keep an eye out for that. But there’s this thing called “direct seating”. And if anybody’s worked with availability groups if you and to stand up a second node you had to go through this restore process with restoring fulls and restoring logs. The great thing about direct seating is that you create the availability group and the database, you say, “My nodes are enabled for direct seating” and SQL Server will populate it for you. You don’t have to do anything else and it just freakin’ works. I love it, it’s great, and there’s still some clunkiness to it. It’s still obviously in its first iteration. And I’ve got a blog post on it in June’s T-SQL Tuesday if people want to check out mikefal.net and read up on it. Um, but yeah it’s a great feature and I’m really excited about it.

Carlos: Now do you have to use the Server 2016 in order to use that feature or is it native to the database?

Mike: It’s native to the database. The Server 2016 stuff actually doesn’t have anything to do with direct seating, it’s more how you configure the cluster underneath so you can do stuff like cloud and you can do distributed availability groups. There’s a whole lot of enhancements, like you said. And server 2016 can blend into those, but that’s obviously beyond the scope of this little conversation.

Carlos: Sure, and touching base on that clustering for a second, one of the things I heard was that Windows 2016 is that now I’m gonna be able to join nodes of different operating systems and I can operate in a lower mode without having to upgrade everything. So that’ll be kind of interesting.

Analytics, SSRS, and Native Data Connectors

Carlos: So we’ve been focused a little bit more on the engine side. What about on the analytics side? Anything, Jonathan, that you’re interested about?

Jonathan: There’s quite a few things. One of the big things, which is more of an annoyance, is the ability for integration services to be able to support multiple versions. That’s a huge, huge benefit, because being a consultant and having to keep installs 2008 R2, 2005 because there’s clients on that. I got to the point where I had to start spinning up VMs because I couldn’t keep stepping on other features and stuff like that. Another thing that I’m really happy about tis the addition to reporting services, like the DataZen being built in. Because one of the thing that a lot of my clients, a lot of my clients are a Microsoft shop and so they want to stay on Microsoft products. You know, and it’s a lot better for me to tell them, “hey, you can do this with reporting services,” instead of having to tell them that they have to go out and get one of those other services. And we won’t say Tableau, right? You know, I use it too al ot and it’s great at what it does. But for costs versus features, Micrsooft is quickly catching up to the Cognos of the market. They do big, big, big things. It’s not 100 percent there, but they’re catching up fast. So I’m actually really happy to see, and too the things that Kevin was talking about. Polybase and you know, he’s going to have a tlak later on R. R integrations. There’s a lot of stuff in the data world that 2016 has in it that I’m excited to see. And I’m going to show my age here – I started with SQL Server when I was 19. Literally. But I started using SQL Server when it was Sybase. [laugh from the audience]

Yeah, see, so that’s taking it back, back, back, right? Yeah so I remember SQL Server 6…6.5, right? 7 was like, wow, it’s a whole different game. So I’m just, [laughs] I’m just so, the massive analytical push that Microsoft is bringing in. The new connectors, the new native connectors… all these native connectors are great, because it sucked having to use connections to all these things and hoping that they support it and support that command. So having all these connections to other high end systems is great.

SSRS Mobile

Carlos: And we haven’t even talked about JSON, right? That’s another one in there. Now we’re going to have Jessica Moss on the show later, depending on how these episodes get laid out, one of the things we’re going to be talking about there is mobile. Give us, if you will, if you can, give us like the 30 second elevator pitch for SSRS Mobile.

Jonathan: One of the great things about it too is that you’re dealing with CEOs and other C-level people who need to make decisions on the fly when they’re in meetings, and they don’t have time to you know, open up their laptops. And we always make fun of CEOs when I see pictures and stuff like that, but there’s actually legit reasons for that, because they don’t have time to open up a workbook and go through multiple excel spreadsheets just to see how much money they’re spending, blah blah blah when they’re trying to pitch their stuff to another CEO. So opening up an iPad and be able to break out and show a graph or something like that on the fly, that’s one reason that the Tableaus and stuff like that of the world were so great at what they did. Speed would definitely add to that.

Kevin: So Jonathan was talking about C-levels and higher ups. This also makes sense for the administrators. Okay, I’m not always in front of my computer. Sometimes an alert happens and I want to check out what’s going on in my system. I have a report that says, “These are nodes that up. These are the nodes that are down. Here are systems that are running, here are how they’re functioning.” If I have an ETL process that I’m concerned about, what’s the status of that ETL process? Make those mobile reports and make it so that I can see, do I need to log into a computer and check something out?

Carlos: Okay, so knuckle-dragging Neanderthal that I am, when I hear mobile reports, let me give you a bit of context. My world of SSRS, one of the things you don’t need is IIS to install SSRS. Now all of a sudden what I’m hearing, which makes sense, “Oh, I can see it on my mobile device.” Okay, that kind of makes sense too, I don’t know why you couldn’t have taken those reports and made them miniature? So now, is there a requirement to have SSIS? So I’m now exposing these out on the internet, is it really just as simple as that?

Jonathan: There’s an application that you install whether you’re using an Android or you’re using an Apple. I would hope there’s a Microsoft application for the three people who use Windows phones. And [laughing], I guess they’re out of luck, they have to get real phones. But there’s always Bing. But there’s applications, though, and you port it to the application and publish there. But one of the great things about it is it’s not just a regular SSRS report that’s miniaturized. It’s actually designed for the mobile format. So when you’re designing in visual studio, you can decide how you want your layouts to be. So think of a visual dashboard and how this lays out. You can design with things like that. So it’s meant to be extremely visual so you can process the data really quickly and go about your day. So that’s one of the benefits.

Carlos: Okay, very cool. So I think my biggest feature or what I’m excited most about, and again I’m a little more on the engine side. It would be the in-memory OLTP options, right? So I’ve drunk that Kool-Aid. We’ve had Jos, Jos de Bruijn on the show talking about that and I think that’s going to be very, very cool. In fact, he makes the argument that in certain instances you can actually use that in-memory technology and reduce your reliance on the cache. It’s like a [] cache as a result. Of course networking and other things still play into that, but we’re going to see the ability to scale, right? To be able to handle more batches at one time. It’s something that I’m very, very interested in. Ultimate that we’re all interested in our environments performing a little bit better. So if we can manage that, that would be good.

The SQL Server Module

Mike: I do want to get one other little thing in there. This is like a “one A”. and 8t’s actually not quite SQL Server 2016, it’s SSMS 2016. For most people who know me, they know I’m a PowerShell guy. And I’ve had to deal, and I get it. I talk to many, many people out there and the bad rap that SQL PS has, and it’s mostly because Microsoft threw something out and it’s had a lot of neglect over the years. So, a change that’s happened with Microsoft, and this started back in April, is that the SQL server tools team has been revamping the SQL PS module. They’ve been putting in some bug fixes, they’ve been tweaking the functionality of it, to basically fix some of these problems. They’ve got a long way to go, but one of the really exciting things is here in July, just two weeks ago, the SQL Server tools team dropped an update to SSMS that included a new module for SQL Server.

It’s called the “SQL Server Module” as opposed to SQL PS, and it has all of the functionality of SQL PS but they added 25-ish new commandlets. A lot on Always Encrypted and a couple for just getting stuff like getting the SQL error log. And really the exciting thing here is it’s showing the Microsoft and SQL Tools team their commitment to, because they’ve been haring this stuff from the customer base, from the user base. Like, “what can we do to make this better, this is such a pain.”

And they’re now responding to that feedback and they’re responding pretty quickly. It’s only been three months that they’ve really been doing this work and suddenly we’ve got a new module with new command lines and there’s more coming. There’s a big community effort right now to try and drive that. So that’s my sort-of SQL 2016, but I really wanted to get on that.

Carlos: [audio cuts out]

Mike: That’s one of the things that we’re talking about, is, and it’s in process. The current release of the SSMS 2016 has this new module in it. It’s already out and people can download it and use it as part of the SSMS toolkit. But there’s more coming, and that’s the thing, there’s so much going on and it’s going on so rapidly.

Microsoft’s Paradigm Shift

Jonathan: So to follow up on your “One A” thing, and this is a “One A, roman numeral one”, that’s it, yeah. So it’s not technically a 2016 thing but it’s a paradigm shift pretty much for Microsoft. And it’s their response and the ability to make changes on the fly. I mean, just Management Studio alone that it can update independently of everything else on the fly. You know, you can check how fast they’re making PowerBI updates and changes, so not having to wait. And I think that goes back to Satya’s, his background with the games and like that too. And we’ve seen that with all these things, so if you have an update for Xbox you just roll it out. You don’t wait until the second Xbox. You change, ready, go. So instead of waiting, I’m ready now. So I was telling Kevin earlier, I want to see what 2018’s gonna bring, right? And we’re just getting to 2016, but I mean, because, what can you do to shock the world when you’re already giving us these updates every two to three months?  If you’re giving us these little updates you’re obviously saving something big for 2018. So, you know, I’m not trying to throw that out to do that yet, but…

Mike: Actually, I don’t know if they’re saving something big for 2018. If you think about it, Microsoft and this paradigm shift which I totally agree with you, they’re going into this mode of faster and faster updates. You know, we already, bringing up SSMS as an example, SSMS is now on a monthly release cycle. They’re going to have updates to it incrementally. Incremental stuff, small stuff, but every month we’re going to have a new update to it. I’m pretty sure, and I’m not an MVP like some people so I don’t have the inside track on this, but SQL Server and all Microsoft products are moving in that. So when you say, “Oh, what’s going to be big in 2018?” It may not be. It may just be, “oh, here’s another incremental update that has this cool new feature.”

Azure

Kevin: In response to Mike, “NDA, NDA, NDA, NDA.” Actually, there’s also the Azure side where we’re already seeing, we saw version 12 of Azure come out pre-2016 and it had a lot of interesting things that made their way to the on-premises version. Well, Azure is going to be the playground for what the next features will be. We’re going to see them in Azure first, we’re going to see them in preview, in that public preview, before we get it on premises.

Carlos: And I think it is kind of a testament to that cloud-first, mobile-first methodology kind of trickling down. And oh! To speak of the devil, we just had a Microsoftee walk in the door. Scott, do you want to say hello?

Scott: Hey everybody! Scott Klein, Microsoft, happy to be here.

Carlos: If our conversation changes a little bit, ladies and gentlemen. That’s why.

From panel: Azure! Azure! Azure! Azure!

Jonathan: Now that there’s a Microsoft person here, I 100 percent love Microsoft. I have three tattoos. I have a tattoo that changes from SQL version to version to version. I have it

From panel: The Kool-Aid is very, very real.

Business Intelligence and Azure: Some Concerns

Jonathan: Right, right. Across my back I have Hekaton written in kanji. [laughing] I mean, and this is still on the 2016 theme. As Kevin was talking about Azure, I’m interested to see what changes are coming to Azure too, because I do have clients that want to run the current version of Azure on their database and their data warehouse. But there’s still holes, there’s still huge holes, you know like if I have an OLTP in SQL database and I have a SQL data warehouse, how do I get the data between them? Do I bring up an Azure VM to run SSIS, you know? What do I do with analysis services? I see data factory more as biz store. That’s how I, you know. I see data factory more as biz store.

Carlos: That’s not far from the truth.

Jonathan: It’s biz store for me. And what do they do with cubes?

From panel: We have a Microsoft guy here, maybe he can answer all those.

Scott: I don’t know if I can answer a cubes question.

Jonathan: ‘Cause right now, I tell them for that either they keep it on premises and they move data back and forth or we grab another VM which defeats the whole point kind of, because you have to pay for the VM and put SQL Server on it.

Scott: Or just spin up an Azure SQL VM. I don’t know.

Carlos: So I guess the question is, what is the interaction between my on premise and my Azure services?

Jonathan: The full lifecycle from your OLTP all the way to analysis cube. You can’t do the whole thing in Azure without having to step out and bring up VMs as well.

Carlos: I guess I will say having talked to Matt Usher, in that we actually brought up that point. So when I thought about Azure SQL data warehouse, he wasn’t talking about cubes necessarily. He was talking about processing, right? And so the destination is not so much the feature they’re offering as it is the computing power behind that.

Scott: I kind of get the feeling, and don’t take this as gospel, that they’re trying to shift away and sort of change, it’s a paradigm shift, right? You go from on premises to the cloud there’s a paradigm shift. And I get the feeling, and this isn’t Microsoft speaking, but I kind of get the feeling that we’re going to do BI and analysis services and kind of this different way in the cloud, right? And we may be going away from cubes or something like that.  I have no idea, right? So don’t take this as gospel. But maybe we’ll see something like this down the road. But I think what they’re going with is, “How can we make this a little bit easier in the cloud?” That’s why you see Azure Data Factory and the ability to look at that Azure Data Factory in more holistic, not just SSIS perspective, right? Because you cannot compare Azure Data Factory and SSIS, I mean those are completely two different things. So I think, I’m looking at Azure Data Factory as a way to move that data and if you look at Azure Data Warehouse holistically, how do we do these things like data warehousing in a much different way?

Carlos: And I think part of that shift is that when I think of Azure data services I think of those streaming components. Much more than, again, like a cube is the final resting place. I don’t meant to say that it’s a graveyard, but

Jonathan: It’s a terminator, yes.

Carlos: I think the services that they’re building that, like, “hey I have this inflow of data and I want to see 15 minute increments,” if you will, that’s the scope of the data I want to look at. So those are the problems that I’m trying to solve because they require higher performance, they shift up and down. I don’t need this necessarily all the time. It kind of works in that cloud “let me help you expand your infrastructure in small trunks” and not have to make such a huge investment. Mike’s over here smiling.

Mike: I don’t think on prem’s the answer for this. I think the minute you have to take data and say, “I need to go on-prem to do this,” then I think you need to relook at your architecture, right? If you need that, put it in an Azure VM with SQL but I don’t think bringing it on-prem is the answer

Kevin: I kind of want to respond to that in that I agree with it on a fundamental level but I also think, we’re in a phase right now with moving to the cloud and cloud development where we still, there’s so much stuff still on prem and a lot of companies are saying, “well I can’t just pick up my stuff and move it to the cloud and I can’t just jump from one side of the cliff or the other. I have to stand on both sides of the cliff.”  So there’s a lot of, when you start talking about these architectures and what-not, you really have to keep in mind the hybrid model.

Mike: And I won’t argue that, absolutely won’t argue that.

Kevin: And I think that’s kind of when you start talking about the lifecycle, you have to think, how am I going to migrate stuff up, how am I going to have both sides communicate with one another? And that’s really where I think you kind of need to be able to step outside. I don’t know, there’s a lot of stuff around it but in general as I talk and this is the point I wanted to stress is as people are looking at the cloud and they’re thinking about the services and all the offerings, you gotta stay focused on the hybrid model because personally in my experience it’s impossible to just say, “Well, we’re going to move to the cloud!” You’ve got to be able to be partway.

Carlos: And I think Microsoft has acknowledged that a little bit in their, like, “oh, hybrid! It’s a thing.”

Scott: And hybrid is just, I think, hybrid is just massively generic, right? Because there’s so many aspects of hybrid and I could be completely wrong –

Carlos: That’s why we invited you, Scott, to be completely wrong.

Scott: Completely wrong. If I’m doing BI in the cloud, I’ve made a decision to go to the cloud I think. Now there might be some outliers around that, but if I’m doing BI in the cloud I’ve pretty much moved my stuff to the cloud. I think, what does hybrid mean? I think that answer is different for everybody.  Hybrid could be, “I’m just doing stretch database.”

Carlos: Sure.

Scott: Or hybrid could be, “I’m backing up to the cloud.” That could be hybrid. What does hybrid really mean? If I’m doing BI or I’m doing SQL Data Warehouse, I’ve pretty much made the decision to –

Jonathan: But a data warehouse is still just the storage equation. That’s just the beginning part of full blown analytics. Like, Microsoft pushed tabular for like four years, but where do you put your tabular models? You need to build up a VM to run analysis services again. Al l those things that are there that people are using today, you can’t just drop those off and go to the cloud. There’s that part of hybrid again. To me, it’s an incomplete model right now. I don’t know what’s coming tomorrow because I’m not an MVP.

Carlos: And things do change all the time, right?

Jonathan: And I heard rumors about things coming. And I can’t say who’s telling them, because who told me may be obvious if I say rumors and things that Microsoft does understand the pain that I’m talking about and they’re working to address the Analysis Services hole, because that’s a huge hole.

Kevin: For the record, I don’t tell Jonathan anything.

[laughing from panel]

Jonathan: It was not Kevin. Kevin has not ruined his 16 days of MVP status for me. Now next month…

Carlos: No promises, right? So kind of wrapping up, Scott you kind of joined us late, but our topic was on favorite 2016 features.

Scott: Oh man.

Carlos: So you’re not technically a SQL guy per se…

Scott: Oh no, I am.

Carlos: Oh you are? Okay.

Scott: I was a SQL Server MVP and then an Azure MVP before joining Microsoft, so.

Jonathan: Do you like how he changed the subject thoug?

Scott: Oh, I did no subject change here.

Carlos: So why don’t you tell us? What is your favorite feature about 2016?

Scott: Oh man. Okay, uh that’s a tough one but I’m going to say, just ‘cause you could stick with core SQL Server stuff but I think with the way your industry is going that I’m a fan of some of the big data things that are in SQL Server like PolyBase and R…

Carlos: That’s what Kevin mentioned.

Scott: Oh really? Yeah, not that… I’m very interested to see where that goes. Yeah, stretch database is awesome and I think the whole hybrid is very needed and we’ll see where that is gaining traction, but I’d like to see where PolyBase goes just because of the whole how can we use the tools? How can we leverage the knowledge and the tools that we already have?

Carlos: In a more familiar environment…

Scott: In a more familiar environment let me do this non-SQL related stuff? And that just fascinates me.

Kevin: So Carlos, what’s your favorite feature?

Carlos: So I did talk about the in-memory OLTP.

From panel – That’s not in 2016.

Carlos: Yes, well I think it’s finally put the big boy pants on in a sense. I feel like I can use it without having to baby it so much.

Jonathan: In 2014 wasn’t it a beta product?

From panel: Oh…. [laughing]

Carlos: [laughing]Scott: Kind of like, did he want MVP status? Hmm…. Let’s see, taking names!

Jonathan: I’m a Linux MVP.

From panel: Ohhh man…. [laughing]

Carlos: It’s getting deep over here.

From panel: Those exist…

From panel: When SQL Server is on Linux GA’s maybe…

SQL Server Management Studio

Carlos: So my “One A, roman numeral a” is not really a feature, but the ability to install SQL Server Management Studio without having to go through the whole install, that’s really cool. It’s kind of like, “Yes! Finally!” Yes, thank you!

Jonathan: But I’ve seen that first screen that you have to click through anyway to get to the real one. It’s like, “Oh my god!”

Mike: Baby, baby steps man. We’ll get there!

Query Store

Kevin: I do want to throw out one more feature since we didn’t have many administrators in the room, unless Mike counts as an administrator. He counts more as a PowerShell guy. Query Store is a gigantic feature in SQL Server 2016. We’ve already taken advantage of it and I’m not even a Database Administrator and I’ve taken advantage of it. We’ve already identified plans that have performed poorly for whatever reason and it’s like, “Oh look! 95 percent of the time your query’s performing great because it’s using this plan. 5 percent of the time it’s horrible because this separate plan.” It’s great being able to see that. Being able to even force that plan like, “Hey, I know the 95 percent plan is really going to work.” I don’t know why you’re getting the 5 percent here. Maybe it’s outdated statistics and maybe it’s something else. But I know we want to stick it in this area. So Query Store is huge.

Scott: I agree with that one. Of all the features that should be used? That one. That one.

Carlos: Yeah, I know when I spoke with the team pre-release of course they were very excited about it. They were seeing good traction in the early release of the usage.

Scott: I think there needs to be some guidance around using that too. What is that saying? With great power comes great responsibility? I think people can shoot themselves in the foot a little bit with that.

Jonathan: Even with me in the BI spectrum, like my background where I came from I was a DBA. And even know when I talk to other BI professionals, and I’m still performance, performance, performance. And that’s a great tool. Are my queries I’m using to export data or whatever, how are they running? And stuff like that. Doing small things like that. I think just to think everybody, not just the administrators, you know Kevin said he’s not an administrator he’s just a F# guy.

(Cross talk among panel members.)

Jonathan: Yeah, that’s that other language. Yeah, it should be used by everybody. Everybody can benefit.

Kevin: I just want to say that Jonathan said that everybody can benefit from F#.

Carlos: For the record. We have it recorded everybody. Well great, thanks for the conversation. From our time perspective I think we need to wrap up, but ultimately I think we can all agree that we’re all looking forward to 2016. Well, I guess it’s here now. We just need a few more implementations of it. Thanks for coming in and being on the panel.

 

 

 

 

Episode 55: Reporting Services Changes in SQL 2016

SQL Server reporting services hasn’t changed in what seems like 10 years.  The rest of the reporting environment at Microsoft has seen some MEGA changes and it seems like we could use a few updates to the environment. Our guest this episode is Jessica Moss, a data architect and Microsoft MVP.  She brings us up to speed on what we can expect in the new version of SSRS and how this ties into the other changes Microsoft is making.

Episode 55 Quote

“So those four different groups that we have Microsoft has aligned the four reporting tools with each of those. So your paginated reports will be your Reporting Services. Your interactive reports will be PowerBI and that new product that we have that you were mentioning. We have our mobile reports which will be our SSRS Mobile or also known as DataZen in the past. And then analytical reports and charts will be Excel.” – Jessica Moss

Steve and I also answer a question we got from Episode 49 about when a table variable might actually be a good choice to use over a temp table.

About Jessica Moss

Jessica M Moss is an architect with New Market Corporation in Richmond, Virginia.  She is a Microsoft SQL Server MVP and well-known practitioner, author, and speaker in Microsoft SQL Server business intelligence. Jessica has created numerous data warehousing solutions for companies in the retail, internet, health services, finance, and energy industries. She’s also written technical content for multiple magazines, websites, and technical books. Jessica enjoys working with the Central Virginia community and speaks regularly at user groups, code camps, and conferences.

Resources

Follow Jessica on Twitter
Jessica’s blog
MSDN: What’s new in SSRS
SQL Server Reporting Services Team Blog

 

Transcript

SQL Data Partners – Transcript

Episode 55, Reporting Services Changes | Air Date: 08/02/2016

Guest: Jessica Moss

Introduction

Carlos: So Jessica, welcome to the show!

Jessica: Thank you for having me.

Carlos: So Compañeros, for those of you who don’t know Jessica is a Richmond native and lives around here, so we see each other from time to time and so Jessica, I h ave to admit that I still keep checking my mailbox looking for that wedding invitation. I’m sure it just got lost in the mail.

Jessica: Oh yes, lost in the mail for about four months, that’s when the wedding was!

Carlos: Oh well, it’s slow. Well congratulations on that. Four months, it doesn’t seem like it’s gone by that fast.

Jessica: That depends on if you were the one that just got married four months ago.

Carlos: Yeah, that’s true.

Jessica: But thank you. Very, very exciting time in my life.

SSRS 2016 Changes

Carlos: No doub.t Well thanks for making a little time for us to come on the show. Ultimately our conversation today is on reporting with a focus on SQL Server Reporting Services. So some of those new features that are coming in 2016 and kind of how that landscape has changed quite a bit in the reporting and analytics space. So I guess, maybe let’s kind of jump off in there first. Why don’t we take a peek and you tell us what’s new when it comes to SQL Server Reporting Services 2016 and some of these new features?

Jessica: So, Reporting Services has actually made some great leaps and bounds within 2016. Microsoft really didn’t make any changes to reporting services for the past five, six years really since the 2018 R2 release, so 2016 they’ve actually done a lot of great work incorporating mobile reports and enhancing the reporting experience that we have today to really pull all of the different tools together and provide a really great and dynamic reporting platform.

Carlos: Right, you had mentioned that things haven’t changed there. We are getting some differences now. The other thing we’re also getting is all these other tools. So we’re also getting PowerBI, you know I don’t know, it seems like every day they’re coming out with something different: things in the cloud, stream analytics, all these different things. Why would we now, why still reinvent the wheel? What is SQL Server Reporting Services going to do for us that these other things can’t?

The Four Report Types

Jessica: That’s a great question and the way that Microsoft is proposing this is that they’re really focused on what the business needs are for reporting. So they’ve broken up those report groups into four different categories. Where the first one is your paginated reports. And when we say paginated, we mean kind of the reports that we think of today. So your Pixel Perfect reports, the ones you might have to send outside of your organization in a particular format, those are going to be paginated.

Carlos: And I also think of it like data reviewed and verified? Is that fair as well? Like my accounting group is going to want to look at that before anybody sends it.

Jessica: Absolutely. Financial reports are a huge way of doing the paginated reports. They want the numbers to be absolutely static so that they can look at it and prove it before it goes out anywhere. Your second group is going to be your interactive reports. Or, the analytics where you’re trying to dig into answer some sort of question where you may not really know what the details or what it is you’re trying to figure out but you have an inkling and you are going to drill into it to try to figure out an answer. Really that interactivity. The third group is going to be your mobile reports, where you have really more of a quick glance at something. You want to get a high level KPI or key performance indicator, where we’re looking at where we want to go from sales comparing this month to last month. I have a quick answer and decision. And then our final fourth group is going to be our analytic reports and charts. And that’s where we’re going to do some analysis and calculations on that information. So those four different groups that we have Microsoft has aligned the four reporting tools with each of those.

So your paginated reports will be your Reporting Services. Your interactive reports will be PowerBI and that new product that we have that you were mentioning. We have our mobile reports which will be our SSRS Mobile or also known as DataZen in the past. And then analytical reports and charts will be Excel.

Carlos: So let’s dive into that for a second, because one of the nice things about SQL Server Reporting Services in the past is that I didn’t need IIS. I could whip my SQL Server install, get that up and running, you know get a service account and have access through a web interface. Now when I hear mobile reports, that makes me think that things have changed a little bit there.

Jessica: You would think so, but actually they have made it even simpler than it was before to setup your entire reporting services configuration and all you have to do is tie it to, if you want, the PowerBI integration. You would point it to the PowerBI service that you have and then from a mobile perspective to be able to connect to that server, you’re actually going to download the PowerBI mobile app. It’s just an application on your phone. You specify the server that you’re going to connect to and that wil allow you to view those reports on your phone.

Carlos: I guess that makes senses, it seems like there’s a delineation there. I was thinking, oh, I’m going to see a web browser, go to a web browser and connect maybe through a VPN or something, and see my reports. What you’re saying is that there’s an app for it that I download and connect up.

Jessica: Absolutely. And that app all you’re gonna need is that encryption layer and your username and password if you’ve got it exposed through the internet or you might have to use VPN if you’ve got that on your intranet instead.

Steve: Okay, just to clarify one thing there then, if we’re using the PowerBI mobile application is that for that second category of interactive reports that you mentioned?

Jessica: That’s one of the great aspects of this application and the way that Microsoft is going is that they’re trying to pull all of these different tools together. So the application is called the PowerBI mobile app and you download and it allows you to connect to both the interactive PowerBI service that you have and the reporting services web portal that you have today.

Steve: Okay. Great.

Carlos: That’s going to be pretty cool. So one of the interesting things there is now I have multiple components. Now I have a report, I have a database server, and I have an application. Right now in the past those were kind of three distinct functions or groups of people that were doing that. Howa re organizations are deploying this? So now if I’m the data guy and I’ve done a little bit of reporting services, just hand out some reports, but the analytics has been kind of kept at bay or that’s another group. Maybe the Analytics group. Maybe I have one or there’s somebody who’s doing that. How are you seeing that mesh? Am I going to have to get cozy with my infrastructure guys so I can make sure the app can deploy and all that stuff?

Jessica: [laughs] It’s actually interesting journey that organizations are taking when we talk about these different reporting aspects and a lot of the analytics that you’re talking about, and data scientists is really where we’re going with that, they want to be able to do that interactivity, dig into that data, ideally as easily as possible which is where some of our data developers can help provide that information. But there is going to be some level of technical expertise that they need to be able to grab all of that together. When you actually start to turn that into an enterprise or a corporate feature of the organization, it tends to go back out of that analytics group back into your developers and your data team, so it can be exposed, maintained because you have all the fun support items that go into that like disaster recovery and making sure that we have a decent uptime.

Carlos: Sure.

Jessica: And all of that, so there is a little bit of a passing back and forth for that, but really supporting and maintaining this is still going to live in the job description of the DBA.

Carlos: Okay companeros, whoever you happen to be sitting next to, grab their hands, kumbaya…sing it with me!

Jessica: [laughs]

Carlos: So one of the things I was always interested in is you mentioned these KPIs that are available. How does somebody normally go about choosing a KPI? Because I kind of feel like that’s a business decision, but they’re putting this into, “Hey, you can do KPIs!” So now it’s like, “You write the report and you choose the KPIs.” How does that work and what are the proper ways of going about getting what those are?

Jessica: So KPIs, or key performance indicators, have been around forever. Especially when we’ve been talking about score carding and dash boarding. What reporting services gives you is the ability to create those separately from your reports and have them exposed right at the first page of the web portal. So ti’s right upfront where you’d want to see it. So even though it is available through the web portal, our data folks are going to be creating those, we still just like before want to work with the business and come up with what those KPIs should be and what they are most interested in seeing from where we are today and our target goal of where we want to be in the future. To define it with the business, then you can incorporate that right into the web portal so that they can see that immediately.

Carlos: So that web porta, right, I’m thinking previous versions when I go in I have severs/reports, server/report server. What’s modifying on that interface? Is it changing?

Jessica: It has not changed at all. You still have your reports layer and that is what used to be report manager portal and has that full availability for you to interact with the reports, view, update, manage them, all of that and the report server behind the scenes is going to be where your reports and all your items and resource are stored.

Carlos: Okay. Now the creation of those reports, so they introduced the…I can’t remember the old name for it, but now it’s the data tools right?

Jessica? To do the development of the report?

Steve: Oh, it was the Business Intelligence Development Studio for a while.

Carlos: Oh there we go. Which then became some kind of data tools, and now that integration with Visual Studio was almost complete, right?

Jessica: Yes, as of SQL Server 2016, SQL Server Data Tools has actually been incorporated into the Business Intelligence version of that. So you can download one set of tools and you get the templates for reporting services, integration services, analysis services, and your database development. So all in one neat package and not only do you get it for 2016, you’re also able to develop in previous versions of each of those. So very exciting that we don’t have to have a whole barrage of these tools on our boxes anymore

Carlos: Because that was confusing, right? You had SQL Server tools, Visual Studio tools, and it was…yeah. It was kind of a mess.

Jessica: Absolutely.

Steve: So wait a second, just to confirm there. So if I wanted to deploy from or create reports and deploy from SQL Server, and I had an old SSRS server running 2008 R2, you’re saying you could deploy from those tools to that older version a well?

Jessica: Absolutely. The only thing you need to do is set a property within the project itself so that it points, so for reporting services it’s either 2008 or earlier, 2008R2 or earlier, 2014 to 2016 and up. So you’ve got three different levels there. You select one of those, it magically changes the code behind the report, and then you can upload it to the appropriate environments.

Steve: Oh, very interesting.

Jessica: Very helpful.

Carlos: But that’s the install. Am I going to get those tools on SQL Server or is that still a Visual Studio component?

Jessica: It is still a Visual Studio component, so it’ll be a separate download so that they can make updates to it and keep it as close to up-to-date as possible where you don’t even have to re-install anything in your server or your 2016 world. So they’ve really separated those two sides of it.

Carlos: So with that new functionality, you already mentioned KPIs, we’re getting some new goodies and all the Power-things with the way that we can display data. It’s more robust, is that the right word to show that data? But I also kind of feel like, anytime we get anything fancy that’s more ways that things can break. So as we get users, and again I’m thinking of the PowerBI space as users can create their own reports and upload them, right, which we’ve kind of had on the SQL Server. We’ve had that SQL Server Designer or that data, whatever that designer tool was?

Jessica: Report Builder.

Carlos: Report Builder, yes, that they put out there. I was …eh…. Let’s take a poll. Have you had any of your non-IT people use Report Builder? I have not.

Jessica: [laughs] I actually had one client that did that.

Steve: I know I haven’t seen it.

Carlos: Yeah, okay, I was just curious to see if maybe I was just doing it wrong.

Jessica: It is something that you have to work into whatever the project or department is. So in the one scenario I had it, it was a group of analysts that were working with Excel to try to create those sorts of reports before. For them, report builder was a great step up and we had an analysis server cube underneath so it was very much of a drag-and-drop environment so it…

Carlos: So everything was sort of set, sort of pre-staged in a sense?

Jessica: Exactly.  But at least the different clients I’ve worked with, it has been few and far between. But in 2016 they have actually made Report Builder a little bit prettier. They’ve refinished it and they’ve added some additional charts for people to use. So maybe we’ll start seeing more companies use it.

Carlos: Interesting. So you mentioned new charts, right. So one of the ones you mentioned was tree map, starburst, customer parameter panes… and again, so now these are scatterplots right? All these things. So I kind of think there’s this, maybe not divide, but now there’s “data” and then displaying that data and then who supports that? So the numbers, okay I get that maybe if I’m the data person I’m responsible for those numbers. But then if you can’t understand what that scatterplot means, how are people getting over that?

Jessica: Yeah, what they’re, and this is that divide from the data folks, from the business versus those of us who have to actually support it and develop it. What some organizations have is a middle layer of a data architect, a data warehouse type team, that is that middle ground between understanding the business and what they’re trying to do. Knowing where that data comes from but not really doing all of the maintenance and support that all of our DBAs do. They really can make that connection between the two people or the two groups and make sure that what the business wants to be displayed is how it’s displayed and what should be there without having to understand all of the maintenance side of things.

Carlos: So it kind of just depends, it sounds like it’s a cultural or environmental thing. You almost have to have a person with that role. But if you are the report person, then…

Then that becomes you! [laughs]

Carlos: Okay, interesting. I guess, other things we should be looking forward to in SQL Server 2016?

Jessica: You mentioned some of the new report features that we have, such as the new charts and the other big thing that they did was re-did the rendering engine for reports. So before we were using HTML4 and Silverlight, they have revamped that to now be in the HTML5 rendering engine. Which means that we can now use reporting services web portal on a variety of different web browsers which is great. You can now use Chrome or Firefox and not just Internet Explorer or Edge.

Carlos: There you go, that’ll be nice.

Jessica: Very helpful for all of us. And the custom parameter pane actually is something that we’ve been asking for forever, whereas before it was static and you could only have so many on the pane that you had there. And it was actually to the point that I had many web applications where we would actually create our own parameter pane within an .ASP web app so we customize it. So it would lay out where we wanted it to and we could change the colors of that and then embed the reporting services report within an iframe within the application.

Carlos: Interesting.

Jessica: So, they’ve really taken us a step away from that, hopefully, give us the ability to at least put those parameters in different places within the report so that’s exciting as well.

Carlos: So I see, so if you had sections for example, you could actually move that parameter lower down in the report?

Jessica: Exactly.

Carlos: Instead of being up there at the top?

Jessica: Oh sorry, not in the report itself. Still in the pain in the top, but in a different order up there.

Carlos: Oh I see. So instead of the way you had to specify it in the report to show up there, you now have some manipulation options there?

Jessica: Right, so you could them all in one column or in one row, or 5×10… whatever is easier for you

Carlos: I see, okay. That is good.

Steve: Wow.

Carlos: I guess one last thought that I had, and Steve and I were talking about this in preparation for this episode and that is why, obviously if you’re an analyst Reporting Services is something that you’ve at least experienced. There’s Tableau, and PowerBI, and all those other features. Again on the data side, I still feel like there’s lots of folks out there who aren’t at least taking advantage of SQL Server Reporting Services. So if someone says, “Hey can you run this query and send me the results?” I still see a lot of people doing that. Now I know that maybe in the beginning, because you had to get into the designer and create some of those reports, if somebody hasn’t’ taken the time to get into SQL Server and into Reporting Services, is it going to be easy enough for them to say, “Okay, you know what? I have a query; I want to make a report out of this.” That it’s going to be pretty straightforward?

Jessica: I hope that the really ramp up to getting people into reporting services will be easier. From a 2016 perspective, we haven’t really made any jumps in that regard.

Carlos: Okay, interesting.

Jessica: You’re still going to need to create your own queries and there are the tools within Reporting Services to be able to do that within Table Designer or if you’re doing Analysis Services, kind of through that drag-and-drop interface. But in general you’re still going to be following the same pattern, especially when we’re talking about paginated reports. Now the mobile reports, they have made that easier at least to reduce that learning curve. One of the interesting things, at least within the mobile report publisher, is that you have the ability to design your report without having any data within it. So they actually provide sample data sets so that you can see how it would look and know what data you would want before having to pull in your data. So that is

Carlos: Why does that sound scary to me?

Jessica: [laughs]

Carlos: That sounds like, “Here’s the data that I want, go make my report do this.” That’s kind of what that sounds like.

Steve: Is that the bait and switch model?

Jessica: It can be! Hopefully before you actually publish it you do get the data the way that you want it to and expose it, but it’s kind of a different way of thinking about creating reports. It’s like, “Hey, I want to see it in a trend, but how should the data look to be able to show this trend?” So it’s a little bit of a different take at least on the mobile side and we’ll see what Microsoft does in the next version or so to bring those two models together. If they can really decrease the time to get up to running with reports.

Carlos: Now one thing you did say, that it didn’t dawn on me, so my mobile report interface, or my mobile report creation and my regular SQL Server report development will be in different tools?

Jessica: Uh, yes, actually.

Carlos: Ahh.

Jessica: We do have multiple tools that we have to work with.

Carlos: Of course! We’re talking about Microsoft reporting; you can’t just have one. Where’s the fun in that?

Jessicia: It is all exposed through the web portal. You can download each of the tools from there. But yes when it comes to the tools, we have the SQL Server data tools that we mentioned which is within Visual Studio, we have our Report Builder 3.0 and those two do our paginated reports. And then we have the Mobile Publisher and this is actually the tool that used to be in DataZen, which is the mobile company that Microsoft purchased and which has been incorporated into our reporting services mobile infrastructure. So that is a separate download as well.

Steve: So a question there on the editors there. You talk about the report builder 3.0 and the SSDT interface into the paginated reports. Have there been any major improvements to the editing and layout of those paginated reports or is that still kind of a bit challenging?

Jessica: What do you mean by challenging, Steve?

Steve: Well, when you’re trying to get a report to laid out to that pixel-perfect lok and you’re trying to get it to format for the page and for the screen and all of the different places in that repot where you can edit the layout and positioning and all those things, it always seems like it wasn’t quite as smooth as regular Visual Studio type tools.

Jessica: And unfortunately they have not added any functionally for managing that pixel perfect aspects. They’ve always had that snap to grid where we could start to get it to line up with other things, and that’s still there. What they really focused on this time was the modern look of it. So all of the charts all of that has been updated. So maybe that will help the users overlook some of the lack of having everything lined up the way you want it. But there’s always ways to get around it but it’s always going to take a little bit of time to get it the way that you want it.

Steve: Okay. I can only hope for improvements there.

Carlos: Yeah, pixel perfect is going to come at a cost there. Awesome, so Jessica, let’s do SQL Family.

Jessica: Great.

SQL Family with Jessica Moss

Steve: Alright, so the first SQL Family question is around keeping your skills sharp. With SQL Server features changing so rapidly with each new release, what kind of things do you do to keep your SQL Server skills sharp?

Jessica: Probably the most helpful that I’ve found with 2016 is a lot of the webcasts that are out there right now. So the PASS organization has virtual chapters for the engine, business intelligence, business analytics. They have literally a whole slide full of the different virtual chapters that are out there and they have just amazing presentations and sessions out there all the way from introductory to a deep dive. So I sign in and watch those as often as I can.

Steve: Okay, I know those re usually really, really good.

Carlos: And it’s interesting that you bring that up. I saw it yesterday, and I don’t know if it happened yesterday or last week, but Microsoft press has actually released all of their employees. And so they are not, I think they’re still going to be putting out books, but hat business has changed so much that the time it takes to get all those chapters and things put together that people are going to webinars and what-not because, how could you create a book on PowerBI for example? It changes every week.

Jessica: That’s incredible.

Carlos: That idea of kind of constant iteration, we’re seeing that in the way people are learning. So we’ve talked a little bit about SQL Server today and kind of the nuances of it, but if you could change on thing about SQL Server, what would it be?

Jessica: I would have to say, some of the licensing aspects.

Carlos: [laughs]

Jessica: Have you heard that one before?

Carlos: No, but I chuckle because I’m surprised I haven’t to be honest. Because it’s like a nightmare! No one wants to deal with that.

Jessica: It actually is. And you know, Carlos, when we give presentations we often get questions of, “Well, how is it like this?” Or, “how many people could I do for this?” and “what edition?” And really my answer every time, “I can give you my best guess but you have to talk to a licensing specialist.” And the fact that we actually have Microsoft licensing specialists, I think we’re doing something wrong. I just wish that was simpler to understand and that everybody would just know what we could do to get it to more people.

Carlos: Right.

Steve: Yeah, that’s a great point. So, the next question is on career advice. What is the best piece of career advice that you’ve received?

Jessica: Another great question. If I had to give or repeat a piece of career advice I’ve been given, it wasn’t by any one person, more of a culmination of a lot of people saying it in different ways. But the most important thing is to do what you love. To enjoy your work if you’re not enjoying what you’re doing, then change what you’re doing. If you focus on what you enjoy and what you love, then you will advance and move forward. You will advance and move forward and be a lot happier.

Steve: That is a very good piece of advice. I know I’ve experienced both sides of that myself.

Carlos: And I think we’re fortunate enough, and I won’t say that there are jobs on every corner, because I know that some sectors are struggling more than others, but I think we are fortunate that we have a lot more opportunities than most.

Jessica: Absolutely.

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

Jessica: So you had actually sent this one over to me before this episode and I was looking at it. I know what first came to my mind which points out one of my faults, but I’m going to go ahead and say it anyway. If I did have one superhero power, it would be teleportation. Because I tend to run late a lot. And there are plenty of times where I’ve thought, “Wow, if I could only just be there right now.”

Carlos: Yes. Voosh!

Jessica: Even though that points out one of my faults, that would definitely be the superhero power that I would like.

Carlos: Well Jessica, thanks so much for being with us today.

Jessica: Thank you so much for having me, this was a great time.

Carlos: So if people wante dot follow up with you, how could they get a hold of you?

Jessica: Absolutely. My website is www.jessicammoss.com . It has contact info and any of my upcoming presentations.

Carlos: Well awesome, thank you Jessica.

Jessica: Thanks Steve! Thanks Carlos!

Steve: Thanks Jessica. Have a great day.

 

Episode 54: An Executive Panel with Andrew Rose and Jim McCullen

What is the leadership of your company thinking about the future of data and the role you will play?  To help give some insight into that question we have asked a few of our friends on the show for an executive panel in episode 54.  Andrew Rose is the CEO of compare.com and Jim McCullen is the VP of IT for Century Distribution. We ask them about what they’re looking for in IT staff, how they go about hiring, what they think of social media, and moving to the cloud.  They give their thoughts on what makes a great member of the team and so I hope you will give today’s episode a listen.

I am also interested to know your thoughts on the exchange.  Did what Andrew and Jim say resonate with you?  How have you tried to straddle both the technical and the business learning?  Let us know in the comments below.

Episode 54 Quote

“It’s really trying to take an interest in the business side as much as possible. I can’t stress how valuable and how important that is. The technical teams that we have here, the staff members that do really take an interest in the business side are so much more successful because they can blend those two.” –

Listen to Learn…

  • The one social media platform you have to utlize in order to get the job you want
  • Why you have to think about yourself as a brand
  • Why you aren’t getting as many training opportunities as you should
  • Why focusing your resume on just the tech is missing the big picture
  • The importance of the value proposition between you and your boss
  • The challenges with moving to the cloud
  • What data professionals MUST know in order to succeed
  • The one thing these executives want you to do TODAY

About Jim McCullen
Jim is the Vice President of Century Distribution Systems and the author of Control Your Day: A New Approach to Email Management Using Microsoft Outlook and Getting Things Done. He’s done everything from development and databases and now leads the IT team for Century.

 

 

About Andrew Rose
Andrew Rose is the CEO of Compare.com, an auto insurance comparison tool. Andrew is also an active volunteer for FIRST LEGO League (FLL) in Virginia and DC for the past 14 years. FLL is a robotics competition for elementary and middle school students which makes use of the LEGO Mindstorms system. Andrew is a graduate of Virginia Tech and the Darden School of Business at the University of Virginia.

 

Resources
SQLCruise
Control Your Day: A New Approach to Email Management
Connect with Jim on LinkedIn
Connect with Andrew on LinkedIn
Compare.com
Century Distribution Systems
16 Tips to Optimize Your LinkedIn Profile and Your Personal Brand

 

Transcript

Coming soon

Episode 53: Storage Options

Compañeros! Welcome back to the SQL Trail. Have you ever wondered what a LUN is? Have you given some thought about getting more into new storage options, but aren’t sure where to start?  You’re in luck because in Episode 53 Steve Stedman and I chat with THE Argenis Fernandez, storage guru and PASS Director-at-Large. We talk storage options, LUNs, IOPs, and why he thinks we all need to approach storage a lot differently.  Steve and I learn something new from him every time we talk to him and this time is no exception.

This episode is sponsored by

Cozyroc-logo-png

Episode 53 Quote:

“We’ve been avoiding the topic of storage for a long time. And you know what? We actually have ITIL to blame. Do you know it’s ITIL that actually started splitting responsibilities between operations and engineering? And then even within operations there will be people in charge of certain things. So isolation in environments and segmentation of duties in companies, that’s actually brought us to where we are today.” – Argenis Fernandez

Listen to learn…

  • Why Argenis thinks that drive letters are outdated… and what you should use instead
  • How ITIL ruins storage in most companies
  • The “Argenis definition” of a LUN, IOPS, and more…
  • The information Argenis thinks all DBAs should know
  • The difference between LUNs, mount points, and data stores
  • How to partition your data files and logs in physical storage vs. in virtualized environments
  • How IOPS fits into the SQL Server storage environment
  • What you should worry about instead of IOPs
  • The one thing he’d change about SQL Server

Join the conversation by leaving a comment below about your experiences with storage. And if this podcast is helpful to you, please leave a review on iTunes so others can find it.

ArgenisAbout Argenis Fernandez
Argenis is a Solutions Architect with PureStorage, a Microsoft SQL Server MVP, VMWare vExpert, and a well-known face to the #SQLFamily community, especially since he’s now the Director-at-Large of SQL PASS. He’s a regular speaker at PASS events, including SQL Server Summit. He also founded the PASS Security Virtual Chapter.

Resources
Follow Argenis on Twitter
Argenis’ blog
LUN Storage and its role in SAN Management
Storage performance: IOPS, latency and throughput
PASS Security Virtual Chapter
What is SQL Server IO Block Size?
Mount Points in SQL Server
What is the CXPACKET Wait Type, and How Do You Reduce It?
Episode 50: SQL Server Settings

Transcript

Carlos: So Argenis, welcome to the program!

Argenis: Thank you sir, thank you for having me.

Carlos: Yes, thanks for coming on. Ultimately today, we’re talking about storage. So I think it’s something that’s not near and dear to an administrator’s heart, it will be when they start digging into what’s going with their SQL Server environment.

Argenis: Yes, so it’s actually something that I wonder, actually wondered for a long time, why isn’t storage part of a DBA’s curricula, right? We’ve been avoiding the topic of storage for a long time. And you know what? We actually have ITIL to blame. Do you know it’s ITIL that actually started splitting responsibilities between operations and engineering? And then even within operations there will be people in charge of certain things, blah blah blah. So isolation in environments and segmentation of duties in companies, that’s actually brought us to where we are today. So it’s kind of a hard problem right now, because you have the storage silos where people only know storage and they don’t know much about databases and all they see has worked out coming in. And on the other hand you have DBAs who are sending that work out to storage and they only care about the rate group or the rate level that they’re using on that storage. And isn’t that a complete disconnect? This is where the problems arise, right?

Carlos: I think that each of those, depending on your environment, particularly, you can have different needs on what your storage needs to do for you.

Argenis: Oh yeah, so, I mean the good DBAs will be asking not just a one terabyte LUN rate ten, they will also say things like, “I want this LUN to be carved more for IOPS and this one to be carved more for bandwidth. And so that low leniency is a thing and high throughput is a thing, and they might not be the same thing on every single volume, depending on how you configure your storage device anyway. But the world is changing really fast and I will tell you, there are a lot of things going on that are going to make a lot of those concepts go away, basically.

Carlos: So before we get into that, I think it would be helpful to, just to cover some basics and review some of these terminologies. You threw out the word LUN. And IOPS. What is it your definition? What is the Argenis definition of a LUN?

Argenis: So a LUN is a logical unit number. It’s basically a concept from SCSI, the SCSI particle, right? For accessing storage devices. And so basically what it means is a target. You’re defining a storage target in your SCSI chain. You can have multiple devices on a single SCSI chain and the LUN will basically be the ID for any given target.

Carlos: Oh, okay.

Argenis: The way we see these on the database side is just drives. Actual, physical disks on Windows.

Carlos: Right, with a letter.

Argenis: So that’s actually something that I’m actively trying to change with people, in terms of the letters. They should think of each and every one in terms of mount points, right? You should really have a minimum amount of letters on your server. I mean come on, it’s 2016. Why would you be using a drive letter for everything? So what I tell folks is, you should have your C drive. Although I still don’t understand how come 2016 is still using the C drive. And the other drive letter that you have should basically be an anchor. A very small volume that acts as a mount point for everything else. So you really do not need to have any other drive letters in your system. That actually does away with a lot of, you know, corporate standards and things like that. But it’s for the better, right? I mean really, how many drive letters can you actually fit into a system? 23? It’s not 26, because C is reserved. B is reserved. A is reserved. You can only use 23 of them. Um, so if you have more than 23 LUNs, what do you do? I get people thinking about those things and you know, basically tell them, “Catch up with the times! Get with the program. It’s 2016, you shouldn’t be using drive letters. “

Carlos: Sure.

Steve: Okay, so with that then, one of the things with SQL Server that you like to do is put everything on one drive letter, typically in the past, and you have your logs on another drive letter. And your tempDB and your backups off to different places as well. How does that all apply then when we’re talking with LUNs and not using so many drive letters?

Argenis: Well each LUN could be a different mount point and then you would be placing your logs into one mount point and then your tempDB into another and then your data files into another, and so forth and so on. But there’s actually a news called a FOD where you know, with the advent of Azure A’s and things like that, the amount of volumes shouldn’t be in the twenties. It should be in the ten’s. And it also depends on the throughput of your entire system. But I’ll give you an example. At my previous job we had a single 25 terabyte LUN that contained all database files and transactional log sitting on the same LUN. I had a separate LUN for backups and a separate LUN for tempDB, but that’s because we were running on enough Azure A.  And we were actually not seeing any issues from running that way, however there’s another school of thought that says, “Yeah, even on a physical service you should split your logs and data files because you may run into queuing issues.” I personally have not run into those. There are some that say that under very high load systems you might run into those issues, which comes down to basically a maximum of 256 queued IOs per LUN. And that might actually affect you under very high performance environments. I have personally not run into those.

Something I tell my customers in particular is to go ahead and create less LUNs when it comes to physical servers, but when you’re talking about virtualized environments this is a completely different conversation and you should definitely split your data files, your transaction logs, your tempDB and your backups into multiple virtually managed devices. And those virtually managed devices don’t have to be all virtualized storage, VMDKs in the case of VMWare or VHDX’s in the case of Hyper-V. They can be RDM or pass-through disks. But you should have more of those in a virtualized environment. And so that’s kind of interesting, right?

On a physical world we’re targeting less volume, less LUNS, but in the virtual world we want more.

Carlos: So those mount points. We sort of talked about mount points as being location for our disks, right? So does that equate to the same on the virtual side? So I have these virtual disks, right? The terminology might be different but it’s still ultimately a mount point. True or false?

Argenis:  So it’s not necessarily a mount point. The way we call it on VMWare are data stores. A data store will be a volume or a LUN on your SAN. On top of that data store we would go ahead and create multiple VMDKs. And so these VMDKs will be your actual virtual disks. And so to catch up on that performance, what you would have on any given virtualized environment, you would have just a ton of data stores spread across multiple aggregates or whatever on the SAN. Create groups or what have you to give you performance on those. And on top of that you would go ahead and create your VMDKs that would actually end up containing your file systems for your database files, your tempDB, your transaction log, et cetera et cetera.

Carlos: Sure. So that is the idea, of striping, in the virtual environment, right?

So striping doesn’t necessarily mean create more data stores. Yeah you definitely can create more data stores. You cannot have a VMDK split across multiple data stores. You could have a volume split across multiple VMDKs but that’s different. And so you can have multiple files for a VMDK sitting on different data stores, but there will be all sorts of issues from that. The thing you don’t want to do in the end is rely on Windows striping for managing performance in those logs. Because in the end, basically letting the operating system do these things for you. And that’s basically a function that should be relegated to hardware as much as possible.

Carlos: It’s going to be much faster there than in the software.

Argenis: Definitely. The other concept for data store in the case of Hyper-V is a CSV, or clustered share volume. It would be the logical unit number would be basically that in the Hyper-V world.

Steve: Alright, so when we talk about performance and how much throughput we can get from storage, one of the things that generally comes up is the term “IOPs”. And I know that’s one of those where there’s lots of different ways of measuring it. Can you maybe talk a little bit about how that applies to storage in the SQL Server environment?

Argenis: Yeah, absolutely. So IOPs stands for “IOs per second”. So input and output operations per second. And so imagine when you’re doing something as simple as writing a single regulatory file, you have to write to the transaction log for that and then obviously there’s an asynchronous process that takes that record that you just wrote to the transaction log and writes it to the data file, right? That process of writing to the transaction log is actually an IO. Because there’s no caching of IOs performed against the transaction log, and this is very important on a SQL Server compliance system. You cannot have any caching of IO performance on a transaction log. Write IOs, that is. Read IOs against the transaction log are a different story.

Carlos: Sure.

Argenis: But when you’re writing to the transaction log, that IO has to be performed with write through all the way. No caching, no software caching, no hardware caching. It actually is to hit stable media. So…

Carlos: That’s how it’s going to do the roll-back, right? That’s why it’s so important, because that’s your transaction information.

Argenis: That’s right. It maintains the acidity of your database, right? It maintains it transactionally consistent. So anyway. IOPs are basically you can measure as, “how many IOs are you performing in a given period of time?” So calculate those per second, et cetera. There is a school of thought that cared a lot about IOPs. I want to say that that school of thought is losing relevance nowadays. The reason for that is what you care about, really, when you perform an IO is latency. It’s very performant, IO. I want that IO to be as fast as possible. And that is for my regular description of a general IO. I just want that IO to go as fast as possible. Which means that entire round trip for that IO to complete should be small. Which means the latency for that, which basically introduces into the latency for that IO should be small.

However, there are situations where we don’t’ care about IOPs. Like, how fast am I performing that IO right now? You care about throughput. For example, when you’re performing an index scan or a partition scan, you’re bringing a whole lot of pages into that buffer pool. What do you care about? How many IOPs you’re pushing into your storage device? No. You care about how gigabytes per second you’re pushing, and hopefully in your SAN it’s measured in gigabytes per second because if it’s not, I think it’s time for you to start looking for a new SAN.

Carlos: I think the point there is being, so the disk while important, it’s not the finish line. The finish line is that the buffer pool has to be filled.

Argenis: I mean, of course, and in the case of a backup for example. We’re not even talking about buffer pool concerns, because 8 pages are handled for a backup page are disfavored. But they are actually kept into buffers and those buffers get allocated and de-allocated and blah blah blah. They’re not about filling out the buffer pool, they’re about maintaining a level of throughput in the entire system when you’re reading from a bunch of database files and you’re writing to a bunch of database backup files. Like, that’s what you care about in the end for that particular operation. But when you’re doing an index scan or a partition scan, then you care about bringing those pages into memory as quick as possible. Or if you’re performing a data load, right? If you’re performing a bulk insert or things like that, you care about throughput where you’re writing a whole lot of data directly straight into the data files. Obviously, there’s some markers that get placed into the transaction logs, et cetera. But you care about throughput in those cases, you really don’t care about IOPs. However, when you are in a very transactional system and a very transactional environment, for example, a banking institution, a financial insurance, things like that. Typically you’re going to find a lot more transactionality in your workload than you would, you know, otherwise. It’s not going to be a more reporting workload and in that case you probably care about IOPs. How many IOPs do you perform in a given period of time? If I have, I don’t know, a thousand clients hitting my database at any point in time concurrently, then my IO subsystem better be able to handle those 1000 customers and then some. So that’s also a measure of performance in a system, but it’s not the only measure and it’s not the only thing you should care about. As a matter of fact, I tend to tell folks, “You probably want to care about a system that can give you low latency and high bandwidth at any point in time.” And so, the amount of IOPs that it can perform might actually be reduced as a function of having less latency and a lot more bandwidth. So does IOPs matter? It’s really more of a religious question nowadays actually.

Carlos: So what advice do you have? You mentioned the difference, or the silos if you will, we have DBAs over here and the SAN folks over there. IOPs has been one of the things that we’ve been asked for or we’re trying to find and give to them. Like, “Hey guys, I’m expecting this many, I’m not seeing that.” How do you see that conversation evolving then?

Argenis: I don’t think that IOPs are a good measure of even remotely anything nowadays quite honestly because, I will give you this example. If you have a system and it’s got a medium latency of 10 milliseconds. Let’s just pick a number. And you have a given amount of IOPs that you can perform on a target device. The moment that you switch to a device that can give you under one millisecond latency, I guarantee you that you’re not going to need that same amount of IOPs.

Episode 52: Revolution Analytics in SQL Server

Welcome back to the SQL Trail! In Episode 52 I, chat with Ginger Grant, also known as “Desert Isle SQL” in the #SQLFamily circle. We talk about using Revolution Analytics or R with SQL Server: its origins, how to get started, and what to look out for as your organization starts looking at implementing R. If you’ve ever wanted to know about how using R in PowerBI or SQL Server will affect you, this episode is for you.

Episode 52 Quote:

“The process that they do is called “chunking”. What they do is they break the processing into chunks and then combine it together. And that’s different from what open source R will do. Open source R will crash. And there are several commands of course you use to make it happen that are not a part of open source R.” – Ginger Grant

Listen to learn…

  • How one language from 1996 is transforming data science today
  • How R got started and why it’s built the way it is
  • What C has to do with R
  • How Revolution Analytics transformed R into a useful data science tool
  • How the open source version differs from the Revolution Analytics version in SQL Server
  • How to use R alongside SQL Server

I want to know – what have your experiences with R been? Have you tried it yet?  And if this podcast is helpful to you, please leave a review on iTunes so others can find it as well. Subscribe to the podcast via iTunesStitcher, and Podbean.

This episode is sponsored by

Show Notes

GingerGrant

About Ginger Grant
Ginger is a Pragmatic Works Senior Consultant, a Microsoft SQL Server MVP, and a regular speaker at SQL Saturday events on data management, PowerBI, and using R. She’s also a trainer for Pragmatic Works and writes about data topics on her blog.

 

Resources
SQL Saturday: Administration, Security and Data Management in Power BI”
SQL Saturday: Combining R with SQL Server 2016
T-SQL Tuesday #79 – Creating R Code to run on SQL Server 2016
Ginger’s Blog
Pragmatic Works
(MSDN) SQL Server R Services
Revolution Analytics

Transcript: Revolution Analytics

Carlos: So Ginger, welcome to the program!

Ginger: Thank you very much! I really appreciate you asking me to be on, Carlos.

Carlos: Yes, thanks for being here from Arizona. I think you’re probably our first guest from Arizona. If I have to remember correctly

Ginger: Probably so.

Carlos: And here we are, we are in June recording this. Have you melted? So are things melting in Arizona yet?

Ginger: No, it’s only gotten up to 119 degrees, not 120.

Carlos: [laughing] Only 119, right? So interesting that you mention the heat, because it seems like one of the “hot things” in the SQL Server space and in the analytics space now is R. That’s why we wanted to have you on and wanted to talk a little bit about R. Maybe help us define what it is. We know it’s coming into SQL Server 2016. Talk about how we might be prepared for it, and have some conversations around where it might be going.

Ginger: Sure, well R is not new. It was actually named when one letter languages were really trendy. So like C. So first they came up with C, and they came up with S, and S was proprietary. And when it became not proprietary it started being called R because it’s named after the two guys who made it open source. And that was in 1996. And so it’s pretty much been exploding since then. And one of the things we’re interested in, of course, because it’s part of SQL Server now.  Last year a company called Revolution Analytics, and they made R, and they worked to incorporate a lot of R into not only into SQL Server but PowerBI as well.

Carlos: Oh interesting. So it’s starting to get into some of the other products as well.

Ginger: Yes.

Carlos: So talk about Revolution Analytics and now that seems different from the open source.

Ginger: It is.

Carlos: What are some of those differences and is there a difference even further between what’s in Revolution Analytics and what’s going to be baked into SQL Server?

Ginger: Yeah, well, good point. So the difference is that R as an open source language was created to be very fast and to run in memory. And in 1996 they weren’t talking, and nobody was thinking about, “I don’t know, let’s do analysis on a terabyte’s worth of data!” But that’s not necessarily an uncommon thing that people want to do now.

Carlos: Sure.

Ginger: Also, the guys who created it were statisticians. And they wrote a lot of it in FORTRAN, which isn’t terribly quick.

Carlos: Right. I’ve heard that it’s not like a regular programming language. It’s these math guys that went and got together, and approached it from that perspective.

Ginger: Right. It’s really procedural, as in you run it line by line. And there’s a bunch of libraries for it. What Revolution Analytics did was like, “Yeah that’s kind of cute, but we want to run large files up against it.” So to do that they did what pretty much everybody else does in the programming world is that they figured out how to swap things in and out of memory.

Carlos: Ah.

Ginger: That’s what revolution analytics brought to it. So you can possibly run out of memory, but you’ll have to work a lot harder at it to make that happen.

Carlos: So they’re doing some of the memory management, kind of like putting in a maximum threshold. Like, “Hey, my server’s got 16 GBs of memory and I’m approaching 10 GBs, I need to start swapping.”

Ginger: Yeah, the process that they do is called “chunking”. What they do is they break the processing into chunks and then combine it together. And that’s different from what open source R will do. Open source R will crash. And there are several commands of course you use to make it happen that are not a part of open source R. It’s in all the revolution commands, it starts with “Rx”, which is kind of cute. It’s like a prescription for revo analytics.

Carlos: [makes drum crash sound]

Ginger: I know! Somebody told me that who used to work for Revolution Analytics and I thought that was kind of clever.

Carlos: Very nice. So now we’re kind of baking this into SQL Server, and of course SQL Server has this in-memory OLTP objects where you can store table and other things in there. Is that going to be a part of the in-memory process or are they still in two different camps?

Ginger: Well, it is an in-memory process, I don’t remember where but in extended events you can see where it starts using memory. It does use the same, I mean if you’re running it on the server, it uses the same memory space as SQL Server does. So DBAs are going to have to start looking at it in the future and looking at overall memory usage in SQL Server and it’s a part of the environment and it can starve out your other processes.

Carlos: So let’s take a look at some of those use cases really quick. So it was written by statisticians and we looked at some of the analytics of it. Why would people be bringing in R when we have a data warehouse and ETL? And even on the Azure side, there’s stream analytics and things like that. Why would we be looking at R?

Ginger: Well one of the reasons for it is that R was originally written for statisticians. So they wrote some code and said, “Great! I’ve determined that the probability of this happening is X.” That’s big in insurance. The probability that a hurricane is coming through is X, and their losses are kind of this. And that’s great, but let’s run that against a different data set. And they’re like, “Oh, well, I guess so.” Because traditionally, R is run against a CSV. And somebody creates that CSV and then they write a little program to go against that CSV. So there’s a lot of extracting going on to make this happen. Well, if you have data scientists and you have people that are looking to make this code more production environment, putting it into SQL Server is a great thing. Also, you’re not tied to that CSV data set that you have around there.

Carlos: So does that mean that I’m going to have a table that is defined? Do I have to have a different kind of table, or can I take any of the tables that I’m used to and now I have this R program and analyze that?

Ginger: Think about it like a stored procedure. You have a defined set of inputs and a defined set of outputs. And instead of running a stored proc, you’d run R. And not only can you get a dataset that would give you a range of probability, but you can also get graphics out of SQL Server. It’ll generate a graphic file for you. Because R is really big on data visualization which is why it’s being incorporated into PowerBI.

Carlos: There you go. Well that makes sense. So if I’m using this stored procedure, from a security perspective then, does that mean that I’m going to be granting access to people at the R level, if you will? Are they going to have access right to the tables to be able to read them and what-not?

Ginger: They’re going to need data access required to read what they need to read. So you can actually just paste your R code there so it’s part of your dev set for an installation.

Carlos: So when you say “pasting your R code”, am I in SQL Server Management Studio, and I have a window, and I paste it in there?

Ginger: Absolutely.

Carlos: Very nice! We can get rid of SQL Server Management Studio.

Ginger: Oh no, because it’s one of the things is that R doesn’t have any management tools. There’s no like, “Oh, how long did this run?” And it’s just, “Ah, when did you start it?” There’s just a timer at the bottom. But if you’re talking SQL Server, you’ve got all the features and functionalities that you have with Management Studio. How long has this proc been running? How much data is this consuming? All of that you have because it’s in SL Server.

Carlos: Okay, so now as people start using it, you mentioned it’s in memory. Let’s look at these other scenarios. When I hear about R from folks, I hear it from analytic people that think R is going to help them answer questions they can’t get answers to. The ETL is not working as fast as they want to, things like that. In thinking about memory, and I’m thinking about an administrator, am I going to have to be cognizant and say, “Look guys, these tables now work with R, and these tables won’t.” Or what are the practicalities of implementing this and being able to support it?

Ginger: I wouldn’t look at is a table-specific thing. You’re going to need some data. Is it going to come from one table or a couple of tables? Are you going to do some joins to get that data? Wherever the data is that’s needed for your inputs, you’re going to need to be able to provide that as in input into R. Whether that’s a single one set of variables or it could be all of the data from three tables and you only want these five fields? So just like you would a stored procedure. Now the one thing I always bring up is that Revolution Analytics is unique in having these in-memory tools and if you have R code that is lying around somewhere, odds are it wasn’t written in Revolution Analytics and was not written to swap in and out of memory. So that’s another thing too. Now those functions are optional. They’re a part of the Microsoft version of SQL Server, but nobody’s making you use them. And if you don’t use them, then you will run out of memory. So you need to be able to look at the code that’s being offered to you and ask them, “Did you write this with Revolution Analytics functions to chunk this in and out of memory or is this going to blow up?” Because nobody’s making you write it well.

Carlos: Interesting. So let’s talk a little bit about that learning perspective and how do people go about learning R? I’m seeing more presentations about it. I’m seeing people talk about it. But I understand that it’s kind of a closed community?

Ginger: Not only is it a closed community, but let’s just say that they’re not terribly nice. Let’s just say that forum postings are, um, entertaining if you have no heart.

Carlos: Post at your own risk.

Ginger: Oh yeah, because if someone thinks that it’s stupid and you should know it already, that’s what the kind of comment you’ll get back. Another thing too is like, for example, is they’ll have their big convention. They have it one year in the US and one year in Europe, and this year it’s in the US. And they’re sold out. And, you know, that’s it. And so that’s how they are. Now one person that I’ll give a lot of credit to for trying to work on changing this is Steph Lock. Steph Lock is single-handedly trying to open up the R community and get it to be more like the SQL community, because that’s where she’s from. And she’s developing “SQL R Days”, kind of like SQL Saturdays. And she’s just kicking it off now. I don’t remember which location won worldwide, but she’s trying to kick that off in a way to kind of get it out of the very academic, very closed-shop mentality that it currently lives in.

Carlos: Interesting. So to circle it back for a second, you were talking about having to use Revolution Analytics to do that swapping. So does that mean that my stored procedure should start with the rx? And if I don’t see any rx, then I know that?

Ginger: If you are looking, as a DBA if you are looking at the code and you don’t see anything that starts with rx, they’re not using it. So as a quick DBA check on the code, if you don’t see rx in front of the function call anywhere, they’re not using it.

Carlos: So now to talk about that community. Microsoft has purchased the Revolution Analytics. Is that getting any better? I mean, is Microsoft providing better support there? I know 2016 just came out.

Ginger: Well, I will say that, you know, you can find support. The problem that I have found is that Revolution Analytics is a relatively small company. And so there’s just not that many people who know the product really well. I don’t know if you know this, but Buck Woody works with a lot of people who used to work with Revolution Analytics. He’s part of the team trying to get more people trained on Revolution Analytics, R, and also on trying to get a better integration of them with Microsoft. But there’s just not that many people.

Carlos: Sure. And again, from a different perspective, these are statisticians. They’re not developers. So I think it is kind of a different audience.

Ginger: It is, and one of the things about learning R is that the first introductory class on Coursera from Johns Hopkins.  And I mentioned this at SQL Saturday Atlanta, I recommend that nobody take the class.

Carlos: Yikes.

Ginger: And the reason why is that one of the big things I think is the strength of R is the visualizations. And this class doesn’t have you do any. EDx has a number of much better classes if people are interested in teaching themselves R. But to know R, you’re like, “Great! Now what do I do?” Well, then you learn stats. Because what people want to know with R is they want to know variances, they want to know groupings, and basically you end up learning a lot of stats if you’re going to be writing R because that’s what you use it for.

Carlos: So now is that a fair point against? I’m a DBA, and I have an analyst come up to me and say, “I think we should start adopting R.” Is that a fair question to be like, “Ok, who’s the statistician? Who’s the data scientist? Who’s going to be putting all of this together?” Because don’t come coming to me for me to write your R stuff!

Ginger: Yeah, that’s a fair question, because the other question, it reminds me of a Dilbert cartoon, you know. “I want a relational database. Well MOM has the most ram.” Why do you want to do R? Because it’s trendy, or what are you trying to do? Do you have a problem that R would be a good fit for? There’s that old saying, where if you have a hammer everything looks like a nail. You have SQL Server is not going away, SQL is really good at providing information that is aggregated in some form if you want to get into some kind of really interesting or nitty-gritty variance analysis or regression analysis or that kind of thing, it’s really good for that. Microsoft is positioning R as their machine learning on-premise solution. So, I do Azure ML as well and I know quite a few people who are like, “I ain’t doing that in the cloud. No. I don’t care it’s in cloud, I’m not going there.” The idea being that anything that you can do in machine learning you can do in R. So think of the kind of analysis.

Carlos: If you’re looking for trends, that’s what I think about with machine learning. Here’s my data, and then point to me where I see similarities and things.

Ginger: Yeah, the simple one being regression analysis. I mean here in Arizona, it gets hot here. As it gets hotter, my air conditioner bill goes up. Those are two points of data that are always in a line. You know, when the temperature drops my bill goes down. That’s one kind of very common thing that’s being done with machine learning that can also be done in R. Another one that’s really common that people don’t necessarily think about is anomaly processing. For example, your credit card. You ever gotten a credit card call from your bank saying, “Hey, did you make this charge?” This doesn’t seem like you. That’s machine learning.

Carlos: Gotcha. And for that I’m grateful, because I’ve had a couple of instances where a purchase had happened out of state and it was definitely not me.

Ginger: Yeah, I used to work near the fraud department of a very large bank, and for a while they told me that you have an address change to Hollywood, Florida, they would call you. Because they had so much fraud in that area that the machine learning picked up on. “If I see one of those, it’s probably fraud.” So it’s pattern recognition.

Carlos: Very good, very good. So I guess last thoughts on R or C, Steve, if you want to jump in here?

Steve:  Yep. You know, I had a couple of questions from the DBA perspective on R. So, if someone starts using R on the SQL Server, you mentioned that can take up more memory or even starve SQL Server’s memory. You also mentioned that PowerBI as an alternative way to run R. So if somebody’s running R in PowerBI, is that then taking up the memory beyond their local client machine? Or is that passing it on along to SQL Server?

Ginger: Well, if you’re talking about using PowerBI you’re talking about a cloud deployment, so that kind of pushes it out there. I don’t, to be honest with you, you should be able to do it with an existing gateway. I’ve not looked at memory problems with PowerBI and accessing data, but if you had an on-prem gateway I assume that it would tax your memory to get it. But all it would do though because it’s running in the cloudspace. What it would do from a PowerBI perspective is it would get the data and then your processing would be run in the cloud so you wouldn’t be hit from a local on-premises on SQL Server just to get the data.

Steve: Okay, great. And then one of the other things around the load and the usage there with SQL Server Enterprise there’s the resource governor features which you can control the amount of memory and CPU and things like that allocated. So do you know anything about how resource governor on SQL Server would affect the memory or CPU utilization associated with R?

Ginger: I don’t know the specifics but I really think that R code would be a good place to put a resource governor on it.

Steve: Yep.

Ginger: Because it’s relatively new so it’s too early to know how much memory that they’re going to be taking. So I see it’s a good use case for it, but as far as the specifics I couldn’t address that.

Carlos: I don’t recall, Steve, off the top of my head. Did they change the max memory setting in 2016? So I think this would also be in your area where that setting would become that much more important.

Steve: Yes, and that’s one I haven’t looked at specifically with 2016, but certainly if someone is using R you’d want to have a better understanding of how that memory is being used by different users.

Ginger: And another way of using R, one of the other things that’s a part of Revolution Analytics with SQL Server 2016 is they include an R server. And you would want to use this R server if you’re going to be processing, say, an HTFS cluster. Or if you wanted to make an OLDB connection to say, Oracle. Then it would run in your R server. So not only does it, and the reason you would want to do that again, is that you would want to take advantage of the ability to swap things in and out of memory. That’s what you would need your R server for. So not only with SQL Server 2016 do you get it incorporated as part of SQL Server, but you also have the ability to install an R server installation for connections to other data sources other than SQL Server.

Steve: Interesting. Okay.

Carlos: Very good. Ginger, let’s do SQL Family.

Ginger: Sure!

[SQL Family transition]

Steve: So one of the things we always ask on the show that is a favorite when it comes up. What is your favorite SQL tool? And this can really be anything that helps you with SQL Server or anything you’re doing with SQL Server.

Ginger: I really like SQL Prompt. It’s a great tool.

Carlos: That’s probably the most popular response to that answer.

Ginger: Yeah, I also like [] tool as well. That’s a good one.

Carlos: You’re talking about the SSMS boost?

Ginger: Yeah.

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

Ginger: And this is on SSIS and this is also a major beef right now with Microsoft. I respect the fact that they want to make things useful for people who are colorblind, but did they have to take the color out of everything? There’s no joy in mudville. SSIS is now totally monochromatic. It’s so much prettier in 2012. Why does it have to look so ugly?

Carlos: Interesting.

Ginger: And oh by the way, they use that same monochromatic “we don’t want any color here” to AzureML. Which makes it demo very badly.

Carlos: Ah, there you go. And there’s no themes you could apply there?

Ginger: You can get light blue, light, dark what have you, but… ehh.

Carlos: Bring back our colors!

Ginger: Exactly.

Steve: Okay, so over your career, what’s the best piece of career advice that you’ve received?

Ginger: So actually this came from Steve Jones. And he was, I was at SQL Saturday in San Diego. I drove out there from Arizona with a friend of mine, we made a weekend of it. And he said that, “you know what, there’s gonna be stuff out there on the internet about you. You can either choose to contribute to it and make what they find your stuff, or you can live with what other people are willing to put out there about you.” And I thought that was pretty profound. And I put up my blog site a little bit after that.

Carlos: There you go. Kind of write your own story.

Ginger: Yep.

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

Ginger: Oh I want to fly. I have always wanted to fly. That would be so cool.

Carlos: I agree. Very cool. Well Ginger, thanks so much for being on the show today.

Ginger: Thanks for having me.

Carlos: Yes. We’ll have the show notes up on SQLDAtaPartners.com/R.

Ginger: Arr.

Carlos:   I was going to make some pirate joke.

Ginger: Yeah, I know that Jamie Johnson always makes pirate jokes when he does R. So hey Jamie- arrrrr!

Carlos: Thanks again for being here.

Ginger: Thank you. I really appreciate you having me.

Carlos: Okay everybody, well thanks again for joining us today. Kind of an interesting conversation and some things to consider as organizations begin to look to R to bring that in-house. Big takeaways for you Steve?

Steve: Well, it’s one of those things where I really have to jump in and give it a try. I’d really like to maybe get an online demo or something and try it out at some point. It’s a ways off from what I’m used to.

Carlos: See, I had the opposite reaction. I’m was thinking, “Where’s my ten-foot pole?” Right? I want to keep that as far away as possible. And one of the things that I do think, to consider, is that you need to make sure that it’s the right version in the database. So  using those Revolution Analytics, those stored procedures that start with “rx”. And then along with making sure that your memory settings are right, that you’re not going to let that take over the database. Which it very easily could with a larger data subset.

Steve: Yep, absolutely and I think that leads us into our Tuning Minute for this session.

Carlos: Nice!

Steve: So SQL Server memory is one of those things that people talk about setting to the max server memory setting. Or making sure that SQL Server’s not going to take up too much memory and squeeze the operating system out. Quite often, it can be at the other end of the spectrum where there may be other things running on your database and SQL Server isn’t given enough memory, and because of that it’s just assumed to be slow, but it might be slow because it doesn’t have enough memory. This is something I’ve come across in performance tuning work quite a bit. And you really need to get in and get an understanding of the database sizing with the overall memory footprint for your database. So take the example of a SQL Server with 8 GBs of RAM on the server, but because of other things running on there SQL Server only gets 1 gigabyte of memory. Due to other applications, it may be Exchange or other server applications that are taking up memory there.

Carlos: Even applications that are kind of in the SQL Server wheelhouse, I see commonly. Like Reporting Services or even Analysis Services is the other big culprit.

Steve: Yep, and that’s a great reason you can run those applications on a different server you’re your database. But they often get lumped together. Imagine that you’ve got a 300 gigabyte table that you’re trying to query that requires a full table scan. How’s that going to perform if your database only has 1 gig of memory to work with?

Carlos: Yeah.

Steve: So then, take it a little bit further than that. On that SQL Server you’ve got ten databases running perhaps, splitting up that 1 gigabyte of memory. And if you’re lucky and it’s evenly split, that’s only getting about 100 MB of ram per database. And that really isn’t much in the SQL Server world. Now throw a few dozen users at that database. And I say a few dozen because it probably won’t handle more than that with that limited amount of memory. All of those users wanting to query larger tables without any memory being allocated basically, because there’s none there. Things can get really slow. So this is an example where you might be able to tune queries all day long, and have no impact on the performance of those. And the only thing you can really do in performance is add more memory or move other applications off to other servers.

Carlos: And then there is an interesting idea here. So I’m thinking some of our development servers might suffer a little bit from this. One time in an engagement, there was this test box. I think the box had been given 4 gigs of memory total. Like you said, SQL Server only had 1 gig because it was running something else, and they were having some issues there. So I said, you know what guys? You actually have some sizeable data here. Why don’t we increase the memory of the server? And they’re like, “I don’t think that’s going to do it.” Because again, it was one of those things where they had created the development server like a year before and kind of forgot about it. Data grows. And I was like, “Let’s try it. We’ll double the memory and if it makes no impact then we can switch it back.” And we did that and recycled the service, and they went back into the application and they’re like, “Whoa, that’s wicked fast.”

Steve: Yeah, and looking at that, comparing the price of that memory to the cost of the developer’s time for one or two weeks to be fighting with it, or three weeks to be fighting with it trying to make those queries run faster and not having them run fast. Memory is the cheap way to go there and oftentimes one of the best solutions.

Carlos: Well awesome. Well thanks again, companeros, for tuning in. Of course, you can reach out to us on Twitter. I am @carloslchacon.

Steve: And I’m on Twitter @SQLEMT.

Carlos: If you have questions or things you want to talk to us about with R as well, you can hit that up on the website. The show notes for today’s episode will be at sqldatapartners.com/r.