Episode 37: Power BI

1400In this episode of the SQL Data Partners podcast I sit with Patrick LeBlanc during SQL Saturday RVA. We chat about Microsoft PowerBI. PowerBI is a business intelligence tool that easily connects disparate data sources to create dashboards. Patrick answers my questions about the application and also explains why he thinks that PowerBI has grown in popularity so quickly.

He reveals what he knows about PowerBI and the Microsoft data stack, such as:

  • the latest free features of PowerBI
  • how organizations are using the free version to get results
  • the security features of PowerBI pro you don’t want to ignore
  • what to do BEFORE you download PowerBI Desktop
  • when PowerBI isn’t the best solution for your organization

Finally, Patrick unveils the underrated Microsoft tool he swears by and the superpower that drives his kids crazy.

Show Notes

About Patrick
Patrick LeBlanc is a Data Platforms Solution Architect and Technology Solutions Professional at Microsoft. He speaks and trains regularly on data virtualization topics at SQL Saturday events and on Microsoft Virtual Academy. He’s also contributed to several books on business intelligence topics: Applied Microsoft Business Intelligence and Microsoft SQL Server 2012 Bible. He’s a father of two out of Alpharetta, Georgia.


Transcription: Power BI

Carlos: Patrick, thanks for being here! Welcome to Richmond.

Patrick: I’m happy. It’s beautiful weather today.

Carlos: Yes! We’re glad to have you. Thanks for coming up. So we want to be talking a little bit about Power BI. It’s very new, it came out fourth quarter of 2015, right? So it’s brand spankin’ new. Take us through just the high level. When we talk about Power BI, what are we talking about?

Patrick: So you know what, Power BI is Microsoft’s end-all-be-all complete business intelligence solution. It’s not just the tools, not just the product, it’s a complete solution.

Carlos: So not just the reporting like dashboard, pre-dashboard requirement?

Patrick: Not at all. You can do everything, right? From soup to nuts.

Carlos: So everything? So I have an Excel document, right, and I have a database. Then I want to start giving my users reports on that data.

Patrick: So think about traditional data warehousing, right? So if I’m a traditional person, if I’m doing a traditional data warehouse, the steps are that I need to transform my data, I need to extract my data, I need to massage it, and I load it somewhere, right? They require dedicated IT resources, right? You need some guys that are really sharp out there to come in. ETL folks. But with Power BI, I can have these disparate data sources, I can use the desktop, just a single desktop application to bring that data in and massage it. I can become intimate with the data, right? I can create these relationships across these disparate data sources and then I can start reporting and creating dashboards directly on top of it without IT guys. I don’t need those guys anymore.

Carlos: Interesting. So, I was in Portland last year and heard from James Phillip. It was the VP of Business Intelligence offerings at Microsoft and he said a couple of interesting things. He said, one, that the move to the cloud is inevitable, and of course PowerBI is kind of based in the cloud, right? They have that service. Although there’s a desktop version which we can talk about. And he also suggested that, as you mentioned, that PowerBI would be the glue of sorts that would help these different reporting needs. His example was that people are already using the cloud for different things. We talked a little bit about it. Like Google analytics, it was a perfect example. You’re not using Google analytics. It’s a cloud solution, lots of people are using that, but then you wanna take Google analytics and compare that with Twitter data, right? So you have two cloud applications, disparate data sources, right? Are you saying is that how people are primarily using PowerBI?

Patrick: So I’m working the education space and so the home page of an institution is critical, right? Especially when you think about a college. People are clicking about on that web page. And so if I just enable Google analytics, I can go to the PowerBI and go to PowerBI.com, put in my credentials for Google Analytics, and guess what you get? A dataset, reports, and dashboards automatically. You don’t have to anything. You don’t have to know anything about reporting, dashboarding. It just dumps it out there for you. That’s one means. But if you want to mingle those two data sources, use the desktop. And you can actually connect to it, it gives you a dataset. You can pull in JSON files from Twitter, because that’s what Twitter exposes, right? Connect them up and start correlating that data. Even better, I can take some of my own data. And so imagine I got people clicking around on that site. Maybe I want to see if admissions correlates to how many people actually are clicking and submitting applications. I can almost do that in real time using PowerBI. So I’m definitely seeing, you know, adoption, the glue. But it’s much much more than that. Much more than that.

Carlos: Okay, so now that kinda almost sounds like, “Hey, IT folks. You’re gonna start having shadow IT!” More of that, right? The marketing folks are using Google analytics and whatever. So then how, I’m an IT person responsible, maybe I’m not a C-level but I’m a small team and I want to take care of my people but I also don’t want them, you know, just going off without me necessarily so what do I need to do to prepare my environment so I can say, “You know what, we’re going to support you with PowerBI.” What do we need to do?

Patrick: So the biggest thing, and I think we were talking about this earlier, you guys, the IT staff, have to be a little more agile. I don’t think they’re going to want you to know the answer, because you don’t set up Google analytics, you don’t know anything about Google analytics. You have to be a little more antsy and have a listening ear. You have to be a little more agile and go, “Let’s take a look and see what this is.” So in regards to those type of data sources, it’s up to you on how your IT staff is going to handle that. But if we’re talking about your data sources that you hang on to, this is where I think the disconnect happens. I can’t tell you, I was at one university. They separated the business people from the IT people, because the business people want access to the data because they have Power BI now, so I can connect and I can pull it in. And so the solution, and this is my Patrick solution is, that so if I’m the IT staff and I have a guy who says, “Hey, Patrick, I want to have access to all the enrollment data of the university.” I don’t want to give that guy access to it. I’m gonna go, “What are you trying to do?” Before you download PowerBI desktop, go, “What are you trying to do?” He says, “I’m trying to do this.” So I say, “I’m going to create a view for you, and I’m going to give you access to that view.” IT peole are going to have to be a little bit supportive of what these gusy are gonna do, because this is a new paradigm that we’re in. PowerBI is not the only desktop application that we have out there that allows the end users to connect.

Carlos: So that’s interesting. That idea as data professionals is that we generally will have the role of being the data stewards, right? So we mentioned so like the marketing team has the Google analytics, right? But they’re setting that up. I was in a meeting today, as a matter of fact, where I get with the marketing folks and they’re like, “Our Google analytics numbers are off. Will you tell me that? I can’t correlate that with my data internally.” And I’m like, “What do you want me to do about it,” right?

Patrick:  I can’t give you any pixie dust to sprinkle over that, but what I would tell you is that you gotta just listen and take a look, because they’re probably right in something in the semantic layer. So you can create this abstracted view in PowerBI. And maybe they messed up the calculation, because when you pull that data in from Google analytics to the desktop, you can mess it up a little bit, right? Maybe just take a look and go, “well, maybe look at your calculation and maybe this is not quite right.” I can’t tell you that you’re going to have the answers for those guys, because you don’t know that data. You’re not intimate with that data. So just a listening ear.

Carlos: So I’m a database guy, right? I feel that PowerBI is in the reporting space, while a lot of jobs now have this like, “We want you to be the DBA and the data architect for our warehouse.” They’re really two different camps. Even from a PASS perspective, right? They have PASS Summit for kind of the engine and they have the PASS Analytics conference. So who’s going to be taking care of that?

Patrick: If you’re in a big enough shop, you’ll have those separation of duties. If you have a big enough shop, there’s going to be a reporting team and there are going to be the DBAs. But unfortunately, most shops these days there’s a DBA. The DBA is the data warehouse guy and the reporting guy or girl and so you have to assume multiple hats. It’s another hat that you’re going to have to wear. Unfortunately, it’s the truth. So even at Microsoft…so my role at Microsoft is as a Data Platform Solution Architect, I cover the engine and I cover the analytics. I cover the entire gamut of the data platform. And so I think it’s just the new role, a new part of the IT department.

Carlos: One thing I think is interesting, as listeners of this show know, that if you work in IT you’ve made the commitment to learn new things. If you don’t have the initiative of learning, we’ve said find another profession. So it sounds like this is among that. So let’s talk about the pieces there, so there’s a web component. Now, I thought if I use the web it’s $9 a month per user. But no, you’re telling me there’s a different configuration.

Patrick:  No, you can actually use this for free. So desktop? Zero dollars. You can download the desktop for free. Nobody pays for the desktop ever, that’s what I was told. I don’t know if that’s going to change, but at least for now. So download it now. For the service, there is a paid component to the service. But the paid component comes in when you want to live query or direct access data that is on premises. When I’m not moving any data to the cloud. If I just want to issue a query, go down on premises and query that data. That’s like the true, that’s when you go from free to paid. Now there’s other aspects of the pro license that will require pay. If you want collaboration, if you want to create what you call a content pack, where if I give you this content pack all this pre-built collateral it automatically goes into the workspace. So there’s a lot of different aspects but when we’re talking about the pro version, think about, “I’m live querying.” If you’re OK with importing your data to the cloud, you can absolutely do all this for free.

Carlos: In our example, I have Google analytics, I have Twitter data…

Patrick: All free. If you go to the service, sign up, sign in today, and you can go click Google Analytics in the service, put your credentials in, and zero dollars. It’s called freemium.

Carlos: Freemium?


Patrick: That’s a very common model. It’s freemium. It’s absolutely free.

Carlos: So is that like, from an adoption perspective, how is –

Patrick: Insane.

Carlos: I think he quoted James again and I’m quoting James from last year: “one hundred thousand new sign-ups a day” or such thing?

Patrick: Insane. I go talk to so many customers and they’re like, “You have to see what we’re doing with PowerBI.” And I’m like, “Are you guys paying for it?” No, no, no, no. If you import, and you deploy, you can do it for free.  Let me show you something else, right? The best part of the precon that I’m doing tomorrow. Let me show you an enterprise BI solution.

Carlos: So take us through that. If I don’t want my stuff in the cloud or maybe let’s compare that to a Tableau or an Oracle, so what are the components that I would need?

Patrick: Take any in-memory product. Any desktop product like even Excel. Even Excel. Access, I’m going back. Think about Access. These are data silos That’s what I call them, because I’ve literally imported that data into that model and then what else can I connect to it, right? How many other things connect to PowerBI desktop and Tableau if I import that data and leverage the model that I build there. Not many things, okay? Because it’s in the silo of Tableau, right? So I can’t get a connection from reporting services to Tableau. I can’t create that connection from the PowerBI desktop to Tableau. If I create a semantic layer on the server level using SQL Server analysis service or if I let it lay in the database, I can use any product to connect to it. But if I want a true enterprise solution where let’s say you’re the dean of a college and I’m a professor. Okay? When I sign in and look at the dashboard, should we see the same thing?

Carlos: I would think not.

Patrick: I would think not. The dean has the whole view. I just see my courses that I teach. If I use the Desktop for that or I use Tableau for that, I can’t control that security at that granular level. If I use analysis services I can dynamically control that so when I sign in from the services and say, “Hey, this is Patrick”, it passes my security context down, it runs that query, and filters out the data based on that login user. It’s a Pro feature.

Carlos: That’s a pro feature. To do the filtering that’s a pro feature.

Patrick: [laughing] That’s a pro feature.

Carlos: [laughing] Patrick wants you to pay the nine dollars a month, ladies and gentlemen.

Patrick: It’s a pro feature. It’s an enterprise solution, though, right? Because or you create a dashboard for the dean, a dashboard for professor one, professor two, professor three, and you maintain them all. Who’s going to do that? Is that worth the $9 a month?

Carlos: Yeah, totally. At that point there’s enough money to do that.

Patrick: Our solution, PowerBI, it’s more than just a desktop application. And it’s not just PowerBI. It’s an entire BI stack, right? They complement each other so well and so that’s why it eclipses anything out there.

Carlos: So we talked about how most people are using the free version, right? People are adopting the cloud and  having their data stay out at the Microsoft base and they’re ok with it?

Patrick: We have the most secure cloud in the world. I wake up every morning and Microsoft sends me these kool-aid packets and I make it and I drink it.  [laughing]

Carlos: [laughing] Azure…yummy….it’s so secure…

Patrick: [laughing]

Carlos: So that brings up another point, right? You work for Microsoft and we talked a little bit about this, right? You have internal documentation, you have access to the people making this stuff, right? I don’t work for Microsoft. I am trying to support my team. I am a good IT soldier. What skills or where am I going to get the training or the knowledge that I need to support them?

Patrick: That’s a good question. [laughing] You know, you are a SQL Server guy. The first place I would start, honestly, I think this is one of the best user communities in the world.

Carlos: Oh no question.

Patrick: Open up a web browser, go to Bing. Not Google, Bing. [laughing]

Carlos: [laughing] Yes, this Kool-Aid is very good, Patrick, thank you.

Patrick: [laughing] Type your question in and I guarantee you’ll find the answer. Just kidding, alright? You’ll find great blog posts.  So, one place you can go is Microsoft Virtual Academy. I kid, but it has tons and tons of free content out there. Actually I have a video out there myself on Analysis Services. Eight hours, free. Sign in, log in, use it. I’m not kidding you, phenomenal. Or TechNet. One place that people don’t read, and we were talking about this earlier, there are a couple of stations that a friend of mine said that they said we shouldn’t put books online. But for SQL Server, Books Online has a ton, a ton, of information for you to learn and that you can disseminate, you know, and share.

Carlos: So another question I have is, so PowerBI is the new hot and shiny, right? Came out in the 4th quarter. We’re sitting here in early February. When I look at the MSDN document diagrams, and we’ll put this link on the website, there are a number of reporting options in the Microsoft space. Excel, PowerPivot, data mining for Excel, SharePoint, the BI center site, SQL Server Analysis Services with Tabular and Multidimensional models, right? Then there’s In-Memory Tabular. Then there’s performance point which at one point was going to save the world with dashboards and things right? So PowerBI is so new, is just released, what is the adoption? If I’m the IT leader, why am I going to jump on the PowerBI bandwagon when there’s this trail of reporting capabilities in the Microsoft history that I’m going to have to spend money to get people trained to get people using even if they’re not trained, I now have an organization that I have to support.  And if google analytics goes down, it becomes my problem.

Patrick: So, I’m going to add a few more to your trail. You forgot about Reporting Services. And in 2016, last April, we bought another product called DataZen and we integrated DataZen into Reporting Services in 2016. So not only in Reporting Services do you have the Paginator Reports, but you can create KPIs and create mobile reports that can be consumed in the PowerBI native application. Alright? So not only do we have all that over stuff. We have more stuff. But the reason you would take PowerBI? Performance point. It’s a great product. Wow, for it’s day, for it’s time, but for it to truly compete, for Microsoft to compete, for your organization to compete, to stay on the edge, you gotta go with PowerBI. And the reason is, think about you’ve got two landscapes right now. You’ve got the cloud and you’ve have on-premises, okay? Microsoft’s goal is to give you parity between the cloud and on-premises. And so whatever you can do in the cloud, you’re going to be able to do on premises. Whatever you can do on premises you’ll be able to do in the cloud. Okay, so I take a PowerBI Desktop, not only will I be able to publish it to the web, but I’ll be able to publish it to on premises. It’s on the roadmap. Go look, it’s out there on the site.

Carlos: I think I missed that.

Patrick: I can take a Reporting Services report and pin it to a PowerBI dashboard. Fully integrated capabilities across the cloud and on premises. So, if you want to stay archaic, I’m going to be honest with you.

Carlos: Kind of like upgrading to XP, right, or Server 2003?

Patrick: Like going from Vista to 2010. [laughing] Or Windows ME? Remember Windows ME? So, Performance Point is a great product. But at some point you’re going to reach a brick wall where you’re not going to be able to do certain things. So there’s not a lot of development going on around Performance Point these days. So why do I go to PowerBI? I think that’s the question you initially asked, right?

Carlos: So am I going to be able to hang my hat and say, “In two years, PowerBI is going to be around.”

Patrick: It’s going to be around.

Carlos: There’s not going to be like PowerSuperBI?


Patrick: I’m not going to say that. I obviously can’t say that. But if you think about two years ago when we released PowerBI version 1. It was horrible. And I’m sure that’s a strong word but it just wasn’t up to par with what we expected it to be. And now with this version of PowerBI, the adoption is mass. It’s everywhere. Everywhere you go, people are talking about PowerBI, you know? So if you want to use a Microsoft BI landscape and you want to stay up to date on it, you have to go to PowerBI. It’s just not a choice.

Carlos: Very good. So as we wrap up the PowerBI discussion, anything else that people should be mindful of or be considering of?

Patrick: Before starting PowerBI?

Carlos: Before starting PowerBI.

Patrick: Know what you’re going to do first. Know what you’re going to do.

Carlos: Start with the end in mind.

Patrick: Don’t download the Desktop until you know what you’re going to do. And I say that not as a bad thing, don’t download the desktop. The desktop is so phenomenal then… and I’m taking my Microsoft hat off and putting the Kool-aid away…. The first time I downloaded it, I had a project that I was going to work on and I ended up doing three million things before I got done. Because there were so many things and capabilities in it.

Carlos: Shiny new object, you want to play around with it.

Patrick: It was like someone gave me a shiny nickel and I was ready to go.  So kind of know what you’re gonna do before you download it, because once you get it downloaded and start playing with it you’re going to get lost in all the capabilities and features. So kind of come up with a game plan. That’s my recommendation.

Carlos: Areas where PowerBI might not be the best solution?

Patrick: Operational reporting. True operational reporting. PowerBI is high level, aggregated data. Now it can show very detailed data, right? But if you need traditional pixel-perfect operational reports that are going to be delivered in somebody’s email box as a PDF? You need some other solution. Reporting Services. PowerBI is not meant for doing things like that. PowerBI is ad-hoc reporting, on-demand reporting. Not operational tables and columns. It can do that capability, but that’s not truly what it’s meant for.

Carlos: That’s interesting, because that kind of goes back to our point as data stewards is that we need to steer that in the sense that yes, you can use your PowerBI, but you’re using that for reporting. Like, I’m not going to be responsible for keeping that up. It’s yours, I’ll give you that data or you’ve download it from the web, but it’s yours. If you want a report from me that tells you how many website visitors we have? Ok, I’ll use Reporting Services and I’ll email that to you on a subscription or whatever and you can do that.

Patrick:  That’s right.

Carlos: Well Patrick, let’s do SQL Family. So we have a couple of questions where I’m going to get to know you a little bit better. So we’ve been talking about tools. I like to know how other people work. A very successful person like yourself, you’ve been doing lots of different things. I want to know what your favorite tool is.

Patrick:  From Microsoft?

Carlos: To get things done in your work. What is your favorite tool, why do you use it, and what does it do for you?

Patrick: What’s my favorite tool for getting work done? Hmm. A computer.

Carlos: [laughing] Okay, an application maybe. Let’s narrow that down just a little bit.

Patrick: [laughing] You know, I don’t know. I use so many different tools to get work done. Honestly, it’s Outlook.

Carlos: There you go. Okay, so any special tips or do you have any add-ins that you use?

Patrick: Nope. I get a lot of junk mail. So it’s funny, we just had a new hire and he’s like, “Man…”

Carlos: You like Clutter? Is that what you’re telling me?

Patrick: Nope, not Clutter. I don’t use Clutter. But we get inundated. We just get all these DLLs and everything and basically the sort. Sort and you can see, “Hey, this person is not… “ delete, delete. And then have a rule. If I’m on the CC line, it does not show up in my inbox. It goes to a folder that’s a lot of junk. If my name is not in the To line, it goes to a CC. So I get a lot of work done then with Outlook, I really do.

Carlos: An Outlook hack. Very good. Now I will say that I started using a program called Boomerang. And that’s been very nice. Now I’m a little bit more needy than other people, I guess, because I’m always like requesting podcast interviews, right? And if they don’t respond to me at a certain time Boomerang can set that up so that it will simply let me know. It will come back to the top of my inbox and say, “They never got back with you.”

Patrick: Nice, I’ll have to look at that. And you know what else? SQL Server data tools.

Carlos: So, as a Microsoft person can I complain a little bit?

Patrick: [laughing] Not that I can answer, but yeah.

Carlos: So it seems that it’s kind of the same product but they made SQL Server data tools, right? So that there’s one for the database for like the installs and SQL Server Management Studio and then you’ve got the Visual Studio implementation.

Patrick: Not anymore. It’s all one. 2015, you install it, and everything’s there.

Carlos: See I’m using 2013 I guess.

Patrick: In 2015 all of them are there now and that’s why it makes me so productive. So I can even do Management Studio type stuff in SQL Server Data Tools now. So I don’t even have to open up Management Studio anymore.

Carlos: Okay, you just inherited a million bucks. What are you going to do with it?

Patrick: [laughing] Put it in the bank.

Carlos: Put it in the bank.

Patrick: I have two kids that gotta go to college.

Carlos: You know, it’s interesting. So when I came up with this question, I thought it would be interesting just to hear different people’s scenarios. And the most common? Pay off my house and save it.

Patrick: I have two kids that gotta go to college.

Carlos: We have our professionals here. We’re not for playing the lotto much.

Patrick: [laughing]

Carlos: So we haven’t talked about it, that you’ve come from Pragmatic Works, you’re working at Microsoft, you’ve been an MVP, right? You’ve had a plethora of different experiences. What’s the best career advice that you’ve received?

Patrick: Listen more and talk less.

Carlos: Interesting.

Patrick: Yep. Steve Jones told me that. He said, “You’re a great presenter but you need to talk less and listen more when people talk to you.”

Carlos: Steve’s a great guy. Good advice. One last question before you go. Okay, if you could have one superhero power, what would it be and why do you want it?

Patrick: I wanna be smart. My kids and I have this conversation. They’re go, “why wouldn’t you want to fly or something?” I say that I don’t want to fly. I want to be the smartest person ever.

Carlos: Like a Doctor Xavier or a Doctor X?

Patrick: Doctor X. There you go, that’s me. But I want to be able to walk though.

Carlos: [laughing] There is that one side effect. Patrick Leblanc, thank you so much for being on the program.

Patrick: Well thank you for having me.


Episode 36: Azure Virtual Servers

1400Hello companeros! Episode 36’s stop on the SQL Trail is with Luis Vargas. He’s a Microsoft Program Manager who currently oversees SQL Server Virtual Servers in Azure. You have questions and Luis answers them. In this episode of SQL Data Partners, he and I discuss everything related to virtual machines in Azure.

In this episode we discuss when to use virtual machines in Azure, the difference between Azure SQL Server and Azure VMs running SQL Server.  We also discuss the changes in Azure adoption across industries.

About Luis
Luis Carlos Vargas Herring is a Principal Program Manager at Microsoft. His current job? To make Azure “the best platform to host a database in the world.” He previously worked to improve SQL Server High Availability and distributed systems. He regularly speaks on SQL Server topics at Microsoft Ignite and the SQL Summit.

Transcription: Virtual Servers

Luis: I’m a program manager. I’ve been in Microsoft and in SQL Server for almost eight years. I recently work for almost four or five years in High Availability. So, if people are familiar with “Always on” and things like availability groups and Failover Clusters and things like that, I work on that for a long time.

Carlos: Wow, very cool.

Luis: I still drive High Availability for SQL so right now we’re finishing SQL Server 2016 which is coming out later this year. So yeah, finishing all the details and we’re starting to look into the next version of SQL and what we are going to want to do for Always On. But outside of that, around two years ago we released Azure virtual machines. This is a frame for people to rent the virtual machine that is running some workload in the public cloud in the Microsoft regions that we have around the world. We started adding immediate support for SQL Server. Pretty much every application requires a data store, so we started working on that. And yeah in the beginning it was just, have a SQL Server, install a ready iso image that you can use to provision your machine, and over time we have done a bunch of enhancements both on the platform itself – as in SQL- to make sure that Azure is the best platform to configure and run a SQL Server workload.

Carlos: Now when you request the V in Azure, do you have the option of selecting which version you want?

Luis: Correct, so we have versions all the way from SQL 2008 r2 to SQL Server 2016 CPt 3.3, which is the latest version of SQL Server 2016. So all the versions are there. We have different editions as well that we support SQL Express, Standard, Enterprise, and Web. So for people who want to do, like, dev tests for example or they want to run like small workloads, we support Express images. So we limit to 10 gigabytes per database and I believe it’s up to 2 gigabytes of memory, I may be wrong on that one, but basically it’s for small workloads and SQL Express is free SQL so you don’t pay for the license. You only pay for the virtual machine that is running on Azure. We have different sessions of virtual machines. We have different types of virtual machines as well.

For example, the “A” series is the first set of machines that we released two years ago. Still, you know for dev tests it works pretty well. You can start with an A1, for example, and the A1 machines has a couple of cores and a couple of gigabytes of RAM and for example, running SQL Express on that size of virtual machine is around $8 a month. It can have a whole virtual machine running for the whole month and you can do that dev test for around eight dollars. It’s a definitely good offering. And then obviously you have the Standard and Enterprise for the largest workloads out there. You’re looking at more mission-critical features like High Availability or compression or some of the security features that we are used to using in SQL 2016. All of those are going to Enterprise edition and then for those you probably want to have a bigger machine like at least eight cores. We go all the way to 32 core machines, so you could have a 32 core machine.

Carlos: Is the adoption rate for most people looking at Azure VMs for SQL Server, are they doing that for testing or are they actually moving their environments? Because they’re going to have data on premises. How often are you seeing people kind of lifting and shifting to VMs? Saying, “OK, I’m going to take this database and I’m going move it to a virtual machine running SQL Server?”

Luis: So, I think it has changed over time. Like, probably the first six to eight months after we released, most of the people were just trying the platform, doing dev tests, getting familiar with it. And I think after that we started seeing a growth on logical virtual machines and more people are using Enterprise edition. So we’re definitely seeing more production workloads running now. At this point, I would say both of them are equally important. Some of the customers bend completely to Azure virtual machines in SQL so we have some examples of big corporations that are running healthcare services, for example, on SQL Server and virtual machines. So they are starting to trust the cloud, for example, for these applications that require privacy and security. We see financial companies, for example, we are supporting their applications as well. So yean, we definitely see a lot more people trusting the cloud and I think it’s just a natural progression. I think companies are seeing the value on the price/cost benefit of the cloud, the fact that they can start with a small machine and they grow it in 10 minutes and they have a bigger machine, when they don’t need it they can stop the machine and they only pay for the time that they use it. So I think that has changed, definitely, how people approach the cloud. And you know a lot of corporations starting from the management side, they start to push IT people from the management side to go and look at the cloud.

Carlos: So why would an organization choose to use the azure virtual machine for SQL Server versus Azure SQL Database? I think previously it was, September, October 2015, with version 12…I feel like Azure SQL Database finally put on the “big boy” pants and was like “Now we’ll support all your data types” and there were a lot more features and functionality. Why would someone still consider using a virtual machine over just using Azure SQL Database?

Luis: A couple of reasons. One is if they want to have control of the statement of SQL Server. So some people still want to able to configure every setting in SQL Server or they have already certified their application with a specific set of features, for example, a specific set of settings on the OS. Or they need some application that is co-located with SQL, like some entity service, for example. So in that case they need some control on when those end with SQL, some Entity Service for example. So for that they have 100% control on anything related to the OS and on SQL Server. So that’s one. The other one is that we still see is people who have dependencies on objects that live inside the user database. So we have some people, for example, who are dependent on things like agent jobs and linked servers and service brokers and things like that. So it’s not a super large number, but still a lot of applications out there require those things. We are definitely working on SQL Database as well to support some of these dependencies into SQL Database, but it’s just a different architecture model and SQL database basically, you have a single tenant on the database and I wouldn’t suppose anything on the instance level. You have the logical view of a server, the databases themselves are independent of each other. Doing things like growthDB, interactions, linked servers, and things like that are not natural to the design of SQL Database. So we are looking into how to simplify some of these things. Like last year, we released Elastic Tools and Elastic Jobs, for example, and .NET APIs for example that allow you to execute some of those operations like growth lift database transactions and specific queries and agent jobs. But it’s still not exactly the same as how you would do it in an iteration of SQL Server.

Carlos: So now to clarify – so the elastic jobs, this is specific to Azure SQL Database? So we’re not necessarily working outside the virtual machines at this point? We’re using that as a service?

Luis: Exactly. We see a lot of people doing “and shift” for virtual machines. They have an application that has been running for two or three years and they don’t know if they’re going to need an application for three or four more years and the hardware is running slow at this point, so instead of going and buying new hardware they migrated to Azure. We have some customers that want to get out of the business of managing data centers and hardware, they just want to focus on the applications, so they use Azure virtual machines. But I think for new applications that are starting from scratch, most of them are going to SQL Database at this point.

Carlos: Yeah, it makes sense. Now, when I get the virtual machine with SQL server, I have control over the OS. But are you seeing users then add like a web server on there as well and try to run everything individually? Or are they still breaking that out and just using that for SQL Server?

Luis: I think, for the web service specifically, I see people separating it. So they put it in a different virtual machine or they use a web server offering – that we have for websites for example-  that we have in Azure. There are things, like in applications for example, they depend on business logic written in some either new .NET assembly or some old C code. Sometimes they put that co-located in SQL Server. So that they avoid growth machine communications, which is just latency. So we see people just installing their own application components together with SQL Server.

Carlos: Sure. So I guess we talked a little bit about deployments. So if I’m going to request a virtual machine with SQL Server on it, how am I going to get my data to that virtual machine? Through backup and restore, through backups? What’s the common scenario there?

Luis: To get the data there, there are a couple of ways. I think that the classic one that we see people doing is they’ll stick in a backup and restore. So they take the backup, upload it into Azure storage. Often people use things like AC Copy, which is a utility that we released from Azure. And it allows you to do a little multi-threading when you read and deliver the blocks of the files. So that is the fastest way to send big files to Azure. So there are people who are taking big one terabyte, two terabyte backups sometimes and then upload it to Azure to AC Copy and then from there they are restoring to the virtual machine. There are a lot of things that we tried to do to simplify scenarios. Like in Management Studio, for example, in SQL ’14, we released a wizard that allows you to deploy a database straight into the virtual machine so you right click on the database, say “Provision a Virtual Machine”, and you can either create a new one or specify an existing one.

Carlos: Sure, yes, those wizards are getting quite nice I think. So it might not relate well to this scenario, but I think the Azure configuration checks in the SQL Server 2016 options are a lot more robust. Things like the Stretch Database and all those checks that say, “Am I ready to move into Azure?” And so yeah, going through that I guess would be beneficial.”

Luis: We’ll continue investing in that. In SQL Server 2016 we have like the first version of it. You know, we got some feedback from customers. Some of the feedback we got, for example, “I want to move more than just my database. I want to move my loggings, and my jobs, and my application settings and things like that. So stuff like that we’re working on. Eventually you’ll be able to say, “I want to move my whole SQL Server instance with all my settings and all my databases” and then we’ll take care of it.

Carlos: Oh wow. So I know that you blink and something changes in the Azure environment, right? There’s a new service or something changes on the portal. They’re changing all the time. But what about the Active Directory and the security implications? You mentioned logins. How is that going to be different between my on-premises and my Azure virtual machine from a security perspective?

Luis: I mean we know Active Directory is super used in the corporate world. Pretty much every company has domains in Active Directory and logins there. And most people use Windows logins. A few people use SQL logins but are moving to Active Directory. So yeah, we released Azure Active Directory which is a service that allows you to manage logins for domains so you can create a domain in the directory service in Azure and then manage your logins there. We support that for SQL Server in virtual machines, in general for virtual machines. So you can create your logins, for example, in the directory service in Azure and then you can use the logins to either run SQL Server as a service account or to authenticate your logins into SQL Server Databases. The other thing is you can synchronously replicate your logins from your corporate account Active Directory into Azure Active Directory as well.

Carlos: So that is now available?

Luis: Yeah, so every couple of minutes you will synchronize your logins and passwords and everything into Azure Active Directory.

Carlos: So now my experience, which I admit is limited, I guess I’m seeing organizations create the Active Directory domain and then they continue to have their domain on premises. Are you seeing cross-trust, one-way trusts, I guess it can run the gamut right? It’s just another domain and you can link to it and people are all doing all kinds of different things with it.

Luis: Yeah, I mean that depends on the corporation again. So some corporations have adopted the cloud model quickly. They use trusted domains between each other and then they’re happy with that. Some companies can have a lot of corporate policies and because of those policies they disallow trust between the domains. So the only thing that they do is synchronized logins and replicate those into Azure. But not the other way back; they don’t synchronize anything back into the on premise domain. It depends on the company.

Carlos: Okay, so we talked about a little bit of the feature sets that you can do. Express, Standard, Enterprise. Are there any feature limitations if I use an Azure VM for SQL Server?

Luis:  All of the features in SQL Server are supported in virtual machines. The only one that has a caveat still is SQL Clusters, SQL Failover Clusters. And that is because SQL Clusters requires shared storage, so basically a high availability solution where you have multiple replicas and they share the same storage and that’s how they do failover. The way that we support it today is through third-party venders so, they allow you to create storage on top of the direct-attached disks that the VMs have. So that’s how we support SQL Clusters today. You can either do that or you can have like an Express route to your on-premises environment, this is like a fast VPN tunnel. And then you can have your storage there, like a SAN. That’s how you can create SQL Clusters in the cloud. We’re working on that to support it natively, I mean we have now new features in Azure called Azure Files. And Azure Files is basically SMB 2.0 chairs that can be accessed by multiple virtual machines. So that’s how we are working to support SQL Clusters. Functionally, it works, people are turning it on and trying it. The only reason we aren’t supporting it yet because we are waiting to support Premium Storage on top of Azure files. So premium storage is like SSD-based fast storage. And for SQL it’s very important to have that fast storage, and so we’re waiting until that comes to support it.

Carlos: Okay, very good. Now one additional feature that I wanted to talk about was potentially using the Azure SQL Database or Azure VM as a disaster recovery scenario. Is that something that you’re seeing customers adopt?

Luis: Well, we are seeing more and more of that. Again we, starting in SQL 2014, we released the support for the Availability Group replicas on virtual machines. And what this means is that you can have one or more secondary replicas in Azure living inside of the virtual machine and these replicas are synchronizing continually from the primary. So we started seeing customers doing it.

Carlos: Now in that scenario, the virtual machine would have to be up all the time, so you’re basically paying for that. Is there a way, or are you seeing scenarios, where you can have like a cold standby? Like once a month I’m sending my backups to it, like a log shipping type approach?

Luis: I mean there are different ways you can enable these recovery solutions in Azure. Some of them are faster to recover, but they cost more money. Some of them are slower to recover but a lot cheaper so the simplest, cheapest way to have some recovery in the cloud is to take backups to Azure storage so you can take your backup locally, for example, and the copy them again to AC Copy into Azure storage and then you maintain your copies there. We have a feature in SQL Server called Backup to Azure Storage. So your backup statement itself in T-SQL or Management Studio can be targeting Azure storage, so that’s another way you can do it.

Carlos: But that’s only in 2014, right?

Luis: Well no, we started in…the T-SQL functionality version of this was in 2012 SP1 CU2, so there was like a basic version there. It was the first one so it doesn’t have all of the performance optimization that we introduced in SQL ‘14 and in SQL ’16. So in SQL ’14, there’s some performance optimizations like the first version we released originally in 2012 was based on the VDI interface, so we got vendors to build the backup solutions on top of that. But that interface wasn’t part of the engine so there was some inter-process communication between the two, and there was a single-threaded process as well. So reading those backups and sending them to Azure was a single process, so it was not the most efficient way but at least you had that functionality. In SQL 2014 we put the holding functionality inside of the engine itself, the SQL Engine, so you don’t have a separate VDI process and we added some multi-threading capabilities so we can read more and we can send more parcel to backup in parallel. And then in SQL ’16 it goes beyond that, so we allow you to do striping of the backups. So you can divide your backups across, like, 64 files if you want. And then you can upload all of those in parallel. So it’s a lot faster to do. That’ll be the fastest way to do your backups to Azure storage. Having said that, if you need to recover when disaster happens you’ll need to go in and provision a new virtual machine, for example, and then you’ll need to go in and restore the backup, right? And that will take some time depending on the site. The other option is having the availability group replica, which is continuously running, and then you can failover like 10 to 15 seconds. It’s very, very fast and your connections are automatically redirected to that SQL Server. But in that case you need to have a virtual machine that is running SQL all the time.

Carlos: Yeah, now in that sense, and I’m thinking more on the Azure SQL Database side, on the VM side I’m assuming that I still have that same option to say, “I want an S2 or S3”, or something large enough to hold my data store. But then when I fail over, it’s, “Okay, now I want P1” or something.

Luis: Exactly. You can do this thing. You can start small. You could start, for example, let’s say we have like a DS4 or DS3. A small machine probably with like four cores. In reality, the only thing that it has to do, unless you use it for like grid workloads or backups or something like that, the only thing that it has to do is reapply the log. I mean, it will receive some log transactions and then you’ll have to reapply them. So the thing that is more important to do that is just have faster storage. So even if you have a small machine, maybe even with just a couple of cores, those are the only ones you have to pay for the SQL license. You can have premium storage and then make sure that virtual machine is able to keep up with the traffic on the primary.

Carlos: There you go. That’s a great scenario. And one that’s cost savings, as I think there are lots and lots of organizations that are out there that are in colos and are paying for a large warm standby. And so this is an opportunity for them to save a little bit for a server that, in theory, hopefully they won’t be using too much.

Luis: Yeah, if you look at the disaster recovery site, very few customers can actually pay for that. I mean they pay for the rent of some physical site, the hardware, you need to have some operations people handling that, and you will still need to pay for the SQL license anyway. Yeah, definitely cheaper. One of the very interesting things that we have in SQL 2016 is we have a basic version of Always On which we call “Basic Availability Groups” and in the standard edition as well.

Carlos: This is in 2016?

Luis: 2016.

Carlos: I did not know that, I’ll have to take a look at that more.

Luis: Yeah, so we announced in SQL 2014 that we were going to depreciate database mirroring and obviously a lot of customers were concerned that we were not going to have a high availability solution in standard. So the plan was just to replace it with Availability Groups. So you’ll still be able to have one replica and the replica can be synchronous or asynchronous, which is actually an advantage over the previous mirroring setting because it would always have to be synchronous.

Carlos: It is still readable? Or not readable?

Luis: It is not readable, no. If you want to read from it, it’d have to be Enterprise edition. But again, it will benefit from all the performance improvements we have done in always on. And the replica can be synchronous or asynchronous, so you can select where to put it so if you want it locally for high availability you can do that. If you want to instead protect it for disaster recovery you can put a second in Azure, for example, if you want.

Carlos: Very cool. Now before we let you go, there is one component that I came up with in my research that I wanted to speak on. And that is so this Resource Manager Deployment versus a Classic Deployment.  Maybe we should have started there, but give us a little insight into the differences there and where the future is going.

Luis: Yeah, so when we released Azure, the whole programmability model, like all the components in Azure: the storage components, the compute components, the network components, all of those were written in a programmability model called the Classic Model. And that was the first model that we defined. You know, now two and a half years later we have heard a lot of things from our customers, and some of the things they have asked for is to have… well, there’s a long list of things, but one of the examples is they wanted to have this year more fine-grained control on the resources and the subscriptions in Azure. So they want to be able to specify by, “I want to grant these types of permissions on this type of object.” So for example, “I want to allow this particular person to be able to stop a virtual machine, but I want to allow this particular user to go and create new storage accounts.” So it’s one of the things that we hear customers asking. Also, they wanted to have a more declarative model to provision multi-tier or multi-component applications. Especially as JSON has become more and more popular. People like to have this concept of, “Allow me to declare what are the things that I want to have in my deployment in JSON, and then you, Microsoft, take care of those and then deploy them as requested.” So as we have learned how to achieve those things with Classic, it turned out that it was going to be already complex and we weren’t going to be able to make it very simple for customers. So we decided to do it using a new model, and the new model is called the Azure Resource Model, ARM. And that’s a model that we released last year. And that is the future, so eventually over time we’re going to migrate all of the deployments to Azure Resource Manager. Though we’re going to start slow. We’re going to offer people the possibility of migrating whenever they want and we’re going to offer some tooling to do that. But eventually the plan is over time that everybody will go into the Azure Resource Model.

Carlos: Okay, so when we talk about this, this is for the administration of the VMs themselves.

Luis: Correct.

Carlos: Awesome, thanks for chatting with us. Luis, let’s do SQL Family.

[SQL Family Segment]

Carlos: So I have a segment of questions that we call SQL Family. We’d like to know a little bit more about you and how you work. So generally, one of the things I always like to ask people is how they work. What tools do they use to make things easier for themselves? And normally we’re talking about database tools, so in your situation it might go a little bit differently. It doesn’t have to be a SQL tool, but what’s your favorite tool and why do you like it and what does it do for you?

Luis: That’s a good question. So basically my tools, what I do is design a lot of these features and then work with the development team to implement them. So a lot of the stuff that I do obviously has to do with Management Studio, has to do with the Azure Portal.

Carlos: So are you the person we should be thanking for the upgrades that are coming to Management Studio?

Luis: [laughing] No, no. I mean, I’m not going to take credit for that. I mean, we have a great team, a user experiences team, that are doing a lot of good stuff like changing the channel for Management in Studio. And there’s a lot of cooler stuff that’s gonna come over the next six months. I think a lot of things are going to be redesigned so expect a lot of new, cool stuff in Management Studio.

Carlos: Very cool. So you’ve just inherited one million dollars. What are you going to do with it?

Luis: Oh, wow. One million dollars. I’d probably invest it. Invest it in something that’s going to win a good return.

Carlos: Smart man!

Luis: Yeah.

Carlos: So, tell me where you’re from Luis. We didn’t chat about that before we started.

Luis: I’m originally from Mexico.  Mexico City. I grew up in Mexico until I was twenty years old and then I moved to England. So I actually did grad school in England, so I spent five years in England and then moved to the US after that.

Carlos: So you’ve lived in several different countries and had different experiences, right? Talked to lots of different people. What’s one of the best pieces of career advice you’ve received?

Luis: Just be consistent. I mean, do what you say and say what you do, right? You know, the more transparency that you give to your work, the more consistent your actions are with what you say, the better. So I think that is a really good piece of advice. Obviously, you know, focus on customers always. You know, instead of trying to solve interesting computer science problems, focus on the things that customers care about because that’s what’s going to resolve the product selling.

Carlos: There are more opportunities.

Luis: Yeah.

Carlos: Good, so Luis, our last question: If you could have one superhero power, what would it be and why would you want it?

Luis: Oh my god, that’s a good one. I don’t know, I’m actually a fan of superheroes and all of the superheroes from Marvel to DC to et cetera et cetera. So I don’t know, it’d have to be one. I know Superman is really powerful, you know? And I don’t believe in Kryptonite so I’d be safe.

Carlos: [laughing] Now here’s a couple of super powers, right? He’s got a couple of things lumped into. You just get one. Which one would you choose?

Luis: Oh wow. I like going for the classic invincibility. Like nothing damaging you. I think that’s really good.

Carlos: Awesome. Thanks for taking some time to be on the show with me.

Luis: Thank you, anytime.





Episode 35: Position Yourself to Better Opportunities


On Episode 35 of The SQL Data Partners Podcast, I talk with Philip Morgan of The Positioning Manual for Technical Firms. Philip’s book has influenced my work at SQL Data Partners, so I wanted to bring him on and share his insights with the rest of you on the SQL Trail. You’ll want to listen to this one, because the benefits of positioning are numerous.


The benefits of narrowing your focus include:

  • Delivering more value to clients and to the organization.
  • Becoming an expert faster in your chosen problem or technology.
  • Gaining control in over how you’re perceived by employers.
  • Going deep into interesting problems you didn’t know existed.
  • Gaining prestige as a solver of expensive problems.

In this episode, I pose your positioning questions to Philip:

  • Won’t positioning as a professional ruin my career?
  • How do I position effectively? What do I focus on?
  • What’s the risk to positioning?
  • Won’t I be bored?
  • What if I’m “found out” to be not as good as I say I am?
  • How do I deal with recruiters?
  • Won’t I be pigeon-holed into a legacy application?
  • How do I continue to evolve as a technologist while also focusing?

First, Philip and I talk about positioning as a tool to create more value through a narrowed focus. Philip discusses how to apply positioning to your SQL Career, even if you’re a W2 employee. Philip and I go over three ways to narrow your focus and how it affects the direction of your career. Then, we talk about how to use positioning to talk to recruiters and get the kind of job you’re looking for. I also ask him about how to turn unwanted work assignments to your advantage using the leverage grained from positioning. He also points out the risk of focusing only on a technology, as opposed to a specific problem. He then gives specific advice for talking to corporate leadership about the value you bring to the company as an IT professional. Philip addresses positioning fears like loss of flexibility, boredom, and being pigeon-holed and balances them with the benefits gained from positioning. Philip points out the scenarios in which narrowing your focus might not be the best solution and I share my experiences with positioning and project work. We then talk about how to pivot and “tweak” positioning over time and how to gently make your expertise known.

About Philip:

Philip Morgan is a positioning strategist who started his career as a Microsoft trainer. He’s the author of two books: The Positioning Manual for Technical Firms and The Positioning Strategy Guide. He’s helped consultants and technical firms focus their brand and position themselves for success. Philip sells several productized services, My Content Sherpa and Drip Sherpa and Agile Marketing. For more advice on positioning, listen to him on the Consulting Pipeline Podcast.

Transcription: Positioning

Carlos L Chacon: This is SQL Data Partners’ Podcast, Episode 35. I am Carlos Chacon, your host, and I welcome all my Compañeros to the show. Thanks for joining us on the SQL trail. This podcast is dedicated to SQL Server related topics. Designed to help you become familiar with what’s out there. How you might use those features or ideas and how you might apply them in your environments. You know from time to time we like to mix it up, and today we are going to do just that. We are going to talk about “Positioning.” How you might position yourself and your career. My guest is Philip Morgan, who is the author of “The Positioning Manual For Technical Firms.” While his book is directed towards businesses, I thought it might be interesting to explore how this positioning idea can influence the number of opportunities that come our way, and the types of opportunities that we can get. Today’s episode is brought to you by “SQL Cruise — Learn. Network. Relax. Grow. Just add water.” Check out sqlcruise.com for more information. Again, it’s always good to have you, compañeros, welcome to the show, let’s get to it. Philip thanks for being here.

Philip Morgan: My pleasure, Carlos, really excited to talk to you.

Carlos: Thanks, I wanted to reach out to you, as I mentioned, you are the author of the positioning manual. You want to take a minute and tell us a little bit more about yourself and what you do?

Philip: Yeah. I’ll keep it pretty short. In the ’90s I got a job and ended up as part of the job, working as a “Microsoft Certified Trainer.” During the first dotcom boom, when money was being thrown at the training industry, I got my start in technology. I was an MCT and MCSE for a number of years. That’s how I got into technology.Overtime that changed into a focus on marketing. Nowadays I help custom software development shops, get more leads for their business. I do that using things like positioning, education based content marketing and marketing automation. I got so interested in positioning, I ended up writing two books — I think I’m toying with the idea of merging them into one — but two books on positioning. I love talking about the subject because it’s terrifying and powerful, all at once.

Carlos: Yes, I agree. Of course I look at it from the business perspective. Some of our listeners might be thinking that same thing. Well, “Hey, this is more of a business thing.” You actually used the word ‘marketing.” Like, “Well, I’m not a business, why would I need to market myself?”What types of benefits might come to an employee who wants to position themselves? Why would you look into that?

Philip: Yeah. It is very much a marketing term. If you go on Amazon, for instance, and search for the term positioning. All the books that come back, and there’s not a whole lot of them, but for…or on the subject they are basically marketing books. I do think the concept is applicable to employees. I think we should spend just a moment giving a working definition what positioning is.I think of positioning as a tool for creating more value, and I think that that is something that both an employee and a self-employee person, or small business or large business can all use that. They can all use tools that help them create more value. The way that tends to work for…especially for a small company as solo freelancer or an employee, you create more value by becoming more specific about what you do. You narrow your focus. For the folks at home listening to this if they want to think as positioning as the same thing as narrowing your focus, I think that’s a pretty good definition for the purposes of our conversation.

Carlos: Now, you think that that is use synonymous with choosing a specific technology to focus on? Or can that be like a specific type of problem that you might focus on? I want to be the networking expert on my organization or a database expert in our case?

Philip: You know, it can be any of those. There are basically three ways…three axes along which you can narrow your focus. One is certainly the problem that you become expert level at solving. That’s one. One is the technology platform, or maybe it’s a subset of a platform, or maybe it’s a tool-set or…but basically the techstack that you focus on.Then the third would be, the audience that you serve, and as an employee I think that third is perhaps the least relevant or the least likely way that you would narrow your focus. Really those first two that we mentioned are how you might narrow down your focus. I think there’s more to benefits that come with narrowing your focus. Earlier we said, doing so help you to leave more value. Generally, the way that happens for most people is they start to become expert level much faster when they narrow their focus to a specific problem or particular technology. You gain experience faster and again you’ve got to remember that a lot of my thinking about this comes out of the world of self-employment, where you have maybe a little more of attitude to make choices about how you are going to spend your time and so forth.

Carlos: Sure.

Philip: That has to be tampered with the idea that you have a job description and if you don’t meet that job description you may have problems. I think there still is some latitude for an employee to say “You know what? I’m really interested in this problem.”

Carlos: Yeah, and then focusing on that, I agree one of the very simple ideas even is just blocking off some time on your calendar to dedicate to that whatever you decide that your positioning should be, because inevitably everyone needs to increase their understanding in that and they’ll need to spend some time with it.Giving yourself that time obviously I think is an important ingredient there.

Philip: Yeah. I mean one of the things that’s a theme that always comes up when we have a conversation about positioning is, you start to move out of a passive reactive place and you start to take control over how you’re perceived. Again, I think there’s very good crossover there between self-employed and W-2 employees.You want to be perceived as having a lot of value to the organization, because that translates usually within limits to job security and higher pay, right?

Carlos: Right.

Philip: When you take control of how you’re perceived, in essence, you are positioning yourself. The caveat that comes along with that is you don’t get to decide what’s valuable to the organization that you work for. They decide and then you have to look at what they’ve decided is important and make a choice about where you want to potentially specialize or narrow down your focus.Unfortunately, you can’t say, “Well, you know, I think that the priority here is performance,” and if the organization doesn’t value that, then good luck getting them to value it.

Carlos: Right. I think even so taking that and saying that’s what I know that I want to work on, then in things like your yearly performance reviews, or talking with your manager, that then becomes a focus, “Hey, you know, I’ve noticed this, or we’re not doing these things, I’d like to do them. I want to take responsibility for them.”You’re offering, again, some value. If you can spend a little time in there and can deliver a win, then you’re providing that value and all of a sudden it may come to light. Again, there’s no foolproof way, but that’s an idea.

Philip: Yes. If you start to take the long view of, “OK, I want to look beyond just the next yearly review. I want to look beyond just the next raise,” you can I think start to make a name for yourself as the person who gets hired to solve a particular type of problem or…If we think about it from the technology perspective, you could change jobs based on the strength of your track record with I’m the person who can integrate SQL Server with this other technology. Maybe you have that technology focus. You can start to shape the direction of your career I think through positioning through choosing a focus, and maybe that’s not your only focus. Maybe that’s not the only thing you do. You develop a strength in that area. That can carry you where you want to go even if your current employer doesn’t have the upward expansion you’d like to see in that specialty.

Carlos: That’s a fair point. Again, if we were to use that marketing term, when recruiters call us or other people come in contact with us since again we’re in the database, people are mostly the ones who listen to this podcast and they say “Hey, we have this great job. Are you interested?”Then assuming you’ve taken some positioning to even say like “OK. I want to work for this type of company that does these types of things, then you can say “Does this job offer these things?” You can have that conversation with the recruiter, and in theory you establish relationships with them and they now know “OK. I’m not going to call Carlos unless it has some features that he wants that’s going to meet his criteria.”

Philip: There’s that, and I think you gain the ability to build up sort of a track record or a body of work that you can show potential employers or you can show recruiters and be like not only am I interested in this, I have a documented track record of helping my employer achieve the kind of victories they want in this area.If you’re just reactive, then that doesn’t really happen unless it just happens by luck.

Carlos: Sure. Some of that I think, as we talked about, means that you are taking on some responsibilities that you may not want or you have to handle them in a certain way. How can someone deal with receiving work assignments that they’re not super happy with but potentially turning them to their advantage?

Philip: I was never good at this when I was an employee. Here’s why is because I think that I did not realize how much leverage I had. I think part of it is not that you’re at odds with your employer but I think you can try to increase the amount of leverage you have. Part of that is by building up some area where you contribute a whole lot of value to the organization.Maybe that’s a useful tool in that situation. I think the key in all of this is seeing it from the organization’s perspective or your supervisor or your boss’ perspective and just saying what are they trying to accomplish here. Maybe there’s a better way. Maybe I can work together with them.

Carlos: Sure.

Philip: My crappy track record with that in the past was just because I didn’t really realize that I think I had more agency and more leverage than I knew at the time.

Carlos: That’s a great point. Again, being able to identify what it is that you want to do will then help shape the discussion in other settings.

Philip: One of the downsides of picking a technology over picking a problem is that the technology changes pretty fast.

Carlos: Changes so quickly.

Philip: At first, that’s fun and exciting. Then you get some years on you and you’re like “Ugh, do I have to learn yet another library or yet another tool set.” Picking problems, I think, is potentially more valuable and potentially less frustrating over time.

Carlos: Sure. I think because ultimately that’s what the managers or that’s what the owners of the organization are trying to do. They didn’t start up a law firm, for example, to run SQL Server or do this development work to run a certain technology. They want to solve this problem and to provide value to their clients and so you can try to do the same thing.

Philip: One of the things I’ll be honest that I miss about having a W-2 job is that in a lot of jobs you can just…At the end of the day, you can go home and leave the job behind. It’s much harder to do that as a self-employed person running a small business or solo business.

Carlos: Sure.

Philip: I guess what I would encourage people, even if you’re in that W-2 situation, even if you just love the fact that when you leave the office whenever you leave the office you don’t think about it until you show up the next day.Even in those cases it might be worth speaking to people who are closer to the ownership of the company or talking to someone who’s responsible for a profit and loss sheet that is somewhere above the hierarchy in where you are. If you’re in IT, talk to the CTO who’s potentially responsible for a P&L sheet or talk to the CMO and, if you can, ask them what are your priorities, what are the things that you’re trying to make happen. It can be very enlightening and then you can start to understand how you have value to the organization and how you might be able to increase that value.

Carlos: Are you saying we have to start reading those company bulletins now, Philip?[laughter]

Philip: Far be it from me to tell you to do something you don’t want to do, but I think there’s some value in that, yeah.

Carlos: I think with like anything this will take effort. There is no “Hey, I’ve decided to position myself. By the way, manager, like I’m only going to do these things now.”

Philip: Right.

Carlos: Episode 35 we talk with Bruce Van Horn about the time that you spend in education. I think because we’re technologists if we want to stay technologists we’ve made a commitment to continue to evolve because if not, we fall out of favor pretty quickly.

Philip: Even if you’re a manager, you still have to stay fluent with the language.

Carlos: One of the fears that people have about this positioning, or about trying to take on some of this responsibility, is this idea of being pigeonholed like “Now that’s going to be the only thing that I do.” I think a potentially good example is they have this legacy application that nobody wants to touch with a 10-foot pole but needs to stay alive. It’s like you just caught the hot potato.Why should we I guess be less afraid of instances like that? Why is positioning not being pigeonholed?

Philip: I think that that fear is legitimate, but I think it also involves a misunderstanding of what positioning is, how it works. I think a lot of it comes from the fact that most people don’t actually know any specialists first hand. They don’t know people who are at the top of their game and specialized in something.We don’t have a lot of data to go with. Certainly the fear of a loss of flexibility and a loss of control, which really that’s the fear that is behind this idea of pigeonholing like “I’m going to be stuck in this cul-de-sac and my career is going to become not very fun and it’s got no place to go.” I think the type of positioning where you run the greatest danger of that actually is when you focus on a particular technology stack rather than focusing on a business problem. I realize that not everybody can just choose to do one or the other. I will say that if you’re focused on a technology stack, that’s the one where you do run the greater risk of that kind of unpleasant pigeonholing. The other things that people fear are boredom, doing the same thing over and over again. They get so good at something that they get this ironic curse of being asked to do that thing all the time and then becoming bored. Another common fear is just you fear, the imposter syndrome, you fear being found out that you’re not really as good as you say you are. Those are all the fears. What I tell people is that going narrow is not nearly as monotone as they think it is because it usually uncovers new interesting problems that you were not aware of before. Instead of going broad but not very deep, you’re going deep and often going quite deep and uncovering new interesting problems that you did not know about. It’s not that the boredom can’t possibly happen. It is possible. It’s just so rare in my experience, especially if you pick an interesting problem to solve, an evergreen problem. The other thing I tell people is that when they fear that pigeonholing, they’re just looking at one side of it. They’re not looking at the side that is the prestige and the security that comes with delivering a lot of value. Whether you’re self-employed or not, when people understand that you can create value for them, they start to value you. They do so financially and in other ways. That’s nice.

Carlos: Right.

Philip: I think there’s a little bit of a tradeoff, obviously. If you want to always be on some new learning curve, which for me has gotten old after a while, but I know some people that’s why they get into technology in the first place is they love the learning curve.

Carlos: Sure.

Philip: If that really is the most important thing to you, then maybe narrowing your focus and positioning yourself in a certain way is not right for you. For a lot of us who have gotten tired of that learning curve, it’s actually nice to have the security and the confidence that comes with a very narrow focus where we’re just not really surprised by stuff very often.At least in the self-employed world, clients really like that. They value you that and they pay for it.

Carlos: Sure. In a corporate environment, you might be past the flaming bag of poo, so to speak, but if you’re willing to take that time and dig into it, find out about it, again providing the value and the manager is not getting pinged any more about that application, guess what, you just provided value there.I had an experience similar to that where I took over an application. I didn’t want anything to do with it. It was old and busted. But got into it, dug my heels in and said, “OK. I’m going to try to do this to the best of my ability.” The next time that a project that the manager wanted to go really well, he came to me and said, “I want you to be on this team.”

Philip: Leverage.

Carlos: That’s right. I had positioned myself in the sense of I’m willing to take something and make sure that it runs efficiently so that you don’t have to deal with it any more.

Philip: That’s great. Yeah, that’s great.

Carlos: Sometimes our positions will change over time. We don’t necessarily or potentially don’t want to stay in the same position. Sometimes just because we kind of stake out a claim doesn’t mean that we’re going to do that for the next 20 years, right?

Philip: Yeah. Actually, your position will have to change, because very few of us, especially in the world of technology, very few of us are in a position that’s not connected to changing social forces or changing economic forces or changing technology forces. Those all force us to change our position and update it over time if we want to remain in a desirable position.Getting back to the idea of pigeonholing, I think that’s another misapprehension that underlies that fear of pigeonholing, which is “When I make this choice and I invest this effort into staking out this position for myself, that’s it.” That’s not it. That’s not it for all of time. Most small businesses that are in a changing economy are updating their position every 6, 12, 18 months. They’re making some kind of tweak. You should just be prepared for that fact that it’s going to change, it’s going to update, and that’s totally normal. What people say is “Aren’t people going to like kind of reference the old position and be confused by the fact that I’ve changed?” I tell people that most people have way more going on and are way too busy to do something like that. In fact, it’s one of my hobbies to go to use the Internet Archive Wayback Machine to look at old versions of people’s websites, because I’m just fascinated at how they change over time. They do. The successful companies do that. They find what works, and they stick with it. They also are tweaking and updating and experimenting.

Carlos: Polishing, yeah. How does someone go about making a position change? They wouldn’t necessarily need to change their actual job to change positions, right?

Philip: Yeah, there are a couple of things you can start to do. One is you want to start by doing some amount of validation. If you have an idea that “Oh, I think this is valuable to the organization for me to become the expert in whatever,” then I think you want to validate that. You don’t want to just go on the assumption or the gut feeling.Again, if you could have a conversation with your boss like “Hey, I’m really interested in this, whatever it is, do you see a future for that? Is that going to be around a year or two from now? Is that important to the company, or is that just my personal obsession with whatever?” You want to do some level of validation. I think within an organization one thing you can start to do is become the guy or gal who is interested in that. I remember when I was working for a marketing agency who did a lot of work for Microsoft. This was when Windows Server 2008, before it came out. That was when Microsoft added operating system-level virtualization in the server product with Hyper-V. I became very interested in that. No one else seemed to be as interested. I set up a wiki on our SharePoint server and just started posting stuff there that came in from wherever, wherever I could find information that was relevant to Hyper-V. I would just post it there as a resource for other people. Like nobody asked me to do that. Nobody said I had to do that. I just did it, because I was interested and I thought it might have value for other people. It did. It led to me becoming the virtualization guy for that company. I think it’s not something where you’re going to change the sign on the door to your office or your cubicle. I think it depends on the culture of your organization, but there are ways to make your interest in this thing known to others. It doesn’t really take long at all to just kind of become like “Oh, that’s the person that you should talk to if you need help with this problem or this aspect of this technology.” You could start an internal mailing list and just mail out stuff via email that relates to this. There are lots of ways to do it. I guess the short brief answer to your question how do you change your positioning, you validate it and then you just start doing it. You just start acting as if you’re the guy or the gal. Reality catches up with you in about three months if you do that. It doesn’t take long.

Carlos: I think we have enough people in the workforce that are content to just do what’s given to them. You’re going to immediately create value by being the exception of doing something that’s not required of you or you’re not being asked.

Philip: People will notice almost, I mean not overnight, but very quickly you’ll be the nail that sticks out. There’s a saying about that in a good way.

Carlos: Sure. I think we have a couple of examples at least from the community. I think about Mike Fal who was on our podcast earlier. He made just a conscious effort to start communicating about PowerShell. He wanted to get into that and how DBAs could take advantage of that. I think a lot of people…He branded that for himself.Now I think if you asked a lot of people in the community “What do you think about when you think about Mike Fal, they’re going to say I think about PowerShell.” With Paul Randal, he does lots of different things, but I think his initial claim to fame was database corruption. He was a database corruption expert and could talk about that ad nauseam. They do other things. That’s not all that they do. Because he became known for that, people went to him for that. Other services, other ideas, he could present those as well.

Philip: I’ve got to say that that happens every time. If someone decides to specialize in something, whatever it is, they continue to get other work, other requests come to them. It’s not like they’ll never get a request to do something outside that specialty. I think that translates pretty well also to an employee.I just want to add that it is very common for people to say “Oh, God, I’ll never do anything other than this thing I’m specializing in again,” and that is almost never true.

Carlos: Companeros, your first positioning assignment is to position yourself so that you can get onto SQL Cruise next year. Tim and Amy Ford have put together a most unique training experience. If you’ve listened to this podcast before, you know that I’ve been on there twice. Listeners of this podcast get a hundred dollars off the cost of admission.You can get those details at sqldatapartners.com/sqlcruise. Learn. Network. Relax. Grow…Just Add Water. Check out sqlcruise.com for more information if you want to know more about that cruise. Philip, the SQL Server community we have what we call SQL Family, and now we’re going to welcome you into part of that. In this part of the program, we like to talk about you for just a moment and how you work a little. One of the things I like to talk about is how people get things done. I’d like to talk with you about your favorite tool.

Philip: Nice.

Carlos: Generally, we talk about SQL Server tools. This can be any tool. What tools do you use to get things done to help you? This can be a free or a paid tool. Why do you like it? What does it do for you?

Philip: Awesome. I imagine SQL Server is involved somewhere with this tool because software is a service tool. Maybe not Microsoft SQL Server but some kind of SQL Server. Anyway, the tool that has been just like magic for me is called Calendly, C-A-L-E-N-D-L-Y.com. Calendly hooks into your calendar. It’s got to be like a Cloud-based calendar for this to work.I think it works with Outlook and Google Calendar. It allows people to book time on your calendar. For somebody like me who is working with clients and appearing on podcasts and stuff like that, it’s a lifesaver because I don’t have to do the email dance back and forth to try to coordinate a time to meet.

Carlos: Time open.

Philip: There’s another one that’s similar. It works a little differently. I’ll mention it, too. It’s called assistant.to. Assistant like Virtual Assistant but assistant.to. It plugs into Gmail so it’s a little less flexible than Calendly, but it lets you propose times.It goes to your calendar and looks up available times, and then you can propose times to meet and someone just clicks a link and boom, everybody gets calendar invites. It’s amazing. [laughs]

Carlos: Yes, there you go. Scheduling that time can be very, very helpful.

Philip: Indeed.

Carlos: Philip, you just inherited $1 million. What are you going to do with it?

Philip: Oh, thank you.[laughter]

Philip: Where do I get the check?

Carlos: You can talk to our producers after the show. I’m sure they’ll hook you up.[laughter]

Philip: This is going to sound super selfish. I would pay cash for a house. I live in a place called Sonoma County, which is in California. The part of Sonoma County I want to live in would require most, if not all, of that million dollars to buy a house.[laughter]

Philip: I don’t think there would be much leftover. Selfish I know. That’s exactly what I’d do with that.

Carlos: There you go. You’ve positioned yourself. You want it. You know what you want to do with it.

Philip: … [laughs] that’s right.

Carlos: Philip, you’ve been in different positions, you talked about being a Microsoft Certified Trainer, now you’re a Consultant, written a book. What’s one of the best pieces of career advice that you’ve received?

Philip: [jokingly] The client is not always right [laughs] . It’s hard to say this without sounding like a jerk, but sometimes saying “no” to clients, “A”, when it’s going to be bad fit. That was something when I started out, I just was trying to find any way to say “yes” to any kind of client, that resulted in a lot of painful situations.If I had wanted to say “no”, really out of their best interests, that would’ve been a good thing. Then also, sometimes clients are just wrong about what’s the right technical approach for something, or what is the right strategy. That’s why they are hiring outside help, is they don’t know, but once they spent that money some clients can get nervous about the investment, and then they’ll get “micro-managey”, which is how a lot of us react to being nervous. Those are cases where you maybe need to start saying “no”, when you’re not used to. “The client is not always right”, is maybe one of the best piece of advices I’ve gotten. [laughs]

Carlos: I wonder if we could flip that back to our conversation about “positioning” and that is, I think that technology, the whole idea of being “pigeon-holed” for example, is that the Manger or someone will come to you and say, “We need this technology to work.” And like we mentioned, if we can figure out “OK, why do we need to do that? What’s the problem you’re trying to solve, right?”Instead of going so much where the technology, if you’re willing to tackle the problem instead. You can, potentially avoid that technology you don’t want to deal with, and potentially do something else.

Philip: Exactly, yeah. That’s probably the second most valuable question you could add, or first most valuable question. Maybe the second most valuable word you could learn is “why.” Why are we going to do that?

Carlos: There you go.

Philip: It generally leads you to a more productive conversation about what needs to happen next?

Carlos: Right. [pause] Our last question Philip. If you could have one Super Hero power, what would it be and why do you want it?

Philip: I’d like to fly. I hate driving.[laughter]

Carlos: Very nice, especially in California right? It can get backed-up pretty quickly.

Philip: It can for sure.

Carlos: Very good. Philip Morgan, thank you for being on the show today.

Philip: My pleasure Carlos, really enjoyed being here.

Carlos: We will have links to the content. We’ll have links to the Show Notes so the content we’ve talked about today at sqldatapartners.com/podcasts and of course, Compañeros. Hope you’ve enjoyed today’s session, I invite you to leave a review or comment on items or Stitcher. This will help others find our program more easily.We do appreciate you tuning in again, if you have specific questions, you want to reach out to me on Twitter I am at carloslchacon, or you can shoot me an email at [email protected] and I will see you on the “SQL Trail.”

Episode 34: In-Memory OLTP

1400Hello, Compañeros! You’ve heard of Hekaton and in-memory OLTP before, but what does in memory really mean for your database? Perhaps you have questions like I did, like how does in memory OLTP differ from disk-based tables? What data types aren’t allowed in in-memory tables? Is in-memory only an “on premises” feature or does Hekaton work in Azure too? Is there a minimum amount of memory required for in-memory tables? In Episode 34 of the SQL Data Partners Podcast, I’m going to talk about in-memory OLTP and in-memory tables with Jos De Bruijn. Jos is a Program Manager at Microsoft for SQL Server In-memory OLTP. I chat with Jos about how in-memory tables work and why they’re just as durable as traditional disk-bound tables. He lays out the whole process. Jos also answers common DBA concerns about them and discusses how to make them efficient and reveals the SQL Server 2016 features that affect in-memory OLTP. After that, we explore Jos’ favorite business tools and listen as he talks about what he’d do with a million dollars, what his favorite superhero power would be, and what advice he’d give to anyone in the SQL Family. Episode 34 Topics:


  • How in-memory tables differ from disk-bound tables
  • How in-memory OLTP works with transaction locks
  • Database recovery with in-memory tables
  • New changes in SQL Server 2016 for log types, foreign keys, unique constraints, subqueries… and more
  • The differences between SQL Server 2014 and 2016…and why you want to look at 2016
  • The “shock absorber” scenario and why in-memory tables work well
  • Jos de Bruijn’s favorite tools in his role as a Program Manager at Microsoft

About Jos Jos de Bruijn is a Senior Program Manager at Microsoft. Originally from Holland, he now works with Microsoft to further develop the Hekaton project. He worked in academia as a professor and researcher before joining Microsoft. His main focus is on query processing, programmability, and transaction semantics for memory optimized tables. Previous Microsoft Ignite sessions include Microsoft SQL Server Unplugged and In-Memory OLTP deep dives. Find him on Twitter at @jdebruijn.

Transcription: In-Memory OLTP

Carlos:  Jos, welcome to the program!

Jos: Thank you!

Carlos: Thanks for tuning in from Seattle. I guess it’s still a little bit rainy out there, as normal, but we’re glad you took time out to visit with us.

Jos: It is indeed rainy, but I’m happy to be here and happy to talk to the SQL Family.

Carlos:  Yes and we do appreciate that. So ultimately today we want to talk about in-memory oltp feature. Now this feature has a couple of names that we need to tag down. I’ve seen in-memory OLTP, in-memory tables, memory optimized tables, right? Or the new “go faster” button in SQL Server.

Jos: I like the go faster button, let’s go with that.

Carlos: All of these have been used to describe what we’re going to talk about here today.

Jos: Oh, you’re forgetting “hekaton”.

Carlos: How could I have forgotten hekaton?

Jos: Hekaton was the original code name of the project. Actually, it’s still the code name we use internally for development of the features that we talk about publically.

Carlos: So that was one of my questions. So in 2016, you still refer to it as “hekaton”?

Jos: At least internally, yes. We call it “hekaton v2”.

Carlos: Okay, very cool.

Jos: It is basically enhancements to in-memory OLTP.

Carlos: Ah, so why don’t you take us through that? Kind of give us a tour of what in-memory OLTP is and how folks are using it?

Jos: there’s a number of ways you can look at it. So, you can look at it purely from what features you get in your database. There are a number of things you get: you get the memory-optimized tables, as we’ve mentioned. You also have the memory-optimized table types, which not everybody always thinks about, which can be used more broadly than memory-optimized tables.

Carlos: We’ll want to dig into that, because I thought that you created the memory-optimized database, and then you created the table using those partitions. It sounds like you’re talking about something even different than that.

Jos: So, okay, let’s take a step back. Table types are the structures for table variables and table-valued parameters. These are programming constructs that you use typically with your stored procedures often to send bigger tablesets to the server. Table valued parameters are where you define a table type in your database.  You can use traditional table types or an in-memory optimized table type. And the in-memory optimized table type lives in the memory space in the user database. It does not live in tempDB, plus it uses the new memory optimized data structures for more efficient data access.

Carlos: Okay, there we go.

Jos: The memory-optimized tables themselves, there is not really any partitioning that comes into play. Basically, memory-optimized tables are just user tables that, from a definition metadata point of view, they are user tables that have the option – the flag- is memory optimized. That’s from a metadata perspective.

From a storage perspective, these tables are stored in memory, so memory space is the main storage location. And actually, this memory space is carved out by the SQL Server exe process. They live fully in memory, so all of the user memory-optimized tables must fit in memory. But because we know that the table lives in memory, we could develop more efficient data access methods and more efficient storage methods for these tables.

Carlos:  So part of this is where we get into the latching and locking mechanisms that were previously used from disk-bound tables, if you will. We can do that differently with in-memory optimized tables.

Jos: That’s right. So with memory-optimized tables, we want to first leverage the fact that these are just in memory. So we can have more efficient data access methods. But then the second aspect is, well, we know that there are looking and latching bottlenecks in highly concurrent database systems. If you have a high throughput OLTP system, you have lots of transactions running at the same time. They try to access the same data structures, the same pages, they take latches, they start waiting for each other, you’re just spinning…not doing anything… you’re waiting…

Carlos: A queue starts…

Jos: You add more cores to your server and your performance goes down because of the latches! So, the core algorithms that we developed were in-memory OLTP. The core transaction processing algorithms are all completely lock free. The algorithms themselves were crafted with all the latest advances in high performance computing, lock-free computing, to develop truly lock-free algorithms. So there’s not even a single spin lock in the core transaction processing.

Carlos:  Wow.

Jos: And we use them on top of that, combination of multi-version concurrency control and optimistic concurrency control. So with multi-versioning, instead of updating a row in place, we write a new row.

Carlos: Just get a new row.

Jos: Always read the old row and optimistic concurrency means that we observe that in most cases, we don’t have to write update that exact same row. It’s going to be rare. That’s kind of an assumption of optimistic concurrency.

Carlos: Right.

Jos: So you can just go through, you don’t need to take a lock. You do detect, of course…it can happen that there is conflict. You detect it at that time, and one of the transactions will fail and it will need to be retried.

Carlos:  I see. It might be worth talking about how that differs from a disk-bound table in the sense that when you write to that, the first thing that has to happen is that it has to write the log file. Right? So once it writes the log file it comes back, and it says, “Ok, now I’m in memory”. And then a checkpoint could happen, and then I can write to disk.

Jos:  That’s right. So the logging works somewhat differently with memory-optimized tables. So first off, we only write to the transaction log at the transaction commit. So we don’t have to undo log records.

Carlos: Okay.

Jos: So with tradition tables, undo log records are maintained such that if you make updates to the data page they get flushed to disk, but if the transaction eventually does not commit, you have the information in the transaction log to roll back exchanges.

Carlos: Right.

Jos: With memory optimized tables, the data is all in memory.  It doesn’t get flushed to disk. And we don’t write any artifacts of the transaction to disk until the transaction actually commits. So we make all the changes to memory, write the new row versions, mark the old versions as deleted, and at the end of the transaction we compile the log records and write that to the transaction log.

Carlos: Okay

Jos:  So it only has the redo information. We never need to undo anything.

Carlos: Almost like a bulk operation.

Jos: Correct. In some sense, yes, you could see it like that. And then we write to the transaction log when the transaction commits, so there’s only one log record so it’s already more efficient. Second, the indexes of memory optimized tables only live in memory, so they are not logged. So there we also get some efficiency.

Carlos: Right.

Jos: We write to a transaction log and it’s flushed to disk. So you do have the usual durability guarantees of transactional memory optimized tables. The fact that the memory is the main storage doesn’t mean that you have a problem if the server fails over. It is all fully logged. There is also a mode where you can say, “I don’t want to have it logged for certain tables.” But the default that you want to have is that it is fully logged for all your tables. So, we commit, we force flush the log record to disk, and only then do we give the control back to the user. So you have the exact same durability guarantees as you have with traditional disk-based tables.

Carlos: Let me just make sure that I understand what’s going on. So, while the table lives in memory, it’s still going to write to the log. Then, behind the scenes there is a disk presence, if you will, of this in-memory table. So that in the event that the server gets turned off, or failover, or high availability, or clustering, that’s where it would read from and roll back any transactions… which I guess wouldn’t happen with any in-memory situation.

Jos: There’s no real undo there. I mean, you don’t replay the whole transaction. But yeah, you mentioned in detail that there is a persistent copy on disk. This persistent copy is maintained in what we call “checkpoint files”.

Carlos: Okay.

Jos: So, when a database checkpoint happens, a number of files on disk are designated as saying, “Okay, these files belong with this database check point. So when you run database recovery for a particular checkpoint. So when you run the database recovery, you always pick the checkpoint as of which you are going to run this recovery.

Carlos: Right, there we go.

Jos: So you’re going to run the checkpoint, so you’re going to actually load the data from the data files that belong to this checkpoint. Those are all in memory, as of this checkpoint, you …

 Carlos: You’ll see that when the database starts you might see that in your error log. It’ll say, “Hey, I’m reapplying this checkpoint process.”

Jos: Yes. So I think that is an important thing to keep in mind. During database recovery, you do load all the data in memory from disk.

Carlos: Right

Jos: So it depends very much on the configuration of the database. But very often you’re going to be very IO bound. So, the faster your IO subsystem, the faster you can load the data into memory and the shorter the recovery time will be. If you’re doing basic server restarts or something like failover clustering, then you need to worry about how this is going to affect your RPO.

Carlos: Now, so with a traditional table, right, when you create the table, at least one page gets created to say, “Hey, here’s my table.” Does SQL server default to, “You’re going to create this in memory, I’m going to carve out a chunk of reserved space for this table?”

Jos: No, there’s no reserved space for tables. We reserve some space for indexes. For tables, maybe there might be like a small block of memory. Yeah, I think we do reserve like a very small portion of memory for the base data structures so we can actually start adding rows. But this is like maybe 60kb or something.

Carlos: Right, okay.

Jos: It’s not really a significant amount so there’s not really any pre-allocation of memory for tables that we do.

Carlos: So one of the big fears that I’ve heard from some of the other companeros, the other data professionals, is that, “Oh, great, the developers now have in-memory tables. They’re just going to slap everything into memory and we’re done.” So what are the considerations that organizations need to look at for in-memory tables. So good candidates for in-memory tables are obviously, lots of transactions would be up there. But that would also mean that that’s a very large table.

Jos: You do need to be careful from a management standpoint in terms of memory utilization, because these tables that you designate for memory optimized they must live completely in memory. So you must have available memory so we have enough. So in your capacity planning you plan capacity for disk storage, and in this case you plan capacity for memory storage.

Carlos: Okay.

Jos: You would have the same capacity considerations, basically, but typically you’re using a subset of your tables. Now we do give you some tools to work with in terms of memory management. So what you can do is you can create a resource pool and combine your database to that resource pool. And you can define a maximum memory percent and also a min memory percent for a given resource pool.

Carlos: Okay.

Jos: So it is best practice to bind your database to a resource pool and give it a certain maximum memory percent, so that if you run out you can always temporarily increase it to get some head room and to also insulate other processes running on the server from your activity. Because memory optimized tables are eager, because it is your data, we are never going to give it away unless a user does an explicit DELETE or DROP.

Carlos: Exactly. So in that scenario, I’ve set up my resource pool, and let’s say for easy numbers, let’s say that I have a gig of memory that I want to allocate to this. Once I hit that threshold, do I just get failures on INSERT or what’s the behavior?

Jos: Yes, you’ll get failures from inserts. So you’ll need to have enough capacity. Same if you run out of disk storage space. If your transaction log runs out of storage space, all DML will start failing.

Carlos: Right.

Jos: You need that available storage space.

Carlos: There was some old functionality for  DVVC pin table, 2000…. you can take the pages and stick them in memory, right? If you didn’t have that resource pool, would you in theory run into that situation? Again, smaller environment. Let’s just say that you have 16 GB of RAM and you create an in-memory table and you take up all that allocation. Your server just goes down, right?

Jos: It does not go down, because we will never take all 100 percent. We go up to a certain percentage, in this case the depo pool. But there is always some headroom for basic server operations.

Carlos: There you go.

Jos: And if things go really south, there’s always the dead connection. We always reserve memory for the dead connection so you can always go in there.

Carlos: Very good. That is the, “save me from myself”!

Jos: Yeah, yeah, exactly.

Carlos: Very good. So are there any data types that aren’t allowed in memory tables or restrictions from that perspective?

Jos: So, yeah, when thinking about restrictions, some of hte folks may be familiar with the feature in 2014 or may have heard of so many restrictions that are there, like, no foreign keys, row size limitations, no native decompiles or stored procedures, and a whole bunch of other things. No alter tables, etc. So we did make some really big gains in terms of functionality that we now support with SQL 2016. Like, we added support for log types, foreign key check constraints, unique constraints, big row sizes, alter table, alter procedure, OR, UNION ALL, SELECT DISTINCT, subqueries, and a lot of other stuff.

Carlos: Okay, very cool! So basically, the version 2 in SQL Server 2016 is the, you know-

Jos: It is the proper ship vehicle for in-memory OLTP v2.

Carlos: Very cool.

Jos: Of course, there are a lot of other goodies in SQL Server 2016, but I think we made a huge leap in terms of the features that we now support. So I would encourage anyone listening who heard about the features in 2014 and heard about all these limitations to take another look at 2016 because I feel like it is really much more full featured. I mean, there are still some pieces missing that we really need, like for example, sp_rename, line alter table, case support for native modules, but you will see that a lot of the gaps that existed before have been filled in the meantime.

Carlos: Yep. Now, we didn’t talk about this earlier, but do we think of in-memory as an on-premises feature? Meaning, so if I’m in Azure SQL database, is there the concept of in-memory tables?

Jos: Yes, so right now in-memory OLTP is in public preview for premium databases of SQL Server Database. We also have the limitation that you need to create a new database that needs to be stand-alone in the elastic pool. But we will be removing these limitations over time and we will be adding support for previous databases for elastic pools and for standard databases as well. So over time we will be covering a bigger spectrum of databases. But you can kind of start playing with it today.

Carlos: Right. So you mentioned some guidance there. Is there a minimum amount of memory that you’re seeing your customers say that they’re using before they try to engage with in-memory tables?

Jos: No. It really depends on the application pattern. We didn’t talk too much about application patterns, so maybe this would be a good point to talk about that.

Carlos: Let’s do that.

Jos: So there is the core, like, high volume transaction processing where you can think about order processing, about stock market sales, foreign currency exchange which some of our customers are doing. That is where you have relatively big volume tables and these are your core transaction tables and you put those in memory. So you need to size accordingly for those. But this is definitely a scenario very suitable for in-memory OLTP. If you think about financial instruments, think about gaming, in these industries latency is very important so you want to have low latency and predictable low latency. And we can make the latency very predictable because we don’t have locks.

Carlos: Sure.

Jos: Because with traditional tables, if there’s a spike in the workload you will get locks and latches and you will get this contention and then you will also have a spike in your latency. So maybe your latency is normally 10ms, all of a sudden it jumps up to 200ms.

Carlos: Right.

Jos:  Some there in-memory OLTP helps tremendously. There are also other scenarios where you can think about more limited user, like smaller tables. For example, let’s say some reference tables that contain metadata for your application that would be updated very frequently. Like, for example, you are a hoster and you want to maintain which user has which VMs on which of your physical machines in your datacenter. These things can be updated quite frequently and need to be accessed frequently and very fast and will have relatively small tables but with high volume. So that’s something to think about.

Another use case we see very frequently is what we call the “shock absorber” scenario. So “shock absorber” means that you have a memory optimized table that is used for the data ingestion. So you have a highly concurrent INSERT workload. For example, think about sensors.

Carlos: Sure.

Jos: You have weather sensors, sensors in your windmills, for example, if your windfarms have sensors are insert into the table highly concurrently. The memory optimized table can deal very well with highly concurrent INSERTS. And what you then do is periodic batch offloads to a traditional table, probably a clustered columnstore index because you want the space savings and the fast analytics queries. And this batch offload is usually much more efficient than the highly concurrent inserts. With this you can actually absorb, because often you have thse kind of shocks where at some point you have some steady flow but then all of a sudden there’s a big shock of data coming in and you can absorb these shocks. Some of our customers are doing that successfully. And a few gigs is enough.

Carlos: Exactly. You can manage that much easier.

Jos: Yeah, there are customers that just have the stored procedure with sql agent in a loop to just continually offload. And you keep your table really small while you are able to ingest a lot of data.

Carlos: Right.

Jos: Another scenario that I think is also very interesting is one customer, EdgeNet, they have a reference table with a data about items that are in shops. They maintain a table that is used by search engines like Bing and Google to find hardware items. You search for hardware items, you quick find a price, location, and store quantity for the physical stores. So what they have is they have a query workload and then they have batch updates that come from their customers. They send them batches once in a while, so that means that with this batch update they need to update the pricing and stock information for almost all the items in the table.

Carlos: Ah.

Jos: With disk space tables, this would take forty minutes and there would be a lot of lock contention and read workload. So they actually built a caching layer to allow cell querying while the memory optimized tables, there is no contention. The updates run in four minutes instead of forty minutes so there is a 10x improvement and they could remove the caching layer completely.

Carlos: Oh wow.

Jos: So, just remove the caching layer.

Carlos: Like a hybrid of the two, right? So you’re using both of them just to do different things. I update here and select from there.

Jos: Yeah, yeah. So this is actually, I think, this can be very powerful. Because there’s a simplification in your architecture in your deployments. That you remove servers. Another powerful example that I often use, another customer uses it for a session state management. For the session state from the web servers. So they scale up the web farm and the session state is maintained by the database. They have a high volume of the websites and they used to have 18 SQL Servers, 18 physical servers running SQL Server. So they had to partition it manually. With in-memory OLTP, they consolidated that to one server.

Carlos: Wow.

Jos: So that is tremendous savings that you can gain. Essentially, you can think about this technology as a way to get much more performance out of your existing hardware. By removing friction you can scale linearly with the number of cores and by shortening the code paths through the memory-optimized algorithms and native compilation. You use fewer CPU instructions for your transactions so you can cram more transactions into a single second running on a single core on the machine.

Carlos: Now in that consolidation example, does that mean that they basically took all the memory from those 18 servers and tacked it into one?

Jos: Their memory footprint is not big.

Carlos: Interesting.

Jos: I don’t know if I could quote an actual number.

Carlos: But it isn’t so much that they took all this memory, but the fact that the speed of the in-memory table behaves so differently that here are efficiencies there.

Jos: Yeah, yeah, yeah. It easily fits in this simple midrange server. It is not high volume of data but high volume of transactions in this case.

Carlos:  Very cool! Well thanks for the overview of in-memory tables. Always good stuff, and I think we’ll make sure that people can reference some of the things you talk about in the Show Notes at SQLDataPartners.com/podcast. So Jos, before we let you go, let’s do SQL Family.

Jos: Okay.

Carlos: So here, we like to get to know you a little better, how you work, what makes you tick. One of the things like to talk about is I like to see how other people work. So I want to know what your favorite tool is. Now generally we talk about SQL Server tools, but as a Program Manager maybe you’re doing some other different things. So what’s your favorite tool? Free tool, paid tool, why do you like it, and how does it help you?

Jos:  Two things come to mind. Outlook and OneNote. I use them both extensively and they serve my needs. With OneNote specifically, I really like the flexibility that you have. You can put any content in there, add any attachments, very easily reorganize structures, and it’s very easy to share with others as well as collaboratively easily with OneNote.

Carlos: Very cool. Yeah, about the collaboration. We’re actually looking at using OneNote as like a wiki type integration. We haven’t done the plugin to SharePoint yet, but I’ve been looking at that and it’s super nice to be able to have documentation to share and everybody knows where to go to.

Jos: Absolutely. It’s really helpful. I mean, in our team we use it a lot for reference information. It’s much easier to find things in OneNote than in a folder structure filled with Word documents.

Carlos: No question. The search feature alone is the huge reason to use that. So, you just inherited one million dollars. What are you going to do with it?

Jos: I’m afraid I’m going to give you a very boring answer. I would pay off my mortgage…

Carlos: There you go, smart man.

Jos:  I would put a lot of it in savings, an investment fund or something like that. And one thing that I might do, that I have been thinking about is picking up sailing again. So maybe I might look into that, some sailing courses. And if I like that…I’m not sure if a million dollars is enough to really buy a boat and maintain it, maybe.

Carlos: Maybe a small sailboat.

Jos: Yeah.

Carlos: Very cool. So, originally you’re from Holland, right? You’ve come over from Microsoft, you’re in Redfin now, and been working with them. I’ve had some different experiences, what are the best pieces of career advice you’ve ever received?

Jos: The best piece of career advice. Yeah, I cannot really pinpoint specific piece of career advice that I have been given, but if you would ask me for a piece of career advice: when you don’t like something you’re doing, stop doing it.

Carlos: There you go.

Jos: That is what I did with my previous career was in academia. I was doing research and lecturing and there were some aspects I did not enjoy about it. So I decided, “Okay, now I need to do something completely different.”

Carlos: Very good, very good. Yeah no, that’s great advice. If you’re not happy about it, stop doing it. I like it. Our last question for this episode: if you could have one superhero power, what would it be and why do you want it?

Jos: What would it be? I don’t know, flying would be cool but if I think about the practical implications of flying I’m not so sure I would be happy with it. If you go too high up and you get too cold. You can collide with airplanes.

Carlos: Yeah, you’d have to register with the FAA.

Jos: Yeah, yeah you bump into the Amazon drones.

Carlos: So, maybe flying’s not your thing. Is there anything else you can think of that you’d enjoy more?

Jos: No, nothing really comes to mind, sorry.

Carlos: Okay, very good. Well Jos, thanks so much for being on the show. It’s been great having you.

Jos: It was great to be here, thanks for having me.








Episode 33: Moving From The Developer Role to the DBA Role


Show Notes

In this episode of SQL Data Partners, I chat with Amy Herold about making the move from development to database administration. Amy’s presentations at SQL Saturdays include Deadlock, Block & Two Smoking Barrels: Breaking Down Blocking and Deadlocks and Making the Leap from Developer to DBA. Amy started as a C#/.NET developer and moved into the database world as an “accidental” DBA.

Amy shares her tips from the trenches, including:

  • The transition from row-based thinking to set-based thinking
  • How to manage developer access in production
  • The database performance activities new DBAs need to master
  • The importance of getting code reviews outside the developer team
  • The tool Amy relies on as a DBA, and the one event you shouldn’t miss
  • Why Amy believes that changing jobs frequently can be good for your career

About Amy
Amy Herold is a SQL Server DBA, instructor, and PASS speaker. She began her career as a .NET developer but eventually moved to SQL Server Database Administration, where she is currently pursuing her MCSE in SQL Server. Amy is a regular attendee of SQL Server User Group meetings in North Texas as well as SQL Saturday events across the US.

Amy on Twitter
Amy’s Blog
SQL Sentry monitoring
SQL Cruise
Making the Leap from Developer to DBA, 24 Hours of Pass

Transcription: From Developer to the DBA Role

Carlos L Chacon: This is “SQL Data Partners” podcast. I am Carlos L Chacon your host and this is episode 33. Compañeros, welcome to the show again. Thanks for joining us on the SQL Trail.

This podcast is dedicated to SQL Server related topics designed to help you become more familiar with features around SQL Server and how you might use these, or ideas and how you might apply them in your environments.Today we are talking about making the change from the developer role to the DBA role, and how those roles or responsibilities differ between the two positions.

My guest is Amy Herold, a database consultant with Fortified Data. Today’s episode is brought to you by SQL Cruise. Learn. Network. Relax. Grow. Just add water.  Check out sqlcruise.com for more information. Compañeros, always good to have you, welcome to the show.

Amy, thanks for being here today.

Amy Herold: Thanks for having me.

Carlos: One of the things we wanted to talk about was the roles and responsibilities, the differences between the DBA and the role of the Developer. Now, as you were coming up or began your career in IT, you started on the developer side, is that right?

Amy: Yes. I stared out as a C# and ASP.net developer. I only came into SQL Server when I landed a particular position, and they asked me if I could write SQL Server Query and sure, yeah. I hadn’t written one before, but it’s code, I can write code so ”Why not, I can do this.”

Carlos: There you go.

Amy: And just pretty much dove in head first. That’s my approach to many things.[laughter]

Carlos: There you go, roll your sleeves up and get to work.

Amy: Exactly.

Carlos: I think it’s a familiar track for a lot of Developers, in that they’re on application, they’re dealing with the back-end, and then all of a sudden that back-end becomes a little more a real deal and more attention needs to get paid to it.One of the first things that you talk about in some of your discussions or your presentations about this topic is the idea of data integrity or availability.

Amy: Yeah. One of the things that I talk about in my sessions is data integrity and availability. Someone told me once that as a DBA, ”You are the keeper of the Gold,” and the first answer when somebody says, ”I want to see the Gold,” your first answers always ”No.”

Carlos: [laughs] Interesting.

Amy: They ask again. Second answer is always “No.” When they come back a third time with a more refined response and they say, “I want to see this piece of gold, I just want to look at it.” “OK, you can look at the gold. You can’t modify the gold — you can’t touch the gold, in any way you can just look at the gold.”

Carlos: Yeah. So, it’s interesting, Ron Deskins who’s one of our compatriots here reach-on Virginia with our user groupie, he talks about the differences right between the developer and the DBA. Ultimate organizations bring in a developer, because they want to change something.As you just described, as a database administrators, a lot of times they want us to keep it the same. [laughs] As though there is kind of these two competing theories or ideas there. Sometimes that can lead to a little bit of budding of heads.

Amy: Yeah. I have budded heads a numerous times. Such as in over permissions or what having though, and it’s your whole world and I get where they are coming from. As a DBA, my responsibility is to make sure that data is there and available for the business.If I grant out too much in the way of permissions to production and a developer takes celebrities. That can prove problematic and quickly becomes an issue. The next thing I know I am stuck having a nice late night restoring a database. No fun is had by anyone.

Carlos: Now surely in only giving the read access you are talking about just to production environments, that wouldn’t necessarily apply to other environments. Would it?

Amy: Oh it just depends. It depends on the company, it depends on the situations. I cleaned house in the last place I was at. I’d have a very long day and I’d run into several developer created issues, and towards the end of the day there was an issue in production.Some data had apparently been modified at some point. I also couldn’t get a backup from that time period. That was the other problem that company had. They don’t keep their backups very long.

So, I couldn’t get a backup around that time period, I was upset by that. But anyway, the data had been updated. I had my suspicions on how it possibly have gotten updated. Suspicions are nothing, I have no concrete proof, so I just went in and cleaned house, both in production and pre-prod.

I’d had enough problems that day in our pre-prod stack that I was done, [laughs] and nobody’s breaking replication anymore.

Carlos: Yeah, there you go. I think that’s a good point. As you mentioned, that responsibility for availability does lie with the database group. I think the idea of formalizing some processes around how that data would get modified or changed is definitely in the wheelhouse up there.

Amy: Yes, absolutely. The only person making any changes in production should be the DBA, in my opinion. No developer should have access to do anything like that. At the very most, they should only have read access in production, even if that could be for a limited amount of time. It depends on the production environment.Is your environment heavily transactional? Is somebody getting in there and running all kinds of query, reason running amok? How is that going impact your production environment?

Carlos: Or sensitive data, things like that.

Amy: True. Yes, or a sensitive data PII. If that’s viewable, then yeah, access definitely needs to be limited.

Carlos: You had mentioned backups there, and I think part of this process…A, number one availability, making sure you have a backup. This is where I think developers can provide a lot of value or can get up to speed very, very quickly when it comes to the database administration tasks, and that is taking your knowledge of coding, applying to best practices, and then using that in some of your administration tasks.

Amy: Oh yeah, definitely, absolutely. Scripting out tasks and making things. As a developer you already have that coding background. You have a strong foundation for being a DBA and being someone that can script out things and automate things, and essentially make your life easier as a DBA.

Carlos: Right. I think, as the role of the DBA continues to change, nobody wants a DBA that just does backups and restores anymore, right?

Amy: No, no.

Carlos: More and more, you’re going to be asked to do more and more things, so being able to very quickly get up to speed and just insure that they’re running, that they’re working, and then being able to move on to other things.

Amy: Exactly. Yes, exactly.

Carlos: The one area that we had discussed — that developers might need a little change in thinking — and that is, when it comes to set-based theory in their scripts or in their logic.

Amy: Yes. It’s definitely a change for a lot of developers who may primarily be doing, say, C# programming or something along the lines where they’re not used to doing set-based development. They may turn around and write something — write a cursor, and do something within a cursor that, if they gave it some more thought, they would find that this could be set-based.I had one situation where I had a process that was running. Not only running overnight. It was part of a job. There were scripts that were called from a job and would run overnight for hours and hours and hours, but that it would also…The same code was called from the website, and it was causing websites timeout in certain cases.

I started looking at that. There were cursors and cursors, could have been even another level in of cursors.

Carlos: Yikes.[laughter]

Amy: Yeah. First I revised that to…I put in while loops instead. That got a little better, but not great. From there I started looking at the code some more and removing the looping altogether, making the entire thing set-based.

There were a couple sections where I wound up writing correlated sub-queries that ran really fast and basically brought that whole thing together. I think probably the last thing I wrote for that.

The process ran. Obviously the website was timing out, so it was running it over a minute. I got it down to, I want to say, 10 seconds. Maybe it was less than that. The developer who wrote it was like, “How’d you do that?” and came back at me. He was very proud of his work, too. He touted it in a meeting.

Carlos: Sure.

Amy: Was like, “I wrote that.” I’m like, “That…

Carlos: It’s kind of complicated to think about it in terms of rows, I think, generally, is what developers tend to kind of lean towards that.

Amy: Yeah, they do. It’s not necessarily that way. I was working on some code recently that was part of a cursor. There was a store proc and it was pulling this massive dataset and then passing the rows from that dataset one at a time to another store proc which had a cursor in it. Oh, it was ugly.I was refactoring that and it got me thinking about some different things. I wrote some things in ways that I hadn’t thought of before.

Carlos: I think it might be important to notice and think that change is usually thinking about columns instead of thinking about rows.

Amy: Yeah.

Carlos: That, to me, has been the best concept or idea when it comes to set-based theory.

Amy: Yes, I hadn’t thought about it like that, but, yeah, that’s definitely true. You’re only pulling back the data that you need in most cases. Depending on the data that you’re pulling back, how can you group that? How can you pull that together to get the same results in a set-based query versus running it one at a time from a blue broad cursor?I feel like many times it’s a matter of thinking outside the box and thinking in terms of doing it set-based, just to get there.

Carlos: One of the next components that the DBA is in charge of that’s not necessarily in the developer’s wheelhouse. That is the underlying architecture of the system. Sometimes, if we don’t necessarily think about the capacity planning aspects of an application, how big is this thing going to get? How many users is it going to have?

Amy: Yeah, and that’s something as a DBA you have to think about. You have to ask those questions ahead of time, be it an internally developed application or a third-party application that the company’s purchasing. Can they give you good numbers on what’s going to be needed six months from now, a year from now?Also, as far as capacity planning, getting a read on your databases and your growth and things like that, and tracking that information over time. End of the year, when someone comes to you and says, “We need to find out how much space we’re going to need for the next year,” you at least have some basis to give them, “OK, we group this much over this year, now, so we need at least this much space next year, maybe a little more.”

Carlos: Right, exactly. In another episode, talking with Erin Stellato, she talked about the backup sizes being an indicator of that. That’s an easy way to tell the growth of your environment.

Amy: Yeah, I hadn’t thought about that way. Yeah, definitely, if you track your backup sizes. I guess if you’re keeping…Yeah, that’s one way. I would say you’d definitely have to track that just depending on how long you keep your backups.

Carlos: Exactly, right. Even looking at the history, and I guess if you’re not trimming that up. I think we talked a little bit about baselines, recording some of that data, and it just that — taking a…[dropped audio]

Carlos: …a quarterly basis or what have you, and getting, “OK, what are some of the stats for my environment right now?”With that capacity planning, and we talked a little bit about some sizing, it would include CPUs, things like that. Then we also kind of get into the requirements around performance, we had talked a little bit about just even changing that from row-based to set-based theory, but there are some other performance requirements for the DBA must adhere to.

Amy: Performance is always important in an enterprise environment. You have indexes. You have index fragmentation to deal with as a DBA and taking care of that. Statistics, making sure those are up to date. You may have poor performance if your stats are out of date. It’s not just code.My primary focus when looking at performance, in many cases I’ll look at the code first. Most of the time, it’s bad and needs revision, or can be redone in some way, making it better.

Then there is the indexes to consider. Maybe you’ve made a change. It could be a small change, and the index that exists already may not be able to handle that. You may need to modify that index.

Yeah, performance is a big topic. Also, on performance, it’s not just code. It’s not just indexes and stats. You have hardware to consider, CPU memory to consider. There’s just all different aspects to performance. That’s the very large topic. I think most DBAs don’t go past the indexes and the stats, and that is even if they get to coding.

I’ve talked to many DBAs in interviews. I asked them how to troubleshoot a long-running store procedure after they mention looking at index and stats. It’s like, “Well, what about the code?” and they said they’d send it back to the developer. It’s not always the case.

There’s one interview I said it got hit by a bus.


Carlos: Yeah, what are you going to do now? Right?

Amy: Yeah, what are you going to do now? You got to fix it. He’s not here, so it’s up to you.

Carlos: Potentially, a good happy medium there is, yes. As a DBA you may not have time to sit there and refactor, that’s their procedure, but if you can at least point the developer in the direction to say, “Look here. Here’s the little section of a code that is the worst performer,” or “taking too many reads,” things like that.At least you’ve then given them something to work with rather than, “Here, go do this again.”

Amy: Yeah, and that’s definitely something to look at. There’s that episode on “Saturday Night Live,” where they have the IT guy and he comes in and says, “Oh, just let me fix it.”[laughter]

Amy: A lot of times that’s just…that’s me. I’m like…[laughter]

Carlos: There you go. That is the consultant coming out of you there, then.

Amy: [laughs] Yeah, and that’s just kind of how I operated. That’s my train of thought. I’m like, “Move, I’ll fix it.” [laughs] Yeah, you’re right. It’s good to send things back to the developer because they need to expand their skillset as well.They need to learn and they need to be able to do things differently if necessary, and if you can give them more information and educate them. Then that’s going to help you with your job as well as make them a better developer.

Carlos: As the developer moves into that DBA position, another area that they might come in contact with is the reporting or the data warehousing groups or functions. I guess groups if you’re large enough to have a group. What are some of the interactions you’ve had with the BI on reporting space?

Amy: I haven’t had a lot of BI interactions. I’m just thinking of my lab position. We had a data warehouse team, and their primary responsibility was the development of processes to pull everything into the data warehouse. I was involved with them in the aspect of deploying their processes and getting those out.In that particular situation, I should have had more oversight into some of the stuff they did, but that wasn’t the case. It just wasn’t how things were. We also, in that case, had a parallel data warehouse, or an APS appliance, analytic platform system.

That was an interesting thing to be able to get to play with and work with, and find out how different that is from just a regular SQL Server environment. As far as BID warehousing, for a DBAs perspective, occasionally you have to generate reports, so you might be doing things that are BI related, depending on what your task was.

It could be just a report on server stats or just something simple, but something that other people are going to view as…

[dropped audio]

Amy: …group, so you have to make sure that they can understand it, they can take in the data. Also, you’ll have to make it probably look nice.

Carlos: I thought it was interesting that you used the word oversight. That was kind of my thinking. I saw your BI in data warehousing components there. You want to provide some oversight. You want to provide maybe some architecture and things.In some point you’re probably going to want to draw the line and just communicate with your management team that you may need some additional resources, or at least some additional training to get into that space because it really is a different animal.

Amy: It is. It really is. We had a number of people come in, in that case, that were leading the data warehouse team. When I say I probably should have had more oversight, those folks on that team were not submitting code for review. They did not do code reviews, or they had code reviews just within their team.Like, “Nope, that’s not a code review.” It needs to be reviewed by someone from the DBA team.

Carlos: True.

Amy: There was that. That data warehouse, it was interesting in the fact that we did have the APS appliance, but there were certain little nuances to it and everything. I thought things could have been done a lot better.

Carlos: Sure, and it’s interesting that you bring up the different roles. We’ve talked about integrating with developers, the data warehouse team, even users. I think that is the other component there, is the communication involved in the DBA role by necessity just has to increase because you’re dealing with so many different people and processes that affect the whole team.Right? We are here talking about data warehousing.

Amy: Yeah. There’s the data warehousing. We had the reporting team. Then we had, also, other developers working on the app side who were doing T-SQL code. Our DBA group was dealing with various teams just throughout IT to accomplish everything that we needed to accomplish on a daily basis.

Carlos: Yeah, and some of the decisions you’re going to make are going to affect those teams, so data refresh policy, how systems interact versus like, “We’re going to use link service here. I have to use an ETL process here. We’re going to use database govies here.” Right?

Amy: Right.

Carlos: That’s the way people can then interact and use that data as well.

Amy: Yeah. It depends on your environment. It depends on the situation. It depends on who’s wanting access to something and where they’re wanting access to. You mentioned link servers. I had one report writer want to do an update, or do something across a link server, and the permission didn’t exist for it already.Oh, he was trying to do a delete instead of…

No, he was trying to do a truncate instead of a delete. [laughs] I’m like, “Just do the delete.” The report writer actually reached out to a member of the business, asking if it was OK to do a delete instead. I’m like, “They don’t care. They want it done. They don’t care how you do it.” [laughs]

Carlos: Yeah, make it happen.

Amy: I kind of [laughs] segued there, but yeah it just came to my mind. I remembered that happening at work.[laughter]

Amy: We don’t care. They just want it done.[laughter]

Carlos: For all you developers out that that want to make the jump for the DBA position, one of the things you need to do is go and check out sqlcruise.com. Amy, are you a former cruiser, by chance?

Amy: I wish. I would love to go. Oh gosh. I was talking to some people about it last weekend, and I was seeing all the pictures coming through on Twitter. I’m like, “That looks fun.”

Carlos: Yes, Tim and Amy Ford have put together a most unique training experience and, of course, they want you on board. Listen to the podcast, get $100 off the cost of admission. You can get those details at sqldatapartners.com/sqlcruise. Learn. Network. Relax. Grow. Just add water.For more information about events in 2016, you can check out sqlcruise.com.

Amy, let’s get into the SQL family questions. Thanks again for this conversation, I should say before we move forward. I think it’s been good. Those who are more interested in taking over those database responsibilities can have a couple of things, potentially, on their list to look at for this year.

When we talk about how people work, one the things I always like to ask is what their favorite tool is. This could be a free tool, paid tool. What tool do you like and what does it do for you?

Amy: Currently, in my current position, we are using SQL Sentry monitoring. That’s a tool I like. It’s definitely better than one of the other monitoring tools out there, paid monitoring tools that I’ve used in the past. It seems to be a little more robust than previous one I’ve used.I will say one tool that I’ve used before that, since I really like to get into SQL code and look at things, I would SQL Compare. I even wrote a power shell. Using the command line version of SQL Compare, I wrote a power shell script that would go out, for developers’ deployments. I would generate the deployment scripts, the roll back scripts, a report on the differences, and dump all that out.

That was trying to maintain control over the code that was being deployed. I eventually was overridden because the developer said, “Trust our code,” and I was saying, “No, no.”


Amy: No, we don’t want to, but I was overridden. I still wrote the script and SQL Compare is, I think, a really neat tool.

Carlos: It is. It is a very cool tool. You just inherited one million dollars. What are you going to do with it?

Amy: Let’s see, aside from thinking of Austin Powers and “One million dollars.”[laughter]

Amy: I guess I’d pay off my house and…

Carlos: There you go, a smart decision.

Amy: …very likely invest the rest.

Carlos: Not even a little bit of splurging? Not even a SQL cruise, Amy?

Amy: OK, I would probably go on some cruise. I would. I would pay off my house, go on to a cruise, and then anything left over I would invest.

Carlos: Then invest the rest.

Amy: Got it. That’ll take a long term here.

Carlos: Very good, very good. Yes, good capacity planning there.

Amy: Yes.

Carlos: You mentioned the previous position that you had. You’re now in consulting.

Amy: Yes.

Carlos: What’s one of the best pieces of career advice you received over your career?

Amy: I think, aside from the, like I said before. Someone once told me that you are the protector of the gold. Gosh, I just thought of another thing. One piece of career advice someone told of that I still stay in contact with, they said that, “You learn more by not staying in the same place.”If you want to take on a bunch of contracts — three-month, six-month contracts — you’re going to gain more experience moving around more frequently than you will if you stay in the same place. I think in many cases that’s very true. I also think you are your own, you can be your own worst enemy. You can limit yourself in ways that you don’t even realize.

At the last place I was at, we hired a DBA who’d been in the same place for 12 years. When I told him he would do good to script things out instead of using the gooey, and pointing and clicking…He’d been a DBA for 12 years and he couldn’t write the simplest queries. That was kind of sad.

He limited himself by staying in the same place, but he’d also limited himself by not taking the initiative to do more and do things differently.

Carlos: Sure. Kind of re-invent himself and advance his career there.

Amy: Yeah. He made the choice to not do those things. I just didn’t see how you could possibly get through being a DBA for 12 years and not do a lot of those things.

Carlos: Everyone has their own unique experience I guess on the SQL trail.

Amy: I guess. [laughs]

Carlos: Our last question for you today, Amy. If you could have one super hero power, what would it be and why would you want it?

Amy: I guess invisibility.

Carlos: There you go. [laughs]

Amy: It’s even better than being the fly on the wall. [laughs] My mom always said that you can always learn a lot more just by being quiet sometimes.

Carlos: There is that.

Amy: You’re visible, you’re quiet, you can learn a whole lot.[laughter]

Carlos: That’s a whole lot of dirty laundry going to come your way there, Amy.[laughter]

Amy: I have some dirty laundry, but you can learn. Sometimes you learn a lot more just by taking things in and listening, and not saying anything.

Carlos: There you go. That is good advice. Listening. That’s why you have two ears, they say. Right?

Amy: Exactly. That’s why you have those two things that God gave you, and [laughs] you need to use them. That and the one in between. [laughs]

Carlos: Amy, thanks so much for being here. I do appreciate it.

Amy: Thanks for having me.

Carlos: Compañeros, thanks again for being here. If you have something that you’d like to…that you think…hmm, let me say that again. Compañeros, if you have something that you’d like to add to our list that the developer should know as they transition to the DBA, you can leave it on the page for today at sqldatapartners.com/podcast where you’ll see today’s episode.You can link there and leave your comments on the page. Of course, if there’s something that you want us to talk about on a future episode, you can reach out to me on Twitter, @CarlosLChacon, or shoot me an email at [email protected].

We do appreciate you being here again. If you like today’s episode, we invite you to leave a comment on iTunes or Stitcher so that other people can find the episodes and enjoy them as well. We’ll see you on the SQL Trail.