5 things you should know about Azure SQL #azure #sql #datahour #datascience

Key Takeaways

hello and welcome everyone to another session in the dot r series we are thrilled to be here with you this evening for a session pull up action-packed learning i'm the rich part of data science team at analytics vidya and i will be the moderator for this session and guys for those who have joined us for the first time let me give a brief introduction to the data sessions the data is a series of webinars conducted by analytics vidya and led by top industry experts it is a fun way to understand the concepts of data science from the leading players in the data tech domain and as the name suggests it's one hour dedicated to data and we are hopeful that these stations are going to be a great source of enrichment and value adding for community members now on to our session today which is five things you should know about azure sql azure sql offers an incredible amount of features that helps modern developers to create cloud-borne solutions that are scalable fast flexible and secure it has evolved a lot in the last years and many of the new features may have gone unnoticed to a developer that is already busy following evolutions of the never-ending amount of frameworks so guys in this data we have mohammed zahid with us she will go over the top features that every developer must know which can be used as they offer a unique way to make you more efficient and productive and i hope you are excited to attend this data are with us so before we kick things off i would like to hand it over to our speaker and a quick recap of the housekeeping items and guys don't worry we are recording this session and we will make the recording available in a few days on our youtube channel and guys please please feel free to use the q a section for asking any questions uh you might have during the session make this webinar more interactive and we will do our best to answer them as the dot or progresses are towards the end and also we will share a poll about the feedback of the session towards the end which i request you to all kindly fill up now on to our speaker in the session of data we have was no zahid because she is a principal pa manager at microsoft azure azure sql database where she enables customers to deploy the most demanding data workloads on azure she has been a data engineer architect and tech speaker working on several big data cloud computing and ai technologies she is a linkedin learning instructor with her course breaking bias in tech and her ml research software is used in ekg machines all around the world and she is an advocate of diversity equity and inclusion now over to you as much ahead the virtual stages are yours thank you thank you everyone uh for joining uh let me start sharing my screen so if the moderator you can give me access to share screen i can get started yeah once again now try yeah i am going to start it now yes okay hi everyone thank you for joining good evening good morning good night depending on wherever you're journey from as i was introduced my name is masama zahid i am a principal pm manager and azure sql database team working at microsoft i've been in the tech space for over 15 years now primarily in the data and ai space and have fun fact i've lived and worked in five different countries and three different continents i'm really excited to share with you all today this tech and this presentation which talks about five things you should know about azure sql database at any point in time if you have any questions in this session please feel free to put them in the q a feature i will spend last five to ten minutes to take those questions and answer as much as i can if i'm not able to get to those questions today i would follow up and you're free to connect with me afterwards as well on my linkedin or twitter as well with that uh let me start sharing why i'm talking to you all about azure sql database this is just a little bit more information about me and one thing that i'm really passionate about is sharing knowledge and the picture on the top left is actually uh the very first time i did public speaking i don't know if you can see the date it's from a very old camera it's 2006 so it's been a while that i've been doing this and sharing a lot of my a lot of my work with different folks uh in terms of why i am talking about this topic and why are your sql database it is one of the oldest database out there a lot of folks as they have started to work on data science today or they have started to look at data solutions you might have not noticed all the different features that are already available there and today in this session the goal of this session is to give you an overview of what you should know at least as a developer to take advantage of azure sql database and also the the capabilities of azure sql internal not just in the cloud but also what are the other things you can do with azure sql i will also give you references of different things that you can go to and learn more there are several samples available that you can go and today try out and build your own applications if you want to and i will also try to kick start some of the conversations around how you can build modern applications in cloud cloud-borne applications using azure sql products that are out there uh there are also some common questions that are here because i've been working with several customers over the years uh i did several developers a lot of folks ask some of these questions to you to me and i'm going to i'm going to try to address some of those questions as well and some of the common myths that folks have about a database system as well in the end i will go over the q a again if there are any questions throughout this session please put them in the q a feature and i would like to get to them towards the last five to ten minutes of this session so i i wanted to start with uh taking back to kind of a history a trip down the history lane in 1989 uh i don't know if you guys can guess what are the different events that happened in 1989 some of you might not have been born by them uh but if you want to share if anybody remembers any major events from 1989 please put them um in the chat i see somebody saying they're not able to scree see the screen is that the case with everyone no it's fine okay i'm going to reshare just one more time just to be sure okay so 1989 uh what are all the things that actually happened uh in that year one of the things uh some of you are cricket fans so sachin tendulkar actually made his debut in death cricket that year and he became uh the youngest man to score test 50 at the age of 16 years and we all have seen his career over the years how it grow how it grew the simpsons was another show that was premiered that year uh so that's another fun thing to know about that year but why i'm talking about 1989 is microsoft sql server actually initial release date for that was april 24th 1989. so it has been 33 plus years that this database has been out there and all the features and all the capabilities that it brings is available for you to actually utilize and over the years you've seen many innovations in the in the product we have seen many innovations as different waves of data architecture have come through and the latest way with with the cloud computing and other platforms as well now i want to focus about azure sql and this i wanted but before going into azure sql i want to actually talk about the sql overall what are all the different products and capabilities that you can use today so if you look at this picture it's quite evident that no matter where you are you can actually deploy as your sql or sql in in your environment it can be on the edge it can be on the cloud so from left to right if you look at this there are plenty of options if you have linux if you want to do on kubernetes you want to do an uh in my cloud platform as a service there are several options for you to actually go and take sql from edge to cloud so it does not limit you uh to deploy it in any of the platforms and it is one of the few databases out there and actually probably the only one it is available in all the major clouds as well so it's not just available in microsoft cloud it's also available in aws and google and other cloud platforms so because of the majority of the product and and the majority of the features that enables you to build the cloud-borne applications it's it is it is used and deployed in many many different ways but i want to focus specifically on the azure sql side of things uh there are several other things that you can you can do and i'm going to take a deep dive into azure sql and tell you a lot of lot more features about azure sql and what you can do with it so in terms of azure sql you have three different options to actually go and deploy um there is the infrastructure as a service option so if you're familiar with cloud computing platform that means that the cloud provider will be responsible for the for the infrastructure and you will be actually deploying your own software on top of that uh platform as a service there are two options to actually go and do uh there is the manage instance option and there is the azure sql database option in terms of the manage instance uh that that that particular offering is for lift and shift so for example if you already have a sql server somewhere lying and you already have certain applications you can actually do a lot of lift and shift on to cloud without changing a single line of code and actually move them into cloud but if you want to let's say do migrate and eventually modernize your applications or build cloud bond applications you can use azure sql database because it has a lot of capabilities like hyper scales elastic pools a lot of other features that i'm going to talk about today in this session a lot of these features are cross-sectional as well so they are available in all the other flavors of sql as well because it's based on the same sql server engine so that's the that's the beauty of azure sql in terms of service tiers how you can actually go today and deploy azure sql database there are three high-level uh ways of you to for you to deploy them you can use a general purpose which is most business workloads actually use that it is a generic one you can actually deploy any type of applications that requires a relational database or require sql or requires any kind of data storage for your transactional systems you can also use business critical which is uh which has a higher sla low latency it has um it has a lot of failure high availability disaster recovery and all of that built into the system and then the cloud-borne applications for the things that you want to scale for future and to want to build today as a startup as uh somebody starting your career maybe your you have an idea that you want to build an app with uh hyperscale is the way to go because it provides you a way to scale down scale up depending on what your workload is and it can over time morph into something that you really want to do i'm going to talk a lot more about several of these features in detail and again if there are any questions please use the q a feature of this webinar and and i would get to that as a developer one of the common things or questions that is asked to me or i come across at different forums is how do i connect to databases so if you go today to any modern database you'll find several different native ways of connecting to that azure sql is is is almost um as i said is one of the the oldest databases as well based on the oldest engine uh it it today has several of those right so you can imagine any of the programming language of frameworks that you want to connect to you we we have a driver for it and again the slide deck and everything would be shared afterwards as well all of these links you will be able to get to if you want to take a screenshot of any of that feel free to do that as well but we have a lot of documentation we have a lot of getting starting guides we have a lot of great material for you to get started into any of these any of these frameworks for languages or programming languages that you want to build your application with and we have a lot of great community around that as well now i want to get started about the topic of today's session so so far i provided some very baseline information about what as your sql is and then i kind of zoomed it into the azure sql cloud pieces and what does it mean and and gave you some introduction about how you can actually deploy what are the different service tiers and what are the different types of drivers that are available so as the session title is to five things talk about five things that your sequel has for developers the first one that a lot of folks don't know a lot of folks have probably a confusion around that as well is azure sql the readers are not blocked by writers so the way the architecture is on the backend everything that is the row versioning is to implement the isolation which is the asset if you look at the eye part of that that's that's the isolation and as a database engine this is what it's one of the one of the great features that is enables is that the read is not blocked by right so you can read the as many kind of parallel reads that you want to from the same database that maybe one application or many applications might be writing to it and i'm going to talk about a little bit in more detail about that in terms of scale what it means for you for applications some examples for customers as well for you to understand how it is beneficial and why it is beneficial second uh thing that i want to share as a developer this is something a lot of folks don't know about azure sequel that a lot of people think about sql database as a relational database and think about typical data types like varchar like integer like all of all of the typical ones but they don't know that azure sql actually is multi-modal you can actually store a graph you can store json support so we have a very strong json support you can actually store json data in it and i'm going to share some examples here with you as well and then you can also store geospatial uh type data in it and it's based on an international standard uh that a lot of other databases are actually using or other platforms such as spark if you if you're using that in your data workstream and it also has geospatial indexes so you can actually index the data which is which is stored as your special data now what does it mean so we're going to talk a little bit about graph models uh if in a typical graph theory if you've come from computer science background or if you've done some interview practices one of the things that a lot of folks actually ask questions in interviews what is a breadth first search or how do you actually get from one data point to another data point uh why use a database to store graph data a lot of customers have very interesting scenarios where they want to understand the connection between data and and to get to one from one point to another point they want to understand what is the the the shortest paths and and that type of use cases are currently being served by specialized databases and if you want to go to a specialized database that means that you have to write an etl job with some something that has to take the data from this database into another place you have to own the compute you have to do this extra work to maintain those systems now in azure sql you can actually store your graph models as edge and node objects if you go to this link you can learn a lot more about what what what does it mean how can you do it and you can actually use things like match you can also use a lot of other kind of functions and and and shortest path type analysis on your data this is just very high level architecture of how graph models actually work in azure sql now why graphing azure sql uh thanks to the azure sql engine graph support uh can can be used along these existing technologies so you don't have to move the data out you don't have to deploy a specialized database to do that you can actually use the engine itself where your data is being stored to to be used for graph use cases and this is an example of an interesting use case where using hyperscale gen 5 skew which is a which is which is type of a compute uh you can actually get to that many 50 billion likes and all of that unique users and songs this is the database you can actually go try it out yourself uh you can see how fast you can get to your data and then how fast you can do analysis uh on this kind of data as well and you can bring your own data try it out and give us your feedback depending on what is your use case and how you will want to use the sql data for graph use cases one of the customers who's been using this and this is an interesting case study it is an energy company based out of europe and what they do is they actually use a sql to store a lot of the data they actually move to nosql at some point and then they realize that trying to do a join on on a nosql database it's almost um it's really really difficult to get the data in time for that so in order for maintenance for any of their energy grids or any of the outages in their energy grids they store data in sql and then use graph to actually get to if one of the let's say grid is down or there is a maintenance happening they can find what's the shortest path and how can they uh how can they get those those users in that area energy from a nearby grid as well so using graph data stored in azure sql they can find the shortest path they can find an alternate path uh to to actually get to that data very fast and this this is one of very interesting use cases that you can read it about uh on the link that's shared on the screen now in terms of json so i talked about uh the first feature which is the read read uh committed snapshots the second feature i talked about multi multi model and then within multi model and i talked about graph and then i'm talking about json at this point so this is again a lot of folks don't know that json is currently heavily used in a lot of integration data points when you want to store data coming in from your application or taking data out downstream to do some interesting things on top of that uh json is is a common data data type that's used across a lot of folks don't know that you can store json data type in in sql databases there's a great support for json already in azure sql it's been there for years um you can actually store json data inside of sql database as it as a column and as a basically as a data type um and and then there's a lot of support around that for example if you store json in in sql you can use functions like json you could find jsonpath exist json object json array a lot of those functions and we are continuously evolving and improving these functions and how they work as well so this is something again you don't need to go to a nosql data stores you don't need to actually go to another data store just for this capability to have kind of unstructured data to be stored in a relational database you can do all of that inside of azure sql using json so so far i talked about two major features one was uh read committed snapshots and then secondly the multi-modal capabilities of azure sql talked about graph talked about json i don't have any slides on geospatial but you can definitely look that up we we have a lot of great support about your special data in azure sql as well the third thing that i want all of you to know about is column store so this is again a lot of folks don't know especially developers are starting new or you've been industry for a long time but focused on a set of features in certain databases have not kept up with what is the latest latest advancement in some of these uh databases as well column store is another way where folks actually have used azure sql database or azure sql in general to store uh data and then kind of retrieve the data for analytical queries and the interesting part of that is column storage is updatable as well so just think about it if you understand the architecture of how how databases work uh this this is this is great uh this is a great feature a lot of our customers actually use it when they have a mixed workload when they want to store transactional data but they want to do some analytical queries on top of that as well and it's it's because of the delta store on the back end it's been available since 2016's it's about about six years or so and one example of that if you are aware about the tpch benchmark uh this is a common benchmark a lot of databases actually actually look at the speed of it or look at the performance of a database uh this is the 10 gigabyte query uh using business critical gen 5 sku in azure sql and if you run that regularly without the column store it will return the data in 55 seconds if you do it with column store any guesses any any number you think that the improvement would be in this case can somebody said ten uh it's four seconds so somebody said five seconds it's really really close so uh that's that's the beauty of column store on top of our relational database on top of a transactional kind of uh system that you can actually use the combination of analytical type of queries on top of a database like azure sql so this is a great feature as well if you're not aware of if you're not tried not use definitely think about that and if you have a use case that has a combination of a combination of transactional as well as analytical data this this is a great feature to do and it's very easy to set up and then actually very easy to use as well so talked about three features so far i'm gonna just repeat so you guys don't forget about that the first one was read committed uh snapshots second one was multimodal and within multimodal we talked about graph json and geospatial capabilities and the third feature i talked about is column store now moving on to the fourth feature and this is again a common question i think today in interview prep and all of those places where folks are learning sql or understanding sql some medium to hard questions actually include these window functions uh if you're not aware of that definitely look them up this is a great way of manipulating data which is stored in rows and columns you can do running totals you can do moving average growth ratios a lot of that i know for data science use cases this is something very very useful to understand a particular set of ranges and how you can do totals than that uh you can do a lot of fancy stuff using vendor functions and and definitely look that up uh you can also define ranking logic uh i think this is one of my favorite interview questions uh that uh that i ask in folks for sql and lag lead and all of these other ones allows you to go to previous and next rows as well and then try different like curricular loops like operation for a sql data store so windowing function is a great way to get to your data to do different type of analysis on your data and a lot of great um kind of uh functionalities available in azure sql for you to try out and it's available it's been available for a while as well so definitely try it out look at store some data try these window functions and give us any feedback if you have for these fifth and the last feature here is that i wanted to share is no more passwords this is a this is another uh thing so a lot of databases and when you have complex applications you don't want to be in business of storing passwords and there are a lot of security issues that can happen over time if you're committing code to github or you have a particular kind of deployment pipeline cicd continuous integration content is a deployment pipeline you don't want to be in business or storing uh passwords and this has been an issue in the database space for a long long time where folks were we have admin and then we have used a separate level and every user has a password password is still the go to way for a lot of people because they're comfortable with it but with microsoft and specifically with azure you can use the azure identity and manage service identity to actually go passwordless what it means is you can actually this is a code snippet it's very simple to set up there is a getting study guide on that as well how you can use and you can actually remove passports and passwords end-to-end from your connection strings and there are several different ways to actually actually use this and then make your applications and the database more secure you have a single identity to get in you actually can maintain that manager this service identity across other uh services in azure as well for example if you're using if you're using storage you can use the same identity to get to that uh you can have different levels of identity as well if you want to and then actually get to that so it's a great feature again a lot of people don't know about it because they are familiar with passwords and they actually go and just go ahead and create the database uh with the set of a lot of passwords as well so definitely check this out if you've already not known about um if you already know about this give us any feedback as well so in the chat out of the five features just put a number out there how many of those features were the ones you already knew about did you know about all five did all five of them were new uh for you just put it in the chat um so we can keep this session more interactive and i can get a feedback directly from you all as well uh that that that that you're available so yeah somebody said all new another person said new to me all new to me four that's great all new awesome then then i think my job here is done i i shared a bunch of things um that that you did not know already about sql database very useful thank you so i actually lied i am not going to talk about just five features i actually have four features to talk about and scenarios and questions from developer perspective because i want to make sure that uh you as a developer building app starting new or maybe in industry for a long while as well and you've used sql databases at different places different types of databases in the past as well uh what i am hearing from the industry you know i am looking at what folks uh have used that for to deploy different type of applications uh thank you for the kind words in the chat somebody said excellent knowledge thanks information a very informative session thank you thank you i want to talk about some of the other features so i'm going to take a quick recap of what i've talked about so far so i provided a little bit history i talked about the the different type of deployment options from edge to cloud how you can actually take sql uh to deploy in almost any platform out there and i also mentioned that you can deploy it on any other cloud as well so you don't have to do a lock-in with a particular vendor if you go this route so that's that's just to to to level set everyone up that you understand that there are options for you to take your current deployment anywhere that you want i talked about different types of drivers and then i went into details about five features so i talked about read committed snapshots i talked about multi-modal and within multi-modal i talked about graph json and geospatial capabilities i talked about third feature which is column store i then i talked about windowing functions and last but not least i talked about going passwordless using azure sql capabilities now a lot of the newer use cases one of the the new buzz in the industry is blockchain a lot of people are thinking about blockchain as a as an area to go into people are working on web3 a lot of that is a bus that a lot of folks are working they're building startups around that so in terms of databases again if you think fundamentally databases are actually the reverse concept of blockchain right it's to store data single place versus blockchain is is pretty distributed in that in that perspective but how you can actually use a database to to know a particular record and have an identity or a proof related to that so for that capabilities we introduce ledger so ledger is a feature uh which is tempered proof tables that means that you can actually uh whatever is it goes in those tables it's actually hashed and there is a cryptographical kind of history around that as well it's verifiable as well so you can use data stored and sql as as a blockchain application as well and uh again go to this link and you will find a lot more information about how customers are actually deploying that uh these tables in ledger these ledger tables are updatable and depend uh or they could be abandoned only depending on what is your use case and but every time any update is happening or an append is happening there is a record of who's doing it there's a lot of auditing there's a lot of evidence so you can actually do a proper audit of your tables so why we are thinking about this so i'll tell you one use case of a customer where a customer was was uh had an app that app was actually used in nursing homes so people in old age go to nursing homes in certain countries in certain places um and then there in that um every time um the nurses over there actually administer an um medicine to the to the elderly in in that nursing home they actually put a record of what they administered so you can imagine that that record is really really important because the life and death of that person really depends on that data because if i accidentally update any any record or i can i can harm the person who's coming after me as a nurse uh taking the shift from me and and they can re-administer the the medicine for some reason and that person can have a lot of severe issues because of that so this is an actual customer without taking the name of the customer um i'm just sharing a use case how they are using ledger tables to actually keep a record and audit and then have temple proof tables to make sure every time the app the nurse goes in the end when they administer the medicine they actually go and update that that application that application stores the data and the packet in the azure sql database and then in in application they're actually uh as a ledger table so you can actually go back and find out exactly who administered that last medicine as well uh there's a history there's a record there is a lot of verification that you can do on top of it so this is a great feature it's a new feature a lot of folks don't know about it um this went into a ga general available recently as well uh and if you have a use case like blockchain you have a use case where you want to have a verifiable record of data and you want to see this history this this is a great way to do um to do that and you can do a combination so you can have a set of tables which are ledger tables and you can have other regular tables as well so this is again you don't have to take a data out to a specialized database just to do this kind of foreclosure this kind of use case uh somebody asked in the chat uh can you please share the powerpoint yes i would share it with the organizers and you will be able to get that afterwards as well uh the seventh thing i want to talk about so we talked about uh five five features and then i talked about the ledger uh the retry logic so as a system design person again i know a lot of folks prepare the system design interview questions or think about system design when they're designing their architecture one of the things or a nightmare for anyone who's on call is uh is when they get that something feared in their systems and they get alarmed at 2am or 1am or 5 am that the system is failing and when the system is failing if you go and kind of do a root case root cause analysis most of the time somebody some developer somebody has forgotten to put in retry logic because of whatever reason your database is down or your firewall has some issues or your network has some issues uh in the application itself if you have not put a retrial logic your your overall application will fail the data will have no place to go if you're not put any any uh kind of backup mechanism there and then if you keep it running for a while the end-to-end application will start failing and a lot of people will be unhappy so um so this is a common common um problem so as a microsoft sequel data client uh library itself so as part of that library itself we have enabled this retranquility logic um you can go into this and learn more about that um it's configurable it is kind of the bottom line for you to actually make sure that you do that as part of your application and part of the design uh this is a very important concept if you have never done this i've never tried it never thought about it at some point your application is going to fail if you don't have it in place either on the application side or on the uh on the in the code layer or of of your basically architecture so this is this is a great feature by default that you can use there is a little bit of configuration you have to do uh definitely go try it out and again if there are any feedback any questions uh happy to happy to answer those as well now last uh question last feature that i want to talk about is um now let's say you have built a great app you have actually uh used a bunch of these features now your whatever you wanted to you you're able to achieve that how you can actually deploy that as a devops engineer or somebody who's who's who owns the infrastructure um deploying database objects outside of your devops cycle has been a norm in the industry for a long time so there's usually a database administrator who actually goes and deploy your tables and indexes and all of that and then and the developers are owning the application code itself but if you look at the industry trends this this this is actually merging into one um one devops cycle where the developers now own piece of the code which actually does the ci cd pipelines it also includes databases we really want to emphasize that your database should should stay with you in every step of the cycle so in order to do that we have actually provided some features then one of them being microsoft we have we have uh some of these integrations with github actions so if you're not use github actions is a great way to actually go and deploy different things very fast one click you can actually do that for database projects now and uh definitely take a look at uh azure data studio sql projects you and there are a lot of great videos one of my colleagues drew has been the person who owns um a lot of this back end and how these features are are enabled there is a lot of great talks by him also so i can share some of that later in the chat or as part of the material uh if you are in the devops space or if you are a developer who owns this type of cycles definitely check that out so the concept of deploying this with github action uh this this emulator that we have in as a visual studio code extension is you don't need to connect to an actual database unless you need to commit anything so you can do all of your development within your favorite ide in this case visual studio and within visual studio you can actually create the database objects you can actually try out different things you can also do ddls again there's no real database yet you can do all of that in your inner loop while you are on your machine or on your kind of server or vpn or whatever you work on when you're ready only to commit the code is when you will need something to to actually go and deploy so basically it saves cost as well um this actually improves the developer productivity a lot and again this topic itself here we have a lot of talks just on this topic so i'm not going to go into a lot of detail here but if you have questions around that feel free to feel free to uh ask those in the chat and i will get towards them in the last five ten minutes as well uh and then finally as i said when you are able to when you want to deploy that you can actually do that as the last step as your outer loop and and part of your github actions this is very simple and straightforward as well so definitely try this out if you if you have not already and if you have any use cases where you want to do uh end-to-end kind of deployment or devops type work um for your database or applications so i'm going to summarize the the five things um that you should know about azure sql i talked about lead committed snapshots i talked about the multi model which includes json uh craft your special talked about column store uh talked about windowing functions no more passwords and then i talked about some extra things which was not really fine talked about eight things uh that includes ledger retry logic and ci cd using github actions so i'll take a pause here uh before going into some of the common questions uh that i get from developers i'm going to one questions that i can see in the chat right here that is about how will we know about transient errors and queries so this is going to be part of your um the application logic itself in terms of the translate errors um if you can clarify and chat what you mean um if that's mostly because of uh any any kind of retry logic or kind of anything that that's not happening on the down down stream where you want to store again having free trial logic is going to to save you from that in terms of uh the the slas azure sql has really great sls that we support uh that make sure that everything that goes into basically we there's no data loss for that right so we do support uh these slas and we maintain that uh slas as a as a number for for our service as well so you want to make sure that uh you don't have data loss and then in order to make sure that your application is also foolproof you need to have this kind of retry logic in in the in the application layer to make sure the data gets to your database um as soon as it you want it to be okay um i will get to the q a part of it towards the end uh as well and uh just talk about some of the questions that common questions that i have seen over the years uh coming from different developers so so this is a fun one um this is what uh i think at some point in my career also i have evaluated different databases or different infrastructures or different options as data architecture as a data architect to to see which one is the best database to store certain type of data or certain um certain number of rules so the volume of it so caution people ask i have n number of rows i have like millions of rows or maybe trillions of pros and billions of drones i don't know is that your sql database a good choice for that so i have an example here for you without taking the database name without taking the uh showing you the actual actual that but this is the actual this is an actual query ran by a customer this is on their system uh this is actual user so i'm not making this number up this is a screenshot taken from their their query it's not a simple query as well it's a security with a where clause it's a query with uh with with with an in statement there as well you can see this query actually ran for um for about the overall execution time is about 27 milliseconds and again it's not a simple query it has um a date between thing and it also also has an end the results that actually return this many rows so in the chat uh could you share could you tell what just quickly tell me what is this like tell me in numbers like is it million billion anyone has a guess how many rows are in the statement or in this result set once again okay 16 to two trillion okay right so it's 1.6 trillion rows and uh some of you are very very close so it's 1.6 trillion rows i don't want to do the math of how much of that data is in this table that the in class is returning this one but this is the amount of data where one customer is actually using it and they're using very efficiently they're storing this in in in this database um so i want to kind of burst this myth that for a big volume kind of uh application uh you can't do this so customers have been doing this again it comes down to how you architect it what are the indexes what are the different types of applications how do you actually scale out all of those different concepts but it's possible and this is an example that i wanted to share so to me um the question really should be what i'm doing as a use case and can this database store that use case uh for not just today uh but also in future can it skill due by future uses as well it's not about a number of rows anymore um because almost um you can see this number which is quite quite eye opening that that you can do this uh very effectively in in sql database now another question a lot of people ask a lot of developers have asked so i want to scale without data movement and this is again a thing of the past where you have to actually move data out to actually provide scale for that so like a replica of something that you will have somewhere else for read use cases and then write use cases or you will have something like um today your application is let's say uh selling something and then there is a major event like black friday that happens in the us for example when you want to scale your application to to to to kind of get to that workload on that black friday but you don't want to pay it throughout the year so how can you achieve that during the time only right so one of the things that we introduced in this azure sql uh is is is having um hyperscale so hyperscale is the the whole uh i showed you in one of the early slides there is the whole service tier that you can actually go and deploy uh you can all you using that tier you can deploy read-only uh replicas uh up to 30 so you can and they are the exact copy of your database as soon as the data lands in your in your master or in your primary your replica has the data available as as i mentioned one of the capabilities that we have you get read committed snapshots so you are able to get that data very fast um and then you can scale as much as as much as you can afford basically right so in in that case um what what happens is that this is this an architecture on the back end or just for you to understand let's say your four week course or four week course uh primary um and then you can actually have um that many replicas and and the beauty about these replicas is that they can be of different configurations they can be 2b cores they can be 16 week course so you can have let's say a particular set of let's say you have a data science department who wants to use this data as soon as it lands and there are a lot more kind of read concurrent uh queries that are coming in from there so for that particular one you can have a 16-bit core one and for other ones you can have to be code or something uh people use this also for maintenance they they they do this for a lot of other things all of these replicas actually read the data from the same page servers which is which is storage so the way we re-architected this in cloud is we separated the compute and storage and that's that's how you you can actually get to that scale so this is a common question can i scale out yes you can scale out you can use red replicas you can use hyperscale to actually do that uh one of the other questions so either you're trying to save a cost or you have a workload where it is a lot of different types of um ups and downs in the in the time when you use the database can you pause and resume a database because you don't want to pay for for for the time when there's no activity for example so for that yes again in the cloud we have an option called serverless where you can pay for use uh when you are basically you pay as based on your cpu cycles you can set it up very easily as part of your application code as well or as part of the database uh depending on how you want to kind of uh maintain this uh it's it's very cost effective and if you have an intermittent workload where you have high kind of active and inductive periods you can actually go ahead and do that so this is uh this is an example of let's say you have no activity between midnight to like 4 a.m you can pass the database and then you can resume it and then start from that point onwards when the database is paused and you want to let you know that you will be charged for the storage still because the data is still on storage storage is really really cheap as compared to compute so um that's how this this works so definitely that's possible a lot of customers we see actually use this they have different types of workloads where they can pause and resume um their database so in summary uh this is some of the common questions around scale that i get from developers either the number of rows either like talking about data movement if you needed a movement to move from one database as a primary to a read replica or something like that uh is that possible or not you can change the scale of your database without any migration of data it's it's a function on the portal itself it's a function on your uh on your uh cli you can do it very easily you don't need to go actually to do that um and move the data out for another set of set of users in your company or or another folks that want to use that data you can actually go serverless for auto scale with auto pause and resume you can actually look at qd bottlenecks that have that that are causing you some problems uh multiple people try to have similar queries on that this is a common problem uh i know we all have done select stars on huge tables and we have all been walked down with um from from dbas and other folks who are responsible for maintaining our databases uh we've got those emails also in the past uh and then uh the way to go is you can you separate the read only where your right is not bothered or not affected by the read load and then if you have kind of sharding issues or things like that that you want to do a very very interesting use cases i've seen customers have actually gone and do using machine learning to decide which visually replica to go to uh as well so there are a lot of interesting ways to actually use this there's like limitless options uh with hyperscale um and then especially with named replicas you can name your replica and then use that replica for that particular purpose only um so so you can imagine there are many many use cases let's say you have an application there is a downstream vendor who wants to get that data uh you can name a particular replica just for that vendor so a lot of people actually maintain the databases in this way where there's a clear separation of who is using what and then sometimes they can charge folks for that as well uh for for that particular case so definitely check this link out let you know a little bit more about scale i will leave you with this um this is something uh as i lead the developer division in this team a lot of folks uh talk about developers uh traditionally a lot of people think that dbas are the only ones who need to know about databases that's not the case anymore um the industry trends are changing application code uh folks people who are developing applications as a full stack developer are trying to getting more understanding of databases as well um and either sql is is for every developer so if you're developing code no matter what language what framework what uh we have drivers for almost everything we have a great community that supports that and then you can use a lot of these built-in services a lot of great capabilities like json and geospatial and few others to actually do that and then um it can enable database across multiple platforms and don't just take it by by my word um definitely uh there are a lot of customer uh customers that that that have used this the beginning of the already talked about their graph use case other bunch of others at microsoft internally as well microsoft dynamics 365 actually uses sql on the back end for for for the product um so if you know about microsoft dynamics uh on the backend it's actually using sql and a bunch of other ones as well there there are a lot of customers there are at this point 10 million plus active databases that that we have for this service and it's the number is growing by the day so as i said being in industry long time having a core engine that that works and that can do a lot of great things there are a lot of great innovations that are happening on top of that for the cloud side of things and overall taking it from edge to cloud going where the developers are and making sure that we get to those um this is talk was inspired by one of my colleagues david mori if you don't know him definitely go follow him he has a great blog about uh this as well uh you can you can definitely read up on that i really like the way he sketches so that's that's the uh that's the one uh which we say azure sql battery is included which means that it comes loaded with a lot of things that you can do today i will get to the resources slide here so you can take a snapshot of it and i think i have a few more minutes to take some questions so let me go to the q a and get to that uh sorry to interrupt uh before we proceed to answer your questions uh i would like to request the attendees to please fill in the poll about feedback as it helps us to conduct more sessions yeah now you can continue thank you thank you yeah feedback is really important uh please provide any feedback uh for this session so i'm going to do q a right now to just just kind of see um the first question i see here is can you demonstrate cloud native apps using microservices to um to do that yes so if you go uh to the documentation page and just search microservices that there is a great um great demo and then some examples over there in the slide that i'm showing right now the second link on the top this one the azure stack app uh full stack app it's a server-less app you can actually this is a learn module it's free to do you can go today and deploy that app end-to-end uh with azure sql uh you get like four hours of uh time on azure's uh microsoft learn uh so definitely go try it out this is a great great great learn module learn basic learning path where you can deploy a full stack app using azure sql it also talks about serverless components and also has microservices components like uh some of that including azure functions and some other components so definitely definitely try this this one out we actually use this as a as a workshop at different places a lot of developers actually love that um so so yeah there is there is a there is a lot of ways to do that uh and maybe in one of the future talks we can go into more detail about uh microservices applications and how how you can do that as a full stack developer but we have a lot of samples on that uh yeah so the question is i'm new to data science is a good experience at sql security is azure sql different from sql management studio what is different is what name okay yeah uh for data science use cases uh in other use cases no it's not as i said azure sql is is using the core sql server engine so it's it's built on the same engine uh it has the same capabilities um that the sql server provides so it's not separate uh the the difference in azure sql is is the cloud component so the all the stuff that about talked about scale so if you had a sql server or sql on-prem or anything like that you had to actually buy a physical servers to actually um to scale that right in terms of azure sql you can go today create an account uh i have the link on the on here for for you to try it for free uh where you if your student you there is azure for students as well that you can go try down um you can try out the scale and then with a few clicker buttons you can actually scale your applications to anything so the azure sql kind of part of it is the cloud component uh is the microsoft offering in azure um how you can actually deploy it and it's a platform as a service where we own uh the patching beyond the maintenance neon all of that you don't need to worry about any of those you just need to bring your code and and and use the database for that so that's the main difference between them um and then if you go to the video section say this one the more videos from our team and there's a there's a i think a lot of great videos on the difference on different different things that i show in the first few slides um okay next question how does microsoft uh synchronize your server hyper scalability storage that deployment infringed with the implementation of data storage i don't uh understand the question fully if if you're talking about the storage how hyperscale does so basically as i showed in one of the slides it's it's basic it's a it's segregation of computer and storage and you can actually in theory um have as many replicas for your data for your compute site and you can actually use this this storage on the back and on the back end the way we do is we have storage um components and we also have many many copies of that as well so that's that's the uh that's the feature uh set on that um okay so the last question and if i'm not able to get to your question uh please uh please follow up connect with me and i'll be happy to get into more details i'll take the last question here which is what is the processing time on ledger feature yes uh so there is a little bit of overhead uh we have a published uh sla some some of the numbers already it's it's not super bad or anything like that it's a it's a few additional milliseconds or microseconds some cases uh and then you can definitely look that up i'll find that link and probably put that as the material that i send out for you to read on if you go to the ledger overview page in microsoft documentation you should be able to get to that as well so think those are all the questions that i see in the in the q a feature i think i went through everything in the chat as well uh thank you again for being here uh depending on the day night or evening whatever it is for you um i hope you learned something new here then you're able to actually go and deploy certain apps uh this is my link linkedin and twitter you're free to connect with me afterwards as well ask any questions that i'm not able to get to and um and looking forward to see what you go and develop with azure sql thank you so much thank you for watching thanks a lot mahatma zahid on behalf of analytics vidya i would like to thank you for your time and for delivering such a wonderful session i'm sure our audience found it uh insightful and hopefully we can conduct more such sessions with you in the future and guys if you have any more queries please connect with mazma zahid in inkling in linkedin and you can find our profile link in the chat box and uh and all other social media platforms too i hope you have you guys have filled in the feedback poll if not i request you to please fill in the poll about feedback as it helps us to conduct more suggestions and if you have any queries please feel free to connect with us you can find all the emails and links in the chat box and the recording of the session will be available in a few days on our youtube channel and that's it guys thank you for coming here we will be back with another session of data tomorrow the link is in the chat section till then bye bye and keep learning thank you bye yeah