Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

Overview, Best Practices and Lessons Learned on Microsoft SQL Server in Windows Azure Virtual Machines

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
[Chuck Heinzelman] Good afternoon, everybody. What?! Where did that come from? It's the last session of the day. Come on—yeah! Hey, I also delivered the first session of the day today, okay? So I've been doing this is 8:30 this morning. You have nothing to complain about. Thank you for coming all the way down to the end of the building. I was worried I was going to be late because I'm like, "Wow, it's this far?" My name is Chuck Heinzelman. I am a senior program manager with Microsoft. I am a member of what used to be known as the SQL Server Customer Advisory Team. We're now known as Azure CAT because we are doing much more than just SQL server these days. I am joined by a colleague of mine, Khalid Mouss. I will let him introduce himself when he comes up on stage. We're here to talk about the best practices and lessons learned from running SQL server in Windows Azure Virtual Machines. When I work with presenters that are new to this—first time presenters, I always stress that they need to put this slide in. This is what I call the invitation to leave. I have to be here. For you guys, there's like 5 million other sessions that are going on. So I want to make sure right up front that you understand what we're talking about in this session. And I will not be too offended if you decide to go some place else because this isn't what you expected. So we're here to talk about infrastructure as a service. I think that's fairly obvious. But we're going to talk about different things related to infrastructure as a service. We're going to talk about some of the HA/DR considerations. We are going to talk about—we are going to spend a good deal of time dealing with the terminology around infrastructure as a service so that everyone has the same level playing field. Because I know it's a new concept to a lot of people, I'm just going to warn you we're going to probably take the first 10 minutes of the session to just talk about Azure Infrastructure as a Service and the terminology and that type of thing. Who in here has played with Azure Infrastructure as a Service? A couple? You 2 don't count. [laughs] How many of you would consider yourselves experts in the terminology and vocabulary around it? So if you don't want to hear that over again, the first 10 minutes are going to be based around that. I know there's a Starbucks downstairs. Feel free to go get a coffee. If you do go I take a Venti Mocha. No, just kidding. So we're going to do that, and then we're going to go into different pieces. We're going to talk about how to use the images that are out there, the licensing behind them, and a bunch of different things. So it's going to be—we've got a lot of demos planned, a lot of information coming at you. It's going to be drinking from the fire hose. All right. With that, the first thing I want to talk about is the Windows Azure Virtual Machines for the the DBA. This is the overview of what is available in our Windows Azure Infrastructure offering. The first thing we need to talk about is some of the terminology. This term and another term I will use later bothers me as a DBA. I see affinity group. Later I will be talking about availability sets. Why would that be confusing to a DBA? Well AlwaysOn has availability groups. So as 1 term in AlwaysOn that’s split into 2 different terms that mean completely different things in Windows Azure Virtual Machines. It takes awhile to get used to. Affinity groups—think of them as a container for all of your different objects. We’re going to talk here about virtual networks. We are going to talk about virtual machines. We are going to talk about storage. And affinity group is a grouping of all of these different objects. What it does is it tells Azure—the controllers in Azure that determine where to put all this stuff in the data center— it tells them how to deal with your objects—behind the scenes. This isn’t anything you have to worry about. But behind the scenes it takes things in the affinity group and puts them in the optimal proper places. So just know that you want to—if you have things that are related to a single application and are related to each other, you want to put them in a common affinity group. Virtual machines use storage. You want to have the virtual machine and the storage live in the same affinity group. Networks—we have virtual networks, we have local networks, and we have DNS servers. In Azure you can define your own VNET. You define an address space, you define subnets, you define DNS servers. This is your playground. It’s not shared with other people. This is your subnet. This is your address space. In your Azure subscription, you can have multiple VNETs that have the same address space because they’re separate from each other. One thing you want to know when you’re defining your networks— you can add subnets after you deploy objects into your networks. The big thing that you can't change is the address space. So if you define a 1721600/16 address space and later decide that you want that to be a different address space, you cannot change that once you have machines deployed into that network. So you really need to think out your network design upfront, whether it's you being—if you know that stuff inside and out or working with your IT department inside and out. You want to make sure the network is properly set up to begin with. Local networks—this is very interesting because you can set up connections from your on-prem environment into your Azure VNET. There are multiple ways you can do it. You can do a permanent VPN tunnel between a supported on-prem VPN device and your VNET. That's a permanent tunnel where you can actually join your cloud-base VMs to your local domain. Or you can put a replicated domain controller up there. Or you can put a new domain controller in a new forest up there and trust between your on-prem and your cloud domain. The option is yours. The choice is yours. You can also do what's called a point-to-site VPN. This is a preview right now—it's not release code. But it's really powerful where from my local machine I can VPN into my virtual network. And when I'm VPNed into my virtual network in Azure I get a local IP address in Azure, and I can address all of my virtual machines by name. So I can do remote desktop right into them. I can copy files right up to them as if I were VPNed in to see my corporate network—very, very powerful. Even though it's in preview I'm using it extensively and it works quite well. It's a certificate-based authentication. I don't have to enter passwords. It's really nice. And then DNS servers—if you have say your own domain up in Azure, you can set that domain controller up as a DNS server and in your virtual network configuration, set up— say 17216.2.4 is a DNS server, and that will act as a DNS server on your network. One thing you need to know about Azure Virtual Machines, they have to be DHCP. But the lease on that IP address is virtually forever. It's kind of like being a static IP. But it is assigned by Windows Azure, not by your domain controllers, not by anything else. They have to be DHCPed. A cloud service—now a cloud service is a container that has a public endpoint. And by public endpoint I really mean it has a public IP address and a public DNS entry. One of the demo environments I have for this conference— the cloud service is called techedbi.cloudapp.net. I can, from any machine connected to the internet, get to that DNS entry, and I can get to the IP address, and through that I can poke holes in it like a fire wall to get at the machines behind it. So you have a cloud service, you tie a network to your cloud service, and then deploy machines into it. We'll talk about that in a little bit when I go into the portal. Storage accounts—I mentioned that you have storage. Storage is Windows Azure page blobs. You upload VHDs, you work with VHDs as blobs. To your virtual machine they look like a drive— no different than if you were using Hyper-V or VMware. In the machine it looks like a drive. It looks like a duck, quacks like a duck. No difference there. Folders in your storage accounts are called containers— just so you know the terminology. And each storage account has 2 access keys. These are ginormous keys, letters, numbers, symbols— the, you aren't going to get this if you tried kind of thing. And those keys can be used by third-party tools to actually go in and connect to your storage account remotely over the Internet. Your keys can be regenerated. I mentioned that you have 2. One of them is a primary, one of them is a secondary. There's really no difference in functionality between the keys. It's just we needed something to call them. So there are 2 separate keys that you can regenerate independently, so if 1 of them gets compromised you can regenerate that key. Then the virtual machines themselves. I mentioned that we have disks—VHDs that you can work with and upload into storage and the virtual machine image itself, which I will talk about a little bit more in the portal. One thing to remember is we do not support the VHDX format. These are all VHD files that we have to deal with. So what I'm going to do really quick is I'm going to dump into the Azure Management Portal—once I get Eeyore of the screen. So this is my Azure Management Portal for the work and the demos I've been doing this week. We can see here—and just to give you a little tour really quick around where all these different things are—because it's not intuitive for some of them—believe me. Affinity groups is under settings. I wouldn't have put it there, but that's where it is. That's why I want to give you just a little tour of the things that we just talked about. So you come into settings, and you create an affinity group. Like I have my—I'll zoom in on this—my TechEd BI Affinity Group. That's the one I was using this morning. Then from there you create all your other objects. So I can then go in and create a network. Where's my network? There it is. And if I look at my network properties in the dashboard here, we can see if I go to configure—if I go to configure—it doesn't like me. Okay. The network is all configurable right from within the portal. Or you can also do this through XML files. So you can see here I;ve got 2 DNS servers to find on my network. I've got various subnets and address spaces. So this is a /17 address space with several subnets carved out of it that I've put my machines into. Now note, you might be expecting that with like a /29 subnet, you'd get 6 IP addresses that are usable—in Azure you get 3. For each subnet that you define, take the number of usable IP addresses that you think you will get and subtract 3. Those are used for Azure's internal purposes. Do not make the assumption that the first machine you deploy into that will get the fourth IP address, though. That's how it happens to work now. Do not assume that it will always work that way. That brings up another thing that actually burned me a couple weeks ago. The scripts that I used 2 months ago to generate an environment similar to what I demo here had a best practice outline of putting SQL server's TempDB on the D drive. Every virtual machine gets a temporary drive called D. Our best practice was, at the time, that you should put the TempDB files on the D drive because it was faster. When we went GA, we actually reversed that best practice. It is no longer a best practice to put your TempDB files there. The reason I bring this up is because we're all used to living in a world where things change slowly overtime. How many SQL 2005 best practices still apply to SQL 2012? Most of them, many of them. It doesn't change that quick in a traditional IT world. In Azure things change very quickly, and you need to stay on top of what's going on with the platform because changes to the platform will affect the SQL server best practices that we have to put out— just like the TempDB location. The best way to stay on top of the changes to the platform that I've found—monitor Scott Guthrie's blog. Whenever we put out major changes to the Azure platform, Scott blogs about it. He's in charge of that whole group at Microsoft. He's the one that's putting out that message. So when you need to monitor a blog for changes, start with his and go from there and then look at—he'll tell you what has been done, then you can dig into the MSDN documentation to see if it affects you or if we have updated our best practices based on it. Scott Guthrie—sometimes known as Scott Gu. Yep. It's one of the annoying things about life inside of Microsoft— we refer to each other by our log-in aliases. You'll write an e-mail and CharlesHe—who's that? Oh that's Chuck. Oh never mind. [laughs] So these are our virtual networks. And just to kind of give you a little tour around some of the other things—storage accounts—I have many storage accounts here. Virtual machines—and this is all documented online and it changes often, so I don't talk directly about it. There are throughput limits for a max number of I/O per storage account. Your disks and that—it's really a performance-tuning exercise. I know CAT—SQL CAT—just published a white paper in conjunction with the product group about performance on Azure for SQL server. You can find it at www.sqlcat.com—a good paper to reference for looking at different performance issues and things. The virtual machine itself—this is where I really want to get into meat of things. The virtual machine is the heart of what you're building. And the thing I really want to stress to you is that once it's built, it's not really much different than a virtual machine in your data center. You interact with it, you own the operating system, you own the patch, and you own everything. It's a virtual machine. The only thing you don't own is the infrastructure that it runs on. So from that standpoint it's not different than a regular virtual machine. But the infrastructure here—I have a bunch of different options here. I can attach disks, I can detach disks. Think of this portal here and the associated PowerShell scripts like what you would do in Hyper-V Manager when you want to add a disk to a VM. You go into Hyper-V Manager, you create a VHD, you add it to the VM, you go into the VM and format it. You do the same thing here. It's just that it's being done—the VHD is stored in as your blob store instead of on your SAN, and it's attached virtually through the network. Same concepts, different view of life. Okay? Any questions before I move on? I'm seeing none. Let's duck back into the power point slides for a little while. I need to talk to you a little bit about deployment and licensing. We have—out in the Azure portal— we have several of what we call gallery images. There are gallery images that are just Windows Server. There are gallery images that are Windows Server with SQL Server on top of it. There are gallery images for SharePoint. There are gallery images for Linux. There's gallery images for a lot of different things. Those images, specifically the SQL Server images, have licensing implications. For Windows Server on all these images, you're paying by the hour. You're charged for usage. You don't have to have a Windows Server license to spin one of these up. You spin up a machine—the rate that you pay to use that machine includes the operating system license. With SQL Server if you use one of our SQL Server gallery images, there's a web edition, there's a standard edition, and there's an enterprise edition. The rate that you pay for that virtual machine includes the SQL licensing— very important. Who here knows what license portability is? Or license mobility? Okay. One person—unless the lights are blinding me. I can't see the people in the back row. License mobility allows you—if you have the appropriate agreement in place—to take the license that you bought for on-prem use and actually move them and use them in Azure, in Azure Virtual Machines. License mobility does not work with the SQL Server gallery images because you're charged the rate for usage. And you can't get away from that. If you are using licensing mobility, you want to start with a Windows Server for gallery image. Now okay, I have to say this—this is what the rules are right now, 5:30 or whatever it is, today in New Orleans. This could change tomorrow, it could change next week. So the recording here could be dated whenever they decide to change these rules. But the Windows Server gallery images, you can then install SQL Server on them and use your license mobility if you have that agreement in place with your software assurance. Different things to think about. You can create your own images. If you have a virtual machine that your running on-prem that you just want to host in the cloud, as long as your licensing permits you moving that to the cloud—because there are different licenses for different software—I won't go into all the specifics. You can take that VHD, upload it into Azure, create a virtual- machine definition around it, and start running it just like you would with Hyper-V. You can create a Sysprep VM image and upload that into Azure and use that as a template to create multiple virtual machines in the cloud. Or you can build a new machine in the cloud, Sysprep it, and then create an image off of it, which is my preferred way of doing it. That's how I like to do it. I don't want to build a full Windows Server machine on-prem, patch it, install all my software, patch all my software, and then upload a 40 or 50 gig VHD to the cloud. I would rather say start with the Windows gallery image, patch it — download speeds in the cloud are actually fairly nice. I downloaded something from MSDN onto one of my virtual machine images the other day, I was getting like 20 mega-second downloads. It was amazing. I can get my bits very effectively there. Or in my case, I have a VHD in the cloud in my blob storage that contains all of my bits that I regularly install. It has the ISO images. I can take that VHD and attach it to whatever VM I'm working on where I need those bits. I don't have to copy them over the wire. I just say use that VHD. So when I'm done building that machine up there, I Sysprep it, then I turn it into an image, and I'll show you— not the Sysprep process because it takes awhile—but I will show you how to do that in the portal, and we will go into that right now. So there's a couple things that we can do here. The first thing that we can do is upload our own VHD. Whether it's Sysprepped or whether it's not Sysprepped, the upload process is the same. You're uploading a VHD file. And that's what this first command is here—this Add-AzureVHD. I give it a destination, which is a path in blob storage. Note I can go over HTTPS so it can be encrypted with SSL. I give it the local file path and tell it how many upload threads to use. If I execute this command, it's going to take that TechEd VHD out of my temp directory on my C drive and upload it into Azure blob storage where I told it to put it. I will not run this command here because it does take awhile to upload. I can't get around the laws of physics. I don't have Mr. Scott here with me. But like any good cooking show, I already have a couple of VHDs uploaded that I'm going to use for the rest of the demo. So once you get the VHD up there, what you do with it next depends on what kind of VHD it is. Is it a Sysprepped VHD that I want to use as an image? Or is it a VHD of an existing VM that I'm just lifting and shifting and running in the cloud. Let me go into the portal really quick and show you something before I— I just want to show you the proof before I actually run these commands. If I come to new virtual machine from gallery, you can see here— I'll zoom in—I have 2 options here. I have the Platform Images, which is what I already talked about, and I have My Images and My Disks. You might be asking yourself, "Chuck, what are the differences between My Images and My Disks?" Wow, no one bid on that this morning either. I was hoping someone would say, "Chuck—" anyway, I'm sorry. I'll stop trying to make bad jokes, although it's the only joke I know. Under My Images, you can see I have 2 virtualized images already. Images are—these are the Sysprepped images that I can use to create multiple VMs off of. I created a couple for some things I was doing. I do a lot with SharePoint VI, so I like to have images with SharePoint installed that I can just scale out easily. My Disks are the VHDs that I can use to just create a new VM. This is a in-place—I'm doing this. So I have none there. Now if I come into PowerShell here, and I do a create disk. Now my TechEd_P2V_Demo disk, coming out of that media location, I give it a label, I tell it this OS is Windows. The OS flag is the key to the PowerShell command. That's what tells this function—this PowerShell command— that this is a bootable image. If you did not put that on there, it would just make a disk out of it that you could attach to an existing VM. This is what tells it to show it in that list of disks that I'm going to create a new VM off of. So what I'm going to do is I'm going to run this command. It should finish shortly because I have a good network connection here. Okay, it shows operation status succeeded. Now it sometimes takes a little while to refresh in the portal, depending on the refresh cycle of the portal itself. Let's see if it showed up in here. You can also come up to your virtual machines and you have a disks tab. It doesn't show up here yet. Let me refresh this and see if it shows up. So what that did is it took that VHD that was in my blob store and created an Azure disk off of it and marked it as having an operating system. So I can take that Azure disk and make a VM out of it. There we go. The P2V demo is right there. Now if I come in and do a new virtual machine from gallery, I can see under My Disks, there's that disk that I created. So if I create a new VM, it's going to use this disk as that VM— as the template for that VM. So the next PowerShell command that I have is the add Azure image. And what this is going to do is this is going to take that Sysprepped VHD that I uploaded, and turn it into an image that I can use to make more VMs off of it. So if I run this—and it comes back and says succeeded. Yay—suceeded. All right. Now if I come in and do a new from gallery and come to My Images— let me refresh this because I'm impatient. So now when I come in and do a new gallery from My Images— I'm being impatient. I must wait. I must learn patience. There we go. Okay, so New, Compute virtual machine from gallery, My Images— right there. That's the one I just created with that PowerShell command. Most things you'll learn can be done either through PowerShell or through script or through the portal. There are some things that can only be done through PowerShell. There are some things right now that can only be done through the portal—some of the some stuff at the point-to-site VPN, they don't have the PowerShell command that's written because it's in preview. They haven't gotten to it yet. Personally I'd rather they the spend time on the feature, and they'll get to the command list whenever they can. The last thing I want to show you in the portal here is the ability to take a machine and create an image off of it. So if I built—let's say that this machine here was a machine that I built in the cloud and then Sysprepped. I want to turn this into an image. What I can do is I can select it once it's shut down. It has to be shut down to do this. I come down to my capture, and I give it an image name and say that I have run Sysprep on this virtual machine. What that's going to do is it's going to delete the VM definition and turn this machine into a template that I can use to create other virtual machines. This is the tour around the management of VM—I do all of this through PowerShell. I hardly use the portal for the VM creation and that because there's a lot more that you can do that's a lot more flexible. In my PowerShell command I can say, "Instantiate this VM. Oh, and automatically join this domain." I can't do that kind of thing through the portal. But there are some great PowerShell commands, and they're well-documented on MSDN for this type of thing. So, migration. We already talked about the lift and shift. I want to take a VHD, move it up to the cloud. So you have a SQL server running on-prem, it's virtualized already, you just want to run it in Azure, you take that VM, you copy it up, you make a new VM definition around it. That's the lift and shift scenario. A hybrid scenario—this is where I have some things on-prem, some things in the cloud. Think of a BI scenario—you have your OLTP transactional system on-prem, but maybe you offload your business intelligence assets into the cloud. Maybe you're always on availability groups with asynchronous secondary in the cloud for disaster recovery. You don't want to do synchronous between on-prem and the cloud— the latency's going to kill you. And then the obvious one is the full rewrite. So if you have an application on-prem that you want to move into the cloud, you might want to a full rewrite and have it use Windows Azure SQL database and web and worker roles and some other things. If you want to take on that—if you were going to rewrite an application anyway, I would suggest looking at maybe doing it in Azure because there's some great functionalities with not only the SQL side of things, but with Azure storage for queues and blobs and tables and a bunch of other things there. I want to show you one thing, and I'm not going to actually run this demo, but I'm going to show you the scripts because I'm running behind. I don't want to hold you guys after the—I already thank you for being here during the happy hour that's going on from 5:00-6:00. So I don't want to keep you past 6:15. What I have here in Management Studio is a set of commands that works with—this was released in CU2 for SP1 for SQL 2012. It's called Backup to Cloud. I can have a storage account running in Azure, and that storage account can be the target for my on-prem SQL backups. So what I have to do is I have to create a credential, and this credential has the secret keyword, and that secret is— remember how I told you a storage account has this massively long key? That's what you put in the credential. Then when you do your backup, instead of backup to disk or backup to tape, you backup to URL. And that URL is the storage container in Azure plus the backup file name. You give it the credentials so that SQL server can authenticate up into the cloud and let it run, What I will say is there is not retry—if the connection fails, you're going to drop it, and you're going to have to restart your backup. I will tell you that. So having good connections is critically important for doing this. The question is have we compared the time to do this backup versus a backup to local disk and then copying up the blob store? Do we have any hard numbers on that? [male speaker, inaudible] [Chuck] All right. Thank you, sir. And with that I'm going to turn it over to my colleague, Khalid. [Khalid] Thank you, Chuck. Good afternoon, everyone. My name is Khalid Mouss. I am part of the team that's been program managing the SQL Server—the part of the team that is responsible for certifying SQL Server on Windows Azure. As part of the certification efforts we have learned a lot, and we had a bunch of lessons learned and recommendations that we would like to go over, and then we'll give a couple of demos. The first section is about connectivity. Let me just get my stuff here. Some high-level recommendations of connectivity—this is basic— remove unused endpoints on virtual machines. By default you will get RDP points and power remote PowerShell. It doesn't necessarily mean you have to keep them so you can—or if you use any other reports for SQL Server to manage SQL Server instances, it doesn't mean you have to keep them all the time. So there is a risk, obviously, if you keep those. So make sure you are very conscious, especially if you are managing a lot of virtual machines, to have diligence to remove unused endpoints once you don't need them. Use virtual networks instead of public RDP ports to administer your VMs. This is important, especially if you have some enterprise applications. You don't want data to go over the Internet, so make sure you actually use virtual networks. Chuck has gone over some of the functionality that you could use them. Use VPN tunnels to connect to database servers. This is even more important for data. If you have applications that are hosted in customer data and so on, it is important that you have your own VPN. Otherwise you are going over the public network. This is more like—some scenarios were run in our labs when we were doing the certification for SQL Server. We design our virtual network a certain way, and then we have to scale our infrastructure. As part of our infrastructure, we have to increase our address space. It wasn't an easy way to reconfigure your virtual network to add additional space. What we had to do, because we have warranty provision from a bunch of VMs and resources on those VPNs, we had to tare down everything and add additional space and rebuild the virtual network from scratch really, which was big, in fact, for us because we had to tare down all of the machines and had to cooridinate it organization-wide— which was not a very trivial task. So be very careful when you set up your VPN, be very conscious and design the network for the future as well because if you have to tare it down, it could be a big impact for your organization. So this is one of the lessons learned that we've learned over time. I think Chuck explained the difference between availability sets and affinity groups. Here this is important, especially if you have committed SLAs, make sure you actually do configure VMs and availability sets. That way if there are planned or unplanned outages, your VMs are not totally down so you have a secondary VM to process your workloads. Affinity groups are important into aspects. The first aspect is pricing. So if—especially in the case if you have a database, like a multi-tier application, make sure that the database— affinity groups will provide you the flexibility to have your data and processing the same data sensor or as close to each other as you can. Otherwise, if you're doing a lot of transactions to the database, your costs will go up and also latency. So make sure—affinity groups will give you the flexibility to keep your data processing together. By defining an affinity group, that will reduce your costs over time and also the latency. So you have to go back and forth between your processing and data. Last but not least, by default you will get Windows authentication. It doesn't mean you have to keep it, especially when you go into a domain and in general you want to switch it between mixed-mode authentication. We will demonstrate all of these in the next demo, but let's go over some of the ideas for this demo. To give you some context, here's one I'm going to go over. We have 2 scenarios. Say you have some large deployments where you have a lot of VMs, but you don't want to manage each VM separately. What you want to do is remote PowerShell to the VMs and manage those databases remotely from probably one central location. So in this case I've got VM1 and just an example for the demo, VM2. Then I am going to log in only to VM1 and I'm going to manage VM2 through Remote PowerShell. So what we will do is we will open a port, which is 1433 by default for SQL, and I will manage VM2 and I will demonstrate how you can do that, how you use Remote PowerShell, and how you can configure and reconfigure the databases in the VM2. I'm showing here just 2 VMs, but this same concept is going to work with any number of VMs—it doesn't matter. In this scenario I have both VMs in the same cloud service, which means that it is one public interface through the Internet. But in the second scenario, I'm going to show you how to do the same thing even if the VM is in a different cloud service. The council is exactly the same—there is just one small tweak that we will demonstrate in that case. So we'll switch. Here's what I have. We've got VM1, VM2, and VM3. So let's take a look at the configuration. I already have them set up so we spend don't time provisioning these VMs, given the time which we have for the demo. Here are the endpoints for VM1. For VM1 the only thing I need is RDP so we will be able to remote the desktop to the VM. For VM2, however, I'm going to need 2 endpoints. I'm going to need PowerShell to be able to access through Remote PowerShell. I kept the Remote Desktop—I don't actually need it, but I kept it in case something goes wrong during the demo and I need to log into it to debug. And then I opened the ports, which I will demonstrate later for SQL which is 1433. So it's port forward into 1433, which is used by SQL management. Then VM3—it's similar to VM2 except that it's in a different cloud service and the way you know that, you look at a dashboard, and then in the dashboard you should see the cloud service, where it is at. So this one is in condemo2, and then condemo2 is in condemo1— or just condemo—and then VM1 is in condemo. So VM1 and VM2 are in same cloud service, while VM3 is in condemo2. Now what we want to do next is go to machine VM1 that I showed you, and I have—because I don't want to spend time typing— I have some PowerShell commands that are already ready. The first thing we want to do is in VM1, so I'll check here. It is my PowerShell command. I'm going to do host name, just to make sure I'm in the right host because I'm dealing with a bunch of VMs, so I've got to be very careful about what VMs I'm in. Here it says I'm in demo VM1, so I'm in the right VM. And then what I'll do is I want to remote PowerShell to VM2. This command is fairly simple, actually. You just enter PSSession, computer name, and then you give it a port and you specify the credentials, and then you configure your user—SSL. Once you do that you go in, and it will ask me for the password, so it's not encoded in the command itself. So I log in. Now I'm in, and I can check host name, and I see demo 2. Now I'm inside demo 2, and I can do anything I want from PowerShell to manage demo 3, including managing the database itself. So what I did is I wrote a few scripts to get them ready inside, so I don't type. If I go here I've got a directory alias or these commands. So I have a few commands. What I'm going to try to do is remotely open port 1433 and manage my database through SSMS. What I have is an instance of SSMS. I'm going to go here to demonstrate that I can connect. So it's a no brainer—I can log into demo 1, no problem. I should be able to. I can see my databases. Now what I want to do is I want to disconnect and try to connect to VM2—or the SQL Server on VM2. I'm using mixed-mode authentication as you noticed. I'm not using Windows authentication for VM2. That's by design because I'm not logging into it from the same computer. So if I try to log in I'm not going to be able to log in because the port is not open. I just want to demonstrate that there is actually a timer of 20 seconds. When the timer goes off in 20 seconds it would tell me there is no network configured, so it would fail. Then what I'll do is I'll go to Remote PowerShell and just run one command and open the port. Basically I'll add the rule in Windows Server's firewall to allow me to have this connection. So as you can see we have a failure. We are not able to connect. Now what I'll do is I'll run the command, so I'll do add firewall. Add firewall basically just runs this. I'll show you what the command looks like. It's over here. Basically in a nutshell, command firewall to add the rule. And then I'm telling it to accept incoming traffic at local port 1433 using the standard TCP protocol, and the action is to allow it. That's pretty much what the command says. So I'm opening this port to be able to allow communication to 1433. Once I do that I can do show. This will give me—it basically shows what I run. It tells me exactly what I just mentioned earlier. If I try to connect—I should be able to connect from VM1 to VM2 as database instance—so let me try again. There we go. I'm able to connect now. I can manage VM2 just as if I'm inside of VM2. Now the exact same concept with VM3. So if I disconnect, VM3 now is in a different cloud service. So VM1, VM2—if you noticed—when it did connect, I didn't have to specify the full-name qualifier and the DS qualifier because they are in the same cloud service. I could just reference VM2 using its name because they are in the same cloud service I am running from VM1. However, if I want to do it for VM3, I have to specify the whole DNS name quantifier and the port number that I'm trying to reach. And if you remember, if I go to the portal and show you the configuration of VM3, I have that port as the port configured for that connection that is forwarded to 1433. It's taking a little time, but we don't have to wait for that. Let's switch. I want to go to endpoints. It's 57000 that's been forwarded to 1433, and that's what I'm using. However, because I didn't open it yet it's, again, going to fail. So if I try to log in, it will time out after 20 seconds. Then if I go here—I'll wait for it to time out—then I open the port, then I go here and I run the same command. Now I have to exit because I was connected to VM2. So I had to exit out from VM2 and connect through Remote PowerShell to VM3, and VM3 is in a different cloud service, as I mentioned. As you can see we just tweak the command to specify the name of the cloud service that we're trying to hit. As you can see it here in the name it's condemo2 and then the port that is associated with the PowerShell. Now indeed it timed out. So what I'll do now is I'll connect the Remote PowerShell. I'm in, so I can do—oh actually I need to put in the password. All right, I'm in. I'll do host name just to confirm. Right here is the demo 3 and if I try again— oh, I didn't actually open the firewall yet. So let me open the firewall. I have this add—that adds the rule so this will actually time out. Let me try again. Now it should be—is it in? Yeah. It looks fine. I guess I may have to—there we go. I'm in now. So I'm able to manage the database instance for VM3. So that's the first demo. What I want to show next is how to take advantage of load balancing. Let's say you have a situation where— To take advantage of load balancing, say you have a machine that you've been using for awhile to handle traffic, and then eventually you're trying to scale up that machine as far as processing is concerned—that's a scale up of the SQL Server itself. What you can do is you can launch another VM and direct traffic to the other machine. This could be a scenario where you have some measuring with the read-only secondary, for example, that you're running some BI applications on it, and then your VM is just not able to keep up with traffic. So what you can do is just open a new VM and then configure a new port that is load balanced. Then Windows Azure, behind the scenes, will create load balance and deal with the load balancing for you. So let's take a quick look at how it would work. I have a small utility to demonstrate how you can do this. There's a little program to show you—all it does is it's just connected to the database on VM1, and it just asks the SQL Server to give me what's your server that you're running on? It's a very simple demo. So that's the code. You basically just have a connection string, and then query itself you just say select server name. Just to demonstrate how it's going to look like—if I run it first without doing any load balancing, all the traffic is going to be showing going to VM1. Let's take a look. I wrote little scripts, so if I do this little script just to rerun the program several times, it just loops and keeps running the ConnDb several times. If I run it the first time without any load balancing configured, it will just show me it's just hidden VM1. You can see here load balancing VM1, VM1. So all traffic is going to VM1, all right? Now what I will do is I will go back to the portal. I'll go to VM2, so these are my VLs—VM1 and VM2— and I'm going to add a load balancing port. This should take just a few seconds. You just go here, add, and then you have to select load balance option. Then you go next. Then just give it a name. I've just called it tSQLPort. And then we know that the only port available is 58000 that is forwarded to 1433. It takes about 30 seconds and then the port will go up. Once the port goes up the load balance is configured for you, and then when I rerun the program to ask it, now it will round robin between those instances. By the way I'm showing here 2 instances that you can scale up to any number that you want. All right, it seems like the port is almost done. It's still kind of busy here telling me it's in progress. But I notice sometimes the portal takes a little while to refresh, so what I'll do is I'll just re-write. So here as you can see, all instances on request have been going to VM1. So if I do it again, it might still go to VM1 and then eventually when VM2 comes in and the load balancing is configured, it will start round robining between those 2. So let's see—we will know as soon as it's configured. And we keep an eye on the portal status, too. Here we go. It's all of the stuff that's here at VM2. It's going to do it towards the end, so if I rerun it again, it's round robining between VM1 and VM2 so traffic is load balanced. And you didn't even have to do a whole lot—all I had to do is really just create a port and tell it I want it load balanced, which is pretty cool, I think. That's that demo. Let's shift some gears and talk about next, some other lessons learned on security or some recommendations. This is more like FYI, Windows Update is on by default on your gallery images that Chuck went over. So all the gallery images, by default, you will have Windows updates already on by default. However, if you do want SQL updates called GDRs to be updated by default, you will need to turn on Microsoft Update by default because we do not turn on Microsoft Update by default. Some of these do require reset, so it's important that you know this. Even though you have high availability or availability groups defined, this is an update you are guest OS image. So if you base all your images on the stock image, they are basically configured because they are SISPrepped the same way. So all the Windows Updates will be programmed to be at the same time. So be very careful if you have multiple VMs, this is one of the lessons we learned internally as well. Make sure if you have high availability that you do change either the timing or configure differently. Otherwise they will all try to update at the same time and that update requires reset—they may reset at the same time. So in that case if they are in the same availability group, you're not going to be—they may still go down at the same time. Basic—use strong passwords, avoid administrator— I think the portal enforces this now. But through PowerShell I don't think it does enforce it— it does now? Okay, that's good. And then, of course, if you use mixed mode you still need to configure SQL database user. So that's also the same kinds of things you have to keep an eye on. You can still use SQL server authentication as I mentioned earlier and that I demonstrated earlier. Enable database connection encryption. This is not enabled by default, so you need to be aware of this. So make sure if you do need encryption that you turn it on. Speaking of encryption—because the images are based on the same SISPrep image, if you run encryption you will need to regenerate the Service Master Key, which is used for all— which is the base key for all your encryption, so make sure you regenerate it and there is the command how to do it. Last but not least I think we are a little out of time, but we're going to show what's coming up on SQL 14. So we're shifting topics a little bit. The scenario here is say you have databases that you already have on-prem as we are showing on the left—the diagrams. We have some corporate network, and you have some backhand databases, and you just want to move them. And you don't want to spend a lot of effort doing that. You want to move them to Windows Azure Virtual Machines. We do have a new utility and it's part of SSMS that will support this, that in SQL 14 you can play with this once the HA/DR comes out in less than a month. What this will show is how actually, what happens behind the scenes and how you can get your database migrated to Windows Azure. A lot of these steps you don't have to do. The tool will do it for you. I'll demonstrate how to do that, too. But let's walk through it. First you're going to need SQL 14, which will come with this pre-installed so you don't have to install any other additional clients or anything like that—it will just be parts of the SQL-14 build. You will need access to Windows Azure. There are 3 ways to get access to Windows Azure. You can use it through the portal, you can download the shared file, or you can use the subscriptions. In this case we'll show we're going to download the subscription file. So we make a request to portal, we download. That's where we get access to Windows Azure so we can migrate our database. That's the access. Next—we're going to need a VM that has SQL on it because we want to migrate this database to it. Well we need to configure it with a drive because eventually we're going to need a backup of that database to be on Azure blob. So we configure a drive that is connected to the blob. We need to configure supports for communication between Windows Azure's public side of things and the private for SQL which is—I believe the port we are using is 11436. We have a piece of code that will do this for you. You don't have to do anything. It will unlock those ports to allow the connection from Windows Azure. Then now we need to get into a database that you have on-prem and get some backups. We need to set up a shared file in that same server or another server that database server has access to. And then the backup will start. So we kick off the back up, we make a copy in that local server. Now we need to transfer that back out into Windows Azure, so the utility will do that for you which will create a blob and will start the transfer. We'll transfer the blobs. Now you have the backups in Windows Azure when it configured the machine to it so we are able to transfer the blob and convert it to the database. So we convert that into the SQL database. Now you have the database on Windows Azure, and we tare down everything, clean up, and then at the end you'll get a report that will show you what happened and if there's any errors and so on. So let's take a look how it actually works. I'm not going to go through all the steps because the tool does all of them. All I have to do—I'll show you in the GUI on how to get it set up. So switching here—I'm going to go to portal and show you the VMs I'm dealing with. I've got 3 VMs configured. I've got what I called SSMS—this actually has SQL 14 installed, and the utility is part of SSMS. They have—this is the backhand database. I have it separate because you may have a secured server somewhere and an enterprise that you basically don't want to have access to directly. So I have that configured in—deployment too. And then I have the targets of virtual machines which is deployment 3. Let's go to this SSMS where we're going to kick off this deployment and connect to it. I guess I'm already connected here, so what I have to do is run SSMS. [inaudible, speaking to himself] It's taking me to my machine. [laughs] That's why—yeah, but I'm not seeing—scroll. Over here? Oh. Yeah, it's kind of—show desktop? That's my desktop. [laughs] Sorry. Yeah. Oh gosh. Here we go. It's showing, but— I usually just use the— Thank you. So anyway, that's interesting. I've got to practice that. I go to SSMS, so when I launch it, I get to SQL 14—upcoming CTP 1. So just a little warning these are very early bits, so it's not 100% stable. But I'll give it a shot. So when I log in, basically I'm logging into the backhand database. I already configured that connection to be able to access that database. I have a couple here I'll try to select one of these databases, and then you just go right click and tasks, and then you'll see— and then you'll see the option to migrate deploy database to Windows Azure VM. That's the option you want. Once you pick that up you go next—this just tells you what the Wizard is in help and so on. There is a bug—you'll have to re-size this to be able to see the connect button—we're going to fix that. Then you have to connect. This will connect be to the database so I can actually see the drop down. So I selected VSTS and then you just go next. This is the shared server that I mentioned when we do the backup. This is where it would be saved, so we have to configure that. And that's just any share—just the shared file. It doesn't have to be anything fancy. You go next. There's, like I mention, 3 ways to authenticate with Windows Azure to get access. I already have the subscription set up with my management certificate, but you do have the option to log into the portal. Again, you have to re-size this window to be able to see it. This is my Windows, right here. You just have to re-size this slightly so you'll see that. And then what you'll do next is you go and, like I said, you can sign into the portal. I'm not going to go there because I already have the management certificate configured, and then you go next. This takes a little time. The reason the subscription I'm using has a lot of cloud services— I think I have over 300 and probably 100s of VMs, so it has a lot of stuff. So this takes a few seconds. It's just going to pause and build the tree behind the scenes. But then it eventually comes back and you can select the cloud service where you want to deploy. In this case I want to deploy to the machine 3, if you remember. So I go here. Once the 3 flashes, I should see it here in the drop down. Now pick the cloud service. There are a couple of scenarios—you could build a machine from scratch or you could deploy your database from an existing VM that you've already set up. In this case because provisioning takes a few minutes, I'm just going to build a machine from scratch. I'm just going to deploy an existing machine that already has SQL installed. It seems like it's done so I can pick here, and I know which one I want to deploy to. I'm going to pick 3 because that's the one I'm targeting. It goes and, again, try and figure out the VMs that are available. I have a VM set up, so I'm going to select that—it's actually 3. And here what you need to do is you need to log in to VM3's database. That's the port I was referring to earlier—11435. Now it's found the instance off the database in VM3 that I had configured, and you do next. At this point it gives you a summary. It says this is what you are about to do—the name of the machine, the cloud service and so on. And then you say yep, I'm good with it. Go finish, and then it will start the process. At this point it's doing all of the diagram stuff that I was showing. It's getting the connection, setting it up, doing the backup, transferring the backup, reconfiguring the machine, copying the backup, recreating the database, and setting it up. That all happens during this time. You can see the steps here. Once they are passed you will get an indicator on progress. It doesn't take that long—less than a minute. Then while it's happening I'm going to go to the portal, and I'm going to show I'm almost done. I'm going to show the database on VM3, so I'm going to remote log in to VM3, and go here, log in. The other one should be ongoing. Almost done. Actually we're on the last 2 steps. All right, the last step is done. Here at the end I mentioned you get a log, and there is a link to it. I'm doing both of these at the same time. So here is the VM3, but let's take a look at the log. In this case everything's successful. Basically, you just get all the steps. But if there is a failure, you'll get this log to help you figure out if, for example, the machine you picked is not the right one or whatever the failure is. And then finally, we'll just go to VM3, go to SSMS, and then we should be able to see that database. Now you may be asking, "If I already deployed the VSTS database that I picked, can I deploy it again?" Yes, you can deploy it a number of times. We do have a restriction right now of 10 gigabytes on the database, but the size will increase in the future. So here I'm connecting. What it does if you have multiple deployments, it will just version them. You can see I've already done a couple this afternoon to test it. I had VSTS and I had VSTS01. I think it versions it—like it adds 010101. That's pretty much it for this demo. Okay, I think we'll go back. That's all I have, so we can switch back to Chuck. I think he'll be going over some other stuff before we wrap up. Thank you. [Chuck] Yep, we've just got a couple of things to talk through really quick. High availability and disaster recovery—we talked about the need for high—or we talked about in different cases here, that touched on high availability. You have things at the infrastructure level—the affinity group, the availability set. The availability set is what gives you the multiple machines that are tied together so Azure knows these 2 are in a set. To hit the Azure SLAs, you actually have to have 2 machines running the same role. Otherwise we don't guarantee the SLA. You tie those together with your availability sets. And between the affinity groups and the availability sets you can check the update and fault domains that your machines are in. Azure will deal with spreading them out appropriately so that they aren't going to both intentionally be taken down at the same time. Then there's the database level stuff, always on availability groups. You can do availability groups in Azure Virtual Machines right now, with one exception—you cannot do a listener. We do not have a floating IP. Remember each machine can only have one IP address. There's a way you can configure availability groups to still react to client connections like database mirroring. That works great with the auto failover. It works beautifully. You can do replication and you can do log shipping still up there. You can also do an availability group between on-prem and the cloud if you'd like to. Now the last thing I want to touch on really quick is business intelligence. I gave a session this morning on business intelligence on running BI in IaaS. For the gallery image there is—the SQL Server gallery image comes with SSAS and SSRS in native mode installed. The AS instance is multidimensional, but the bits for installing SQL Server are on the C drive. So you can actually do another install and install a tabular instance. The native mode is not configured for RS, it's just installed and ready to go. You're using the exact same tools that you're used to using on-prem. It's really, really straight forward. Like I said, the only difference here is the VM is hosted in the cloud. You can also build your own environment, and this I talked through this morning. There's a paper on MSDN about how to do this with a single server for a dev and test environment in IaaS, and watch sqlcat.com toward the end of July/early August for some additional information on building your own BI environment using SharePoint, PowerPivot, PowerView, SQL Server in infrastructure as a service that will be coming, like I said, late July/early August is what we're targeting right now. Related content—a lot of these sessions have passed already. I have to apologize—they put us late in the week. We were hoping that this session would be early on in the first days so all the rest of the sessions would be coming up. But we do have coming up at 1:00 tomorrow, a session with the engineering team. It's a roundtable Q&A session. If you have questions about anything related to SQL Server and IaaS, the brain power that's going to be in that room is going to be amazing. Do you know what room it's in? Yes, performance tuning at 10:00. Right after lunch we'll do the panel discussion. Yes, everything's been recorded. So the ones that have already passed off of this you can see them on the recordings. Room 386 if you want to talk to the engineering team. We're going to have a moderated roundtable session. Where to find us—tomorrow morning I'll be down in the booth. A lot of us are hanging out in the booth out down the show floor. I know it's going to be the last day tomorrow, so it's the morning and then the session in the afternoon. I'll come back. We'll do some Q&A, but there's a little housekeeping that I'll do here at the end. This is very important. Please fill out your evaluations. The numbers—they ask you to rate it by number— that's for the conference organizers. The comments are for us. If you have something that you liked, please tell us so we can keep doing it. If there's something you didn't like, at least tell us so we can stop doing it. With that, we've got a couple minutes for Q&A if anyone has any questions. I know we've had some questions along the way. It's been a lot of information and it's late in the day. Well if I don't have any questions, thank you for coming and enjoy the rest of TechEd. Have fun tonight.

Video Details

Duration: 1 hour, 13 minutes and 28 seconds
Country: United States
Language: English
Genre: None
Views: 9
Posted by: asoboleva99 on Jul 9, 2013

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B329#fbid=kG7OLm6xV3l

Langauges to MT;
Fra, Ger, Spa, Bra, Kor, Jpn, Rus, CHT, Ita

Caption and Translate

    Sign In/Register for Dotsub to translate this video.