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

Lap Around Windows Azure SQL Database and 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
[Lap Around Windows Azure SQL Database & Microsoft SQL Server in Windows Azure Virtual Machines] Okay, I think it's about time to get started. I'm going to jump right in, because we've got a lot of content to cover. I have a little bit of a cold, so my voice is cracking a little bit. I hope you forgive me. My name is Greg Leake, and I am a Technical Product Manager in the SQL Server Team, and I'm going to speak today, although I've got Dandy here up front who might be listening as well, so he's here. If you know Dandy, he's running a bunch of other sessions today. I thought I'd take some load off and do this one all solo here. Before I get started, let me ask the question, this is a lap around our 2 key offerings for relational data in the cloud based on SQL Server, and by the cloud, I mean the Azure cloud, but I'm also going to talk about on premises and hybrid scenarios. Let me get started by asking all of you how many of you have done Azure development already? And of those, how many are using what used to be called SQL Azure but is now called Windows Azure SQL Database? Okay, about the same number. We find that pretty much maybe 80% to 85% of all Windows Azure apps deployed are actually today using Windows Azure SQL Database, which is our PaaS offering, platform as a service, and I'll get into the details there later. How many of you have spun up Azure VMs that are using Azure? And how many of you are running SQL Server yet on those virtual machines? Great, so I'm going to talk about both offerings and compare and contrast the scenarios for each and do a whole ton of demos. For all of you who have not done Azure development, this should be a great introductory session to relational data in the Azure cloud as well as put in the mix the whole Azure platform and what relational data means to the Azure platform. Let's jump right in. This is my depiction—and probably Azure team's depiction, because I stole their slide—of the Azure platform because I'm going to focus more narrowly on a couple of these areas, but I need to set the stage for what relational data is all about in the Azure cloud and then also set the stage for the fact that there are multiple data services on Azure. It's not all about just relational data. At the top what we have are app services that you would typically code against. The first square you see up there is caching. As a database developer, or a DBA, that ought to be near and dear to your heart. This is one of the LEGO blocks of all the LEGO blocks up here that you might assemble into a complete Azure solution. Why is caching important? This is an object cache. It's an object cache that can be used in a distributed fashion to store session state for web apps that are running on multiple load balanced web servers. It's also an object cache for new database developers that can store frequently used query results, any object. It's got a memcached D interface. It can be used from .net or Java or node.js. It's platform agnostic. But this is important when you think about database development on the Azure cloud in terms of increasing perf, because if you know the data doesn't need to be up to date and the query run every time, you can set a cache policy on any object such that maybe the query is only run once every 30 minutes in a data refresh, once every minute, once every 30 seconds, once a night, and then you're reducing the load on your database server, no matter what kind of database server that is, such that not as many concurrent queries are going to be run at the same time as users pound your website or the mobile app or whatever the application is that you build. That sets the stage for app services. Caching is very important. Identity very important. Windows Azure Active Directory Service is an active directory service up in the cloud, although I'm going to talk a little about Azure VMs, how you can even attach VMs running in the Azure cloud to your own corporate domain controller and active directory structure using Azure virtual networking. Identity is a key service. Service bus. If you think BizTalk/MSMQ service bus is a cloud-designed integration technology that includes a durable message queue that you'd use in the app service layer. Media is more about, as the name suggests, streaming media, broadcasts and the like, real-time TV shows, etc. CDN, content delivery network, another type of cache server that will take objects stored in Azure storage and front end them in one of—I think we've got 24 front-end edge cache locations around the world such that users in Brazil, even though the data center hosting the app might be in the US or in Europe, users in Brazil might be getting their static content like PDF files, word docs, etc., from a front-end cache server delivered much more quickly because it's more local to them. It doesn't have to diverse as far geographically over the Internet. CDN is content delivery network. Integration services, HPC is high performance, computing, analytics, which we'll talk about. That's the app services layer. Down below, virtual machines. I'm going to talk extensively about virtual machines and specifically hosting SQL server in an Azure virtual machine. This is one way to run compute. Windows Azure virtual machines were in preview as of June of last year and went into GA commercial release, just on April 16th. Virtual machines are now fully supported of Microsoft service on Azure, which is good news, and virtual machines, I might use the words "infrastructure as a service." It's all about infrastructure as a service. Spin up a virtual machine. Load whatever software you want on it. It's yours to party on. It just happens to be running on a Windows Azure Data Center, so you didn't have the outlier for the cost of the hardware, the purchase requisition for the hardware, the networking, the setup and the hardware warranties, that data center space. All of that goes away, because you're hosting that VM in one of our data centers instead of your data center. Cloud services, I'm going to talk about a way to use platform as a service. A great example of this is hosting a website that's load-balanced web form, but it could be any type of load-balanced service. For instance, I do a lot of load testing on SQL server. I use Visual Studio Load Test, if any of you have used that, or you could pick another load test tool, and I spin up the agents that drive load, which are running on multiple VMs, on a cloud service such that I can dial up to 100 agents or down to 1 agent or even shut it off overnight so I'm not getting charged, so I use cloud services in more interesting ways than just running a website. Websites are the one in the middle, which I guess I skipped, are a quicker and easier way to create a website hosted in Azure that could be attached to a back-end database like SQL server in a VM or Azure SQL database, but it doesn't have to be an asp.net site. There are templates that do things like WordPress, [inaudible], all sorts of templates that get you started backed by either Azure SQL Database or potentially MySQL as well as an option on websites. Mobile services are kind of like websites, again, platform as a service. A really easy way to get started by creating a back-end rest service that lives in the data center such that you can then build native UI mobile device applications for iOS, Android, Windows phone, of course, Windows 8, but all of those device applications that run locally on the device and are optimized for that specific device are utilizing the exact same back-end rest service running on Azure with all the security, etc., built in. That's mobile services. Now let's get into the data services. SQL Database, formally called SQL Azure, now called Azure SQL Database. I'm going to talk about that at length. Based on SQL Server 2012 technology. Again, a PaaS service, and by a PaaS service I mean platform as a service, and I'll describe what I mean by that and what the advantage is and sometimes the constraints are around a platform as a service in terms of taking away a lot of the management overhead that you ordinarily would have, but at the same time that sounds great. Take away the management overhead that you ordinarily would have, think about you're also giving up some level of control, and hence why it's so critically important not only to have Azure SQL Database but now also have the ability to host SQL Server in Azure VM where you still have the maintenance of a VM, you still have to apply service packs to the VM, configure the OS, configure the disk, configure the networking, apply service packs, upgrade your database. The same kind of IT management overhead of that VM exists whether it's running in our data center or your data center. Platform as a service takes away all that. We do all the security patching. We do all the OS upgrades. You don't configure a VM. You don't worry about an operating system. All that's abstracted away. There are applications in your organizations that will be ideally suited for Azure SQL Database and platform as a service, but I'll guarantee for larger companies here there are applications in your organization where Azure SQL Database makes you give up so much control that you'll want to look to SQL Server in an Azure VM with the IaaS feature where you maintain that low-level control. Okay, HDInsight, our big data service. A big part of our data platform. HDInsight is our implementation of a dupe running in Azure clusters. It's in preview now. It's not yet generally released. But we're doing—I think you've seen demos in the keynotes already, and there are lots of sessions on big data that will be covered here at TechEd. Tables. Table is a NoSQL store. It's a semi-structured store that sits on Azure storage, and if you've heard the term NoSQL it's basically a file-system based key file index strategy where you don't have a relational data engine. It can give you some great scalability and some great economics, because you just charge for the storage, which the non-reserved rate is at 10 cents a gigabyte a month. It's really cheap, but you don't have all the great relational query capabilities. It's not a relational database. It's a file system, a key file index structure sitting on Azure disks. And then blob storage, which I call Azure storage now. Everything here is roughly based on Azure storage. When you use the table structure, you attach a hard drive to an Azure virtual machine. That'll fit in Azure storage. That's a core service of the Azure platform. You might just use it to store files, but I'm going to show you hopefully some really cool ways you can use it above and beyond storing files in the cloud. But think of it as essentially a dropbox, a sky drive. It's a file system in the cloud, Azure storage. And then in networking what's really important here is especially Azure virtual networking. This is going to allow you to do some really cool hybrid scenarios. Not every database can be moved into the cloud. You have compliance issues in certain databases, privacy concerns with certain types of data, and so it's really critically important as an enterprise player that Microsoft provides the ability for you to host an application in the cloud and seamlessly access data hosted in the cloud but also seamlessly access some primary databases that you want to remain completely on premises, and it's Azure virtual networking that allows you to do that. You can mix and match or have maybe even your entire database still hosted in your data center but the app running in the Azure cloud, whether it's a mobile app, a website, or what have you. And then traffic manager, really quickly, traffic manager is a way to achieve disaster recovery within an application such that you have the application running in multiple Azure data centers, and then if one fails, you can have fail over to another region. For instance, an earthquake, a flood, in our data center. Then it can fail over to a secondary data center in another part of the world. Where are those data centers? The white dots are the content delivery front-end cache servers, but the primary data centers are the green dots here. We've got 4 in the US, 2 in Europe, and 2 in Asia, and when you create an app or a database or a VM one of the first things you do is you choose which data center to locate that in. What do I want to mention? I want to mention that we also announced that we're adding a couple of new data centers— they're not there yet—in Australia. Of course, Azure as a platform continues to grow. There will be 2 new primary data centers in Australia. I don't know the exact timeline, but those are announced. That's an overview of the platform, and I'm now going to focus in on data services and virtual machines. Data services beyond just the relational. Let's start on the left-hand side of the slide with the non-relational stores. I talked about the blob store. It's best for an expensive, scalable storage of data. It's an underlying platform component of Azure that's one of the first Azure services ever shipped. It's a file system in the cloud, and I'll show today many ways that you can use it beyond storing GIF images or JPEG images for your website or PDF files or streaming media content. Tables I just mentioned. That's the NoSQL store. HDInsight I mentioned as well. That's our big data solution that sits essentially on top of Azure virtual machines with some pretty nifty quick configuration of dupe clusters for Map/Reduce jobs, and then finally on the relational side where I'm going to now squarely focus SQL Server running in an Azure VM, that's the IaaS offering, infrastructure as a service, and then Azure SQL Database, and I get fined if I call it SQL Azure today, so someone keep track, Azure SQL Database, which is our platform as a service offering, and I'm going to demo a bunch of these starting right about now. At a high level, where would you use one versus another? I talked already a little bit about control versus lowered admin costs. On infrastructure as a server with a VM you're running boxed product in an Azure virtual machine. It's got whatever boxed product edition you install. It's got all those features. It's no different than running SQL Server in a VM in your data center, whether that's VMware or Hyper-V. It's no different than running SQL Server in any VM. It's boxed product. You install SQL Server Enterprise, you've got Enterprise features. You install SQL Server Standard Edition, you've got Standard Edition features. App compatibility is key here. Azure SQL Database doesn't yet have and probably never will have all of the features of SQL Server Enterprise. If you've got apps that you're looking to migrate or new apps that you're building where Azure SQL Database doesn't support those features you know that running SQL Server in an Azure VM will give you all of those features that are in boxed product, and that's the best way of thinking about SQL Server in an Azure virtual machine. You get to control the environment. It's boxed product. You eliminate all the hardware costs, and the hardware costs are just part of it. It's the time it takes to requisition hardware for a new project, to get it shipped in. It's the space it takes in your data center. It's the power it takes. It sucks your electric bill down. It's the bandwidth that it takes to have an ISP that's providing high bandwidth Internet access or even DMZ and local CorpNet access. It takes all of those costs away. Those infrastructure costs go away. And not only do they go away, but the expense that you pay in terms of the pay as you go. You're paying for the virtual machine for the size of it and how many hours it runs. You get a monthly bill. That's op ex expenditure. It's not cap ex. There are certain tax advantages to hosting things in the cloud, because you don't have to capitalize and tax deductions, your hardware or your software. It's all op ex. You can write it off as an expense on your taxes as you submit your taxes. And then it can decrease time to market. Provisioning a new virtual Azure machine takes minutes versus buying new hardware and setting it up in your data center. Getting it installed, configuring it, etc., can take days, weeks, months. And then platform as a service. It's a fully managed service. It's still going to eliminate all those hardware costs, so it does what IaaS does, but it's also about that and administrative costs, lowering admin costs. You don't think about creating a virtual machine. You don't think about applying service packs or security fixes. We do all of that for you as part of the service in the data center. You don't think about setting up high availability. It turns out—and I'll demo this—every SQL Azure Database is highly available by default. It has 3 replicas. That means we can have an SLA on Azure SQL Database. Today that SLA is 99.9% uptime, but that's at the database layer. The idea is no app downtime. I mentioned the auto upgrading. Someone ought to raise their hand and say, "What happens when you apply an SP to SQL Server or to the OS?" The first thing you have to do is reboot. How can you have no app downtime in Azure SQL Database if you're doing all this patching and management of the software under the covers or behind the scenes in the data center? And the reason is because we've got essentially an always-on cluster always running, and we do a rolling upgrade, so we have a fail-over node, a primary node, and even a third fail-over node to give you that SLA and that uptime even as we do various security patches or upgrades of the database software or the OS software in our data centers. That's the key with the Azure SQL Database platform as a service. With that said, I think I'll talk about a couple scenarios and jump into demo. Really key scenarios. Notice this slide for SQL Server in an Azure virtual machine, which is on the left of this slide. The first scenario is tier 2 and tier 3. The biggest Azure virtual machine today that you can create is 8 cores and 56 gigabyte RAM. A lot of tier 1 databases aren't going to be hostable in the cloud from a performance standpoint. One of the first things you need to do is do your planning and capacity planning for what's appropriate to move to the Azure cloud today and what's not. Those VMs will grow over time, but that's what's selectable today. Yes, we have a quick question.>>[inaudible audience question] The biggest Azure virtual machine today is 8 virtual cores with 56 gigabytes of RAM. For you BI guys that love databases, servers with hundreds and hundreds of gigabytes of RAM, you realize right away that's going to be a constraint for certain types of workloads. But for tier 2 and tier 3 apps, which probably describe the vast majority of apps running in most businesses that are not those tier 1 apps, it may be an ideal solution for you because it offers that boxed compatibility. It's very easy to migrate apps to SQL Server in an Azure virtual machine because it is boxed product. It's no different than running SQL Server in your own data center because you can use Azure virtual networking to attach a virtual machine to your own corporate active directory and still even apps using Windows authentication will work against your own active directory structure. It makes it very easy to migrate existing apps and get running very quickly. Development test is a key scenario where you spin up a dev test environment. Your dev team might be small. It might be big. In a dev test environment you spin up a SQL Server and your developers are right away running against a database that might be your system test database or your load test database. You can turn it off at night, and the first developer in the morning comes in and turns it on so you're not even getting charged for it while it's running overnight and not in use. Dev test scenarios are key. Another really interesting scenario with Azure that I hear a lot from customers and have over the years is using Azure for disaster recovery purposes. One of the cool things that you can do with SQL Server in an Azure virtual machine is you can set up AlwaysOn clusters in the cloud. Okay, that's cool. It sounds a little bit like Azure SQL Database I was describing that already has 3 replicas built in. But one of the really cool things you can do is a hybrid scenario where your primary database is in your data center, but you spin up a secondary fail-over database in the Azure cloud. Should your primary database in your data center go kaput for whatever reason it would then be AlwaysOn availability groups, which is a feature built into SQL Server 2012. It would automatically fail over to the disaster recovery database running in one of our data centers. If your data center is flooded, earthquake, or something goes wrong you get that fail over and what we'll call a hybrid HADR disaster recovery scenario. And then for Azure SQL Database the PaaS service, really we're positioning that for developing new cloud apps. I talked about some of the features. I'm going to show them off extensively here in a second, and then also you can extend on-premises apps. There is no reason why for both SQL Server and Azure VM or Azure SQL DB—your app doesn't have to live in an Azure data center. Your app might live on premises but be talking to an Azure SQL Database or be talking to SQL Server in an Azure VM. Your app might live on Amazon— God forbid I said the word—and yet be talking to Azure SQL Database running in an Azure data center or SQL Server running in an Azure VM. They are completely agnostic as to where your app logic lives. Okay, these are the sizes. You asked the question. These are the sizes. I'm going to talk about virtual machines now. And the numberings look off, but I tried to arrange them by increasing size. We went to this A0 through A7 naming structure because if you think about it, once you reach extra large what do you call the next size, ridiculously large? Eventually that naming scheme runs out of juice as we continue to increase the virtual machine image sizes. We're A0 through A7. A0 is shared. Literally, you're running in shared resources. They're not dedicated, and then 1 core all the way up to 7 cores. At launch, we added 2. We added the A6 and A7 images. They have a lot more RAM in them. They weren't available at preview, but on April 16th we launch with them, which is important, especially for a database that had a lot of read operations that can come out of memory or maybe some specific BI scenarios where you need that more memory, and this is what we have today. As you can imagine, this will grow over time. One interesting note on the right column is how big can a database be in terms of physical size of the database? What you do with SQL Server running in Azure VM is you attach virtual hard drives to that VM just like you would in any Hyper-V or VMware environment. You're attaching your virtual hard drives. Those virtual hard drives live in Azure storage, as I mentioned at the outset, so the number of hard drives you can attach is listed for each of the sizes with the most being 16 virtual hard drives. Each virtual hard drive can be up to 1 terabyte. Something really important to mention about Azure storage, not SQL Server specifically but Azure storage as a base storage service, because it's highly redundant. There are advantages to having these hard drives hosted in Azure storage, which is network-attached storage. The first redundancy level is that every right is synchronously committed in 3 availability regions within a data center, so it's very, very safe just from that standpoint. It's also by default asynchronously committed, every right operation asynchronously committed to a secondary data center somewhere else in the world, and that's called geo redundancy. Anything, including your virtual hard drives that are in Azure storage, are extremely safe. They're geo replicated and replicated within a data center. You think about files that are important, and one of the most important files probably for all of you seeing as you're in a SQL Server session is a database backup. One of the key scenarios that I'll talk about and that we're enabling is backup your on-premises SQL database directly into Azure storage. One of my first jobs when I was in high school coming out of high school was every Friday I'd take the tape backups to a bank vault, and that was the company's disaster recovery policy. Now Azure storage, cloud storage offers you the capability of getting it offsite in a very safe and protected fashion. That's really key for thinking about Azure virtual machines or just Azure storage as a service. Yes. [male speaker] If this is too much detail I can take it offline, but do you do any kind of a staged restore? For example, if I had a specialty and I wanted to be able, during a 2-week period, to have the ability to say, "Wow, I need this table back as it was at Wednesday at 7 PM." [Greg Leake] Let's take this one offline. I'm going to need to connect you with an Azure storage low-level expert to answer that one. Yes. [inaudible audience question] [Greg Leake] Not today. Hot, cold, etc.? [male speaker] No, no, latency SLAs. [Greg Leake] Oh, in latency SLA? Not today. [inaudible audience question] [Greg Leake] No, not today. What we have done is publish guidelines, and there are guidelines, and you can imagine things change in the future that you'll get 500 disk I/O operations per data volume when you're attaching it to a virtual machine, and then you use a strategy—and I'm going to demo this— of SQL striping and file groups to stripe tables across multiple volumes to increase your IOPS rate, which of course has a lot to do with the latency as well. We publish guidelines that you're going to get 500. Today in our benchmarking we're getting 1,000 to 1,500, but you are on shared resources, and so you could think about future enhancements that would be pretty cool, but we could get SLAs down to that level. Today not yet. Great question. Let's demo SQL Server running in Azure virtual machines. What I've got going here is Internet Explorer. How exciting is that? This is the HTML5 interface to Windows Azure. It used to be Silverlight. Silverlight is cool, but you couldn't run it on an iPad or a mobile phone, so now everything on Azure, the interface is HTML5. You can do all your management from any device you're running whether it's Windows or some other operating system or even a mobile device. It's an HTML5 website. Every operation that I do here and that you can do in this portal via graphical points and clicks has a rest interface, because I know most of you are developers and DBAs that use scripts to automate everything. Everything can be automated, either via a rest API or powershell. Two ways that we expose everything that you can do in an Azure data center are powershell and a rest API. You can create JavaScript code that drives creation of a virtual machine, deletes a virtual machine, whatever. Everything that I do here can be driven via one of those APIs. I'm in my virtual machine. You'll notice when I showed that overview slide in the beginning with the map and all the services you'll see most of those listed right here. There are mobile services, cloud services. This is Azure SQL database. Here's storage where I could go in and create tables if I wanted to or file containers, media services, etc. You see almost everything. There are websites up there. But I'm going to focus on virtual machines. I've got 3 pre-provisioned, but let's start from scratch. How easy is it to create SQL Server running in an Azure virtual machine and start using it? I'll say new, and notice you can do a quick create which would be a blank image, and then you can put anything you want in it, or you can do from gallery, so I'm going to choose from gallery, and what we've done is we've provided a bunch of preloaded images that have various Microsoft software on them, SQL Server being one of the workloads. There is a Sharepoint. There is a BizTalk, etc. And you'll get charged the compute rate plus a little upcharge because it turns out if you use this gallery image you don't even need a SQL Server license. It's built into the hourly rate depending on whether you install Web Edition, Enterprise Edition, or Standard Edition. Of course, SQL Express is always free. Those are the template images. You can create your own templates, and they'll show up in your own gallery. If you're an IT administrator and you've got business groups that are going to be wanting to create Windows Azure virtual machines you can create your own templates with your own corporate virus scanning software, anything preinstalled, and they'll be available in this gallery. System Center also can manage these virtual machines, so it's not limited to this Azure portal. I'm going to focus on the Azure portal. There are a lot of System Center demos, and that's just a theme of Microsoft is taking out entire enterprise software stack not just the tools that you use in the low-lying developer frameworks but the higher level tools like SQL Server Management Studio, like System Center, and enabling them such that on premises and the cloud work exactly the same way, and they can work in hybrid environments. That's really important to note on this section. That's not a question. Let's go with SQL Server 2012 and 2008. You'll notice on SQL 2012 on Windows 2012 isn't here yet. It'll be here very shortly. This list of gallery images will grow quickly over time. This has got SQL Server preinstalled. I'll give the machine a name, and I'll call it "techedlgl." Here are those sizes. Let's go 4 core, 7 gigabytes of memory. Sounds good for a start. I'm going to give the virtual machine and administrator— this user name will be added to the administrator's group, and of course, I've got to type in my password twice correctly, which I'm very bad at when I'm talking, and I'll go next. Okay, now I'm going to give it a DNS name, and I called it "techeddemolgl," and we'll see if that checks out. Notice it knows whether that DNS is available or not. This is how you're going to do, for instance, remote logins over the Internet to this virtual machine running in one of our data centers. Storage account. You can attach it to an existing storage account. I'm going to have it create a new storage account for me from which all my virtual hard drives that I create and attach will then be stored in that particular account. A region and affinity group. Affinity groups are ways to group resources within, for instance, a single data center. I'm going to choose the data center right out of the gate, and I'm going to put it in—Southeast Asia sounds good. There is one of those 8 data centers. I'll choose my subscription, and I'll click next, and availability set. This is an interesting one. With VMs, if you create 2 that are identical mirror images of the VM, you're in an availability set, and you qualify for an SLA on the VM of 99.95%. Without an availability set, you have that one VM, and you don't qualify for the SLA. I'm not going to use an availability set here in the interest of time. I'm going to click check. Done. Now you're going to see it's going to start provisioning. This is going to take probably about 5 minutes, but in 5 minutes I've now got a completely pre-provisioned Windows server with SQL Server Enterprise already ready to go. But since this is a practical audience, I'm going to give you some tips, because the very first things that you'll find that I won't call frustrating but you have to do have to do with enabling this so that remote login will be enabled by default but remote SQL Server—so you can SSMS to attach this from your local laptop or inside your corporate firewall. The first thing you want to do—and to show this I'm going to go to an existing VM. Let's go to this guy. Notice here we get a dashboard. I'm not going to go into all the metrics, but we show some common PerfMon metrics for CPU usage, disk usage, etc. Of course, you can remote in and use PerfMon all you want. But one of these things is Connect, so Connect right from the portal is immediately going to open up an RDP session, and you type in your credentials, the ones that you created when you created the VM, and you're in. It turns out I'm already connected to this guy, so I'm going to pop into him. Here is the VM. I'm in an RDP, a remote desktop session to the VM running— in this case I think this one is running in the West US. The one I created is going to be in Singapore, and I've got Server Manager up, and I wanted to show you before I showed Server Manager the first thing that you want to do, of course, is configure your firewall, the local firewall on this machine. Whoops, I can go to control, because it's firewalled just like every Windows server is firewalled, and of course, SQL Server, you know what port, 1433. You want 1433 accessible if you're going to be querying this database from outside of a Windows Azure data center. I've got my firewall rule set up where I'm allowing inbound port 1433. Once you do that locally on the server— and of course, you could script this with powershell. I'm doing it manually, but you could script all this. Once you do that locally, which I won't do, because setting firewall rules is pretty boring, you also want to go to this, endpoints on the dashboard, and you'll notice the 2 that are there by default are powershell and remote desktop. You can remove them if you want, but you know what happens if you remove remote desktop. You won't be able to remote desktop in, of course. But you can remove anything and control the ports however you want. But I added this one SQL TCP port of 1433. You've got to do those 2 in combination to be able to get, for instance, SQL Server Management Studio to connect to this. Okay, I'm going to check off what I'm doing in my demos here. We've logged in. You can see if I go back to my remote desktop session here Seattle 1, I've got PerfMon there, but there is Enterprise Manager running locally on this VM, and it's already got SQL Server installed and configured to some extent. To some extent comes in a second in the next demo, which is optimizing this installation. I mentioned SSMS, and I added that endpoint. One of the first things I can do is come over here to SQL Server Management Studio, and I'm connected here to my local laptop database. I've got Enterprise actually running on my ASUS laptop here. That's the first database you see, which is an on-premises database. Of course, Management Studio can connect to any of your SQL server on-premises databases, but let's show you it as a SQL Server boxed product. I've got this. I guess I've got zoom, so you can see this. LGL, so there is the .cloudapp.net. That's my VM in an Azure data center, and I'm going to connect to it just like I would an on-premises SQL server, and voila, now there it is. You get a little white dot on your database, but there are my databases, of which right now I only have one, my tables. It's full of SQL Server—it doesn't matter. Management from a single pane of glass, whether your database is on premises or hosted in a SQL Server running in an Azure VM, and later I'll show the same thing hooking up, of course, to Azure SQL database. It doesn't matter how you deploy your database, on premises, platform as a service with Azure SQL Database, or SQL Server running in Azure VM, the deployment option you decide. That's fine, but we want all of our tools to work in a familiar and natural way no matter where you choose to deploy your database, hence the single pane of glass functionality here with SQL Server Management Studio. Okay, so let's talk about disk optimizations for SQL Server in an Azure VM. You'll notice over here on this guy— let's go back to dashboard. On dashboard I can click this little attach button down here. This is how I would create, and I'll attach either an existing VHD, an existing disk that maybe has something on it or an empty disk, and by the way, you can import and export local Hyper-V VHDs from on premises to Azure either using the portal here or using System Center. That's really important so you can create, again, your templates. You maybe store in on premises and you want to move it up to Azure. You can do that, and you can also use the new version of System Center to export a VMware virtual machine to a Hyper-V virtual machine and then move it into Azure. Everything on Azure and virtual machines is Hyper-V by default. But you can use that conversion that they've got now in System Center to convert a VMware virtual image to Hyper-V and then move it into Azure. Here's where I would specify the size of the data drive, and you can be up to 1 terabyte, so 1,000 gigabytes is a terabyte last I checked, and I would say go ahead and create it. By default for SQL Server for your data drives you want to start with no caching. It may sound counter-intuitive, but one of the reasons that you want to start with no caching is that you can use the cache, especially a read cache. You wouldn't want to use a write cache on these data drives for a database workload for data integrity reasons. But a read cache seems reasonable. The reason you don't want to use it is that's a local host cache, and it will increase your CPU usage of your local SQL Server machine. Now, depending on the I/O characteristics of your database workload we just published a white paper that says, "Hey, for some database workloads using read caching may be appropriate." There are some guidelines we published today, as a matter of fact, on MSDN around when to do that, but I just say no database caching, hit the checkbox, and all of a sudden what's going to happen when I hit this checkbox it's going to take a couple minutes, not really even a couple minutes, probably about a minute. If I go back to my remote login session here let's pull up Server Manager and look at the disk management. Another one of these will pop in here as an unformatted, uninitialized hard drive. It will show up as a local Windows server hard drive, and then I can initialize it, format it, partition it, do whatever I want with it. In this case, you can see my existing VM has 2 hard drives, one that's initialized and unallocated and one that I've allocated, and if I click on the one I've allocated, which is this one, you can see I've assigned a logical drive letter of F to it. I'm hosting. With SQL Server you always want to host your transaction log and your data drive, your data file, your MDFs and your LDF, on a data drive. Don't host it on the operating system drive. Temp DB, fine to keep on the OS drive. Always host transaction logs and data files on a data drive that you attach like I just attached here. In this particular case, I'm running a database. It's got a single database called SB Test. It's not a very interesting database, but I'll use it to illustrate a point here in a second. This is running on one data drive. The transaction log is on that same data drive as the MDF file, so at least I don't have it on the operating system drive. I've got it on my data drive. But I'm only using 1 data drive. Let's go run a quick benchmark on this guy. I'll pull up PerfMon, and I've got a client VM that's running a benchmark tool that is going to pound this guy with a bunch of threads hammering this guy with various read and write operations from another virtual machine to the SQL Server, and I've got it prepopulated in a powershell script, so hopefully all I have to do is hit enter. And then when I go back over here to the actual SQL Server box, in a second we should see this get some activity if I did everything right when I set up this demo. We'll give it a second. I've been known to make mistakes when I set these demos up. That looks pretty good. Well, maybe, maybe not. Let me see what I did wrong, if I did anything wrong. No, it's still running. I think I'm just impatient. It creates a bunch of connections up front and then gets ready and does a driver test. There we go. There is my activity, and what I'm plotting here is processor time, and then I'm looking at logical disk reads per second and logical disk writes per second as a quick look-see to see what this thing is doing, and it starts off pretty good. But you're going to see after maybe about 3 or 4 minutes when it gets in a steady state the CPU is going to start to really fluctuate and only average out—in fact, I think it's starting already— only average out at about 50%-40% CPU usage. And if I go in and look at what's going on in the logical disk I can get some information on my reads and my writes, but let's keep going with this one. Even though it's not doing what I want it to be doing right now that's fine. Why is it a problem if your CPU—if you're counting this you know you're counting it with enough users. Why is it a problem that your CPU wouldn't be at 100%, which it is right now? You're not getting utilization. It means you've probably got a disk I/O constraint, because you can't get your CPU usage up because you're bound on the disk access. Does this make sense to everybody? How would I tune this guy? I think this settles out after a while, and the CPU starts to drop when it gets into a certain phase of the benchmark. I would tune this guy by instead of putting both of those on the same logical volume I've got another image here where I switched—sorry, a little harsh change. I accidentally provisioned Windows Server 2008. It should have been Server 2012, but the same idea. I've got 5 data drives here, and I'm using J for transaction log for this database, and I'm using SQL striping and secondary files with a file group to stripe my table data across all the other 4 data disks. Now I'm getting I/O across all the different data drives if this makes sense. In fact, if we wanted to look at this SQL that does that, if you're not familiar with secondary file groups, this is how I did it. You're creating a file group, and I'm calling it SBtest 1 or SBtest_FG1 and in secondary files. The first one goes on the F data volume. The next one goes on the G data volume, the G drive. The next the H drive, and the final one for the data file is the I drive, and the last one, the J is for the log. If you have multiple databases you could have multiple transaction logs also chopped up across different drives, and it turns out then when you create your database after you've created all these secondary files you've got something that looks like this, create table, on that file group. Now, you're used to running on-premises hardware probably where you've got a RAID array and you don't think about doing things this way. Your RAID array takes care of that. You're automatically getting OS or hardware striping in your SANs array or what have you via RAID 10 or RAID 5 or what have you. But today on Windows Azure we recommend that you use this SQL striping strategy not OS level striping. You can use OS level striping if you want, but you'll get better performance characteristics out of SQL 2012 in Azure by using this strategy of partitioning files across a file group that's spread across multiple data volumes. Does this make sense to everybody, or did I confuse everybody on that one? Let's go back and see if it's doing what I thought it would be doing. I guess the test finished. We got a little bit of fluctuation. If I was to run this same test on this guy that's properly partitioned across all those volumes, which is this guy, I'd go back to—whoops, I don't want paint. I want this, and hit enter on this guy, which is going to hit the other one. I don't need paint, and then I'll go back to that guy, and we'll look at the PerfMon for this guy, and what will happen is when the load starts, it will be pegged consistently at 100% CPU and you'll see the number of disk writes and reads is tremendously higher. In fact, a factor of 5 higher than the one where I've got a single data drive. This is really important. There is a great MSDN paper that was published today on how to properly configure SQL Server for an Azure VM in conjunction with Azure storage, and here it goes. It'll take a while, but it will get right up to 100 and stay there, my CPU that I'm looking at. Okay, that's partitioning. Let's move some data to this Azure VM. That'll be the next thing I do. I'm going to show you an app I've got running, and it's a pretty simple app. It's an app you can download off of MSDN, and it's a little stock trading scenario, and I'll log in, so think of a little sample app emulating E-Trade or Ameritrade or something. I'll log in as a user, and I get some quote data. I've got some account data. I've got a portfolio. If I wanted to buy a stock, I can go ahead and place an order. A web application with a back-end service. I'm going to use this at the end of the presentation as well and show you how we upgraded this for mobile access and a whole bunch of other cool stuff using Azure. But right now it's running locally on my laptop. What if I wanted the quote database to be hosted in Azure but maybe keep my account database local on my laptop on SQL Server, because maybe there are some privacy concerns with that data, compliance reasons why I want part of that data set in one database that's on premises but another part I'm perfectly happen to put up in the Azure cloud. I'm going to migrate this database right now from one premises to my SQL Server running in an Azure VM. I can close out of these guys. I don't need them anymore. And you'll notice in SQL Server Management Studio here's that database running on premises. Here it is on my local ASUS laptop here, and I'm going to take the quote database, and I'm going to right click on it, and this integration will get even tighter with the next version of SQL Server, and I think you're hearing a lot about that today and tomorrow. I can't go into details, but the integration gets tighter and tighter. And I'm going to say "Export data to your application." I'm going to hit next. I've pumped up the font, so if it looks ugly that's so everybody could see the demos. And you'll notice right here, "Save to local disk." It's going to create a backpack file. A backpack file is kind of like a backup file, but it's an open file format that third parties can look into and create, etc. The backpack includes schema and data, and you'll notice I can save that backpack either to local disk or save it directly to Azure storage. I've got an Azure storage account. This is my encryption key that I pasted in, so I prepopulated it. It's a very long, digital cert key, and I'm going to connect to my Azure storage account. Once I'm connected to that, I'm going to pick a container, which you can think of it as a subfolder, and I'm going to put it in a demo container, and I'm going to hit next. And it already exists, so I'm going to overwrite the existing one, and I'm going to hit finish, and now it's taking this entire on-premises database, schema data, stored proxy, everything, and putting it into this backpack file from which I can now recreate that database in another server in another location, and I'm going to do it on my SQL Server Azure VM. We'll let this go. It takes a little bit of time. [inaudible audience question] [Greg Leake] I will show that in a minute. The export and import is obvious, because I'm going to do the import operation in a second in my VM. There is another option for Azure SQL data. This is creating a backpack. That backpack can then be put in either SQL Server in an Azure VM or we have something called an import service for Azure SQL Database. You can also move it into Azure SQL Database. But there is another option on that menu that this gentleman noticed which is you can do it all in one fell swoop against Azure SQL Database. Just right click on a database and say, "Stand me up a new Azure SQL Database up in the cloud," the PaaS solution, and it will go through a wizard and do that automatically as well. But anyway, here I've got my backpack, and I'll show that option that this gentleman noticed. When I went to Task here he noticed "Deploy database to SQL Azure." This essentially in one fell swoop creates the backpack, talks to a Azure SQL Database in the cloud and imports the backpack in one fell swoop as the wizard. In this case, I've just created the backpack so far. I've exported the file to a storage account in Azure. Now I'm going to log in to my Azure virtual machine— well, I'm already logged in—which is down here. And I'm going to say Tasks. Let's see if I do this right. Import data to your application. And you'll notice, again, I can go to my Azure storage account, connect right up to it. It's found my demo container, and I don't want account. I want quote. That's the one I created. I've got a couple of these out there, and I'll click next, next, finish, and boom, it's now standing that database up in my SQL Server in an Azure virtual machine. You can also, of course, use integration services to move data between these things, including Azure SQL Database or SQL Server in an Azure VM, like the data import/export wizard, and you can use the bulk copy command. They are just SQL Server Databases under the covers. [inaudible audience question]>>This is very small. [inaudible audience question] [Greg Leake] It's not, because one thing that you've got to think about is I'm going over this facility's firewall, through their firewall, so it's as fast as this network is, and that's always going to be an issue is moving lots and lots of data from on-premises to the cloud can be very tricky sometimes, and if you're moving it truly from on-premises over the pipe it's always going to depend on how good your pipe is on the outbound side. If you're a small company with an ISP that's got very low bandwidth rates, then you're going to pay that price when you try to move large volumes of data. But this is going and moving, and this network is really bogged down right now. It's usually much faster when I do this demo within CorpNet within Microsoft. What was I going to mention? It can take hours. Even for very large terabyte data sets it could take a couple of days to move those data sets. I'm not going to lie to you, and that's the bandwidth issue of getting from on-premises into the data center. Once you have it in your Azure storage account, though, once it's in your Azure storage account, now, that becomes a local operation within the Azure data center, and you're not paying that bandwidth hop-over-the-Internet price, because it's in your Azure storage now, and everything you do within Azure now is within that data center, unless you move it to another data center, of course. Then you're hopping over the Internet again. Anyway, it's done, and now I have that quote database here running in SQL Server in an Azure VM. There are my tables, everything. I've noticed one thing that it doesn't do. It moves the login, but it creates a random login password for security reasons, so if I wanted to use this database, which I won't do right now, I'd have to go in and assign the password that I want that my app expects, because I'm passing in an encrypted password from my app. But then all I need to do for my app to use this database for the quotes is literally go into the web.config file or wherever I'm storing my connection string and point at this database instead of the original one, and voila, the app just works the same as if it's all running locally in my data center, or in this case, on my laptop, and I won't do that step. The latencies, well, maybe I should do that step, because the latencies are interesting to think about. If I was to do that step, I would have to do a couple of operations that I hope I get right. I'm going to do what I just said I had to do, because this backpack operation creates a random password. Hopefully I'm getting this right. I might not be. Okay. And I've got to get this one as well. Looks good. Okay, and then I'll go back to my app, and I'm going to do something funky. Everybody close your eyes. I'm going to use this thing that I built that allows me to change a configuration setting without opening up web.configure or anything. This is an app that's built with the Azure SDK that can examine everything going on behind the scenes here. Let me log in correctly. And I'm storing all my configuration settings in an Azure SQL Database, actually, and hence, I'm using this interface to change them. One of the settings is what's the pointer, the connection string to my quote database? I'm actually not putting it in web.config. I've got it here in my repository, and so I'm going to change this value from Leake-ASUS to this guy, and I can get that guy really quick with a copy and paste by doing that and pasting him in there and updating my configuration, and now with any luck, we'll see if I did this right. There, believe it or not, all these quotes now are coming—the account information is still coming locally, but all the quotes are coming from that database I just stood up in a SQL Server in an Azure virtual machine. And you think about latencies, because we're talking about moving data, but from an app performance standpoint this is perfectly reasonable. My latency might be 50 milliseconds, but 50 milliseconds is still sub second [inaudible] for all these pages. If I was to get a quote—for instance, this guy. Let's get a quote on S101. You can see it's still a really, really fast app. That's that. And of course, we could also have this data in an Azure SQL Database. I just wanted to show some of the import/export capabilities in SSMS for SQL Server in an Azure virtual machine. Back to slides really briefly. Whoops, that's becoming annoying. There is another feature that's not as well documented that we just added in Service Pack 1 Cumulative Update 2, which is called Backup to URL. A more efficient way than using a backpack is to actually take your backup file and have that go into Windows Azure storage, and this can only be driven today by T-SQL. You'll see new features in the next version of SQL Server that make this even easier via graphical consoles and the like, but this would be the T-SQL where you hook up to your storage account, and then you do a backup of the database directly to Azure storage. The backup is going to be a much more compressed file than a backpack, and then you can do a restore on the other side from that Azure storage URL. This was just added in SP1 of SQL Server 2012, so I wanted to mention that as well. Let's talk about disaster recovery in VMs. Here are 2 scenarios for disaster recovery. Are you all familiar with AlwaysOn availability groups that are new for SQL Server 2012? That's where you can have a cluster of servers, one acting as the primary and many potentially secondary databases that then act as fail-over nodes should the primary database fail. AlwaysOn works on Windows Azure, and you can create multiple VMs, one running SQL Server the primary database, and then AlwaysOn Synchronous or Asynchronous secondaries that are your fail-over nodes in what's called an availability group such that you can achieve HA on Windows Azure using SQL Server VMs. Another key scenario is a hybrid scenario on the right where I mentioned at the outset—I think I talked about this— where you can have your primary database on premises and just use Windows Azure VMs as your fail-over replica, in which case you're likely using asynchronous secondaries because of the latency going over the Internet, but you have a fail-over node running in an Azure data center while your primary database remains on premises, and that's the 2 boxes on the right, the or condition. You'll notice in this diagram it does require a domain controller be hosted either in your on-premises environment or on Azure. You do have to spin up a domain controller, because AlwaysOn relies on that Windows authentication and a domain active directory structure. Yes. [inaudible audience question]>>What's that? [inaudible audience question]>>Which device? [inaudible audience question] [Greg Leake] The trick here is—and this is very key— is you can use Azure virtual networking, in which case if you use Azure virtual networking, which I didn't demo, but it's extremely important, you essentially have a site-to-site VPN between your corporate site and your Azure "virtual private cloud" or virtual private clouds, and any of those Azure VMs can be domain joined to your corporate active directory server. It looks to your internal usage, System Center or active directory, just as if their VM is running locally in your data center. They just happen to be running in one of our data centers, and they're going over essentially a VPN, whatever the latest protocol is, and it's really slick, because it's a great scenario not only for doing this scenario where you're going from on-premises into Windows Azure and looking at Azure as an extension of your corporate data center but it's also really key for when you want to host an app in Windows Azure and have that app talk to a database that you want to stay on premises either for performance reasons or compliance reasons. Your app can still be hosted on Azure but be talking over this secure VPN virtual network to an on-premises database. That's a great question. Yes. [inaudible audience question] [Greg Leake] There are sessions on this I think starting tomorrow on Azure virtual networking. I'm not an expert but typically you use—and there are a couple layers. There is a light version, and then there is the normal version, I guess, that does more, and they're compatible with certain VPN devices from Cisco and F5, and there is a list of these devices. [inaudible audience question] [Greg Leake] Follow up with me, and I'll point you at the right session, because I don't know the answer. I imagine the answer is yes, but I don't know. We should get you to the right person. And then here is the Azure virtual networking slide, and you can define your own virtual private cloud in Azure. You can create an entire virtual private cloud in an Azure data center where you create your own subnets, your own IP domain structure. You can simulate something that you've got on-premises or what have you, and you can have multiple of these virtual networks run in your Azure subscription. Guidance, so really quick to cap what I said on SQL Server on an Azure VM. Capacity plan your workloads. Not everything is going to move up. You can increase performance by choosing the best VM image size, the number of cores and memory. Oh, one thing I learned is use SQL Server EE versus SE, and the reason there is there is a feature in EE that greatly impacts the efficiency of SQL Server against transaction logs. This is true of on premises or in the Azure cloud, and it's called GroupCommit. It's a feature in EE. SQL Server EE versus SE, that alone for certain workloads can increase your performance. Use multiple data drives, which I went through extensively with the file groups and SQL striping, and then for high availability AlwaysOn availability groups you need that domain controller. The good news is there are 2 MSDN tutorials that show for both those diagrams for the AlwaysOn that I showed in the previous slide that go through step by step how to set both environments up, whether it's a hybrid environment or an all-Azure environment for AlwaysOn. There are some really good tutorials up there now. Recapping SQL Server in an Azure virtual machine. Low TCO, no app changes required, all the familiar development tools work, a library of templates or you can create your own template. You've got high availability with the AlwaysOn capabilities. It supports all boxed product features like TDE, transparent data encryption, audits, anything that's in Enterprise. It's there when you install Enterprise on an Azure virtual machine. All the BI capabilities, etc. You have all that low-level flexibility control of the VM. You can do Windows authentication, corporate trust boundary for your app and your database, because you can domain attach these VMs or run a PDC up actually in the Azure data center if you want, either way. These are some of the big advantages, and its managed infrastructure, you get this SLA, and you get the single pane of glass management. I showed it with SQL Server Management Studio, because this is a SQL session, but there are lots of System Center sessions where you'll see System Center managing virtual machines, whether they're running locally or in the Azure cloud or a combination, the hybrid capabilities. On to Windows Azure SQL Database. This is the platform as a service offering. It's still the SQL Server engine, so it's still all the T-SQL stored proxies, all that. The engine is the same engine. It's a SQL Server 2012 engine. And so all those tools still work, SQL Server Management Studio, SQL Server Data Tools, ado.net, JDBC, all that stuff works exactly like it's an on-premises SQL Server box. That's really good. It's all the same T-SQL constructs. You host it in one of the same 8 data centers, soon to be 10 data centers. It's lower pricing, because there is no VM involved. One really big difference is that when you're running on a VM you're running on a dedicated resource. When you're running on Azure SQL Database, you're running on a multi-tenant environment. There is no VM that you create. You're on shared resources, and that's why it's so inexpensive. We can pack lots and lots of customer databases into these big containers we call nodes. But it also has some downside, and it has some downside because shared resources means you have a noisy neighbor problem potentially. Your neighbor is on your node, and you don't control what neighbors are on your node, and he's using up a whole hunk of CPU or disk. Well, that's impacting the performance of your database. What Azure SQL Database does is it imposes throttles essentially. It uses a SQL Server resource governor to impose throttles on individual databases to minimize that impact. But that impact is still there, and then we keep a certain number of cores in Azure SQL Database for management tasks like automated backups, replication. All of those things that happen automatically behind the scenes is done on dedicated cores that aren't even available for use by customer databases. That is a big difference. It's like the difference between—and people laugh who have kids when I use this analogy, but I'm going to use it anyway— a private swimming pool that you pay to build in your backyard. You've paid to build it. You've brought in the equipment, the contractors. It cost you a lot of money. You've got to maintain it. You've got to clean out the leaves and put the chemicals in it year after year, but it is your pool, just your pool for you to use versus a public swimming pool where you don't have to pay to build it. You pay as you use it, and it could be very economical. You save a lot of money, but on a hot summer day you may have to stand in line for the diving board in a public swimming pool. This is really key, the notion of shared resources. [inaudible audience question] [Greg Leake] Can you repeat that? [inaudible audience question] [Greg Leake] Yes, there are free trial offers available for Azure SQL Database. Is that what the question is? Yes, you can try it. There are a bunch of free trials available. You go up to MSDN. There is even a new 20-megabyte database that's free. There are certain ones if you have an MSDN subscription. I can't go through them all, but yes, it's something that you can try. But let's put it this way cost-wise. The smallest database that you get charged for on Azure SQL Database is a 100-megabyte database typically. That's $5 a month. $60 a year. That's with 3 replicas and HA built in. Before everybody panics about query throttles and shared resources, there are some huge advantages to a multi-tenant architecture like this, and cost is one of them. As an example, when you spin up Office 365 and you create an online hosted Sharepoint site or an Access app, a web app hosted on the web via Access, behind the scenes it's using Azure SQL Database. Think of the millions/hundreds of millions of Office users and think about if we had to spin up a VM for each of those customers that are creating some Access database or a Sharepoint site that needs to use a SQL Server backing database. The multi-tenant nature of Azure SQL Database lets us do that very, very economically in a way that would not be Internet consumer scale if you're spinning up individual VMs for each one of these things. It wouldn't scale at an Internet level and hence the low pricing. $5 a month is the cheapest. The biggest database you can host in Azure SQL Database, another limitation, is 150 gigabytes. It doesn't include transaction log. That's just the database file, so my little stock database has 6.1 million records and it's less than a gigabyte. Structured data is what you're going to store in an Azure SQL Database. You're not going to start throwing blobs in this thing. Instead you're going to throw your blobs in Azure Storage and store a link to those blobs in your database. But a 150-gigabyte database describes the vast majority of the size of most relational databases out there in the world. Not all of them. You've got a lot that are probably a lot bigger. But if you think about all the business compartmentals, small business, etc., whether it's Oracle, DB2, SQL Server, most databases, 150 gigabytes for a relational database describes probably 90% of them in the world. It's perfectly appropriate for a wide variety of databases, and that doesn't include the transaction log space. Transaction logging you don't even think about. It's handled automatically by the servers. But 150 gigabytes, getting back to my price example, is $228 a month. And that's with 3 replicas, an SLA on the data tier, etc. There are some huge advantages to this platform as a service in terms of cost efficiencies, but yes, you do give up a level of control, and hence why we want to have SQL Server in an Azure VM as another choice for hosting relational data in the cloud. And built-in high availability and redundancy, which looks like this. You create an Azure SQL Database logical server. It's not a physical server. It's a logical entity. Behind the scenes our software in the Azure data center creates these 3 replicas, just like an AlwaysOn availability group, in that data center such that you get this high availability, and I'm going to show that really quick right now, and that's why we have this 99.9% uptime SLA on the data tier, and we can do rolling upgrades, and you don't have to worry about security patching, Windows Update, or any of that that you would if you're running a VM. Let's show you how you create an Azure SQL Database. I'll go back to my management console here for Azure, and there is a tab here called DB. These are all Azure SQL Databases, and I've got a bunch of them, because I do a lot of demos, and it happens to be the product I concentrate on in Microsoft. I'm going to create a new Azure SQL Database. I'm going to click new, and I'm going to do a custom create. I'm going to type in the name of this, and let's call this Dandy, who is sitting in the front row, DandyTechEdDB. And here are the size constraints. By the way, you're only charged for the size that you're using. It looks at it on a daily basis, and so you're charged on a monthly bill. But if I choose 150 gigabytes, you're not going to get charged for 150 gigabytes unless you use 150 gigabytes all X number of days out of the month. If for half those days you're only using 1 gigabyte, you're only getting charged the 1-gigabyte rate. This is a max size. If I tried to insert a record that made it bigger than this max size it would come back and say the space is all used up. I'll go with 150 gigabytes. That's fine. I'm going to create a new server. I could pick one of my existing logical servers. Remember, a server in Azure SQL Database is logical, and I'm going to give it SQL auth login credentials that I can remember and the same thing I use for every demo. And like with VM, you choose the data center. Let's put this one in Northern Europe, which is in Dublin, Ireland. It sounds pretty good. I'll hit the checkbox. I want you to watch this provisioning. I'm going to dismiss the completed jobs. Down here at the bottom you see these little bars going? It's telling me it's provisioning the Azure SQL Database. Now, remember my VM. It's done. Well, it's almost done, I think. The VM took about 5 minutes. This literally takes about 10 seconds, and you've got your database ready to go, because the provisioning in almost instantaneous, which is really cool. In fact, it is done now. The bars aren't moving. Now somewhere in this long list—what did I call it, Dandy? Dandy something? I've got a database. Whoops, sort the other way. You go to page 2. There it is, DandyTechEdDB. There is the new database. It's a SQL Server Database ready to be programmed and get used. It could be a dev test database. It could be your production database or what have you. Let's go back to SQL Server Management. Let me configure this first. One of the things about Azure SQL Database is there is a couple of things that are built in that are kind of important. Let me go back to this tab. Sorry, I'm repeating this whole operation. Where is it, DandyTechEd? I'm going to find the server that it's on. We'll go to the dashboard here, and I get a server name. You notice it's a randomly generated name, because it's a logical entity, and on the server I'm going to go to configure. It's firewalled automatically. Now, I clicked a box that said, "Go ahead and let my apps within my subscription access this database," or my apps on my Azure data center within the data center access this database, but I didn't enable any access from the outside world yet. I've got to configure firewall rules, and I'm going to do the all rule for a demo, which is, of course, close enough to all. You can do this programmatically. Again, everything I've done in this management console can be driven by powershell or via T-SQL and/or rest APIs. It's giving me a little warning there, and yes, I've got access, and I'm going to hit the save button. Now any client IP can attach. Why am I going to do this? Because I'm going to now use SQL Server Management Studio from behind this convention center's firewall to go attach to this guy. The firewall rule update can sometimes take a minute or two to propagate through the servers, but we'll see if we're okay or not. I'll go up to SQL Server Management Studio where I'm now attached to my local database and my SQL Server in an Azure VM, and I'll create yet another connection. Oh, I forgot one thing. I can't remember the name of that server. They're tricky. There it is. Let's go to the dashboard. I've got to get the full DNS name of this server. I'll copy it right out of the portal, and I'll paste it in here, and I created a login credential, and voila. A single pane of glass. I think this is going to work. And I'm going to be connected to on-premises SQL Server running in an Azure VM and our PaaS offering as your SQL DB all within SQL Server Management Studio, so here we've got the blue dot, and we can see the databases that I've got. There is the DandyTechEdDB that doesn't have anything defined. The other thing that you get with Azure SQL DB is a web-based management portal where you can do some simple schema design graphically. None of you professional DBAs would ever use it, but it's got a point and click interface for creating tables and new values, etc. The other thing I should mention is I mentioned Azure SQL Database is used to back all Access web apps and all Office 365 online hosted Sharepoint apps, so it's already used by Office 365 extensively. It's also used in a couple of other ways within Azure. If I go back to my management portal, I talked about websites and mobile services. If I was to create a new mobile service, and this is creating essentially a rest back-end such that an iPhone or an Android or a Windows phone or Windows 8 or whatever can attach to the same common back-end business service, if I was to create one of these, notice one of the things you get is "Use an existing Azure SQL Database." Mobile services and websites also can automatically be backed by an Azure SQL Database, and then they're part of your Azure solution there. I don't need to create a mobile service. I've got about 3 minutes, so I'm going to bring this all together and show a couple of things. The first thing I want to show is I've shown a .net app, but I thought it would be kind of cool to show you this. Whoops, I think I clicked on the wrong one. This is a Spring framework app done in hibernate and JPA and the Spring framework, an ORM open-source technology. It's actually run by VMware. This is a sample app directly off their website running in Azure, running on Tomcat servers that are load balanced using SQL Azure Database, our PaaS offering via the SQL Server JDBC 4.0 driver. It's not just about .net Microsoft technology. We have a whole slew of offerings not only for a SQL database to be connected to from .net and JDBC, and in this case, a Spring framework app, and I'll do something more interesting here than show the graphic. I'll actually hit the database here. The platform itself can also—I didn't show you the images. You can spin up Linux images in Azure. There are not just Windows Server images. There is a whole gallery of images that are [inaudible]. There are something like 6 different distributions of Linux that are available to spin up, so if you wanted to do MySQL or Java development you can do that on the Azure platform too, but I wanted to show a Java app, a Spring framework app, actually using Azure cloud services, running on Windows with Tomcat and everything, and then using an Azure SQL Database just through our standard SQL Server JDBC 4.0 driver that's a free download on the Internet. And then the last thing I wanted to show is the fancier app, which is the next version of the stock trader app, which is already published on MSDN, but it's an update we did. This is the same app I showed in the beginning where I moved one database. This is now running as HTML5, so I'll log in here against those rest services I talked about, and it's hitting a Windows Azure SQL Database. This is getting my market summary. It's just a different UI, but it's HTML5. Of course, this is going to look great as a website in an iPhone or an Android or a Windows phone or just in a standard browser on any platform. There is my portfolio, my account, but because we're hosting this as a rest service hitting the Azure SQL Database and exposing a rest interface from the client we can do some interesting things that are code reused. Here's a Windows 8 version hitting the same back-end in the same database, so all the business logic is just created once in the Azure data center. All the database logic is created once in the Azure data center, and any device type can hook up to it. Here's the Windows 8, and this is a sample app. All of the code is published. You've got MSDN/StockTrader. You can download all the source for everything, but I'm saying it's a sample app to apologize for the fact that this isn't the prettiest Windows 8 app that you've ever seen, because a bunch of developers put this together. But it's got the things you'd expect, snap to view. It's even got search. If I wanted to get a stock quote, I can type in s:12, hit enter, and it'll actually get the stock quote, and I can buy shares, etc. That's the Windows 8 version. Okay, that's cool. HTML5 Windows 8, well, if we went a step further, if I go back to desktop, you'll notice there is an about mobile. If you've got an Android, just go up to Google Play and search for Azure StockTrader in the Google Play store. If you've got an iPhone, do a search for Azure StockTrader in the Apple store, and we've got versions that are about to get updated. The Android version especially we've redone for Ice Cream Sandwich and a bunch of stuff that makes the Android app look nicer than our current one. The iOS one already looks pretty good. The front end is a native UI for that device, but they're running against a common Azure SQL Database database and a common rest business service, in this case running in .net in the Windows Azure Data Center. And just to show it, I've got some screen shots. I don't have an emulator, but I do have a couple of screen shots here of that cross platform scenario. Any data, any size and any device, and this is all streaming out JSON. That's just JavaScript object notation. It's all JSON and jQuery being used on the different devices. The Android is on the right, and the iPhone is on the left, and those are downloadable right now as samples from those respective stores. There is a real openness message around Azure and around SQL Server and also obviously around Azure SQL Database, and those are the links, and PBT is published, so you can click the links, and we're using a partner technology where we're interestingly even sharing code on the client devices, something called Xamarin, where you can cross-compile .net across these devices. You're doing all your infrastructure coding in .net. It's running locally on an iPhone or locally on an Android, but the UI is constructed natively for the best user experience of that application, so this is pulling in all the pieces about how you'd use Azure SQL Database, SQL Server in an Azure VM within the construct of a larger Azure app such as this mobile app that could be delivered internationally or what have you. And with that, I think my timer says zero, so I better start taking questions right now. My red light is blinking. Any follow-up questions? Yes. [male speaker] When you were setting up that Azure SQL Database, and I might have said that backwards, there was an option for web, so there were 2 toggles. [Greg Leake] Yeah, there is a web edition and a business edition. It turns out it matters not. It's an artifact of how we originally did it, and it will change eventually. The web edition is 1 gigabyte to 5 gigabytes. The business edition is beyond 5 gigabytes to 150. [inaudible audience question] [Greg Leake] No, it's the same tiered charge for the storage, so no. It's an artifact that will eventually disappear. Good eye. Yes. [inaudible audience question] [Greg Leake] And fill out an evaluation before you leave, please. Thank you. [male speaker] Do you have plans to support SQL CLR in SQL Azure? [Greg Leake] The feature set of Azure SQL Database grows. Right now we don't have a timeline for putting SQL CLR into Azure SQL Database. That is another feature that's available in boxed product that of course is available in SQL Server and in Azure VM but not in our PaaS offering today. It's a good question. No timeline yet. Yes. [male speaker] I wanted to clarify a statement you made. When you're using SQL within the structure as a service I thought you said if you choose the offering from the gallery that the licensing is included in the subscription. [Greg Leake] That's correct, so there are 2 ways to license SQL Server in an Azure virtual machine. One is to install from the gallery template image, and the prices are published on the Azure site. You're charged the compute rate plus a little bit more depending on the edition you charge, a little bit less for Standard than Enterprise as part of the hourly rate. Then you don't even need a SQL Server license. If you are an organization that has SQL Server licenses on premises, if you have software assurance, you get license mobility, and you can take on-premises existing licenses and move them right up into Azure virtual machines, but you have to have software assurance to get what we call license mobility, in which case you're just charged the core compute rate, and you're using your own SQL Server license on the Azure virtual machine. Those are the 2 ways to license SQL Server in an Azure VM Great question. Okay, any other questions I think I'm going to take up on stage, so please come up, because I don't want to keep everybody too much longer. Thank you very much. [applause]

Video Details

Duration: 1 hour, 19 minutes and 24 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 6
Posted by: asoboleva99 on Jun 28, 2013

Come to this session for an overview of running relational databases in the Azure cloud. Specifically we compare and contrast both PaaS (Azure SQL DB) and IaaS (SQL Server in an Azure VM) options, and discuss scenarios for each. Architecture, performance, deployment, and other technical topics are covered, with lots of demos to give the technical audience a clear understanding of current options and the roadmap for running enterprise databases in the Azure cloud.

Caption and Translate

    Sign In/Register for Dotsub to translate this video.