Hello 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.

Show Notes

Luis’ Sessions on Channel 9
Microsoft SQL Server and Microsoft Azure: Hybrid Scenarios and Features
SQL Server on Azure Virtual Machines overview
*Untranscribed introductory portion*

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.