Welcome to lab two and in lab two, we will look into how to create SQL Server on a CR. So in the previous lab we saw that how we can host web applications on as your, in this unit we will see that how we can go and provision our DBMS on as your so in order to do that, you know the first concept in which we need to learn over here is something called as details and EDI deals Without this, it's very difficult that you understand how to how to go and provision on SQL. So how can you provision SQL Server on a car right so first you need to understand DTU and edu. You also need to know how to calculate this DTU by using the DTU calculator. So Microsoft has created something called as DTU calculator. So we will see that how to go and calculate DTU using edu and then once we understand the concept of DT and edu, we will then create an SQL Server database on as your and we will apply this Did you and add your concept and what we will also be doing is we will use our SQL Server Management Studio.

Remember, SQL Server Management Studio is an offline tool, which helps you to connect to SQL Server. So we'll use SQL Server Management Studio and connect to the as your SQL Server. So this is the whole goal of this 30 minutes video. First we'll understand ddu eg deals, we will see how the calculation happens of DTU. We will also see how you know how to use the DTU calculator. And there is also a command line which helps us to do the DTU calculation and then we will talk about SQL Server.

Then we'll go and create an SQL Server on as your and then we will see how to use SQL Server Management Studio to connect to the SQL Server offers your so let us start. Now before we start ddu before we define edu, let's first try to understand what is that one big reason which has compelled us to come to as your what is that slogan which has us to come to a car. So, that one slogan that one thing you know, which has attracted us to cloud is are attracted to as your is this thing called as pay as you go pay for only what you use nothing more nothing less. So, from that perspective what Microsoft has done as you know, they have given you something called as pricing tiers. So, if you look at your even in the previous video I have discussed about it.

So, they have given something called as pricing tiers. So, you can go to the pricing tier and in this pricing tier, you know, they have given lot of permutations and combinations. So, they have said that, okay, so you know, your workload Well, you know what you want, right, so here it is, you can get a simple virtual machine which is free with one GB storage, and, and depending on your workload, you can select something like an isolated, more stronger virtual machine, which is with four core and 14 gb ram. So, depending on your workload, go ahead. Make your choice of your virtual machine of your configuration. And depending on that configuration, I will charge you.

Now this sentence looks absolutely fair that you determine your workload. And depending on the workload, you decide your configuration so you choose from one of these configurations, you know what are given, right? And then for that configuration, you make the payment. So, for every configuration here, there is a payment given down below, right. So, if you ask me the sentence is absolutely fair, which says that decide the workload and then decide your configuration depending on the workload and then you pay for the price. But here is the problem.

The problem is that how do you determine the workload because this concept of workload changes from application to application it changes from person to person the way they view the application. For example, if you are talking about web application must be number of users are important to you. Right, or if you are using SQL server or our DBMS must be number of transactions are important, or must be, you know, for some people, Ram is important. And for some people how much is the processor uses important. So this workload actually is not just a single thing, but it is actually calculated from lot of factors. And depending on the services we use, for example, if you're using our DBMS, then the calculation of this load can be from a very different perspective.

If you're using a web application, the calculation can be a very different perspective. Let us first try to understand that for our DBMS for SQL Server, right, what kind of things are taken into consideration to calculate this workload? Now, when you talk about a typical RDBMS, or SQL server or Oracle or whatever our DBMS, right, there are five important factors which domain the workload The first one is the number of writes and the number of reads So the amount of number of writes and amount of number of reads are more, that means the workload is more. And if they are less, that means the workload is less, right. The third factor is the amount of RAM consumed, you know, while this data is read is, is written to the primary file and read from the primary file. The fourth factor is the processor percentage used.

So, when you're reading this data, when you're writing this data, the processor will work right. So how much is the processor working out? And the fifth factor is that what kind of how much is the amount of data written to a log file? When you talk about RDBMS, there are there are two primary files one is a primary file, you know where the actual data resides. And then there is a temporary file called a lock file, you know, wherein the rough transactions are done. So in this unit, we write the transactions in this we write the recovery options and so on, right?

So what amount of data is actually pushed to the log file? So there are five minutes important factors which says that how a workload for RDBMS should be calculated number of writes ram number of reads CPU and the amount of transactions written to a log file. Now, how do I take all of these five factors? So, let us say I have something with me somebody tells me okay the number of writes for a given instance is thousand number of reads can be approximately 20,000 the number of RAM I need the amount of RAM I need is two GB and so on, so on by using all of this you know, who will tell me that I have to use a p two v two processor or a p two v two virtual machine or should I use the P one or must be should I use the free right?

So, how do I now map so many factors to this pricing tier, right? For that, you know what Microsoft has done is you know they have created a unit called as ddu database engine units, this database transaction units is nothing but it is a calculation which which is derived from these five factors that is CPU number of rights ram number of reads and you know the amount of data written to a log file. So, if you really ask me, that's the diagram. So, a dq is nothing but it is a unit in a which is calculated on the base of these five factors. So, the first important conclusion we can make about deduce that dq is a unit of measure for RDBMS and it is calculated from five important factors that is CPU number of writes ram number of reads and logs written per second. And the next important thing you know about detail is that did you say is that okay for this configuration, you will get guaranteed performance for this GTO means what?

For example, You look at your logs written per seconds you look at your reads you look at your rights, and then you say that you want to use 20 DTU fine. So for 20 DTU the basic configuration is okay. After that, let's say that after a year you're load increases, right? Then you say okay, now I need approximately it's a 30 GPU so he says, okay, for 30 do you don't have to do anything you don't have to upgrade you can still use basic and in that basic configuration, we can guarantee you still the same kind of performance. Then you say okay, now I need some hundred deals fine. So 400 us also again, you can still go ahead with basic, we guarantee you the same performance.

After that you say okay, now you need 1000 did you owe 4000? Did you I cannot guarantee you the performance, you need to now upgrade yourself to standard. So did you when you when you calculate your ddu and when you say that okay this is my DTU then for that DTU there is a specific configuration which is chosen and then that configuration says that for this range of DTU I can guarantee you performance. So, summarizing from all of this what exactly is a GPU Did you is a unit Did you is a is a CR unit, especially for RDBMS which guarantees that for that configuration, you will get the necessary performance. Again, let me repeat dt is a unit it's a RDBMS unit, which guarantees that for the next level of configuration, you will get the necessary performance. Now, there is one more flavor of DTU termed as Edu So, when you go to as your and when you're creating an ad DBMS resource right or SQL Server resource whatever our DBMS you choose.

So, you select your DTU when you assign a DTU for example, if you say that okay you have you want to allocate five DTU to a database, then those details are dedicated for the database. So, let us say you go and create a database called as DB one and if you look at five DTU, no other database can use these five details. But now, let us say you have an instance where you have two databases like DB one and DB two. So, let us say that in the morning DB one consumes a lot of details and DB two does not. And then in the evening DB two consumes a lot of details and DB one does not. So, rather than allocating dedicated details, you can go and you can allow for locate, elastic, deduce, share, deduce the calculation of d d was either it's a D or a d d does not change is the same thing.

Okay, so the calculation by using those five factors, they don't change, but eg dues are shared among many databases. Okay, so you create a pool of details, and that's termed as edu. So did you is dedicated for a database, while Edu is a shared pool of details, which are shared among multiple databases. And also in Edu you can define a min and a max. So you can say that, okay, the minimum Edu you can consume from this is four, let's say, and the maximum you can consume is eight. Why do you want to do a minimum and a maximum because you don't want one database to take all of the details and the other database does not have anything.

So you can also define a min and a max in an Edu pool so that all the databases get at least a minimum set of details. So with all that knowledge, I have moment, it is now time to go and calculate it to you. So, we know our workload, we know that how many logs per seconds are written, we know that how much CPU is utilized, number of reads number of rams, right? So, how do we know and now go and calculate ddu? Because, you know, is that formula we have to learn? Do we have to know some literature and so on, right, a lot of things are there.

So you don't have to do any one of these, you can go to this site called as DTU calculator.as, your website's dotnet. So, in this site, you know you can go and you can upload a CSV file, you can see that there is a file upload given out here. So you can choose a file and you can upload the CSV file and in the CSV file, you need to go and put all those factors you know, which we have discussed, right. So you have to go and put you know, how much is a processor time, you know, how much is the disk reads, how much of the disk writes and so on, and then upload the CSV file over year and then say calculate But now the question comes, is that okay, from where do I get the CSV file? And how should I do things you know, because for example, let us let us consider a situation, you know, where you have offline SQL server which is running, and that offline SQL Server you want to migrate to as your, so you'd like to go and run some kind of a tool.

And if that tool can capture, you know, all these values, like number of, you know, logs written, and processor time and all that right, then I can go and upload that CSV file to as your and then decide that what is my final configuration, right. So for that, you know, they have also given a command line utility out here, you can also run a PowerShell script as well. So if you download this command line utility, you end up with some kind of thing like this. So you can see that when you extract extract that command line utility, it's actually a zip file or our file. If we extract it. You come to some Like this.

Now, in this unit, there are two things. One is there is a config file and one is there is an E xe. So if you go and if you open the config file, so what happens is in the config file, so if you go and see the config file, or too many things out there open config file, where is my okay? Let me open it in Edit plus evaluation version. Okay? So there it is, this conflict file is out here.

So in this config file, he says that I'm good to go and capture all these counters right? describes this screen and so on. And I will put a file in the C drive in the temp. Okay, so with all that, let me go and run the CFC. So this actually goes and this runs the E xe and what does e xe actually does is it actually goes to your computer and it goes to your SQL Server instance and it starts capturing the values so you can see at this moment, I Have an SQL server running on my machine, it actually goes and captures all this. And then it goes and it gives out a file in the C temp.

So in the C temp directory, you can see that there's a file which is made. So this one is a recent one again, just this one is a recent one, right? So now this file if you open it, so if you open this file in your Excel, it looks something like this, right? So you can see that there is a time here and then there is a processor time in the discretes and all that okay. So, this file now defines my workload. This file has now captured my workload right?

So now what I can do is now I can go to my go to this site here and I can say that okay, I am looking for two core machine. Now you need to go and define that how much cores you want. Okay, the scores are not calculated automatically. So this you need to define. So I'll say that okay. Must be I need at least two core machines.

Must be a one quarter machine let us say for now just to be just to be more miser so I went to the failure and so that is my C drive in my temp right. So you must use volume attempts so I can take up the latest file like this one for 30 am we take this and I'll say calculate. So, once I do a calculate it looks at my performance levels and then it decides my service data tier. The service tier is nothing but that is mapped to your pricing tiers. Remember the service tiers are nothing but the standard basic, isolated premium and so on. So that is your service tier.

So it says me that, based on your database utilization, we recommend that you migrate your SQL Server workload to standard as one. Right? So, you don't have to worry too much about how to calculate a DTU you can just go to this DTU calculator.as, your website's dotnet capture your CSV file using the utility and then upload over here and from that you can you be you will get a recommendation saying that what is good for you. Now, what I do is that I am a technical miser. So, what I do is that if you suggest me standard s one I tried to go below that first just for testing purpose right, if it works, well I just keep it to that level. If it does not then I go and actually you know, take this recommendation.

So, what I do is that whatever is recommended by Microsoft I try to go below that, why do I go below that? Because I believe that you know when these calculations happen right they you know all the vendors in the day try to give you a top number right. So, if you say okay this is of 100 rupees right they will try to give you as 200 rupees right. So, always from the negotiation perspective what I do is that, I try to first go a lower end and in case if the lower end is not meeting my requirements, then I go to upper end, right. But be very careful with this approach. In case your project is very mission critical, a lot of people are using it don't take that risk, but in case your project is not so critical, then you can come to the negotiation table, you can go below down if it works, it is great.

If it does not then you can take the necessary recommendation. So now that we have understood the concept of DTU and edu, so let us go ahead and create an SQL Server database and let us see that how to apply these principles. So you can see at the left menu there is something called as SQL databases here, you can click on it, and I'll click on add here and let's give a database name. So I will say this is my DB one. Now remember that I had a free subscription if you remember. Okay, that free subscription has expired.

So, with all my heavy heart, I have put my credit card out there and he has given me pay as you go. So what I will what I will be doing is, as I've said I am a technical miser so I will be creating this account, I will be showing the demo, I will delete it. Okay, so I'll create this SQL Server, I will use it for some time, I will show the demo and I will delete it. Okay. So even if you want to learn, you can do the same way, do not try to hunt around for three years, your passes, you know, it's, it's very rare that you can get as your pass for six months, right? So learning as your will take at least three, four months.

So I would suggest go and put your credit card when you put money for knowledge, nothing, nothing goes waste, right? So I put my credit card and I have got this pay as you go. So I'll say okay, this belongs to this group. So remember, we had talked about groups, resource groups in the previous classes. And he also then gives you options do you want to create a blank database? Or like if you want to go and you know, create from a sample database like adventureworks, I'll say I want a blank database at this moment.

Now you can see here is a configuration that do you want to use elastic pool so if you say yes, that means you will be using either to you. So if you press this Yes, it will actually give you the facility to create edu. If you say not now, that means this will create ddu. So remember, when you say just edu, it is dedicated to a database. When you say edu, it is shared among many databases. So now let us go ahead and click on configure the server.

Remember, you have one is a database. The other one is you need a server instance on which your database runs, right. So this is my instance. And whenever I want to connect to my database, I'll be connecting to this instance. Okay, so instance means this one, this is the server name, this is the server name. And inside this we have database, right?

So you can see, I opened my Management Studio. So when you go and actually connect, you connect to an instance. And in that instance, you can have one database or multiple ones. So that is that instance. So I'll say this is my instance. 123 something and some username and password.

What do you need to give here? So I'll just put your admin and some password here. What is this area? It says you cannot have admin is it so I'll just give it over here let's say Shiv Shiva that's a nice word. Okay. And let me put a password here and you're on so I'll put a password right okay everything okay this will be created in West Europe in case you want it somewhere near please go and choose your location if it is available and also select so that you can see now I have created my instance and it is now attached you know to my database.

Now I have selected this as a GPU so it says that Okay, I will give you something like hundred Did you you can see, this is a standard one s3 you can see this hundred DTU right. So if I click on this, it also gives you more options. It says that, okay, for the standard, the ranges from 10 to 3000. Did you and will cost you 1000 rupees approximately per month 991, right? If you want something like more than 4000, then this is something a premium. So if I click on premium, you will see that I'm getting a different kind of figure here, right?

So let me just go and select the basic one, I just want five DTU. So you can see this is a basic five DTU. And I can also go and put a very less storage I at this moment, as I've said, I'm here for teaching. So I don't want to spend too much money, right? So I've just minimize it to some small figure out here to GB. So, you can see now, when, as your is talking to you about our DBMS allocation, it is talking in terms of ddu.

And if you remember I said that did you say is that four standard, he guarantees that till 3000 DTU, you will get the same kind of performance, but the time you say if you want for 4000 DTU. And if you are still on standard, your performance will deteriorate for that you need to go and take premium, right. So at this moment, I'll just take this I do think I'll keep it minimum. And I will say Apply. So that that's that's a scenario for our Bure, ddu right. In case if I select elastic pool that means Edu then the scenario changes.

So if we look at this elastic DTU you will find now things are different so I've clicked on elastic Did you I will add elastic Did you pool now you can see that he is asking for a pool name. So this will be pool one right and this pool one I can attach to multiple databases. Remember, elastic is all about sharing this GPUs with multiple database you can see on the comment at the top right. So again, here it says okay, configure your pool. So I can I can go and say configure my pool here and I can see okay, I am looking at max to max I'm looking at 20 DTU and minimum I'm looking at 10 Did you remember when I explained the fundamental of Edu I said that in Edu you need to spend To find the maximum the mean. Now, let us say that the maximum this pool has is 22 and let us say if you have two databases than one database if it consumes a whole 20 DTU the other database would starve right.

So, now with this what happens is, I say that minimum to minimum one database should get tended to you at least right and maximum Yes, you can try for 20 if it is available. So, you can see now with this range, you know we are becoming fair to all the databases, which is belonging to that elastic pool, right. So, for now, let me go and choose this elastic pool at this moment, I'll assist select, OK, select that it is so also I can go and configure my pricing tier. I'll just ensure that I take the basic one I'm not really interested to take a big one, right. So you can see now, this is a very basic 50. edu, and this 50. edu is shared in such a way, right I've also created an Edu right Over here, and I'll just select this. Right.

So that is my elastic pool I've created. And this elastic pool now I have assigned to this database. So I've created Carla's pool one. And tomorrow if I want, I can go ahead and assign this pool one to someone else as well right. So that is my DB one is created. And I will just say create.

And with this, you know, it should actually go and create an SQL Server database, it should actually provide me an SQL Server services, you know, it should provide me SQL Server services to me right. So that it is it is going and creating the SQL database. Let's wait for a minute and once you know the database has been created in the alerts, it will tell me that you know, everything is created. So yes, validation is successful. That is I'm so happy to hear that right. Let us see what happens.

Also, you can see that he's showing me that the deployment is in progress, right. So once you know the deployment is successful, it will actually To show me, my SQL server has been created, right. So let us give it a minute or two until this deployment happens. And once the deployment is successful, then in my SQL database, it should list over here, that database name. And then what we will do is we will go and connect to this SQL database using our Management Studio. And once we have connected using the Management Studio, the rest is easy right creating a database creating a table creating a stored procedure, all of this things remains the same.

So now I can see that the deployment has is successful. And if I do a refresh, you can see that my DB one is online, it is pay as you go right I can click on it, I can see all the properties you know over here. Now you can see that this is my my instance one, right? So I'm going to I can connect to this server, you know from SQL Server. So I will copy this and I will give it over here you can see I've directly copied my instance 123 dot windows dotnet. And I will say SQL Server authentication if you remember I have put the username as Shiva and my password right at 123.

And this should actually now go and connect to my online as your you can see here before connecting to all mammals, you're, you know, just to make sure that everything is right. And everything is more secured, he's telling you that you are not signed into a car. So let us go ahead and sign in. So with this, you know, he knows that you are a proper as your user, so I'm going to go and sign in. So at this moment, I'm using this thing questpond interview questions@gmail.com that's such a horrible ID. I'm using that.

So let me use that. Question on interview questions. gmail.com. Okay, I'm sorry. You know, it's so hard Double ID but I just chose that Id at the start, I'm going to use it. And I hope you will ask me for a password.

So you can see like over here also there is a double security just to make sure that people aren't not connecting through a unknown. SQL Server Management Studio. Remember, when you're connecting to SQL Server Management Studio, you are actually directly connecting to that port right? So it's just making sure that some unknown user is not getting connected. So there it is, I've given the proper thing so I hope that once this is done, he should allow me to log in right it's doing something it's saying not responding. Okay, we'll wait until we get a success.

So that it is it is validating it is authenticating, it is authorizing. Okay. Okay. Oh, why what happened? Isn't that I put some wrong password here is it So now that it is it is valid. So you can see now I'll say, okay, add my client IP, and allow me to connect so that it is connecting.

So remember through SQL Server Management Studio, it almost similar remains the same thing. But the only thing that changes is that you need to again log in with your as your account for the double security check, right. So once it is connected, you should see your Object Explorer. So you can see at the left hand side, just move it in between. So there it is, by going a bit blue, but things are same almost the way we have done it in SQL Server. So there you can see my DB one, right?

I can go and create a table here. So I can go and create a new table absolutely same the way you have done for your normal offline SQL Server. The things hold same. So I can go and give some table name here. I can create tables, I can fire SQL. You can use SQL Server Management Studio almost as you were using it offline, right?

It's bit slow because we are directly operating online. So definitely it's it's, it would it would have a lag right So let us say I create one table here with ID I'll say this is an end a primary key okay? Okay my mouse okay. So this will be name and something like n where care right? And save this let's say this is TBL one table you can see almost all the steps are same the way we have done for SQL Server offline right? So that was the basic thing you know that how to go and create an SQL Server database on as your and what things you need to remember when you are actually creating or providing this RDBMS service you know, you need to remember the details.

You need to remember the details and so on. So you can see almost same the way we have done right click Edit, select, you know all the things I've seen right. Also if definitely you know one of the ways you will use this as your RDBMS in your office. SQL Server as your RDB ms in your MVC application or in your C sharp application is wire connection strings. So you can see that there is something called as a show database connection string here. So you can go and click on this connection string.

And you need to copy this connection string into your MVC application in the config file, you need to provide your username and password and then you can connect it in the same way. You can also see that if in case you're connecting via JDBC, you know there are some other connection string so those are also provided at the top right. So that was this basic video. So in this video, we were trying to understand what it takes to create SQL Server resource on a car. Now before we proceed to the next video, you know here is a small request, you know for everyone. You know, we record a lot of videos we put it on questpond right, but we need to get the pulse of the audience that which video series they are looking at more.

For example, if you're seeing MVC we record more on NBC. If you are Seeing design patterns we record more or design patterns right? So do let me know that if you're watching does your series on which video you are on. So you can go to facebook.com slash correspond and you can put here that I am on as your lap two, I am on as your lap three, right as we go ahead. So that brings us to the end of this video. Now the next video is going to be on function apps that is something called as function apps.

You knows you're very sleek, very nice. So we'll talk about that. Thank you so much.