﻿WEBVTT NOTE duration:"00:46:28.6530000" language:en-us NOTE Confidence: 0.872454762458801 00:00:00.310 --> 00:00:20.320 Thanks for joining us today, um in today's session. We're going to look at sort of what's new in Azure SQL database. One of the sort of massive complaints but one of the big issues that we see not only with our communities. But as well with our customers is that people kind of struggled to keep up. NOTE Confidence: 0.859401345252991 00:00:21.110 --> 00:00:41.120 Announcements we do so I'm going to do a not necessarily a deep technical dive in all the features. I mean, we've released close on 100 features in the last 12 months, will just cover some of the high level ones. They will be more in depth stations. I'll tell you where to go for those if you want to know specifics about very. NOTE Confidence: 0.88788902759552 00:00:41.910 --> 00:01:01.920 And this is just to give you a general overview of where we are where we standing what's happened since the last time there was a sequel bits again and then just introduction so my name is Elaine normal. I'm a senior program manager at Microsoft and I work specifically in the Azure SQL, database team more specific. NOTE Confidence: 0.829765975475311 00:01:02.710 --> 00:01:07.650 Look after the hyperscale feature for Azure SQL, database so. NOTE Confidence: 0.884314358234406 00:01:08.670 --> 00:01:28.680 If we just briefly cover sort of what we're going to look at today. Some of the topics that will look at in depth. The first thing I'm going to add some context. 'cause there seems to be a lot of confusion about some other things that we discuss especially in the Azure. SQL database world. We then going to look at the new innovations and sort of new fees. NOTE Confidence: 0.900342226028442 00:01:29.470 --> 00:01:49.480 In the product on the platform layer and these include all the intelligent features so the performance based features as well as the security based features will have a brief overview of what is hyper scale? How you should think about it and how it can sort of change the way that you use these databases and then part of hyper scale is obviously the RE? NOTE Confidence: 0.862818837165833 00:01:50.270 --> 00:02:10.280 Scale out for Azure SQL, database will then very will look at manage instance and sort of So what we've done there and perhaps some of the things that are coming up for manage instance and then the last one is we look at some of the SQL enhancements. For anyone who was in the keynote this morning. You would've heard Bob's Bob would talk about. NOTE Confidence: 0.868862628936768 00:02:11.070 --> 00:02:31.080 The database recovery that feature is already in Azure. SQL database, so if you think about Microsoft and kind of how we go about creating a lot of our features and products that we do, we're a cloud first company so there's? NOTE Confidence: 0.896813631057739 00:02:31.870 --> 00:02:51.880 You will see that land in the Azure platform, sometimes before they land in SQL server box and that's purely because of the amount of time it takes us to ship the SQL server box product, so the first thing I'm going to do here is and this is an interactive session guys like I mean, if someone has questions feel free to ask I'm not. NOTE Confidence: 0.91509610414505 00:02:52.670 --> 00:03:03.120 Stand up here for the next 45 minutes and just talk, the whole time, but if I had to ask how many databases do you think there are sitting in Azure today on Azure SQL database? NOTE Confidence: 0.9061079621315 00:03:07.180 --> 00:03:23.690 Yeah, so, so it's actually an interesting slide 'cause. None of those are right. It's somewhere between 5 and 7 and a half. So we have about 6000000 active databases. Today I just think those numbers are a little bit out of date. But we have about 6000000 active databases today and that's NOTE Confidence: 0.90903514623642 00:03:24.650 --> 00:03:44.660 It's very important for you to understand sort of the amount of Telemetry that we gather from all of these databases on a daily basis. I think the last number that I heard was every single day so in a 24 hour cycle. We gather close on 8 petabytes with Telemetry from all of these databases and that really allows us to. NOTE Confidence: 0.904628872871399 00:03:45.450 --> 00:03:54.670 That we do on the platform layer that really differentiates our product from the big competitors in the cloud space specifically. NOTE Confidence: 0.86716228723526 00:03:55.990 --> 00:04:03.000 So they say 5 but I know it's a little bit more than that now so if you then look at. NOTE Confidence: 0.826444804668427 00:04:04.410 --> 00:04:07.460 Sort of the entire relational. NOTE Confidence: 0.88173371553421 00:04:08.470 --> 00:04:28.480 Ecosystem that lives within Azure. There are sort of these major policy that we strive to him. Whether you're speaking about SQL server Azure. SQL database or whether you talking about my SQL, Postgres or Maria DB. These are sort of the main things that we try and align to for everything right so hybrid is very important for us, so making it easy. NOTE Confidence: 0.888912320137024 00:04:29.270 --> 00:04:49.280 From where they are today up into the Azure platform, and I mean, we've put a lot of development time into making sure that that process for customers is easy. It's frictionless. They shouldn't have to sort of do a lot of redevelopment to move up into the Azure platform, and I think that's NOTE Confidence: 0.878433704376221 00:04:50.070 --> 00:05:10.080 The way manage instance sort of really comes into its own in that manage instance gives our customers. The sort of the breadth of the SQL code base that they didn't have in the previous deployments of Azure. SQL database, the scale and performance. Obviously, those are super in. NOTE Confidence: 0.856834769248962 00:05:10.870 --> 00:05:30.880 When we look at hyper scale one of the things that the architecture for hyperscale allows us to do is allows us to do near practically zero downtime upgrade so you can go from a 2 Core 2 and 8 equal machine without incurring any down time. NOTE Confidence: 0.888469576835632 00:05:31.670 --> 00:05:51.680 And it all happens asynchronously. So, your system won't have to sort of wait for the new compute aspect to come in. There will be a point when it cuts over and at worst you'll probably see a connection dropped that takes place when that happens. But if you had to build retry logic until apps a lot of those things. NOTE Confidence: 0.93029773235321 00:05:52.480 --> 00:06:04.940 Wouldn't be a problem for you, but that's really somewhere where we focus. A lot is making sure that they are product that can scale whether that is vertically or horizontally to meet the needs of our customers are doing. NOTE Confidence: 0.914530694484711 00:06:05.850 --> 00:06:25.860 Security and compliance is absolutely sort of first and foremost in everything that we think about. We try and sort of make sure that everything we build will be compliant and will be secure on the Azure platform. We have more compliance certifications for Azure SQL, database than any. NOTE Confidence: 0.889946937561035 00:06:26.650 --> 00:06:46.660 Providers out there and some of our big investments that day, so for example, we've recently brought out the BY OK for transparent data encryption in other words. Customers can bring their own certificates to encrypt their databases and that will essentially mean that it doesn't matter. NOTE Confidence: 0.886461853981018 00:06:47.450 --> 00:07:07.460 Not even Microsoft can get English. We don't own those sorts. Not that we would want to. But the point being is gives customers that sort of peace of mind to do things like that, and then the Bolton Intelligence, which is what I was speaking about earlier. All this telemetry that we gather we run a lot of machine learning models in the background. NOTE Confidence: 0.909955859184265 00:07:08.250 --> 00:07:28.260 Network traffic we look at how the compute the memory. All of these things are being utilized and we can then provide intelligent insight into how these databases can be optimized should you be thinking about scaling up scaling out whatever the scenario and will show you kind of a lot of those features in the demos that I'm going to do and then. NOTE Confidence: 0.851235091686249 00:07:29.050 --> 00:07:49.060 This one is as I mentioned sort of the choice of deployment right so SQL is this is a sequel based conference side safe to say that if I only talk about SQL that's fine. But we do offer my SQL options. We do offer. Postgres options and we do offer. Maria DB options right and they'll be more that will come to. NOTE Confidence: 0.917029917240143 00:07:49.850 --> 00:07:56.090 And the whole point there is you shouldn't have to change what you're doing to take advantage of what the cloud is going to be offering. NOTE Confidence: 0.878598690032959 00:07:57.970 --> 00:08:03.570 And then kind of the last thing in terms of sort of China, said some other context. NOTE Confidence: 0.881010055541992 00:08:04.860 --> 00:08:24.870 I think before we jump into the something that's very important to understand is if I speak about Azure. SQL database that is a sort of a family of products and in that family, we have single databases. We have elastic pools and we have managed instance, so anything. I talk about here today. When I say management and so when I when I. NOTE Confidence: 0.913618743419647 00:08:25.660 --> 00:08:45.670 Those are interchangeable terms, they're essentially just 3 deployment options of the same product. It's the same engineering team we drive the same sort of agenda for everything that we both so a lot of people seem to think manage instances. This thing that stands out on its own and it's a product deployment unto itself. That's not necessarily the case. NOTE Confidence: 0.891953349113464 00:08:46.460 --> 00:08:51.510 Part and parcel of everything that we do with Azure SQL database. NOTE Confidence: 0.904373347759247 00:08:52.420 --> 00:09:12.430 And then sort of the very last thing is the migration thing, so you've probably heard it a couple of times you heard it this morning in the keynote one of the big things we're seeing today. Whether that's with customers. Partners anyone is sort of this whole notion of the end of support for 2018, 28, R 2 that's coming. NOTE Confidence: 0.774678170681 00:09:13.220 --> 00:09:14.680 This year now. NOTE Confidence: 0.923262476921082 00:09:15.670 --> 00:09:35.680 A lot of the customers that I deal with have legacy applications. They're not really interested in modernizing the underlying data layer. They just want to move that thing somewhere where they'll continue to get support. They will continue to get the base features in SQL and how you move that is very important so we have a bunch of first party tools, we have. NOTE Confidence: 0.885579526424408 00:09:36.620 --> 00:09:56.630 Partners in the space that will do everything from sequel to SQL Oracle to SQL my SQL to my SQL, Cassandra to Cosmos. The migration story is really important to us and those tools are absolutely sort of front and center in how we try and May. NOTE Confidence: 0.920352280139923 00:09:57.420 --> 00:09:59.610 Easy as possible for people. NOTE Confidence: 0.905295729637146 00:10:00.560 --> 00:10:05.880 So that's kind of the bulk of the context that I want to sit in terms of you know where we are with. NOTE Confidence: 0.919518709182739 00:10:06.780 --> 00:10:26.790 Azure SQL database what we thinking about and here we have a list of what are probably the biggest features that we've released in the last I'd say 6 months. They are a bunch of other ones here, there are things that happen internally. You know like optimization of how we utilize infrastructure. There's a bunch of things that we don't necessarily list here. NOTE Confidence: 0.91524875164032 00:10:27.580 --> 00:10:47.590 Know that the full list for the last 12 months is probably about 100 or so features big so if we just look under the hybrid story. Consistency is super important for us. So when I say consistency. The experience that you have with your SQL service today on premise is the same experience you should be having. NOTE Confidence: 0.880127727985382 00:10:48.380 --> 00:11:08.390 Cool database all the time right so if you're using features on Prem Today you should be able to use those in the cloud. So closing that gap between between what we offer in the cloud versus what we are fun. Prim is definitely a big thing. And when we get to the manage instance story. I'll sort of dive into a bit more exactly what falls under. NOTE Confidence: 0.894537150859833 00:11:09.180 --> 00:11:29.190 And the interoperability and frictionless migrations. We've spoken about those already under the performance and scale. We've got hyper scale. That's a very, very big one for us that happens to be the feature that II look after so we can dive into that a bit more there will be a session this afternoon. NOTE Confidence: 0.900627255439758 00:11:29.980 --> 00:11:49.990 With me and Kevin Farley and will do a deep dive into the underlying architecture of hyperscale how it works, how the reads and writes work and how it is very different from anything else. You've seen the cloud. Today it's really a database. That's been built with sort of cloud first thing. NOTE Confidence: 0.864483296871185 00:11:50.780 --> 00:12:10.790 From the gate code right so the the type of performance and the type of deployments that we often hyper scale will never come to box equal it's just not possible. The infrastructure that a customer would need and these things to do hyper scale on prim is unfeasible so absolutely if you're in. NOTE Confidence: 0.851926803588867 00:12:11.580 --> 00:12:17.550 This afternoon, I will briefly cover today accelerated database recovery. NOTE Confidence: 0.89672577381134 00:12:18.680 --> 00:12:38.690 This one has got a bunch of excitement in the sense that what it will all you do is if you have these long writing transactions and something goes wrong or the SQL database had to crash. Usually the rollback for those things is extremely sort of time, consuming and it partially brings the database down. NOTE Confidence: 0.878734827041626 00:12:39.480 --> 00:12:59.490 Call back has to take place so with accelerated database will cover you get super aggressive log truncations and all of these things that really bring almost that will bring that time for a rollback transaction to be virtually zero and interestingly enough accelerated database recovery is an extremely import. NOTE Confidence: 0.870688915252686 00:13:00.280 --> 00:13:11.620 It's what allows us to do a lot of the things that we do in hyper scale and again. That's 1 of those features that we've had it in SQL database for. NOTE Confidence: 0.895602762699127 00:13:12.600 --> 00:13:32.610 Probably 3 or 4 months already and it's only going to be shipping. In 2019 to the box version of SQL, so that's 1 of those fantastic examples of things that we do in the cloud first and then it will sort of come down to box equal as we get there read replicas. That is also part of the hyper scale family of. NOTE Confidence: 0.880449235439301 00:13:33.400 --> 00:13:53.410 In that what it will allow you to do is it will allow you to scale out your secondaires so the number of Secondaires. You you have attached your data. You can scale out to a up to 14. Sorry up to 4 with future plans for us to sort of increase the number that you can have when we show you the Ark. NOTE Confidence: 0.905617892742157 00:13:54.200 --> 00:14:14.210 So you'll understand why this becomes possible because in Hyperscale will not copy in your data. The whole time, so you don't have these like 4 or 5 physical copies of the data. It's one copy of the data that has multiple compute nodes attached to it, and those read replicas are ideal for customers that have. NOTE Confidence: 0.903207540512085 00:14:15.000 --> 00:14:35.010 Very either read heavy workloads or that are very sensitive to other workloads impacting their write workloads. So if you have a highly transactional system? What a lot of people do is they put by an OLTP in the same sort of machine and then when someone kicks off a bad query in the by space. NOTE Confidence: 0.88394683599472 00:14:35.800 --> 00:14:55.810 Well TP transactions suffers result of this with read scale replicas. You will be able to provision individual compute nodes for those read heavy workloads and you can connect directly to them right. So you will have initially for that what we what we plan on having is you will use the app. NOTE Confidence: 0.895297467708588 00:14:56.600 --> 00:15:13.480 Read only in your connection string and then as you execute a query. It'll get around Rob and to one of the readables, but we do plan on introducing dedicated DNS. For every single one of the secondary so that you can connect directly to your secondary without going through the primary, the whole time. NOTE Confidence: 0.829541563987732 00:15:14.390 --> 00:15:34.400 Under the security section always encrypted with security enclaves so you, saying she in a nutshell what we do. I'm not going to cover that one in a lot of depth. There's a session on that. Andreas vulture will be having a session and then we call that a piece of the CPU in Nazi. NOTE Confidence: 0.901311039924622 00:15:35.190 --> 00:15:55.200 Secure enclave and it allows us to provide additional functionality for always encrypted data right because today if you use always encrypt becaus the data is encrypted it's hard to do certain functions on it with that, we can sort of open up more of the surface area because there is this secure enclave that has the ability to decrypt that. NOTE Confidence: 0.897637069225311 00:15:55.990 --> 00:16:16.000 Securely within the system threat detection again. That's 1 of the platform based features. I will show you a demo on exactly how that works, but threat detection is really about us monitoring network packets. We look for very obvious sort of attack vectors things like SQL injection. NOTE Confidence: 0.890779137611389 00:16:16.790 --> 00:16:36.800 We monitor those and we alert our customers on the platform while before most people will pick it up vulnerability assessment is I mean, we've been building SQL for 25 years, so if anyone's going to understand best practices. It's going to be Microsoft so vulnerability assessment will do these onetime scan. NOTE Confidence: 0.887423276901245 00:16:37.590 --> 00:16:57.600 Will not one time you can schedule them, but it does a point in time scan of your database and it generates a report that tells you these are the things that you should consider changing these are some of the issues on your database potentially go fix. These information protection. I've also going to demo to show you on that. But what it allows you to do is it allows you to. NOTE Confidence: 0.879034519195557 00:16:58.390 --> 00:17:18.400 So specifically in context for this audience that is a massive enabler for GDP are right so if you look at what GDP are essentially is it involves you understanding what data you have as well as the ability to audit who accesses. NOTE Confidence: 0.880852699279785 00:17:19.210 --> 00:17:39.220 Right so when to understand the data. You have you have to be able to label all the data right so you have to have some kind of idea of I contain age. I contain name and surname and how that data gets used by people. The interesting thing with the GDPR thing is that a single columns if you think of data. NOTE Confidence: 0.919282376766205 00:17:40.010 --> 00:18:00.020 It's not necessarily a sensitive column, but when you combine that of both with like a first name or an address or something like that, then all of a sudden as a combination of columns. They become sensitive so a lot of what we building. There is to try give people guidance in terms of you should think about classifying these columns. NOTE Confidence: 0.875247180461884 00:18:00.810 --> 00:18:20.820 Think about adding labels to these columns at will dive we've got a nice demo for you, there as well. If it works. Let's have a look and then static data masking that from a developer standpoint, you know if you want to give a developer access to data but you don't necessarily want to give them some type of data. NOTE Confidence: 0.860109448432922 00:18:21.610 --> 00:18:41.620 Specially, if you think about credit card numbers and things like that with static data masking you can really basically provide them a full backup that is completely most from the get go and I can host that in a different environment and then in the intelligence section. Intelligent query processing that's a huge topic Pedro Lopes has a NOTE Confidence: 0.918349027633667 00:18:42.410 --> 00:19:02.420 But I'm not even going to cover that one today, but that is an area where we have invested a lot, so there are a bunch of things we're doing there, to ensure that at runtime of a query. We ensure that the query. Optimizer has the base set of information possible to generate the best possible plan to execute through this data, so I mean, if you? NOTE Confidence: 0.902877032756805 00:19:03.210 --> 00:19:23.220 What happened was that runtime we would generate a plan and then we would execute against that plan so a lot of this is now very adaptive sort of things where we re evaluate the plan as the execution takes place and we will restate some of the estimates that we made initially when generating the plan and potentially even influence how the planets. NOTE Confidence: 0.867746889591217 00:19:24.010 --> 00:19:44.020 So if that's a topic you're interested absolutely go. Watch Pedro Station. It's very, very good topic the intelligent performance. I will cover that order tuning I'll cover that and then this one's kind of a red herring for us in that machine learning services has been on box equal since 26th. NOTE Confidence: 0.890408575534821 00:19:44.810 --> 00:20:04.820 It's something we plan on releasing and something that we sort of working towards it's currently in preview. It's slightly harder than people would believe but that will definitely be coming for Azure. SQL database and it will allow you to basically run your machine learning models if they're admin are and sort of. NOTE Confidence: 0.854271233081818 00:20:05.610 --> 00:20:09.360 Paithan directly in the database, the same way you do for SQL today. NOTE Confidence: 0.901095867156982 00:20:10.580 --> 00:20:30.590 And then just sort of very largely like a lot of these things right or definitely best of breed sort of things right like if you're an existing SQL customer it financially. It just doesn't make sense for you to go to any of the other clouds right like there is a lot. We do for existing customers to incentivize them to move so if you think of. NOTE Confidence: 0.916239142417908 00:20:31.380 --> 00:20:51.390 Old legacy applications that you have these things that are running 28. No one wants to go and modernize those applications. No one wants to go and modernize those databases that thing just has to continue running because once a month. Some guy goes in and he pulls a report. Most people have those type of scenarios, so instead of going through all the development. NOTE Confidence: 0.903653562068939 00:20:52.180 --> 00:21:02.420 Updating that system and sort of doing the changes to the application. You can just bring that across into the Azure platform, and the other big thing here is. NOTE Confidence: 0.867283761501312 00:21:03.370 --> 00:21:06.720 Who here is familiar with running AJ clusters? NOTE Confidence: 0.900911450386047 00:21:08.050 --> 00:21:28.060 Anyone always on clusters right there are fairly complex thing to maintain from a from a cost perspective. They cost quite a bit 'cause, you're going to double up on hardware or Triple. A pawn hardware alot of these things high availability disaster recovery. All of these things are built into the Azure platform, and we offer a lot of these features out the box. NOTE Confidence: 0.897783041000366 00:21:28.850 --> 00:21:48.860 Every database that we deployed in Azure complies to our 49. Tesla so 99.99 percent of the time that database will be available and that's because we use the always on technology in the background to ensure that that database is always available and always and nothing can really go. NOTE Confidence: 0.888737916946411 00:21:49.650 --> 00:21:54.080 Failure will fail over for you and we maintain that entire environment for you. NOTE Confidence: 0.892427206039429 00:21:55.830 --> 00:22:15.840 That's a high level of kind of some other things were going to cover. We're going to dive a bit deeper now into and do some of the demos for some of the platform based features so the first 2 that were going to cover here is the intelligent in the security or the intelligent security and performance features that we've got let me just get my. NOTE Confidence: 0.803413569927216 00:22:16.630 --> 00:22:26.160 Duplicate and we can do that alright so if we look over here. NOTE Confidence: 0.877483308315277 00:22:27.210 --> 00:22:30.400 Oh, that's of a smoke. Let me just zoom in on my browser quickly. NOTE Confidence: 0.881573736667633 00:22:31.830 --> 00:22:51.840 So if we go up here. I should be able to better can even see that fine right. So here what we've got is this is actually a an application that is running on the Azure platform. And it is very creatively called the my bad websites application and essentially what it is. NOTE Confidence: 0.904720067977905 00:22:52.630 --> 00:23:11.000 That we've built to simulate things like SQL injections brute force attacks on the database anything like that, so if I come and I actually look at this application. It's a very simple little application. But there's a lot of these out there right there is a lot of these type of applications where. NOTE Confidence: 0.902725458145142 00:23:12.270 --> 00:23:32.280 Especially older applications where people haven't really put a lot of security with different a couple of years ago right. Let's just go with that statement to say anyone did anyone, but it has become a lot easier for people to sort of take advantage of these older applications. So here we have this little application and you'll see if I had. NOTE Confidence: 0.842085242271423 00:23:33.070 --> 00:23:53.080 Go in simulate one of these so let's take this one over here, so this, or equals one equals this one over here, so you can see here for the demo instructions. I there's a username and a password. Let's log in with those so it's user right? That's the username for this website. NOTE Confidence: 0.889139711856842 00:23:53.870 --> 00:24:11.920 So there's 1234 so really to be fair. There is no real reason why this website shouldn't get attacked because of that password. But for arguments. Licious log into this website quickly and you'll see. It's a very basic little CRM. Let's try that again. NOTE Confidence: 0.899550378322601 00:24:16.020 --> 00:24:34.260 OK let's see if it'll go right now right so this is it's like a small little CRM. It contains customers say instead of information so you go first names. You've got last name. You got where people love you. Got email addresses. We have all of this information that, like you really wouldn't want someone to get hold of at any given time. NOTE Confidence: 0.887533605098724 00:24:35.530 --> 00:24:47.670 Now, if we log out and we then sort of do a SQL injection here, which essentially means you can enter this parameter that one over there. NOTE Confidence: 0.920796692371368 00:24:49.080 --> 00:25:09.090 And the previous password is 1234. So I can literally typing anything because of how this injection specifically works. You will see that if I click login. It still let me into the website because I've basically fooled the SQL database into thinking that, like well. This guy should be allowed in here and that's really how SQL injection. NOTE Confidence: 0.913396835327148 00:25:09.880 --> 00:25:28.480 And these kind of things that's really how they work. You have people that go around and they write weird little statements like that, and they continue to try get into your website and if you look specifically. I'll give you a very good example of how that how exactly that works, but here for example, if I run this one. NOTE Confidence: 0.881035923957825 00:25:31.080 --> 00:25:34.210 In the username and again, I can fill in any password I want. NOTE Confidence: 0.926300585269928 00:25:37.060 --> 00:25:57.070 Now that might look like a failure to you and in fact, it is, but this is how they operate when they're looking for attack vectors. The problem with this specific statement is that it is telling you everything about the environment where this database is running right so it tells you what the version of SQL. NOTE Confidence: 0.824867725372314 00:25:57.860 --> 00:26:00.240 I just gotta find it here quickly. NOTE Confidence: 0.902395486831665 00:26:01.520 --> 00:26:21.530 There is a anyways there is there. There is one of the return strings here is the version of SQL where it is running so using this they can then refine their attack. 'cause now they know, Oh, Well, you're on Azure. SQL database now. I know more about your environment and then they get smaller and smaller in Florida, they can actually get into the system. NOTE Confidence: 0.904933750629425 00:26:22.320 --> 00:26:42.330 Generally, how it works, So what we've done to kind of prevent a lot of these things is we've introduced the advanced security suite. So this is specifically a managed instance. It applies to Azure. SQL database as well, and you'll see, there on the side. I've got advanced threat protection and you'll see. I actually have 3 threats. NOTE Confidence: 0.888049244880676 00:26:43.120 --> 00:27:03.130 One from that's 1 of them from today, which is a potential sequel injection and that was that first query that I actually ran the second one was a bit more brute force, but ultimately what it is telling me here is that I have this high, severity incident that just took place what actually happens is I. NOTE Confidence: 0.908154845237732 00:27:04.290 --> 00:27:24.300 I'm not going to actually open my email because that's just bad for demos. But I actually would have got an email as well. That said listen this database has just undergone some type of security event. Please go have a look at it, you can take that a step further and you can actually ensure that if any of these events come through you can actually go lock the database down. NOTE Confidence: 0.905461490154266 00:27:25.090 --> 00:27:45.100 Without you having to do anything so you can go close the entire firewall so that nothing is you know out of this database. But what we have here is you'll see. I can see, there's a new potential attack. That's taken place. I can actually drill down specifically to which database it was and then more than that I can actually go and see exactly what query was run. NOTE Confidence: 0.914846181869507 00:27:45.890 --> 00:28:05.110 And they tried to do this attack right so this is something that is very critical on the Azure platform. It's something that we like if you have a database. You have access to these tools right so I recommend everyone turns them on. Everyone has a look at how these operate, but they really are designed to sort of protect you from? NOTE Confidence: 0.9102423787117 00:28:06.450 --> 00:28:15.420 For lack of a better word bad coding on the front end that allows people to inject the sequel statements as they try get into your website. NOTE Confidence: 0.907425582408905 00:28:17.220 --> 00:28:37.230 So that's the that's the first sort of security feature that will that we've covered is the threat detection. The next one that I mentioned was vulnerability assessment, so again vulnerability assessment is more around proactive than reactive type of security and what you have here is you. NOTE Confidence: 0.884856224060059 00:28:38.020 --> 00:28:58.030 As some data that's loaded into it and you run these onetime scans you can schedule. Those scans to run once a day. You can schedule and run once a month. Whatever you want. The results from those reports can be sent to you via email. These are grateful things like auditors or anyone who needs to get access. NOTE Confidence: 0.912002563476563 00:28:58.820 --> 00:29:18.830 But what it does is it it basically looks at the whole database and it sort of gives you these high level. Not high level gives you very specific recommendations around things you should consider doing differently from a security standpoint within that database. So you'll see as an example. There is a this one. Let's look at this one specific. NOTE Confidence: 0.882731139659882 00:29:19.620 --> 00:29:28.520 The set of principles in the database that have elevated rights so for example, there's this cloud essay user that has sysadmin in this database. NOTE Confidence: 0.913440346717834 00:29:29.540 --> 00:29:49.550 Normally that is not very good practice. You shouldn't have thousands of people running around there that have sysadmin into production databases. So these recommendations are really to help you sort of lock down these environments and ensure that not only you compliant but you are as secure as possible in these invite. NOTE Confidence: 0.901937365531921 00:29:50.340 --> 00:30:10.350 The other option that vulnerability assessment does allow you is it allows you to approve any of these recommendations that we make as baselines and the manager approve something as a baseline. The next time you run a scan. It won't come up again, but knowing that by doing a baseline you're essentially overriding. NOTE Confidence: 0.868068993091583 00:30:31.170 --> 00:30:39.910 Is unencrypted and this is just saying well this database? Is unencrypted you should consider going to go and turn it on. NOTE Confidence: 0.910754382610321 00:30:41.840 --> 00:31:01.850 And that sort of is the bulk of one vulnerability assessment is going to do for you. It's going to offer you a lot of these insights into how to secure the platform and make it so that it's easier for you to understand this 'cause you can imagine if you're running. Hundreds and thousands of these databases to kind of like go look at all these things manually per data. NOTE Confidence: 0.870005309581757 00:31:02.640 --> 00:31:22.650 Very necessarily want to do that all the time so that kind of covers some of the platform based security features that we've released recently. We're now going to look at some of the performance based features and the first one, that we will look at is. NOTE Confidence: 0.886584401130676 00:31:23.440 --> 00:31:43.450 This is SQL, Azure Sequel Analytics, so for anyone who's ever who's used in Azure. SQL database before will understand will know that there are both in performance recommendations from the portal that you can access so we monitor workloads wymon. NOTE Confidence: 0.905275404453278 00:31:44.240 --> 00:32:04.250 How your databases utilizing CPU Amen we make recommendations for example, we can tell you if you had to index this column. You could save 30%. CPU by indexing this column in this way. And that's nice but again, it's on a single database level. So what I'm showing today is sort of the next. NOTE Confidence: 0.910716891288757 00:32:05.040 --> 00:32:25.050 We're going to look at what happens if I'm monitoring 10000 databases across 20 different subscriptions in the Azure platform. So this is really where Azure sequel. Analytics comes in and you'll see here on this specific one. Although not a huge amount. I still have 100 a little bit over 100. NOTE Confidence: 0.905679762363434 00:32:25.840 --> 00:32:45.850 Across multiple manage instances that I can monitor the performance for all that a high level. But I still have the ability to drill down into the individual recommendations per database. So what you're seeing here is is here. All my databases and then here it is showing me resource usage across the. NOTE Confidence: 0.890151977539063 00:32:46.640 --> 00:33:06.650 The ones that I should be concerned about all the red ones. The Orange ones. Just means multiple databases. If they if they doubled up stack like that, and then it kind of shows me query durations. So there's a lot of things I can see here, but this is for 100 databases at a time right so this is not one database that I'm monitoring. This is an entire environment, so if I drill down. NOTE Confidence: 0.805044174194336 00:33:07.440 --> 00:33:10.370 Into the insights for these. NOTE Confidence: 0.921934366226196 00:33:11.390 --> 00:33:31.400 What this tool does allow you to do is it allows you to essentially monitor not only multiple deployments across single subscriptions. But you can do it across multiple subscriptions now, so if you for example, working consultancy and you have 100 customers. You wouldn't have to set this up. NOTE Confidence: 0.914312303066254 00:33:32.190 --> 00:33:52.200 Single customer you could have a single installation that goes across multiple subscriptions for all of your customers in the same place and you can do all of the performance checking the Chewning. All of these sort of recommendations from a performance perspective will get surfaced up to you in SQL Azure Sequel Analytics. NOTE Confidence: 0.90123838186264 00:33:52.990 --> 00:34:04.420 Can do from here then is we can dive into a specific database. So I'm going to drill down into this database over here and if you then look closer. Where is it now? NOTE Confidence: 0.886000275611877 00:34:05.820 --> 00:34:06.910 It has moved. NOTE Confidence: 0.896071195602417 00:34:07.820 --> 00:34:27.830 If you then look closer. I can actually go and I can I can view the individual queries that are causing performance related issues on this database there. We go so there's the query that it's saying is taking up a lot of resources. It's a long running query. So you should consider optimizing that query to prevent sort of the. NOTE Confidence: 0.880941331386566 00:34:28.620 --> 00:34:46.290 That, it has resulted in right and you get all types of things you get network related problems. You get CPU related memory related. All of them will be surfaced up through this tool so from a platform perspective we really offering a bunch of tools that. NOTE Confidence: 0.92250120639801 00:34:47.700 --> 00:34:52.740 Probably are going to be very hard for us to deploy inbox equal because. NOTE Confidence: 0.918802320957184 00:34:54.110 --> 00:35:14.120 I don't know the exact numbers but I wouldn't be surprised if it's like 95% of all SQL installations are not actually exposed to the Internet right so there's no way that we can gather that Telemetry to offer these type of insights so these are some of those features that really are a big incentive to move some workloads up into Azure. NOTE Confidence: 0.892752885818481 00:35:14.910 --> 00:35:34.920 I have nothing against IWS, but they can't do things like this, because of the way that audience gets deployed. RDS is essentially SQL running in a VM so they don't have access to this type of Telemetry, then and have access to this so these really are differentiating features that you can only find in the ash. NOTE Confidence: 0.940330564975739 00:35:37.250 --> 00:35:41.250 If we then jump back to the slides. NOTE Confidence: 0.359740227460861 00:35:45.750 --> 00:35:46.290 Yeah. NOTE Confidence: 0.517909586429596 00:35:48.240 --> 00:35:48.580 No. NOTE Confidence: 0.845739185810089 00:35:53.240 --> 00:35:54.830 Sorry. Let me just get this out. NOTE Confidence: 0.825694024562836 00:35:59.770 --> 00:36:04.810 OK, I don't know why the dicks being funny covered that alright. NOTE Confidence: 0.893388688564301 00:36:06.400 --> 00:36:26.410 So that's kind of the the major platform based features from a security and performance perspective. The next thing we're going to look at is hyper scale, so as I mentioned earlier this is kind of my this is one of the features that I own at Microsoft so we could. NOTE Confidence: 0.912721395492554 00:36:27.200 --> 00:36:44.520 In the next 2 hours talking on this, but this is really just like just an overview of what we're doing here. If you as I said if you want to know more about this. There is a full session later this afternoon that will go a lot deeper than this, but really what hyper scale is about is. NOTE Confidence: 0.89595890045166 00:36:45.930 --> 00:36:52.590 We have separated compute from storage completely in the hyper scale scenario. NOTE Confidence: 0.900498569011688 00:36:53.550 --> 00:37:06.170 Which has allowed us to offer support for very large databases so things in excess of 8 Terabytes was the previous limited measure, we now support 100 terabytes in in Azure. NOTE Confidence: 0.924371659755707 00:37:07.410 --> 00:37:27.420 But we've managed to do all of that without compromising on performance for very large databases. And I'll kind of give a brief walk through how we've done a lot of that. The system is also being built in a different way than what we traditionally thought about availability so if you look at any. NOTE Confidence: 0.889062404632568 00:37:28.210 --> 00:37:48.220 And if you look at most SQL server deployments. They rely on always on to kind of bold hi into them with hyper scale. We've changed the way we thought about it whereby we've both redundancy into the storage layer so that even if the computes go down. It's pretty easy for us in Azure to bring those compute nodes. NOTE Confidence: 0.897307872772217 00:37:49.220 --> 00:38:09.230 But everything like your buffer pools and all of that stuff is actually persisted on the storage layer so they won't be any sort of massive issues with you, having a failure in a node that node will just sort of be recreated and reattached the storage and it also then allows us to do things like the readable secondaires that we have for hyper scale. NOTE Confidence: 0.866014540195465 00:38:10.020 --> 00:38:30.030 The the sort of the one of the major points here is we now allow scaling up to 100. Terabytes from an architectural standpoint that 100 terabyte number is purely a purely a marketing number. It's not it's not a. NOTE Confidence: 0.897968888282776 00:38:30.820 --> 00:38:50.830 Imitation we can go way above that given how we've architected the system and a lot of the things that we've changed and Dunn have made it so that traditional functions like doing restores and backups and all of these things that were that were in the past were. NOTE Confidence: 0.892771780490875 00:38:51.620 --> 00:39:11.630 Pendant on the size of your data so you can imagine doing a restore of a 50 terabyte database would take in most cases, a couple of days with hyper scale. We can do that in sort of sub 15 minutes kind of thing, so we can restore an entire 15 terabyte 50 terabyte. NOTE Confidence: 0.895314812660217 00:39:12.420 --> 00:39:32.430 In less than 15 minutes and that sort of the 15 minutes. That's required is a number that is irrespective of size of data so 50. Terabytes will take the same amount of time as 100 terabytes to restore and that's because of how we've architected this. NOTE Confidence: 0.900834739208221 00:39:33.220 --> 00:39:46.230 Checkpoints in all of these things actually get pushed down into the computer page. Servers and make it a lot more efficient for dealing with these large data sets and then obviously high performance so. NOTE Confidence: 0.920387506484985 00:39:47.810 --> 00:40:07.820 One of the things that we've done here is we've separated the logs from the data from the compute. So, your log file is no longer dependent on how fast you can write into the data file so you have massive log regeneration that can take place so very good for things like butt. NOTE Confidence: 0.787047743797302 00:40:08.770 --> 00:40:14.290 And all the backups are snap snapshot dependent right so. NOTE Confidence: 0.892357051372528 00:40:15.420 --> 00:40:35.430 That's kind of the 10000 foot view of what we've done. You have these compute nodes. You have your primary compute node, which is running RBPX, which is the resilient buffer pool extensions that were introduced in SQL 2016 and then that would be your primary read write node now in this specific. NOTE Confidence: 0.904674887657166 00:40:36.220 --> 00:40:56.230 Diagram here we have a single primary compute node, which is read write and we have 3 readable secondaires attached to it right and those readable secondaires are all attached to the same storage architecture. It's not separate storage architecture. It's not it's not duplicated storage as is the. NOTE Confidence: 0.87288761138916 00:40:57.020 --> 00:41:05.860 Always on Will you have physically got 3 or 4 copies of your data depending on the number of quorums you've got and ultimately. NOTE Confidence: 0.89554762840271 00:41:06.890 --> 00:41:08.410 If I just move on here. NOTE Confidence: 0.896231830120087 00:41:09.410 --> 00:41:29.420 What happens here is we've introduced this concept of having a log server so you actually have a log server that is capable that has its own computer sign to it, you right into the log server, and the minute a record has been sort of hard and on to the storage layer we consider that transaction. NOTE Confidence: 0.872430920600891 00:41:30.210 --> 00:41:50.220 Complete the log server is then responsible for populating that data into all the secondaires for the RBPX layer and it is then responsible for populating the page service, which also run RV PX on them. So you have local SSD for the localized orbx layer and you have. NOTE Confidence: 0.85212630033493 00:41:51.070 --> 00:42:02.060 Premium remote storage for the for the individual page service, which are one terabyte RBPX files essentially and those remote. NOTE Confidence: 0.911182522773743 00:42:03.120 --> 00:42:23.130 The remote servers also have their own computer attached so that's why we can do. The backups in these things so fast because we have the ability to push down all of those compute heavy functions until the page servers and ultimately the time it takes a backup to restore is dependent on the time it takes a single. NOTE Confidence: 0.886496782302856 00:42:23.920 --> 00:42:43.930 To do it because if you have 101 terabyte page servers. The slowest page. Server is going to determine how long it's going to take you to restore 100 Terabytes and then from those page servers. We then write down into standard. Azure Blob storage and we do file snapshots on top of that. NOTE Confidence: 0.917614936828613 00:42:44.720 --> 00:43:01.590 3 different way of thinking about how we architect these databases and how we think about high availability. So it is very different from what you're going to find today in business critical or general purpose where you have say my tightly coupling between compute and storage. NOTE Confidence: 0.884965181350708 00:43:04.030 --> 00:43:24.040 The next sort of major thing that will look at is manage instance and I'm happy to sort of not announce because it's not really. It's not really non public knowledge, but apart from the fact that we've gone general generally available for business. NOTE Confidence: 0.88209730386734 00:43:24.830 --> 00:43:34.240 General purpose tears in manage instance, we will be bringing out the public preview for Hyperscale managed instance. NOTE Confidence: 0.902146935462952 00:43:35.300 --> 00:43:55.310 Probably in the next 6 months or so which means you'll be able to have manage instances that can now support up to 100 terabytes on the instance level so that'll solve a lot of issues for customers. A lot of customers that are looking at moving to MI have kind of these issues, where my database is too large to currently. NOTE Confidence: 0.469659864902496 00:43:56.730 --> 00:43:58.170 And. NOTE Confidence: 0.859495460987091 00:43:59.290 --> 00:44:19.300 When age win Hyperscale comes to MI it will have that exact same underlying storage infrastructure that we see with the standard deployments. They are chasing me for time the only thing I really had left to show you was access. NOTE Confidence: 0.492676168680191 00:44:20.090 --> 00:44:21.400 Probably. NOTE Confidence: 0.916493475437164 00:44:23.200 --> 00:44:43.210 But if you do want to know more about this make sure that you go to Pedro session. If you think about how we do rollbacks today in SQL server, essentially what happens if a sequel server crashes in the middle of a long running query. The first thing we do is we analyze the logs to see what's going on there and we understand. NOTE Confidence: 0.8931725025177 00:44:44.360 --> 00:45:04.370 We What is this sort of the oldest Eddie page that we've got to move back to them. There is a phase? Where we have to win the SQL instance comes back up. We have to go redo all of that. The problem with that is that your database is partially available. While these reduce are running and that's usually one of the phases that takes a lot of time and then once the reader has complete. NOTE Confidence: 0.894233226776123 00:45:05.160 --> 00:45:25.170 Can do all of that right basically to the oldest committed transaction so you can imagine if you're going to write workload that is. That's been writing for 30 minutes. That's a lot of logs that you've got to like get through to undo that whole thing if it crashes in the middle of it with accelerated data accelerated data. NOTE Confidence: 0.853112876415253 00:45:25.960 --> 00:45:45.970 We've introduced the concept of an S log, which is a special log stream essentially and how it differs is we still do the analyze phase because that's really just a metadata thing, but then the real redo runs off of the S log. NOTE Confidence: 0.888160824775696 00:45:46.760 --> 00:46:06.770 It starts from the last checkpoint right your database is fully available during accelerated database recovery and then sort of the last thing is, we instantaneously have the ability to undo the S log up to that point in time. So it's not a case of well. I gotta go to first go redo it and then undo everything we have the ability to just shift the. NOTE Confidence: 0.889014065265656 00:46:07.560 --> 00:46:27.570 To be for the bad transaction number so cutting down and reducing considerably on the amount of time. It takes you to complete a rollback transaction and that kind of interested in. I think over 2 minutes or something. So I'll take questions. If anyone has any kind of ran through a lot of features here, so feel free to ask questions.