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

Windows Azure SQL Database for the DBA

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
[TechEd 2013 - Windows Azure SQL Database for the DBA - Scott Klein, Tom LaRock] Welcome, everybody. We appreciate you walking down to the four corners of the earth to come to our session. My name is Scott Klein, my own quick introduction. We're here to talk about Windows Azure SQL Database. So a quick introduction. My name is Scott Klein. That's me. I'm a Windows Azure technical evangelist for Microsoft. There's my blog, my email address, and my Twitter [ - [email protected] - @sqlscott] if you want to tweet all the great things we say today. And this is my good friend Thomas LaRock. You want to introduce yourself? [Thomas] Hi. Yeah. Thomas LaRock. You can email me at [email protected] [ - [email protected] - @sqlrockstar] I sometimes tweet, I sometimes blog at And you're modest. This man wrote the book called Pro SQL Database for Windows Azure. He wrote the book on what we're about to talk about today. You can't learn from anybody better than Scott Klein in this room right now. As a matter of fact, Apress has a booth here at TechEd. You can get his book there. [Scott] We're going to keep patting ourselves on the back. Wednesday at noon we are doing a book signing down at the Apress booth for that book, and we will give some away today. We'll ask some questions. We don't have them here. We'll have to go down and get them from Apress. [Thomas] Yeah, a smart person would have brought some with them for this session. That didn't happen. >>[Scott] Okay. Yes. All right. Let me back up. We are talking about— DBAs here? >>[Thomas] How many DBAs? Excellent. [Scott] Most of you. Developers for the rest of you? Or architects? [Thomas] Managers? A couple of managers. We have pictures for them. [Scott] Yes, we do. Wearing many hats? This guy. Wearing many hats? One of everything? Okay. So what we wanted to do is I got with Tom and Grant Fritchey— I don't know if you know Grant Fritchey—and we basically said the cloud is coming. Whether you like it or not, the cloud is coming. So what does that mean for— Application developers get it. They understand the benefits of the Azure platform. Many benefits. But do the DBAs get it? Do the people hosting SQL Server, do they understand the benefits of the Azure platform? And so as we go around and were talking to people, DBAs initially thought, "Well, because it's a PaaS service, I'm out of a job." "As a DBA I'm out of a job." Who thought that? Don't lie. >>[Thomas] One person? Only one person thought they were out of a job? [Scott] Okay. You all thought it; you're just being shy. Okay. You all thought you were out of a job. So what we wanted to do was talk about whether we like it or not, it's coming. It's coming, so get used to it. But the initial thought was, okay, Metcalf, DBAs initially thought you were out of a job. And what we're going to talk about today is that is not true. The DBAs in this room and everywhere, when you go to the cloud, they're still very relevant. Moving to Azure is just a paradigm shift. So what does moving to the cloud mean for you as a DBA? What tools are available? What tools do you use today that still work against Windows Azure SQL Database? What new tools are there? How do you debug? How do you troubleshoot? Things like that, right? Because it is still very valid. I'm assuming that's why you're here today, correct? For validation that you still have a job, right? Yeah. Okay. So that's what we're going to talk about. I'm going to talk about just a few things— management/administration, maintenance, things like that. Tom's going to jump in and talk about monitoring, tuning, and troubleshooting. We do have a lot to talk about. We will try to get through everything. If we don't get through everything, Tom and I are going to be available after and we're happy to discuss with you later and things like that. Right? [Thomas] Expo. You're working the booth? >>[Scott] Yeah, I'm working in the booth. [Thomas] Find us at the Microsoft booth tonight. [Scott] Ask the Experts. >>[Thomas] Is that tonight? >>[Scott] That's tomorrow night. Tomorrow night. >>[Thomas] So 2 chances, tonight and tomorrow night, to see us. [Scott] And then you have our contact information. Before we jump into the agenda, how many people are familiar with Windows Azure SQL Database? Okay. Deep level or just, "I've been playing with it; I kind of understand it"? [Thomas] "I've read your book." >>[Scott laughing] "That qualifies me as an expert." Who's got a deep technical knowledge of Azure, do you think? One of you? Most of you are just, "I've been playing with it." Is that true? True statement? "I've just kind of been playing with it"? Okay. So what we want to do is I want to talk architecture because as we start going through this, it helps to know what is Windows Azure SQL Database and how different or how similar is it to on-premise SQL Server. So we're going to talk architecture real quick. And Tom can jump in at any time, okay? So let's talk about the client layer. The good thing is that Windows Azure SQL Database is SQL Server. It's not a no SQL solution; it is SQL Server. It is SQL Server 2012, which means all the protocols, all the languages, everything you use today still works against Windows Azure SQL Database. In fact, if you have a fairly simple database or maybe even a somewhat complex database and were to take that database, migrate it to Windows Azure SQL Database and just keep your application on-prem and all you have to do is just change the connection string, your app still works because it is SQL Server. So ADO.NET, ODBC, PHP, all the programming languages and things like that that you use today still works. So nothing there. And it still talks TDS, port 1433. It is SQL Server. The difference here is this new layer. It's the services layer. The services layer does provisioning, billing and metering, and connection routing. Provisioning. This means when I create a database— So we know on-premise, right? When I provision a server or create a server or SQL Server on-prem, I can name it things like Batman, Superman, Ratbert, Dilbert, things like that. You've all done it, right? So in provisioning when I provision a Windows Azure SQL Database Server, do I get a physical server? No, I don't. I get a TDS endpoint. But it still smells and acts like SQL Server, and we'll show you that in the demo. So what provisioning does is when I go and I get SQL Server Management Studio or the portal and I say, "Provision a database or a server," it goes out and says, "All right, in the data center go find the least busy server to provision my new database." Pop quiz. When I create a database in Windows Azure SQL Database, how many databases do I really get? One? Three. I do. Right, correct. >>[Thomas] Three copies and the original. [Scott] Two copies and the original. >>[Thomas] Okay. [Scott] So I get the primary and 2 replica, and they're always in sync, and my application always talks to the primary. So for the price of that one database, I get the high availability and disaster recovery automatically. Automatically. You can't do that for free on-prem, but you get it for the cost of that one database in SQL Azure. So the services layer says when I create a database, there it is. Find the least busy server on my data center, provision that database. Done. And it does that for the 2 replicas as well. So this is what we do as provisioning. Billing and metering says, how many databases do I have, how long have they been running, what's their size, so obviously, we can bill you accordingly. That's easy. And then connection routing. So when I provision a database, we're always thinking about performance for you, so when I provision a new database, it figures out where in the data center is the least busy server to create your database. It may not stay on that server, but you don't care because it may move it around to find the least busy server. But you don't care, right? So when I come in and I connect to my database, it comes into that TDS layer and says, "All right, route my connection to this server, to my database." Done. This is the services layer. Make sense? Any questions? Other than that, it is SQL Server. There are physical SQL Server boxes in the data center hosting our databases. We just can't get access to that physical box. This is logical administration versus physical administration. How many of us like to do patches and do upgrades and do all the heavy work on SQL Server? Raise your hand. See? Nobody does, right? We just want to code. We just want to do DBA stuff. We just want to work with SQL Server. This is PaaS. This is let me work with our databases. So we don't give you the ability to manage your servers. You don't want to do that, so let us do that. This is PaaS. But it is SQL Server behind the scenes. Does that make sense? Any questions? All right. So with that, let's jump in. The great thing is since it's SQL Server, all the tools that you use today, plus some, you can use to work with Windows Azure SQL Database. So what's awesome is SQL Server Management Studio, we have some Management Portals. And any PowerShell people here? Just one? The rest of you are missing out. [Thomas] That's also the one that was afraid of losing a job too. [Scott laughing] That's right. >>[Thomas] Is there a correlation there? [Scott] Yeah, I think so. That's right. So PowerShell still works. So let's talk about these individually. SQL Server Management Studio. The great thing is everything still works. So all the features that you can do—I shouldn't say all— most of the features that you can do today on-prem still work against Windows Azure SQL Database. So your job is still relevant. Statistics, Object Explorer, the query window, execution plans, statistics, indexes. All those things you do today you still need to do in Windows Azure SQL Database. And the tools that are still there you still use for SQL Server Management Studio. So you've got a lot of features in SQL Server Management Studio. However, has anybody ever wished— Who here has wished that there was a web-based version of SQL Server Management Studio? [Thomas] Really? >>[Scott] That's it? The rest of you are lying. Really? >>[Thomas] Why would they want a web-based— [Scott] Why would they want a web-based version? We do have what's called— So there's 2 portals. We have 2 portals—the Windows Azure Management Portal. This is the portal that we have that manages all of Windows Azure, so websites, mobile services, virtual networks, SQL Database, server provisioning, database provisioning, monitoring, configuration, some levels of security, things like that. We'll show you that. But then we have what's called the SQL Database Management Portal. It's kind of a lite version, a web-based version of SQL Server Management Studio. It does a lot of things you can do in SQL Server Management Studio via the Web— create tables, views, stored procedures, run queries, do execution plans, query execution plans, estimated execution plans. If you're dealing with scalability, it does federations. We won't talk about that. But it does a lot of things that SQL Server Management Studio does. It does not do security things. You can't create users and logins and things like that. That's still done through SQL Server Management Studio. But it does do quite a bit. >>[Thomas] It does. [Scott] And it's very nice. >>[Thomas] It's getting better. I'll say that. He works for Microsoft. I'll tell you the real thing. It's getting better. [Scott] So I'm the glass is half full; he's the— [Thomas] No. I've been using Azure for years now. So the tools that you have today are just light years ahead of where we were just last year. It gets better all the time. >>[Scott] Yeah. And then we have PowerShell. PowerShell is awesome. This allows you to do a lot of the things that you can do programmatically that you can do in SQL Server Management Studio and in the SQL Database Management Studio and even in the Windows Azure Management Portal or the SQL Database Management Portal. The things you can do with PowerShell give you basically more control over deployment, automate your management and deployment. It has PaaS and IaaS support, so Windows Azure SQL Database, SQL Server in a VM, a lot of those things, and it's automatically installed when you install the Windows Azure SDK. And there is a whole slew of PowerShell scripts, probably well over 100, 150 or so—somewhere between there— that allow you to do programmability with PowerShell to provision databases, provision servers and things like that. Make sense? We good so far? Yes. [inaudible audience member question] [Scott] So the question is, is this just SQL Server, doesn't do analysis services? That's a good question. So let me say this. If you guys have questions—because we put this together based on what we think DBAs think—if you have questions, let's make this very interactive. Raise your hand, stop me, say, "What about this?" So please keep this interactive. So to answer your question, does this just do SQL Server and not analysis services, a lot of the BI stuff you're asking, right? Right now Windows Azure SQL Database, our PaaS offering just does SQL Server. There's no analysis services or BI in Windows Azure SQL Database. If you need BI in the cloud, the answer is—anybody know? [Thomas] I do. >>[Scott] What's that? >>[audience member] It's coming? [Scott] We've been saying that for what? >>[Thomas chuckling] It's coming. Be patient. [Scott] But what's the answer? If you need BI in the cloud right now, what do you need? [inaudible audience member response] >>[Scott] Say that louder. [audience member] A virtual machine. >>[Scott] A virtual machine. SQL Server and a Windows Azure virtual machine. I'll show you how easy that is to spin up. We'll take a little tangent and show you how to do that. [Thomas] That just came out this spring? [Scott] We've been able to do VMs for a while. Within the last year or two you've been able to create your own VMs and upload those to Windows Azure. Starting in June, general availability is that you can now pick from the portal, spin one up, and that's your VM. Quick tangent. There are pros and cons to that. In Windows Azure SQL Database it's PaaS. So Microsoft is managing that. If you provision a VM, you own that VM. So you're responsible for maintenance, licenses, the image, things like that. Okay? Make sense? But it does give you full SQL Server. So a lot of the features that aren't there in Windows Azure SQL Database do the VM, BI stuff. All right. Demo time. [Demo - Management Tools] I'm not going to spend a lot of time at this. This is just a quick introduction. What I've got here is I've connected to— Can you guys see that? >>[audience] No. [Thomas] No, they can't. >>[Scott] That's because it's Server Core. It has no UI. [laughter] >>[Thomas] Ah! That was funny. >>[Scott] I'm here all week. Try the veal. [Thomas] Do we have to do— >>[Scott] We've got to do the— [Thomas] Duplicate? No. I want screen. [Scott] Let's do the— [Thomas] You need to extend your screen. There you go. [Scott] Let's try that. >>[Thomas] Or duplicate. >>[Scott] There. Can you see that? [Thomas] There you go. >>[Scott] The problem is I can't see it here. [laughing] [Thomas] No, no. You want duplicate, not extend. >>[Scott] Let's do that then. [Thomas] There you go. >>[Scott] There you go. Let's try that. Come on. And then— >>[Thomas] No? [Scott] All right. Let's do this. We'll make it easy. We'll do it this way, and hopefully we can see it. We can see it here. [Thomas] Hold on, hold on, hold on. Before, was it window speed? [Scott] Yeah. >>[Thomas] Yeah, okay. I think you're right. >>[Scott] Okay. You guys can see that, right? [Thomas] You can just move your mouse over here. >>[Scott] Oh, there we go. Let's do that. All right, cool. Where there's a will, there's a way. This is SQL Server Management Studio, and on the top I have connected to my local box. So notice that I have all the nodes. You guys are familiar with this, right? There should be no surprises there. But on the bottom I've connected to my Windows Azure SQL Database Server. A server is not a physical box. While there are physical boxes in the back end, this is what? Can you guys see that? Oops. What's my server name? [Thomas] Something [Scott] Yeah, [mumbling] This is Windows Azure SQL Database. This is my TDS endpoint. It's specific to me, but I have databases associated with that. So when I create a database, I may create one database, it may go in one container at one end of the warehouse. If I create a second database, we guarantee for high SLA and high availability that it will not go on the same physical box as my first database. But we handle that for you because when I connected it said, "There's all your databases," and it guaranteed that all 1, 2, 3, 4, 5 of those databases are in separate servers in the warehouse but that middle layer, services layer, said, "Here's all my databases." "Come and display them in SQL Server Management Studio." So you don't have to worry about that. But let's take a look at this real quick. What do you see here? What's noticeably different between these 2 nodes? [inaudible audience member response] >>[Scott] What's that? [audience member] Security. >>[Scott] Security is there? Replication. Server objects. >>[Thomas] No agent. >>[audience member] No control. [Thomas] No control. >>[Scott] Hold on a minute. See? That's the automatic [shrieks], "I'm out of a job!" [Thomas] I'd say levels of complexity. >>[Scott] That's right. So AlwaysOn, Management, Integration Services, things like this. But what did I say earlier? This is logical administration versus— >>[Thomas] Physical. [Scott] physical administration. So when I create a database in Windows Azure SQL Database, do I care where it goes? Do I care what the path is? We're keeping the log, so we don't care. So everything that's missing down here are things that we're managing for you anyway. Make sense? But that does not mean that you still don't have things to do. Just because there's nodes missing— Replication, AlwaysOn. We're managing AlwaysOn. We're providing the high availability disaster recovery. No problem. Replication. Who here has lost hair over replication? Yeah. [grunts] Right? So we have what's called Windows Azure SQL Data Sync. Much easier to use. We won't get into that. All of that stuff is online. If you want to talk about it after, we can do that. So there's differences here, but there are still things to do. I still have the ability to create databases, I can still create a query. Since this is SQL Server Management Studio, I can write a query and I still have the ability to do my— Let's do this. What are those? Those are my execution plans. Those are my estimated and my actual execution plans. So I still can write queries to do re-indexing and statistics and query plans and a lot of the DBA functionality. Just because we remove a lot of the things as far as the physical administration, it still means that you still have things to do as far as a lot of the DBA roles. Does that make sense? Questions? Yes. [inaudible audience member question] >>[Scott] In what way? [audience member] Creating jobs. >>[Scott] Creating jobs. So just like we said before, we are working on that. But that does not mean that you can create— I can still create a job on-prem that connects to Windows Azure SQL Database and runs. That you can do all day long. And if you think about this, why isn't the agent there? We're running out of time, but let's think about this. Why wouldn't the agent be there? [audience member] Because we're not managing it. [Scott] Well, we could still manage it. But think about this. My database, when I create a database, may very well physically go in the same server as Tom's or yours. So if I have an agent, how do we segregate that so that we're not running agents to other people's databases, things like that? So those kind of things. But that does not stop me from creating a job on-prem and connecting and running that job in the cloud. I can do that all day long. You had a question? [audience member] Yeah. Say I need to recover that data. [Scott] We're going to get to that. Disaster recovery, backups, things like that. We're going to get to that actually right now. Any other questions? Is this making sense? Yes. >>[audience member] Is IIS still available? [Thomas] IIS, Internet Information Services. >>[audience member] I'm sorry. [inaudible] >>[Scott] Integration Services? >>[audience member] Yeah. [Scott] Just because the stuff is not native to Windows Azure SQL Database, I can create an SSIS package on-prem and run that against the cloud all day. I can still do analysis services. You can still run those locally and still work against the cloud. The only downside to that is now I may have data going back and forth. Until those services become native to Windows Azure SQL Database— Like, there's Windows Azure SQL Database reporting, so we do offer reporting native to Windows Azure SQL Database. And that's a whole other discussion. We can talk about that. It's just analysis services and integration services are not native yet. So if you need to do things and automate a lot of things, you could run your agent locally to access even an SSIS package and things like that. You can still do that. All right. So let's keep going. Any other questions? Because we only get— Okay. >>[Thomas] Back to slides? [Scott] Yeah. >>[Thomas] Let's do this. Look how good you are. >>[Scott] Man! All right. We will get to backups, but I want to talk about security and basically getting there really quick because we still have— [Thomas] We're good. >>[Scott] We're good. All right. From a security perspective, how many people are thinking, "Oh, okay, I don't want to take my data and move it out of the cloud," because I know you had a discussion about that earlier. You were down there at the thing and like, "What about my data?" Who worries about that? Come on. Most of you should raise your hands. Basically, you're saying, "Take my data. Let us host it." So what does that mean? And then for migration, even if we get over the hump of security issue, how do I get my data to the cloud? So let's talk about that. I love this one. "Of course you want to know where your data is being stored, madam." "Let me see—right now it's passing over Peru at the moment." We want to know where our data is. We want to know that it's secure. We don't want to know that it's floating out there in the cloudsphere somewhere. We want to know it's protected, we want to know it's secure, we want to know a lot of things—that our data is secure. On-premise how do we secure our data? What do we do to secure our data on-premise? [audience member] Backups. >>[Scott] Backups, right? What else? [inaudible audience member response] >>[Scott] What's that? [audience member] Firewalls. >>[Scott] Firewalls. [audience member] Change the port number. >>[Scott] Change the port number. Unfortunately, you can't do that with Windows Azure SQL Database. It's 1433. So how do we guarantee that your data is secure? This has to go along with things like encryption. Why do people encrypt their data and things like that? So let's think about this a minute. We have what's called firewall rules. We understand from an OS perspective how to configure firewall rules. We've taken that a little further, and this is where you guys come in, a little further, what's called Windows Azure SQL Database firewall rules. And these firewall rules can be applied at the server level and the database level. How important are these? I could give you right now my server name, that [mumbling] I can give you that, I can give you my username, I can give you my password. I can give you all of that cleartext. You will never get access to my data because through the firewall rules, it's through the firewall rules where I grant who has access to my data. In fact, by default, even when I provision a Windows Azure SQL Database Server and a database, even I don't have access to it until I say I through my IP address have access to this database and server. Does that make sense? So I have it at 2 levels—the server side, which says I have access to the entire SQL Database Server and all the databases, or I have it at the database level, which means I am only providing access to this one database, no other databases on that TDS endpoint, just that one database. Make sense? Now we'll show you a demo of that. Then from a security perspective, again, here's where DBAs come in. From the users, logins, roles, permissions, and groups, all that still applies. Since it is SQL Server—you guys know this—I create a login, I create a user, assign that user permissions, I assign that user to groups and things like that. That still works. The only difference is that we have a couple extra roles. We have loginmanager role and dbmanager role. This is server-level roles for security for creating logins and creating databases. But grant, alter, all that stuff still works. So you're still creating users, you're still creating logins, you're still assigning those users to groups and giving them permissions since it is SQL Server. Okay? So are we good so far? Okay. From a migration standpoint, we're just going to spend one slide on this. This is where you guys come in as well. As a DBA, you're basically saying, "How do I get my database from on-prem to Windows Azure SQL Database?" And there's many ways to get there. There's the DAC. Who is familiar with the DAC? DAC packs? You can still do DAC packs—export, import—and we'll show you how to do that. Then from the Windows Azure side we have what's called the Windows Azure Import/Export Service. Easily through what's called—anybody heard of a backpack? Yeah. SQL Server 2012. Tom and one other guy. It's backpack. What's a DAC pack? [Thomas] Schema. >>[inaudible audience member response] [Scott] That's right. So it's the same artifact, backpack, but it just also includes data. So I can import and export my databases quite easily through Import/Export Service and through SQL Server Management Studio. And then who is familiar with SQL Server Data Tools? Anybody use SQL Server Data Tools? Yeah, that's awesome. You can do migration as well. We're not going to fire that up, but I'll show you that. So very quick demo. [Demo - Security and Migration] So basically, security. So what I'm going to do is jump over here. And what I'm going to do is let's look at firewall rules first. So here I have my database. Let's go back to my databases. And here's my list of servers. These are all the list of servers. Notice here these are my server names, so we can't name them Batman, Superman like we talked about. But how do I give you access? I could go in here and, like I said, I can give you my server name, and the server name is this. That's my server name. I could give you that, I can give you the username and the password, but until I go in through my firewall rules here and say who has access to that, you're not getting access to it. You're just not. But we make it so easy for you. They are IP address based, and so instead of going out, we make it so super simple. Instead of having to do IP config or //who or whatever, we tell you what your IP address is. And to add a firewall rule, bang, save, done. Now I have access to this entire server and all the databases on that server. I could also do a range because I could do something like .1 to .255, for example. [Thomas] You could also do 0.0.0. >>[Scott] I could do to 255.255.255. But please don't do that. Please don't do that. And I could have more than one firewall rule, but this is the IP address that Windows Azure received. So it would be like your corporate IP address, things like that. So in order for you to get access to that, again, I can give you my server name, my username and password, but until I come in here and give you your IP address— I'd have to enter your IP address—you won't get to my data. So as a DBA, you're managing who has access to this as well. And again, you can do this through PowerShell, you can do this through APIs. This is all programmatic and available. And then what I also wanted to show you was the SQL Database Management Portal, and Tom was awesome enough to bring this up. I didn't show you it in the first one. But we basically get to that if we go to a database over here. Yeah. Click OK. Oops. >>[Thomas] You're in the wrong server. Second one down. [Scott] Yeah. Let's go to some databases here. What we did is we basically picked a database, and this is the Windows Azure Management Portal. This is where I can go through here and in this case I can say databases, I can create a new server. So how long does it take you to provision a SQL Server on-prem? Come on. [Thomas] You mean buying the hardware, getting that approved? [Scott] Getting it approved, going through all the red tape. How long does it take you? Even if you had the box on-prem, how long would it take you to set it up? [Thomas] Longer than a day. >>[Scott] Longer than a day, right? A day? All right. I'm going to provision a server in about 10 seconds. You ready? Wait. Let me do a server. Server, Add. Done. About 10 seconds. Then it will come back and say my server is provisioned. Isn't that faster? That's much faster than a day. Yeah! Come on. So there's my server, right? But again, it's not a physical server, it's a logical server because notice my server name is now [mumbling] 70f or whatever. So that's my logical server. But now I can come in here and just start adding databases to that. Create New Database. There it is. Bang. So I can set collation, I can say what server it is, give a name, what edition we want. We won't get into editions. It's still the same. It's still SQL Server. Yeah, go ahead. >>[inaudible audience member question] [Scott] No. I could go in here. It's basically by database size. Number of databases and how big they are. So I can have as many servers as I want, however many your subscription supports, like 610 or something like that. But I could have, say, 10 servers. No databases, I don't get charged. But as soon as I start adding databases to that, that's when I start getting charged. And it's basically by size, how much data. It's like $5 for a 1 gig database per month. [Thomas] The way I usually think of billing is I think of it as storage. So anything storage-related I think I have to pay for. But it's also outbound. So it's free to put data into the cloud, right? >>[Scott] Yes. [Thomas] All data going in, up to the cloud, that's free. Free to go in, costs to come out. That's what you have to keep in mind when it comes to the billing. So if you put a whole bunch of data in there and you expect thousands or millions of people to use it and be pulling that stuff out, that's what you end up paying for. [Scott] Yeah, so it's a flat cost for how much data you have in there, but the minute data leaves the data center— So best practices states put your database in the cloud, put your application in the cloud. As long as data stays in the data center, it's free all day long. The minute you export data from the cloud to on-prem, it's still pennies on the dollar, it's like 9 cents per gigabyte, still cheap. [inaudible audience member question] >>[Scott] What's that? [inaudible audience member question] >>[Scott] Anywhere up to 150 gig. So 150 gig. And people panic about that a little bit. They're like, "I have a bigger database!" Well, okay, we have to ask. Do you have blobs in there? If you have blobs in there, then get them out and we can talk about that. Then if you're still bigger than 150 gig, then we talk about let's start partitioning, using multiple 150 gig databases, partitioning your data out until you get better performance anyway. That's a whole other discussion. But right now the biggest database is 150 gig. Yes. >>[inaudible audience member question] [Scott] Yeah, good question. The question is, where do we want it because again, we have 8 data centers dedicated to Azure in the world—4 in the US, 2 in Europe, 2 in Asia. [inaudible audience member question] [Scott] You can. >>[audience member] Okay. That changed recently then. [Thomas] Repeat the question. [Scott] So the question is, really it boils down to where should you create your database and your server and your application? Really you want to create them closest to where your users are. Basically, it's for latency and performance and things like that. That makes sense. For cost reasons, you want to try to put your application and your database in the same data center as well. So let's say we're in New Orleans. Probably the closest data center is in South Central US. I think it's San Antonio, Texas. >>[inaudible audience member question] [Scott] The question is, if we accidentally say, "Oops," it's pretty easy to change, especially your server. There's an option there to say, "I want this," and move your server and your databases to another data center. And if you have any issues, you can call support and say, "You know what? I want this now in this data center." You definitely want to keep things in the same data center just for cost, performance, and things like that. Lastly, really quick because I'm going to turn the time over to Tom, this is the Windows Azure SQL Database Management Portal. It's SSMS Lite, we'll call it. Basically, here's my administration page. So I can do a new query, I can do a design, I can do tables, views, stored procedures all through the Web. So I can create, I can write queries, I can add data, I can insert data, I can make changes here, so views, stored procedures. Through an administration page I can look at query performance. So I can write a query, and it gives me the last few queries for this database. So I can easily just within a snapshot say what queries have run and what can I do here. If I open a query window, there's my actual and estimated execution of query plans. It doesn't do security, but it's fairly good for a web-based SQL Server Management Studio. So I can be anywhere and not have SSMS with me, but I can fire this up, connect to my database, and be good. [Thomas] I was going to show them if I click on the query, it will bring me to more details about it. It should. Is this hung up? What is it doing? >>[Scott] Oh, you've got error details. [Thomas] All right. Let's grab a different one. But the point is, because he was talking about— Am I not connected right now? It's idle connection, I think. [Scott] Yeah, it may have timed out. >>[Thomas] We'll have to come back to that. [Scott] But anyway, the point is that you've got SQL Server Management Studio and then you have the SQL Database Management Portal to do a lot of things you can do in SQL Server Management Studio as far as execution plans, query plans, things like that. For the last demo on this, what I want to do— >>[Thomas] Oh, I'm sorry. [Scott] No. You're good. Let me jump out back to SQL Server Management Studio and do this. If you're running SQL Server 2012, here's my on-prem databases. I've got AdventureWorks, I've got ContosoExpense, I've got some databases, and I want to deploy. If you're running SQL Server 2012, I can right mouse click a database, and it really doesn't get any easier than that. Deploy Database to SQL Azure. Yeah! That's awesome. One click, done. That's awesome, right? So what this does is just a general one-time click and go, take my database, wrap it up in a backpack, deploy that to a database. I don't have to precreate the database in Windows Azure SQL Database. It will create the database for me, deploy it, done. If I want to create and keep that backpack artifact, I would choose this option. Tasks, Export Data-tier Application. This essentially says, "Let me keep that backpack artifact "and let me save that backpack to either on-prem to my local machine or to Windows Azure blob storage." Because at that point, once I do that, I can actually go into— and I wanted to show you this as well. See these 2 buttons down here? Import and Export? We'll go more into this in a bit, but this is Import/Export. This allows me to take a database in Windows Azure SQL Database, export it out and back it up to on-prem or take a database from on-prem, load it into Windows Azure SQL Database. So I've got a lot of features and functionality around that. Any questions about that? Yes. [audience member] The option to allow Azure services, does that, and if so, how does that prevent [inaudible] [Scott] So the question is, if we go back to the firewall rule page, there's that allow Windows Azure Services access to my database. And the question is, if I enable that, if I give someone else my username and password and server name and things like that, can someone else from another subscription gain access to my database? You know, honestly, I'm not quite sure. I know you used to be able to, but I don't think that's true anymore. But let's take that offline, and I'll find out. I'll get you an answer. I don't know. Yes. >>[inaudible audience member question] [Thomas] We are going to touch upon that. >>[Scott] Yeah, we will touch on it. If we don't answer your question, I think we'll do that at the point in time restore and things like that. All right. Let me turn it over to Tom. [Thomas] That's a great question, actually, because we're going to talk a little bit about backups and restores right now. All right. We're good. Some of the routine tasks that you're going to be doing as a DBA today you're still going to want to do in Azure. So your question right there was about if somebody makes a mistake, what am I supposed to do? Azure is really good at this thing that's called high availability. High availability is not the same as disaster recovery. Anybody here confused about that? HA and DR, these are 2 separate concepts. I used to have a manager who thought that once he had an HA he didn't need his DR anymore. That probably explains why he was a manager, but that's beside the point. I mean, if you're going to be using SQL Database, you're going to be responsible for your own DR. So if somebody drops a table, gets rid of data, things like that, you're going to need to have a copy of your database, just like you do today. Here's the thing that scares people about it. You don't have a backup command. [gasps] "What will I do without that backup command?" I don't know. Probably a dozen different things. You just have to think a little differently. Azure does make it kind of easy for you to get copies of your data. The question is, how frequent do you need it? Today most people are accustomed to doing backups, say— I used to do my backups, fulls weekly, differentials nightly, transaction logs every hour or so. That used to be just the standard. Most people are used to that. If you really wanted to do that, you could. You could still do that. You have to do a little bit of the legwork. There's no maintenance plan for you to do that. You have to kind of roll your own scripts for it. But it can be done. So we're going to talk a little bit about backup, restores, indexes, and stats because those are really the big 3 things that you're doing today and you're still going to do with the Windows Azure SQL Database because it's still SQL Server. It's not that different. It's just a platform. It's just a different version. That's all it really is. So why do you want backup? Of course. Because you have to go back to a point in time. Administration errors, application errors, data center loss—all of these are good reasons. Remember, Azure is going to keep it highly available for you. That's great. So if somebody drops a table, that means now instead of that being dropped in one place, it's dropped in 3 places. That's just great, right? Same thing like if a corruption happens and you have replication set up, that corruption gets replicated. That's just fabulous, right? So there is always going to be a need for you to go back to a certain point in time. So the Import/Export Service is one way for you to get that done. If you wanted to, if you said, "We're going to have a big migration coming up." "Let me do an export," which is going to create a backpack, and you can save that off to blob storage. That's one way for you to get yourself a copy of your database. However, the big key here is to understand that Import/Export Service is not going to give you a transactionally consistent view of your data. They do not guarantee it. Just think of Import/Export as a big old BCP. That's it, just a bulk copy, in and out, nothing more. So if you need transactional consistency, you've got to do things a little bit differently. Again, there's no backup, but what they do have is they have the ability for you to create a database as a copy of another one. And ingeniously, you do it like this. You say CREATE DATABASE, new database, AS COPY OF old database. And you're done. Now you have a transactionally consistent view of your data. That is your new backup. So that's something that you might schedule as a job. Go run this command against that instance of SQL Database, create a copy of my data. You do that once a week, once a night, however you need in order to recover back. Make sense? It's not conceptually different than what you're doing right now. It's just a new command. Yeah. [inaudible audience member question] [Thomas] No. All that HA is done for you. That's right. [audience member] Doesn't that get kind of expensive? Let's say you want the last 7 or 14 days [inaudible] [Thomas] So the question is, doesn't that get expensive, because if I need the last 7 to 14 days, I have 14 databases. So my answer is, how do you store that today without paying for it? Where are you putting that today? You're putting it on a server somewhere that you bought, you paid for, and you have storage somewhere, and it's probably getting backed up to tape for you and offloaded and you're paying Iron Mountain for that. I mean, it's not like you're doing that for free today, right? [audience member] No. I'm just thinking it's a little bit different as far as [inaudible] [Thomas] It doesn't have to be. You can create a copy of the database, and then you can export it to a backpack. [Scott] And then delete the copy. >>[Thomas] And then delete the copy. Right. And how long do you want to store those? But it's the same thing. How many copies do you want, backpacks do you want? And now you're paying for that storage. So yes, you're going to have to pay for it one way or the other, but again, it's not like you do it really for free anyway today. You just have to architect something different. But yeah, you can script that out and have it run, say create the copy, export as a backpack, delete the copy. Yeah. >>[inaudible audience member question] [Thomas] That's your transactionally consistent view. Right. Oh, no. Can you roll forward? So the question is, can I roll forward in the transaction log? And the answer is no. No. Just think of it as a full backup. I'm sorry. Can we hold questions till the end, because I'm not sure I'm actually going to finish. We've got about 30 minutes left, so I want to make sure I get through my slides. Is that okay? All right. Indexes. Because it's still an instance of SQL Server, fragmentation is going to still happen. This is going to lead to poor performance, long running queries. You're going to want to manage the indexes same way you're doing it today, although if you're using maintenance plans, I guess you'll have to change. You're going to need to script it out yourself, you're going to need to run these jobs, you're going to have to go through your database, and you're going to have to defrag those indexes yourself. You're going to want to do the ALTER INDEX REBUILD, and you're going to want to use online. There's no question about that. The DMO queries to determine if fragmentation exists, they're still there for you. So the same methods for you to go in and say, "Hey, how much fragmentation do I have? Do I need to rebuild/reorg?" All of that exists for you. There's really no change for you there. But you're going to need to build those jobs yourself, automate it yourself. Stats, same thing. The only DBCC command that is available in Windows Azure SQL Database is DBCC SHOW_STATISTICS. You don't get to run any other DBCC commands. So you're going to want to use that to your advantage. You're going to want to do your checks for stats as well. Just make sure if they have to be updated. Let's go on. [Demo - SQL Database Copy] Let's do this. I know that we had a couple of questions about database copy when we were on backup. Those other 2 questions, if you want, ask them now while I'm going through. [Scott] Who had a couple questions? >>[inaudible audience member question] [Thomas] I'm sorry. Say that again. [inaudible audience member question] [Thomas] No. CREATE DATABASE has been around for a long time. You can CREATE DATABASE, database name. >>[Scott] I have to type on this one. That keyboard won't work. >>[Thomas] Just move it over there. He's like, "Why is this stuff showing up?" >>[Scott] Yeah, exactly. [Thomas] So yeah, CREATE DATABASE has been around for a long time. And we're just basically saying CREATE DATABASE, new database AS COPY OF source database name to get the copy. Yes. [inaudible audience member question] [Thomas] No. Log shipping is not supported in Windows Azure SQL Database right now. But again, we give you those 2 high availability replicas, so it's automatic layer anyway. [inaudible audience member question] [Thomas] What do you mean by compressed? The question is, is the CREATE DATABASE— >>[audience member] The backup of it. Is it in compressed form? >>[Thomas] No. It's a duplicate copy of the original one. So the goal is you can create it, get your copy, backpack it out, delete it, and so you'll only be charged a couple cents. I saw another hand. [inaudible audience member question] >>[Scott] That's right. You're not allowed to run CHECKDB. You're not. That could throttle the instance, so they're not going to allow you to even do that. They're probably checking it for you. I'm assuming you guys are doing CHECKDBs for us. So corruption should be handled, especially all the soft ones. Those are usually what you're going to find anyway. Yes. Less administrative overhead for you. Total cost of ownership comes down. There's less options for you, less things for you to worry about. [audience member] Do we call every time we have a problem with our database? [Thomas] The question is, do you call every time you have a problem with your database? I'm actually going to get to that in the next section. But you can call Microsoft. Customer support, they support this. And we're going to go through a lot of the common errors that they've been seeing. I just did a CREATE DATABASE. That's how simple it was. Now NewDB, so that's a logically consistent view of my data. And what you would do then is if I come here— Where's the refresh? [Scott] F5. >>[Thomas] Really? >>[Scott laughing] Yeah. [Thomas] I thought there was a little button that said Refresh. Wow, I really don't like your mouse. I've got to have my own mouse. So the database will be there, and then it's the Export Service, and I'll just walk you through that real quick. [Scott] This doesn't list our databases. [Thomas] So there's my NewDB. It still says it's copying, not done yet. How about that? Maybe we'll have to come back to that after. [Scott] There's ways to check that because as soon as you execute the CREATE DATABASE AS COPY OF, it will come back and say its standard SQL message back, "Okay, this command executed successfully." But if you've got a large database, that copy, basically what it does is put a job out there and say, "Hey, copy my database." That copy may be gone, so there's— [Thomas] There's DMVs you can check and see the status of the database and it will tell you if it's still copying. So what I'm going to do instead of waiting for that one, we're just going to show the export here. When I go to Export Database, options and filename it whatever I want. The blob storage account, so you're going to need your own storage account for this. We have account setup and the container, and we'll do the password real quick. That should work. Now that's going to export it. There's a backpack. And then if I come over to my storage accounts, here's my clouddbastorage, I go to my containers, and I'm going to see a list. Here's my container. I get a list of all the objects that are currently in that container. There's the one I just ran. Where is it? What's today? Did I name it something? It's this one. [Scott] It had the date after it. >>[Thomas] Okay. All right. It's still copying. Wow, that should be done by now. I thought I chose the smaller one. All right. Back to my slides. I'm getting confused between which laptop I'm on. Any other questions about backups or copying of a database? All right. I'm happy to talk more about the process later. And you can find Scott and I tonight down at the Microsoft booth. Monitoring, tuning, and troubleshooting. To your question, basically, what do you do? Do you call Microsoft every time you're having a problem? This section is actually information I got from Bob Ward, who runs CSS, and he's very focused on supporting Azure these days. So a lot of the common errors that they see. Connectivity is a big one. We'll talk about some of the errors that you'll see in Windows Azure SQL Database, and we'll talk a little bit about performance and monitoring because being a DBA, you're still going to get blamed for bad query performance, even if Microsoft is hosting it and not you anymore, right? So you're going to want to know how do you troubleshoot these types of queries, where do you go, how do you get the job done. Oh, you've got another cartoon. "The trouble with being on a cloud—" Nice. You can't tell people to reboot it? So you can't tell Microsoft to reboot it, I guess. [Connectivity] Connectivity. Let's talk about those errors. Connectivity errors fall into 3 buckets—WASD errors, general errors, and loss of connection. So why does your connection fail? These are the 4 most common reasons why a connection will fail. You will find out that the firewall, you're not allowed to get in. Like Scott showed you, you can't even get in until you can figure that first initial one. Nobody's getting in. Authentication is another one. Invalid logins. You would be surprised at how many times people don't put in the correct password and can't get in and actually do call Microsoft to say they can't connect to the cloud. And it's just a password failure. And then denial of service, which is not somebody trying to take down your website; it's you putting in that bad password so many times that at some point Azure finally says, "You know what? You've tried enough." "Why don't you take a break for a little bit?" And then you get back an error message saying, "Yeah, you're being denied right now because you've had too many failed login attempts." General connectivity errors. You get the old Server Not Found. This actually does happen because things do get moved around. But it actually happens because do you remember the name of his server? It had, like, 7 letters to it. And you transpose 2 of them and you're sitting there banging away on the keyboard. "It keeps saying Server Not Found. I know I'm paying for this." And yes, they do call Microsoft and say, "Where is my server?" It turns out your server has been there the whole time; you just typed in the wrong name. Semaphore timeouts. Semaphore timeout period. How often does that come up? [Scott] Rare but it happens. >>[Thomas] Rare. All right. But it does happen. It's a very distinctive error message, though. And network latency. Again, it is still SQL Server, so you can see these 2 things. You have to understand that there is a network between you and your data still and that a semaphore timeout actually can happen, especially if the server is just a little bit overloaded. You might get back that error message. That should resolve itself because Azure should be smart enough to know when things are being overtaxed and be able to move things around for you. The network latency is the one that's the pre-login handshake. That's what that is. I actually had that yesterday when we were trying to get set up for the precon. Again, it's a very specific error message. In almost all those connection ones you will get back a very specific error message that tells you exactly what is happening. It's mind-boggling that you get back such a useful error message from a Microsoft product. It really is. [laughter] It's not just like, "Hey, general error happened." I mean, you get back something very specific. Pre-login handshake failed. That's very useful to know. It's very specific. And then Microsoft can help you troubleshoot from there if you need it. Loss of connection. You can have loss of connection. This is another pain point for a lot of people because they just feel frustrated that their connections get dropped. One is an idle connection, which we've already seen today. Sure, it got me frustrated maybe the first time. Today it happened, I know it happened, and I moved on. I just have to reconnect. It was an idle connection. I had connected earlier when we started the presentation, and then we didn't get back to it for I don't know how many minutes, and then at that point I had been timed out. It was an idle connection. So WASD simply looks and says, "That guy has been idle for a while." "I'm just going to kill it." And then when you try to go back, you get back this error message that says, "You've been killed." Failover. When failovers happen, you're highly available. If there's something wrong with the data right here, they're going to move you over to somewhere else. That's a failover. You're going to lose a connection. So that can happen. That's why that third tile, retry logic is absolutely needed. So back in the day—and jeez, I don't want to call everybody in here old, but I'm guessing they're experienced enough to remember the day when they did not expect to always be connected. >>[Scott] Experienced. [Thomas] Experienced. Absolutely. This audience is experienced. There used to be a time in your code when you would have retry logic because you just did not assume that you would be connected. It's just, "Hey, I may not be connected, and so I'll just retry." Okay. And that was a fairly standard thing. Somewhere along the way we got really spoiled, and now the standard became, okay, open the connection and then just start working. And I'll just always be connected, right? No. No, you're not. You may not. So that retry logic, everything old is new again. It's coming back in vogue. And there's a link, I think, if you have it at the end or I know it's in the precon slides, where they spell out exactly what you want to do in your application code to put that retry logic in there because you may not always be connected to your data source. Troubleshooting connectivity. You can kind of see what that says. That's an error message. "The current IP address is not included in the existing firewall rules." "Do you want to update the firewall rules?" That's a very common one. Actually, yesterday we had an issue trying to get connected through the network here. And it was funny because they were looking at us, saying, "Well, we're not blocking anything, so it must be something you guys are doing." And they were only talking to the people that kind of do this, right? We're like, "No, we know where the error is." "It's somewhere on your end, and we can show you why." So one of the things I did, we would bring up just general tools— telnet, traceroute—and between that and the network guys staring at it— and always blame the network, by the way— they started to understand, yeah, it was something, and they were good enough that they got into the internals of the center here. So the people providing the network, they weren't blocking anything. It was a router somewhere deep in the bowels of this convention center that was actually blocking port 1433 and preventing us from connecting. And they found it, and they didn't have to use any fancy tools. Telnet, traceroute, that was enough for them to understand what was happening. So those are good tools. Management Studio, Database Management Portal, these are tools that you will want to use when you're trying to troubleshoot things yourself as well. Can I connect through the Management Portal, which we could, but we couldn't connect through Management Studio. So you start to understand where the trouble might be. Those are the standard tools. If you do call Microsoft, they're going to say, "Let's try these things." So you might as well try them yourself first and try to walk through some of the errors. There's also the system views. You have sys.event_log and sys.database connection stats. We'll actually look at those a little bit. [WASD Errors] There are system views that help you understand what's happening in your system. The WASD errors. These are errors on the Azure side. Failover, quota, throttling, "Not Supported", database copy. Failover I don't really consider to be an error. It's kind of the perk that's— You don't want it to happen, but you're glad that it's been set up for you to happen. So it could be an error on their end. There could be something wrong on their end, or it could just be due to throttling. But instances will get moved. You'll see the message, "server not available," and that's why you want to have the retry logic to be present. So there could be something, an error on the Azure side, that causes a failover. Yeah. >>[inaudible audience member question] [Thomas] Can you define in SLA in certain windows when a failover won't happen? My answer is no. No. Unless you're a really good customer of theirs, I'm going to say no. If you're a general customer, you're not going to define anything. But I bet if you wanted to have— Don't they have private data centers? If you wanted to have a private data center and pay for something like that, absolutely. I bet you could work it out. [Scott] You can call Microsoft and go, "Hey, I need guaranteed—" [Thomas] But it's going to cost you. General use of Azure, you're not going to get that. No. But you can get it if you pay, I bet. >>[Scott] Yeah. True statement. [Thomas] If you've got money, anything can happen. [laughter] Quota. Max worker threads per instance, 180 threads per instance. So that instance that we just created, we can connect 180 connections. That is the max worker threads. There's message 10928 and 10929. If you exceed the limit, you get message 10928, and it basically tells you the connection has been terminated. This actually, what they see is 928 happen when blocking is going on. So a common example is somebody rebuilds an index and it affects 179 other people, and they all get blocked. Anybody ever see this? One query block many, many others? Believe it or not, that happens in Azure as well. And if too many threads are trying to get connected, you might actually get this error message that comes back. And 929 is for the Azure instance itself. So if all the people using the instance have too many connections, then you might see some throttling. So it's almost like this thing where 928 is kind of like, "Oh, it's you," and 929 is, "It's not you. It's actually all of you." All y'all, as Alan would say. It's all y'all. That's the problem. Max database size is also a quota. I would say 928 and 929, understand that when you do have your retry logic, 928 is you're probably going to be able to get back in faster because you're really more responsible for the 928 error. If the 929 error is happening, you have to understand because that's something at an instance level, even though if you have retry logic, it may take you a little bit longer to get back in because there's more people that need to be throttled and kicked out. Throttling. It's a watchdog service. There is a watchdog service that looks to prevent resource problems. There's actually 2 levels to this. Your session can be killed—and it would be killed with an explanation— if you are consuming too many resources. It's just flat out that's just the way it's going to work. You will get that error message. It will say you've been killed. Connection has been terminated, and it will probably tell you why. But there's 2 types of throttling that you want to understand. One is soft throttling. That's when your database is being affected, something in your database is doing something. That's a soft throttle, and that's when the service says, "I need to do something about you." Then the other one is hard throttling, which is something is affecting the entire instance, so a disk failure. This is when failovers start to kick in. So a hard throttle is it's going to affect all the databases on the instance. A soft throttle is it's just going to affect the one database. So 2 different terms for you to understand. Again, you can find this information in the event log, which is what we're going to look at. These are examples of the error messages. I picked out 5. What I like about this is I notice they all start with 40,000. I asked Bob Ward about that. It turns out if you go into SQL Server 2012 and you looked at the sys.messages table and you just looked at the ones that start in the 40,000 range, you're going to see the bulk of the error messages for Azure. You'll start to understand what is and isn't possible in Azure because you can simply read the error messages. So baked into the product, into the box, are all those error messages for you. So if you just wanted to go through it— I found it interesting one night. I don't have much of a life. I just went through a few of those messages myself. So session blocking the system task for a long period of time. Session consuming too many locks. When does lock escalation kick in? How many locks can I have before lock escalation kicks in? [inaudible audience member response] >>[Thomas] 5000. Who said that? Give that man the book. Get him a book later. 5000 is right. A million seems kind of high, doesn't it? If you've consumed a million locks, just think about that for a second. This is why I like to tell people, "If you've been throttled, you've done something to earn it." [laughter] So don't call Microsoft and complain that you're being throttled. You've done something bad. I mean, they're giving you a million locks. I looked at that and I go, "That can't be right." >>[Scott laughing] [Thomas] They're really letting you get— "You should stop that now." [Scott] Yeah. You've got to be doing something really, really bad to get throttled. [Thomas] Oh, my God. "Don't make me come back there. Don't make me come—" "I'm gonna— Don't make me come back there." And finally they do it. Session consuming too much tempdb space. Ah! We've talked about tempdb. Nobody's even mentioned tempdb and said, "Hey, I don't get a chance to optimize tempdb for performance." No, you don't. But you'll be throttled if you use too much space. But you can't put tempdb on different drives or anything. That's just taken care of for you. But it's a shared resource tempdb still even though we continue to ask for separate tempdbs. But it's still a shared resource, so if you consume too much of that, you will be throttled. Transaction too much log space and session consuming memory are another two. Like I said, feel free to check out all those wonderful error messages. It really is cool. "Not Supported." Certain things aren't supported, like a USE <db> command. You can't switch context of a database. Once you connect, you're connected. Although in Management Studio if my initial connection is the master, apparently I can toggle, which is just a USE <db> command, to a different database. I get one-time toggle. That's it. Once I've made that connection, I can't go back. So it's good and bad. ALTER DATABASE. Yeah. You're not altering your database. Well, MAXSIZE. The only altered database command is MAXSIZE. Yes, sir. >>[inaudible audience member question] [Thomas] What about the database compatibility level? [inaudible audience member question] >>[Thomas] Okay. How often do— [inaudible audience member question] >>[Thomas] I'm not sure I understand. How often do they change the database compatibility level? [inaudible audience member question] [Thomas] That's right. You don't have the option to do a compatibility level in WASD. It's not there. [inaudible audience member question] [Thomas] So how often is there a new version of Windows Azure? [inaudible audience member question] [Scott] Microsoft manages that for you. >>[Thomas] Right. You don't have the option. Microsoft is doing that for you. If you want to know how often they're doing it, I— [inaudible audience member question] [Thomas] I think he's asking about a version. >>[Scott] Yeah. [Thomas] So there's 2 things. You've built an application to run against SQL 2008. Windows Azure SQL Database is a different version. That's it. That's all you really need to consider. It's just a different version. It's not somebody going in and changing compatibility mode, they're saying, "Hey, make this version of Windows Azure actually compatible to 2005." That doesn't exist. That just does not exist. It's not an option for you to have. So it's never getting changed in that sense, I guess. Yes. >>[audience member] Can I have cross-database queries? >>[Thomas] No. Cross-database queries. No. Distributed queries. [Scott] Not native to Windows Azure SQL Database. You can through front end code but not native to Windows Azure SQL Database. [Thomas] We mentioned DBCCs, and the DMOs are database scoped. You're not getting insight into anything that's happening at the server level. No server scope DMOs are available for you. Yes, sir. >>[inaudible audience member question] [Thomas] You could if you set that up yourself, yes. But SQL audit is not an option for you, but you would have to go in— So you could do something—horrible that I'm going to suggest this— you could use triggers for that. But yeah, you have to roll your own audit solution. But there's no change data capture, there's no change tracking, right? [Scott] No, not yet. >>[Thomas] He would have to use triggers. [Scott] At that point, yeah. >>[audience member] This is not a good PCI solution. [Thomas] I'm not sure I would say that because you could use third party tools in order to get it done. [Scott] There are third party, and if you talk to me after, I'll give you the name for it. [audience member] You can't change the port. >>[Thomas] No. [inaudible audience member comment] >>[Thomas] That's right. You're right. [audience member] So there's a lot of things that you can't do. [Thomas] We'll have to talk later with him and everybody else at Microsoft. That's a great conversation to have with them. And I'm going to stand right next to you when we have it. [Performance Monitoring and Troubleshooting] All right. Let me get through this last bit. We're going to go a few minutes over. Performance monitoring. These are the basics that everybody goes through. Running/blocking, plan change, code, indexes, network latency, statistics. It's all the standard stuff. These are the basics. These are the performance scenarios that they see inside of Azure, the things that they saw that they said were unexpected. They were seeing a lot of WRITELOG waits and SE_REPL* waits. So what they see are people have code that has a lot of little transactions, especially ORMs where they built things to execute one row at a time. So they see a lot of WRITELOG activity, they see a lot of replication. SE replication, commit, acknowledgment or whatever, that's really Windows Azure going out to the other 2 copies and waiting for acknowledgement to come back. So those are kind of the big waits, and if you can go and look at your wait events as well, you'll see those things. You might be surprised by some of them. Query timeouts. They do see blocking. They recommend that you use tracer tokens, application tracing in order to figure out what's really happening because you can't run Profiler, you can't run SQL Trace. There's no extended events—not yet. So you're kind of in the dark and you're going to have to rely a lot on application tracing in order to get some information. But you can do query plan and tuning the query indexes. We showed you a little bit of that in the Management Portal. CPU and I/O. No specific restrictions. There is load balancing, and throttling or failover can happen. So these are the performance errors where Bob was pretty much telling us you fall into 1 of these 3 things. You might get throttled for CPU and I/O. It's possible. A lot of times they see more blocking issues than anything else, but they do come across plan changes, just odd performance things, the weird things that really were people just freaking out over async network I/O and calling Microsoft and saying, "What's wrong with the network?" and Microsoft saying, "It's a client thing on your end, actually." Oh, wow. Deadlocks is misspelled. That's the Belgian version for deadlocks. So deadlocks still happen because it is still SQL Server. Deadlocks may still happen, right? They've actually made it easy for you to get deadlock information out without having to use Profiler, without having to go to a system help Extended Event. It's as simple as 3 clicks and a save and a reopen for you to look at the deadlock graph. All the information is right there for you. It's actually pretty cool. I think I may have time to actually show you. DMVs for performance monitoring. Some of these are new, some are not. dm_exec_requests is still there, exec_query_stats, exec_query_plan. These are actually already in the box. If you're not using these now, you should. They can change your life as a DBA. dm_db_wait_stats is a database-specific view in Azure to show you the wait events for your instance. It's really cool. It's what I wish would be put into the box. I'd like to have that today. There are missing index DMOs. They are only meant as a guide. Please do not blindly take the missing index suggestions that SQL Server gives you and decide that that is how you want to do all your performance tuning. And if you do, please take my card so I can help you afterwards. They are meant as a guide, but they are still there for you. They are there for you in Azure as well. A look at some of the example wait types. Just to give you an example, that's what comes back. That's what it looks like. In this case, WRITELOG. REPL_COMMIT_ACK, that's the second one up there. That's the one I think I wanted to point out. So some new names for you, not necessarily anything to panic about, but you'd probably be inquisitive, you'd want to ask questions, and I'm pretty sure there's a lot of documentation in Azure. Azure has a pretty good documentation on MSDN. You can probably get a lot of details on some of these waits. I bet Bob has already blogged about some of them. [inaudible audience member question] [Thomas] No. Azure has replication. You don't have replication in Azure. You're not doing anything, but Azure is replicating. Do they use mirroring? [Scott] For the copies? >>[Thomas] Yeah. [Scott] We're using kind of a synchronization for the copies. [audience member] You don't have any control over that. >>[Scott] No. [Thomas] That's just it, you don't have any control. So for this example, if I came across here, I'd look at that and I'd make a judgment, and I might want to call Microsoft and say, "My server for my instance, I actually see a lot of replication acknowledgements, and I think it's high. Is there something wrong?" It's a fair question to ask if you feel that that's a problem and say, "It seems like I'm waiting for just the acknowledgement to come back." "Can you help me out and figure out is it normal or is there something wrong?" They'll probably want to look into that for you as well. But you don't control it. No, not at all. Question. [inaudible audience member question] [Thomas] It's synchronous. The replication in Azure is synchronous. It waits. It makes sure the copies are there? >>[Scott] It does the first one. And then it will do the third one. [Thomas] So it's both. Yeah, yeah. It's both. [Scott] So it sort of waits. It waits for the first one, and as soon as the first one commits, then it comes back and then it will do the third one. [Demo - Looking at the Internals] [Thomas] So I'm over. You know what? I was going to show you some of the DMVs. I think I'm just going to skip that for now. Let's just look at some of the best practices real quick. [Best Practices] This is really a review for today. You want to have your own copies for DR purposes. Clustered indexes are required. If you didn't know that already, every table needs to have a clustered index. You can actually create a table without one, but you won't be able to use it until you've actually put a clustered index on it. Rebuild your indexes online. If you do the ALTER DATABASE and change the MAXSIZE, that will disconnect people. DMVs are database scoped. We know that. Your DATETIME is UTC. Please don't call Microsoft and ask them to change the time zone. That's just not going to happen. You're getting UTC. Learn what that means and how to use it. Troubleshooting checklist—again, all the stuff that we've gone through. Checking the portal. You can actually check the portal to see your particular data center and see if there's an issue there. It's quite possible you're having a problem, you don't know what it is, but if you go to the website, you might see that they have acknowledged it and they say, "There's a problem in the data center right now and we're working on it." Dashboard posting for an outage. That's what that is. You can check the SQL Management Portal, another way to do some basic troubleshooting as well. General connectivity. The Internet provider issues like we had—well, that was a firewall thing. I've been at hotels where I can't connect up to Azure because the ISP is blocking 1433. So there are going to be times where you're somewhere and you're like, "I can't understand why I'm getting out to Azure." You might find that your ISP is actually being blocked. And your own firewall configurations. Final thoughts. Monitor the applications, your database, your connectivity. Think of Azure as not just a different version; you're going to need to think about it as a different platform. You're going to need to think of new architecture is what it comes down to. It's just a gentle shift to something a little bit different than what you're used to in the box. It's a shift in a really good way. It really is. The trade-off is a lot of your administrative overhead goes away. You don't have to worry about placement of tempdb and things like that. You get to focus now on better design and architecture because good database performance starts with good database design, and you'd really want to be focusing on that rather than fighting the fires down at that hardware level. You just don't want to be doing that. So that is it. I'm happy to walk people through. I'll be at the Microsoft booth tonight if you want to look a little bit more at some of the Azure stuff and some of the DMVs. I'm happy to show you some of that stuff, look at some deadlocks, things like that, whatever you want. Scott and I, we're around all week. >>[Scott] Yeah. Was this helpful? >>[audience members] Yes. [applause] [Scott] We will be here after to answer questions. >>[Thomas] References. [Scott] We'll be down— >>[Thomas] References, related content. Here's the important one. What we'd really like is for you to win something by filling out the— Oh, great. The QR code isn't in there. Please go fill out the review, session evaluation. [Scott] Yeah. Anyway, thank you very much. >>[Thomas] Thank you.

Video Details

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

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

Caption and Translate

    Sign In/Register for Dotsub to translate this video.