Azure Data Studio Power Hour | Data Exposed Live

[Music] hi i'm anna hoffman and welcome to this episode of data exposed live i am super excited for you all to be joining me here today we have a very exciting show planned and azure data studio power hour and i wore my shirt because i am super excited about having the azure data studio team on the show today to show you a lot of demos give you some tips and tricks and answer your questions so without further ado i'm going to start introducing the many team members that we have uh joining us here today so the first person i'm going to bring up is alan allen thanks so much for joining us today hi anna it's great to be on the show um yeah my name is alan yu i'm a pm working on azure data studio and i've really been working on uh the notebooks experience across azure data studio for some time so yep excited to be on the show cool yeah we're excited to to have you and for folks who haven't seen the notebook capabilities you're going to get to see those and learn more about them as we get into it all right the next person i'm going to bring up is uh drew drew thanks so much for joining us can you kick it off by telling us a little bit about what you do yeah yeah i'm another pm that works on the azure data studio team as well as on other sql tools so i get to focus on how developers might be working with databases and then the tools that they get to use to do that awesome great all right the next person i'm going to bring up is julie julie thanks so much for joining us can you tell us a little bit about what you do for azure data studio hi anna thanks for having me today so i've been working with alan drew and others uh in the team in nigeria studio team to bring a lot of the notebook experiences that i'm going to show today as well especially some with some kql flavor kql that's something we'll have to talk about because some people might not know about that either lots of learning to happen here um okay so next i'm going to bring up udisha adisha again thanks so much for joining us can you tell us a little bit about what you do on the azure data studio team uh thanks anna for having me i am an engineering manager on azure data studio team uh me and my team mostly focuses on the database developer scenarios both in azure data studio in zak fx and in other tools like ssct and so on awesome great great to have you and last but certainly not least uh we have vasu joining us um havey sue can you tell us a little bit about what you do yeah so i'm a software engineer on the notebooks team and so i helped develop many of the different experiences we have on within notebooks alongside the team i'm looking forward to showing them to you guys awesome we are looking forward to having you show them to us um okay so as you all can see we have a packed agenda because we're going to be showing lots of demos from all the wonderful folks on the call today we're also going to be standing by to answer any questions that you have so feel free whether you're streaming from twitch twitter youtube learn tv wherever the microsoft reactor uh please put your questions in the chat because we can see them or your comments if you think something's really cool you want to ooh and ah like please feel free to do that i see we've gotten some people uh saying hello um so hello thank you for joining us we're happy to have you with us um okay so as we get into it before we get started i thought it might be useful to have kind of an overview of what is azure data studio um so for that i'm going to pass to alan to give us kind of a brief introduction of what this tool is for great that's a great place to start so to start off azure data studio is this cross-platform tool so you can imagine that it works on windows mac os and on linux to allow you to manage databases across sql server azure sql postgres azure arc and other services as well you know we primarily focus on the experiences to connect query and manage these different databases while also focusing on investments such as notebooks and developer experiences with things like schema compare and database projects and really it's i think it'd be much better that instead of just kind of uh giving this really high level view of these things for all of you to be able to see all these experiences and that's why we have so many folks on the call from the team who have been working on these experiences and then we'll be able to show these um high fidelity demos for all of you to see these features in action and kind of see what we've been investing in recently and what we will be investing um in the future as well awesome thanks so much alan hey one more question that i get a lot and i just wanted to like kind of clear it out before we get started azure data studio is it just for azure stuff oh yeah so this is probably the favorite question that we get maybe every time we present azure data studio but just to be clear uh yes we do support uh i guess no we we do support uh both on-prem and also azure uh so you know just because it has azure in the name does not mean uh that it only works for azure services so we do support both on-prem for sql server postgres and more to come awesome that's great to hear and hopefully kind of we can just keep clearing that message up for everyone using azure data studio um okay so now without further ado we're going to get right into some demos i know that's why you guys join the stream so let's get right into it i'm going to uh be bringing up drew to talk to us about source control in azure data studio so drew tell us all about it yeah so we're going to kind of dive right into the azure data studio interface onto the the side panels and one of the foundational things that no matter what your role is and how you're interacting with databases you might want to be able to leverage source control to better share and manage changes throughout different scripts that you're using whether it's for troubleshooting or operational or for development needs so i'm on the database connections pane here there's a file folder pane where we'll be able to work with different files and folders and notebooks but then there's also the source control icon down here at the bottom and one of the things that azure data studio brings to us is a kind of a more graphical easy way to get started with source control so if you're new to azure data studio or you're new to source control or both um we can say hey my team or maybe i want to use one of the microsoft provided toolkits to work with my databases we can do that right through source control so if i click this clone repository link it's going to ask me for a repository url if i pop over to the tiger toolbox i can grab that link and paste it in and azure data studio is going to ask me where i want to put it i'm going to put it in the pile of source control i have on my laptop and it's going to start cloning that repository or bringing copies of it down to my computer that's that's that's nice that's convenient but the real power of source control comes from being able to manage changes and then share those with other people so that's what we're going to do we're going to take a look at a script that works with the database statistics run it see if we need to make any changes see if there have been changes made to it so we're going to open up that cloned repository and then be able to work from source control there awesome so azure data studio is reloading and now if i go to that folder pane i've got all these files like i didn't have to drag drop copy none of that so let's go to the statistics folder view stats last update i can connect this script to my production server because i like living on the edge no i can't let's wake it up there we go so now i've got this statistics script and it's going to give me an idea of when when the the stats were last refreshed on the um on the database now as i look at these columns i realize that the stats last refresh date is all the way over here there's some stuff over on the left like the database id and the database name that i don't really need when i'm running this against a specific database now i can start looking at the script and say hmm do i want to make changes well before i even start making changes myself in the file pane down at the bottom there's a timeline option and what it's going to do is it's going to pull up the source control history for this file wondering who's made changes last what were they and so once this once this load that's got to go way back in time the last time someone updated this file was about four years ago it'll let me know who it was and what message they left for us what if i want to make a change and leave a message for someone else so let me go ahead and remove the database id by commenting it out and the database name so when i run the script now i've got fewer columns between me and that stats date no database id no database name i like that a little bit better so i'll go ahead and save it oh and the source control pane got real busy for a second and it said wait a minute there's been a change you can see the changes that have been made and so i'm going to leave a little message removing database name column i will add that change and commit it lots of words thrown around that aren't exactly common english terms but functionally what we've done is we've said as a part of this file's history now when i open up the file pane and look at that timeline my change is now recorded if i wanted to share that out to my team i can further leverage source control to push and pull by pushing out and pulling in changes but all of that is kind of really graphically embedded in azure data studio there's all these indicators like flying at you but if you focus on the source control pane as well as the timeline on a file you're all set to start using source control in azure data studio awesome this is really cool i actually had no idea about the timeline capability so i'm gonna get to start using that uh we've got some comments coming in from twitch uh this is a great power hour for sure i agree um so you know thanks so much drew for showing us this i think it's a lot easier for sure than using um like get bash and like trying to figure out all the commands and then forgetting the commands like i always do so um this is awesome so yes awesome thanks so much okay so kind of continuing along with this source control idea or things that developers do is this idea of sql database projects which is something you used to be able to do i think you can still do with something like ssdt and visual studio but to learn more about how this relates to azure data studio i'm going to bring up udisha thanks for joining us you know i don't know a lot about this space so looking forward to hearing from you on sql database projects oh and you're on mute it's okay it happens to all of us thank you so uh yeah when we are talking about developers and then we are talking about incrementally or collaboratively developing anything uh that anything is also a database schema so when it comes to developing database schema in a collaborative fashion or in an incremental way database projects become a very important part of it so as you said database projects have been there in visual studio and what we have recently done is we have brought in the capability into azure data studio so that we are our clock cross plat developers can use the same we have also maintained the compatibility with ssdt projects so if you already had a project in ssdt which you started years ago you can use the same projects and people on your team who are using a mic windows linux they can all collaborate on the project together so for example i have here ssdt open in visual studio and i have a project that i created here and all i did in azure data studio is i opened the same exact same project and as you can see the project continues to work load build everything both in ssdt so if i want to build it here i can it will work and if i want to then go back and continue using it in azure data studio i can still do it so now that i have a database project what do i do with it uh so like any other uh project any other safe.net project you can start adding things adding items to this project in a very declarative way so you can always say that i want to say add a new table to this project i'm just going to say this is my table it gives me a template i can change it or i can just keep it the way it is i can also go and change some of the existing tables so for example i can just say my column i can say this column as an end sorry and i can say that it's not sure i made these changes uh or a teammate of mine made these changes now we want to make sure that i can deploy these changes to my production database or a test database that i have but when i have a database already existing this database will have the tables some views things which were created before but all my project as you can see is in very declarative format it says create table it doesn't say alter this table to add this column so what the tool gives you is as a engineer as a database developer you do not need to worry about what changes need to be applied you only target what your final database should look like and the tool takes care of incrementally figuring out what are the changes and then deploying only the changes so let's take a quick look if i just go here i can say build publish any of these things i am going to choose publish because i want to publish my database and i choose uh the same database which i was showing so you choose adventure works and you can either generate script or publish here just to show what changes it will add to the database i'm going to go with generate script so it will build the project into a dac pack which is uh which is a schema file for the database and then it will try to find the differences between the dac path and the uh the actual target database and create a script based on only differences so once it does the so it does the same that's pretty cool though it's kind of doing the hard work for us and making sure we don't enter like a broken state exactly exactly and you do not have to worry about or constantly remembering which stage your say your test database your pre-plot database or your production database is in you just care about what the ultimate target state you want it to be in right no that's that's really useful so typically this just takes a few seconds and then it'll go ahead and apply the script for us correct so we can do both it lets you see visualize the script it also you can also skip the part of visualizing the script and just go and directly deploy it awesome it's taking more time today of course it is it always happens you know when you do demos you never know something that takes a few seconds takes a few seconds longer you know while we're waiting um we did get a quick question and i'm not sure if this directly relates but maybe you can take a stab at it while we're here uh the question is what data modeling capabilities does azure data studio have for uh for the database development yes so uh you can actually for for any database you can try to create any schema that you if i'm understanding the question right uh you can try to create the schema based on the various different type of objects available or you can create your own objects create add your own scripts and create the schema based on that it is uh then once you create the schema it will take care of making sure as part of the build that the schema is actually in the right state it matches it runs rules to make sure that it matches the target version that you have chosen so for example if you say that i'm going to target sql azure or if you say you're going to target a 2016 or a 2019 server it will then take care of making sure that the model of your of your project is actually valid for that particular version of sql so those are the capabilities that it gives you even before you are going and deploying the database awesome cool thanks for helping with that question okay let's let's take a look is it so here yeah so the script as you can see that it did not so it had a whole lot of objects in the database in the database project but it didn't care about uh creating all of them because those were already created it just went and explicitly changed one column and created the one extra table that it wanted to create i can publish it directly or i can just run the script and it will do the publishing for me so once it's done i can go back and see that whatever i wanted it to create i'm just going to refresh it and it has already created those things awesome and this small change that you had made in this instance what was that small change oh started a column my column i just added a new table and changed a column in a table so it just remembered that it's supposed to make only those two changes and nothing else i'm trying to see why is it taking a while but if you believe me it it will create those two ah i can try to see if i can well it's really going to test me it's um it's nervous to be in front of so many eyes i guess so awesome cool uh well this has been really useful and i think we we see uh unfortunately you know it's a little slow right at this second but it's cool to see how it generates that script i think that's the biggest piece that i got from this is like it's really taking a lot of the hard manual work out of the things that you're typically doing or having to do and just kind of generating the script for you and then of course you can apply that script as you want or with the publish exactly exactly absolutely awesome great oh and it looks like it it did come back and the new table is there uh yes that's what i'm trying for it to oh come on that's all right i'm oh let's let's let's keep going and we can check back yeah to make sure that table was created correctly awesome thanks so much for showing us sql database projects in azure data studio all right rolling right along um we are going to bring back up drew who is going to show us another demo uh this time this demo is just some key tips so i'm going to learn a few things here i think yeah this is going to be cool um so azure data studio works for a number of different databases and platforms even but the the tips that i'm going to focus on here in a minute are going to be more focused on if you're using azure studio with azure sql what are some interesting ways that it connects however there was a great question that came in that udc talked a little bit about with data modeling and building schemas with azure data studio another component of agitator studio is the extensibility and so community members can build extensions that provide extra or specialized functionality one of those examples is a schema visualization extension that then takes your existing schema and provides a diagram of it oh cool um so this this might have been of interest to the person that asked that question awesome but uh moving right along into using azure data studio with azure and just kind of some things to know um the first one is sometimes you're in the portal you've created a sql server and you're ready to use it in azure data studio one thing to keep in mind is that for a database these connection strings they're useful but they're in kind of this format where it's like all right i've got to copy out my data my server name and then the database but if you copy the whole connection string to the clipboard and then we go back into azure data studio and just watch what happens i'm going to open up a new connection let me actually copy the connection string copy that connection string and now it has pasted the connection string into the dialog for me one more time just to be sure so i've copied the connection string out of the portal into my control c and now azure data studio new connection and so it's parsed the connection string out of my clipboard here the password's not right because it's that like placeholder string from the portal so i'm gonna type in my super secure password there um and now so now i'm gonna be able to connect wait that's amazing i've never i didn't know that cool so yes so when i connect um this is an azure sql server list database so it's saving me money by falling asleep um right before my demo starts um so when i hit connect it's going to say wait a minute i'm sleeping i need to wake back up so it's going to take a moment to connect here but what has happened is we were able to buy in the portal or in an application settings file where you have a connection string if you put it in your clipboard if you've copied it if you've copied and put it in your clipboard when you hit that new connection button it parses it out for you just make sure that the password if it was a placeholder has been replaced but then you're able to connect directly to that database there are a couple other ways to connect to databases and work with databases that are in azure from azure data studio so we see it up here now and i've connected to the server as an admin user that might not be the case in all of your environments but it could be the case especially in like a development environment where you're needing to add and remove test databases frequently when you diesha talked about database development she talked about backpack files which is just a schema there's another kind of database portability file called a backpack file that includes not just to the schema but also potentially data something that we're able to do through azure data studio is let's say i've got this server and i've got testdb1 on it but i need another database i need to restore a copy of some of my work if i right click on the database in object explorer i can use the data tier application wizard and of these operations i can create a database from a backpack file so since i have admin access on this server i'm able to import a backpack it's going to ask me to find backpack on my computer and i have adventure works to one surprise and it's going to create a new database for me so i'm going to go ahead and hit next and let it start importing this is going to take just just a few minutes for it to import and create this new database for us that'll be nice and then we can get on with our development but let's say you know i've got test db1 i've got test db2 um i could have other databases on there but instead of going back into the portal and using that beautiful graphical interface i'm going to go down here below the servers in the connection pane there's an azure area in the bottom left and under my visual studio enterprise subscription i can break out and look at my different sql databases so that's nice again another another graphical view but there are some people who are just really good with command lines um and so there's a number of different ways that azure data studio makes command lines more convenient i mean you've got the the terminal right here that's fantastic but if you hover over the top level subscription in that azure view there's a little terminal button and what it does is it asks you which directory do you want to go into do you want bash or powershell this is the azure cloud shell so i'm going to open that up wow so i can access the azure cloud shell that means i get all the like tools that are included in the azure cloud shell without installing them wow if you spell then yes wow the good news is i don't know how to get it to give you the output the version but what i do want to do is i want to take a look at some of the the databases that i have available and what their sizing is because in reality if i need to do some development maybe i want to scale up my database real quick and make it a little bit faster so for the learn live resource group on my let's see my learn live test server i can list the databases that are available there there's a couple of them so let me scroll to the top real quick this first one is going to be the master database and this bottom one is test db1 the min capacity is half a cpu but the current capacity is one cpu i'm going to be doing some of the sql projects work that ud show is showing so what i want to do is not paste in a connection string a lot of people are really interested by the azure cloud show by the way awesome so i'm going to use the azure cloud shell real quick to use the azure cli to scale up that database so i'm going to update in the learnlive resource group on the learn live test server the test db one to capacity four so i'm gonna go from one cpu to four cpus let that start running also takes about a minute but since we are close on time i'm gonna go back and check on that backpack hey that succeeded so i can use this graphical interface over here again and refresh do it oh there's my new adventure works nice so i can check that out it's gonna offer me the ability to connect to it i will skip on that for the time then back in my cloud shell it's finished scaling that up to capacity 4 so i'm going to have terrible redraw issues cool so now you're going to check if that feeling was successful so i better see a database with oh capacity four so now it's fired up ready to do the work for me and since it's serverless it'll turn off within where's the pausing time there will be a property that says that it'll scale down in 60 minutes auto pause delay 60 minutes so even though i've fired it up real far it'll still turn itself off when i'm no longer used those are the tips that i have for uh checking out azure sql databases in azure data studio there's a ton of different tools kind of across all of the different uh offerings between sql server and azure sql database but there's some pretty nifty like almost easter eggs around azure database yeah i mean i learned a few things in there myself that i'm gonna start doing um and it's great because earlier in the session before you did your tips uh someone craig asked you have tips for using azure data studio for admin which is exactly what you showed us so thanks so much drew been great to have you and and your your different demos um now we're going to shift gears so some of you have maybe been hearing about notebooks maybe some of you have heard about notebooks um we're gonna do some more advanced things related to notebooks but first i wanted to bring up the sue to just tell us a little bit more about what notebooks are absolutely yeah so notebooks in general um was the console that was brought by jupiter notebook and so they're open source web application that allows us to create and share documents that contain this live code visualizations and narrative text experience all in one environment and so within azure day studio as well we support a number of kernels and uh are allowed uh to attach to different servers with those kernels and so i can definitely show you more um and exactly azure data studio itself and it it clearly shows the different amount of kernels that we have listed here from sql only to powershell as well as attached to a drop down that clearly dictates you know what you want to attach to for with sql server instance to postgres instance to many more different instances it's really simple to use and uh easy to get started just by adding code cells to text cells uh numerous different um toolbars experiences as well so it's all in this one experience that really allows us to do a lot uh within notebooks awesome cool i i'm a huge notebooks fans coming from like a data science background um you know using python notebooks was always a big deal and the fact that you can run sql and powershell notebooks is just huge um so awesome i love what you all are doing um with notebooks and today i think you were going to show us something relatively new around parameterization yeah so yeah parameterization it's a bit of a mouthful but we basically have three different ways to parameterize notebooks in azure data studio and so parameterization is actually the ability to run a notebook the same notebook with different set of parameters um each of these different ways easily allows users of any background to quickly and be quickly parameterize and execute these notebooks so the first method that i want to go ahead and show you guys is utilizing paper mill which is an open source python library that really fundamentally brought parameterization of notebooks forward and it quickly allows us to parameterize execute notebooks via the command line as well as a python api so here i'm going to show a sample notebook that's really simple all in python 3 so anybody can follow along as well here we have our two sets of values where we're going to set this as a parameter cell and it's really easy to do by clicking on the specific cell and making it a parameter cell and you can clearly see that it's already set as parameters now you can go ahead and save that as well and now we just do simple more addition and multiplication just for our understanding and you know uh clear use case here and so we can go ahead and run all now we understand what the output for these two values would be and so for paper mill as i said we can utilize the command line interface to quickly and easily allow us to parameterize this notebook with different values so we'll go ahead and just type in paper mill and we can go ahead and say this is an input i p y and v since it knows exactly our current directory we can just list out a specific name for your output path or output name of the notebook say you're in the same directory and then to go ahead and add those new parameters what you want to do is dash p which specifies that you're going to add a new parameters for this specific value and so dash p for 10 so we're going to make x equals 10 and we're going to do another dash p for y equals 20. you can also add this kind of options and parameters using a yaml file or a raw strings file as well for the specific options so go ahead and just enter that it'll execute it very quickly and we can go ahead and open that output right here and you can quickly see that there's already something new here and it's injected those values for us uh of both x and y and so it sometimes does it in different formats but they're both there and you can see that the output here is actually uh focused on the new values that we just did so that's really cool that you know it's really instantaneous in that sense that we're able to take the same notebook go ahead and output it into a new set of parameters so this really allows us to uh get this approach of uh automation and you know workflows for notebooks as a whole and our next step that we really wanted to do was um utilize uri parameterization and so in finding new ways for parameterizing a notebook we discovered that passing parameters via a notebook uri is something that we wanted to utilize we saw the potential of user store users storing their notebooks in remote locations as well as different within on different locations within their files as well so your parameterization allows the user to programmatically add these parameters to the end of the notebook uri so here i'll show you guys a quick sample of what we have here and i'll just close this and so notebook uri parameterization specifically uses our azure data studio uri handler which is shown here as this uh as a generic format that we pass in uh links that you can directly open via the web browser or a markdown cell that you have linked as well and so in the uri query all you have to do is state an ampersand to indicate that you want to inject a new parameter so here we have a notebook that actually we just showed in the input ipymb and that one contains the exact same but is now stored in our github sql server samples and so we're going to actually open this with the same format of x equals 10 and y equals 20. you'll get a notification that they'll want you to open this specific uri and i'll ask you if you would like to download and open and we're going to go ahead and do that and so now loads it up with the format that it actually came back that it stored in github and you can see it's quickly injected those cells and we can go ahead and run all quickly and there you go the same exact values but in a different format and method of parameterization so uh we have another specific demo that we wanted to show you for our last parameterization method and that was utilizing uh kql magic and kql magic specifically allows us to query this uh uh azure monitor cert resources specifically data explorer log analytics application insights and in this specific notebook and it's much larger than the other ones a little bit more complex we're actually going to look at weather data and specifically understand you know for specific states how we can analyze that so i went ahead and ran this as first time purposes and you can see clearly that we set the state filter to be georgia and it's clearly tagged as parameters so go ahead and just look at the data when you're setting up the connection this is a string and it allows you to connect directly by copying and authenticating by the clipboard we'll go ahead and utilize the kql magic within python that quickly allows us to analyze a different data and you can clearly see there's a lot of different event types specifically for these storm events uh we'll go ahead and look at the specific filter for that state for georgia and we can see that all these there's numerous you know uh storm events happening in georgia in the past for this certain time period and um you know we can clearly see which ones are the most which ones are the last least and uh then we can analyze it using actually plotly library as well same thing where we set the state filter to be that and you know we can analyze specific areas of the state and understand you know what exactly we want to analyze from here so now let's say we want to choose another state quickly and easily without having to do paper mill you know all that stuff and execution uh we actually created a new run with parameters action here which allows for a really easy uh you know entry point for us and it quickly allows us to say oh instead of georgia we want to say washington and so we'll go ahead and run this uh it might take a little bit of time but uh this quickly illustrates that you know you're we're able to uh inject new new exact new parameter value for that state filter and be able to look at the data so instead of authenticating in this format i'm just going to open up the saved version so it's actually shown much faster here and so exactly the same where we're seeing that we're actually showing the same database that's going to be used and that specific cluster so we'll understand that the state filter that this time is being used is washington and so we'll go ahead and see clearly already that you know georgia it was thunderstorm rain and now it's high heavy snow and high wind and so the last but not least you can clearly see and you know comparative do a comparative analysis of washington state versus georgia many other different applications as well using the potly library so these are all our different uh parameterization methods and our demos for now this is what we've kind of developed so far for uh automating notebooks as a whole and um yeah we're definitely excited to get more feedback and see how everybody gets to use this awesome thanks so much what i think is so cool about the way the azure data studio team develops is you're kind of developing for different personas of people working with the data so like if you want to be like in the command line you're going to do something if you want like this easy parameterized notebook uh button you're gonna do that too so i think it's super cool i'm gonna have to play with this i haven't um yet so thanks so much thank you thank you yeah absolutely guys sorry i cut you out just a little bit you were saying let me know yeah let me know if you need any help and those are guys and check out the docs that are already posted on our azure data studio awesome cool yeah we will definitely post a link for folks to kind of learn more thanks so much thank you so much awesome okay so we have a few more demos left and we want to get to some of your questions so i'm going to pull up uh julie who's going to be diving a little bit deeper into that kql stuff that basu kind of mentioned as well as how you can do even more with azure monitor data so julie over to you all right awesome thank you anna okay so uh i if i remember correctly and i've i've been in some of your shows and i've talked about crystal query language or kql and some of the cases i've mentioned about azure that i explore now if you are dps out there and you're actually spinning up at your sql dbs um just the way you know drew has shown you earlier today um there is a way to actually enable logging of your ssqldb that you can analyze so if you're coming from the sql server world it's sort of similar to as if you're looking at the event logs now this is event logs that actually get admitted to azure monitor logs also previously known as azure log analytics all right so let's take a look at that now so let's see here okay so here's a quick screenshot of the azure portal so in the in the actual sequel db uh portal i suppose you can click on diagnostic settings and you can turn it on and you can pick a few things like hey i want to um you know start collecting errors or start collecting data database with statistics and please route this event log to log analytics workspace so today i mean i'm actually going to show you exactly how to access that now in the past right in the uh previews or well in the current version of the azure data studio main version and the only way you can do this is through kql magic which is something that fazu has shown you earlier so this is something that i have run before so you would use reload extension kq magic to load the kql magic so it's just a package in in python that you have to install and then i actually use parameterization but this is already pre-run so we don't have to run it again um just to save us time so um what some of the tables that you want to start taking a look at in azure monitor logs or azure log analytics workspace is called azure diagnostics so this is one of the main tables where the event logs from azure skill db will be admitting to and interestingly and i actually uh learning as i i say as i'm preparing the demo and and talking to you about this is that interestingly the um the column lengths actually expand depending on what kind of metrics you want to you want to collect okay so here's the one that i have already pre-run so anna let's take a look at this how actually i've got a lot of errors here 108 errors and some deadlock events like oh this is not good okay let's so let's take a look at just the error event itself so um let's just jump to analyze errors so now we um to us now we've jumped to a specific session section in in my notebook so this one here just specifically analyzing the error event and then just drawing a chart so it looks something like this so it looks like the common error is the error 208 awesome so this makes it pretty easy yeah yeah this is one of the ways that i actually have used this to also look at errors look at who dropped my tables as an example so that's quite handy now when we talked about this a while back uh some of you actually have provided feedback where you know you want to be able to easily see the azure monitor workspace um with that using kql magic or without using python so that's exactly what we're doing so this is a bit of a sneak peek of what we've actually just added all right so if i go to the extension gallery here so i'm using the insiders version you should be able to search for azure monitor logs so if you install this um in the insider's build of azure.studio on the connections field you can now start browsing your azure monitor logs workspace so if you click on the new connection and then choose azure monitor logs so essentially that extension enables us to choose a new connection type which is action monitor logs here and then the workspace id is just the id from the portal um unfortunately you have to use the guide at the moment and and not the friendlier name so you paste in the workspace id and then click connect and where you go so it looks something like this wow that's so cool yeah so it makes it super easy so for example now you can browse here and then you know right click so i'm sure i click the right one and right click and then say take 10 and it will just start writing uh rhyming a que writing a query and writing a query on your behalf and then shows you the the details so and then you know you can start uh writing it to our queries so this is really cool i i love that you can actually see what's in there because sometimes at least for me like i'm not sure what exactly is contained in these logs so it's cool you can even kind of navigate through right right so here's a an example of a notebook now that actually can connect to azure monitor logs and the name on the kernel is actually subject to change we're still typing up a couple of the labeling here so um so yeah the kernel connect to log analytics for now make sure that you attach to the database sorry workspace for uh for the monitor logs and you can start writing all the kkyoku's that you want so here's an example of the exact same one the the one that i used will kick you out with kcr magic and um yeah and you can start you know looking at the uh graphs like this too so very very easy and straightforward and there's my deadlock again [Laughter] right yeah so yeah awesome well julie this was so cool and you know another i know you you know you're full of sneak peeks but when you drop down that kernel i saw so many kernels i haven't seen before um so i'm sure there's some news there at some point yeah yeah we'll show you that next time so yeah thank you awesome thanks so much julie i hope folks can check out kql and that new extension seems cool all right so we've seen so much we still have one more demo for you all and for that i'm going to bring back up alan um hey allen oh you're on mute that's okay we only did two this episode so we're doing pretty good all right here you hear me now yeah okay cool so yeah for this last demo it's a relatively shorter one um just to make sure you have time for questions and whatnot uh but very similar to what just julie just showed as you know we have an extension marketplace which is very similar to what you may have seen in visual studio code and one thing that we do want to highlight that we feel like that we you know we could always give an extra mention for is that postgres sql extension does work in azure data studio and it does support many of the experiences that you can expect to see for sql server and azure sql you can see by going to the extension marketplace here search for postgres and you can see all the different features that we do support in addition to more features that will come along and will be invested in over the next few months so you know you can imagine that over in the connections view here you can make a connection to um postgres database and you know this could also mean uh you know on-prem postgres this could mean post azure postgres uh postgres for azure arc um postgres hyperscale and just all these different flavors of postgres are all supported inside of azure data studio um so we highly encourage you to try this experience out um you know you could also run um you know basic queries so you know you could expect that you know very similar to what you've seen in the demos earlier that if you wanted to go ahead and you know create this customers table and also insert some values and query it you can get that same results grid and it's just a very consistent experience that we're trying to bring to multiple database uh languages and paradigms inside of azure data so that you know you don't have to relearn it whenever you want to try a different database inside of azure jc and also just manage these different databases you can't organize it in these different server groups um yes and you know you can't expect there will um there is and there will be additional support around postgres notebooks um with this extension as well but yeah you know i i think this is another good point to encourage the community to get involved with us through github so you know if you hop on over to here go to our azure data studio github and click on issues and you can see that you know one of the value props around azure data studio is ability for this deep customer engagement which you may not see with um you know all the time but here you can see like you can directly see when engineers are assigned to a specific issue that you bring up both for bugs or new features and we just really want to encourage all of you to be part of this journey because your feedback as you can see from the demos and some of the demo uh presenters also mentioned that your feedback is valuable and is eventually our things that we incorporate inside the product as well um so yeah we do encourage you to go on github you could even sort this by reactions to see some of the most highly support or highly upvoted issues and i think just one last plug is that there's some additional links here for you to all think about uh trying out if you're thinking about next steps and we're really impressed with the demos that you saw here to check out our project on github check out our official documentation at ak.msl azure data studio and also follow us on twitter at hashtag datastudio but yeah that was just kind of a quick overview over our postgres experience so you can expect that we will continue to make a an improved postgres experience inside of azure data studio and more languages to come awesome cool it's really exciting and i saw some of those upvoted things or a lot of other languages or databases that people want to see so seems like lots more to come definitely an exciting space um one thing i wanted to mention alan just for folks that are a little bit new is a few times people mentioned the insider build can you just give us a brief like what is the insider build yeah so you know if we over hop over to github and again this is kind of one of the value props of having all of our engineering done out in the open like this is that if you just kind of scroll down towards the bottom you can see that there's these insider build links that you can download anytime and what's really great about this is that we do publish insider's builds daily so that you can go ahead and try out some very preview features before we announce them in our stable release so when you think about azure data studio we have this official stable monthly release very similar to what vs code vs code does and you can expect that those releases are very um you know those are where there's a lot of bug fixes that come in all at the same time and we do a very thorough testing for these builds but if you're a little bit more like wanting to try cutting edge features and or you engage with folks in our team and heard that there's some feature that you want to get some feedback on before it officially moves into stable insider's build is a great way to get your hands on some of these uh very early features um and you could you know just download these uh you know any any day and you can just see the latest address video there awesome cool thanks so much alan um wow we have covered so much today like so many demos so many things like it's been really exciting to hear from all of you i'm gonna bring you all back up uh we did get a few questions it looks like we have just a few minutes so i'm just gonna ask um a few of those questions i think maybe are gonna resonate with the most number of people uh this first one i found really interesting and whoever wants to answer just give me a little wave and i'll pass to you but this one comes from aks they say i basically use ssms except notebooks what are the things in sql query that we can do in data studio that we can't do in ssms i can start to answer that and other folks can certainly chime in for the the components that i may miss but for ssms it's a really focused windows only tool that provides a lot of wizards and graphical interfaces for some of the deeper admin things on sql sql server mostly on-prem azure data studio branches out for a lot of experiences across a number of different potential roles but also in how people might be coming to use their data so if they're not on windows but they're on mac os or linux they can use azure data studio maybe they're in an environment that has not only sql server but it also has postgres and for a single tool that they want to have open more often or all the time azure data studio might be a better fit because azure data studio does incorporate julie's demos showed some of the the data explorer kql or things uh alan demo the postgres support so there's there's kind of more ways to enter into using azure data studio from a query editing capability for folks that are used to using vs code some of the the keyboard shortcuts the interface azure data studio may be more familiar and may be easier to kind of on onboard into using databases along with your development and so that's that's another kind of minor difference of how people might approach does anyone else have anything to add to that yeah i think the i think drew you hit all the kind of major points there i do want to add that you know one of the reasons why we created azure data studio in the first place is that as we're looking at this developer landscape and just kind of this data landscape as well more and more uh you know teams are looking for like it's important for us to be able to support services and experiences where our customers are so you know whether you're on you know some whether you only have macs in your workplace or you only have or you have like multiple databases that you're managing it's something that added jcu just kind of brings all these experiences together and also has a clear roadmap for supporting more database services down the road so as we think about how do we make databases experiences consistent across multiple databases and things like that you can expect that these investments are going to be made in azure data studio so that flexibility is really key awesome thanks uh both drew and alan i think it's really helpful to kind of hear your perspectives on ads and ssms and also understand that ssms isn't you know going away that's another big big thing i hear from folks um hey we are at the top of the hour this has been an amazing show uh speakers i want to thank you all for joining us agitated studio team it's been so great to have you on the show um just to share you know it seems like people are pretty excited about azure data studio so we hope um you all are also getting excited about azure data studio uh for our viewers um we did i did see a few questions about links we'll be sure to put those links in the description for some of the notebooks and things that you saw today um with all that being said i want to thank our viewers hey viewers you know we're here every wednesday at 9 a.m pacific so be sure to subscribe to our channel give this video a like and check out our other videos on our azure sql youtube page um again thanks so much for joining us for data exposed live and we hope to see you next time on data exposed [Music] you

Channel: Azure SQL

Views: 603

Rating: 5 out of 5

Keywords: microsoft, developer, azure data studio, ads, azure sql

Id: _I0sPvQonus

Length: 61min 19sec (3679 seconds)

Published: Wed Jul 28 2021