Azure SQL Database Tutorial | Azure SQL Overview | Microsoft Azure Training | Edureka

[Music] azure sql is evergreen meaning it does not need to be patched or upgraded and it has a solid track record of innovation and reliability for mission critical workloads companies are choosing azure for their sql workloads the question of whether or not to put your business data in the cloud is getting easier and easier these days and a lot of that is thanks to microsoft investment in microsoft azure microsoft strategy was to create an infrastructure like microsoft azure sql database that can manage big data and everything on down from that it's working quite well so hello everyone this is dhruv from medical and i welcome you all to this session where i will be talking about azure sql so without any further ado let's take a look at today's agenda so we will start this session by first understanding why one needs azure sql and what actually it is moving ahead we will also understand its architecture as well as different kind of models and service tiers furthermore we are going to have a brief understanding of azure sql database along with a demo as well as of azure sql managed instance and of sql server on azure virtual machine and both of these we will also understand with demo lastly we will also see the use cases of azure sql before we begin do consider subscribing to our youtube channel and hit the bell icon to stay updated on trending technologies and also if you are interested in online training certification in microsoft azure check out the link given in the description box below first let's understand why one needs an azure sql database extensively so azure sql database is the preferred platform for their transition to the cloud there are at least five key reasons driving widespread adoption of the azure cloud model so the first one is cost savings well if you see the most efficient advantage of transitioning to azure sql database is that you no longer need to invest heavily in high priced on-site hardware instead the costume for azure database can be treated as ongoing operating expense this helps in budgeting as well as cash flow also you don't need to be concerned about replacement depreciation and other financial worries surrounding capital assets and expenditures the second key reason is boosted scalability and performance so azure sql database is a highly scalable and flexible model by design far from on-site hardware you can rapidly spin up additional instances to harbor traffic spikes seasonal flows etc microsoft specially designed sql azure for cloud applications giving it a performance edge over other database as a service solutions in many factors also azure now fully supports both postgresql and mysql hence if there are pre-existing applications running on mysql or postgresql and one wants to seamlessly preserve functionality in the cloud then azure can fully support the transition third is high security so the azure sql database service boosted by a network firewall and other safeguards is widely considered extremely secure for development environments as well as many production environments fourth reason is time is on your side so traditionally to host a sql server you require a lot of resources for example you need to buy and set up a physical server with required hardware capacity once the server is set up you need to ensure the required software is installed further you also need to set up networking handle failovers capacity planning etc so with azure sql you just need to deploy the service and everything is managed by azure hence you save not only monetary resources but also the time invested in setting up and reading a solution for azure sql in a couple of minutes the fifth and the last reason is business continuity and disaster recovery for those who would prefer to take incremental steps towards cloud acquisition before moving their entire database to the cloud azure has two our trending tools asr and azure backup that supports the azure cloud to fully protect your on-site execution from downtime and data loss now that you know why we need azure sql service let's briefly understand what it actually is so azure sql is a family of many secured and intelligent products that use the sql server database engine in the azure cloud so these products are azure sql database which support modern cloud applications on an intelligent managed database service that includes serverless compute second is azure sql managed instance you can modernize your existing sql server applications at scale with an intelligent fully managed instance as a service with almost 100 feature parity with the sql server database engine best for most migrations to the cloud third is sql server on azure virtual machine you can lift and shift your sql server workloads with ease and maintain 100 sql server compatibility on operating system level x's using sql server on azure virtual machines azure sql is built upon the familiar sql server engine so you can migrate applications with ease and continue to use the tools languages and resources you are familiar with your skills and experience transfers to the cloud so you can do even more with what you already have now that you have understood why we need azure sql and what actually it is let's have a brief understanding of its architecture so there are four layers in azure sql architecture so we'll briefly understand each one of them first is a client layer so to be able to access sql database the client layer acts as an interface for applications it includes sql server tools open database connectivity ado.net and hypertext preprocessor tabular data stream transfers data between applications and sql databases and also communicates with applications hence ado.net and odbc can connect to sql without any additional demands so the next layer in the architecture is the service layer which is in between the platform and the client layers that acts as a doorway between the two as you can see in the diagram provisioning billing and routing connections come under this layer it validates microsoft azure sql database request and authenticates a user also it establishes a connection between the client and the server and routes packets through its disconnection third is platform layer the layer has systems that host the actual azure sql server in the data center each sql database is stored in one of the nodes and is replicated twice across two different physical servers azure sql makes sure that multiple copies of servers are kept within the azure cloud it also ensures that the copies are synchronized when clients manipulate the data on them the fourth and the last layer is infrastructure layer this is the first layer from the bottom of the architecture and is responsible for the administration of the operating system and the physical hardware so these were the layers in the architecture of azure sql so now let's understand different models and service tiers in azure sql so in this the first is deployment models so azure sql provides the following deployment options for database so the first one is managed instance this is premierely targeted towards on premises customers in case if you already have a sql server instance in our on-premises data center and you want to migrate that into azure with minimum changes to our application and the maximum compatibility then you will go forward for the managed instance second is single database which represents a fully managed isolated database you might use the option if you have modern cloud applications and microservices that need a single reliable data source a single database is similar to a contained database in the sql server database engine the third deployment model is elastic pool which is a collection of single databases with a shared set of resources such as cpu or memory single databases can be moved into and out of an elastic pool the next kind of models are purchasing models so sql database offers three types of purchasing models the first one is the vehicle based purchasing model which is new and it offers a totally different approach to sizing your database it is easier to translate local workloads to a v code based model because the components are what we are used to the v code based model lets you choose the number of vectors the amount of memory and the amount and speed of storage the vcore based purchasing model also allows you to use azure hybrid benefit for sql server to gain cost savings the second one is the dtu based purchasing model which offers a blend of compute memory and input output resources in three service tiers to support light to heavy database workloads compute sizes within each tier provide a different mix of these resources to which you can add additional storage resources as you can see from the following diagram the dtu model offers a pre-configured and predefined amount of compute resources vcore is all about independent scalability where you can look into a specific area such as the cpu code count and memory resources something that you cannot control at the same granular level when using the dtu based model a third purchasing model is a serverless model which automatically scales compute based on workload demand and builds for the amount of compute used per second a serverless compute tier also automatically pauses database during inactive periods when only storage is built and automatically resumes databases when activity returns now let's see the service tiers for azure sql database so the first one is general purpose or standard model it is based on a separation of computing and storage service the architectural model depends on the high availability and reliability of azure premium storage that transparently copies database files and guarantees for zero data loss if underlying infrastructure failure happens second is business critical or premium service to your model it is based on a cluster of database engine processes both the sql database engine process and underlying mdfs or ldf files are placed on the same node with locally attached ssd storage providing low latency to a workload high availability is implemented using technology similar to sql server always on availability group third is hyper scale service to your model it is the newest service tier in the v code based purchasing model this tier is a highly scalable storage and computes performance tier that leverages the azure architecture to scale out the storage azure sql database beyond the limits available for the general purpose and business critical service tiers so now that you have understanding of azure sql let's understand one of its major product is your sql database so azure sql database is a fully managed platform as a service database engine that handles most of the database management functions such as upgrading patching backups and monitoring without user involvement azure sql database is always running on the latest stable version of the sql server database engine and patched os with 99.99 availability platform service capabilities that are built into the azure sql database enable you to focus on the domain specific database administration and optimization activities that are critical for your business with azure sql database you can create highly available and high performance data storage layer for the applications and solutions in azure sql database can be the right choice of modern cloud applications because it enables you to process both relational data and non-relational structures such as graphs json spatial and xml now let's understand the key features of azure sql database so first of all azure sql database has extensive monitoring and alerting capabilities so azure sql database provides advanced monitoring and troubleshooting features that help you get deeper insights into workload characteristics these features and tools include the built-in monitoring capabilities provided by the latest version of the sql database engine they enable you to find real-time performance insights and the second one is a platform as and service monitoring capability is provided by the azure that enable you to monitor and troubleshoot a large number of database instance second feature is availability capabilities so azure sql database enables your business to continue operating during disruptions in a traditional sql server environment you generally have at least two machines locally set up these machines have exact synchronously maintained copies of the data to protect against a failure of a single machine or component this environment provides high availability but it doesn't protect against a natural disaster destroying your data center third is built-in intelligence so with azure sql database you get built-in intelligence that helps you dramatically reduce the cost of running and managing databases and that maximizes both performance and security of your application running millions of customer workloads around the clock sql database collects and processes a massive amount of telemetry data while also fully respecting customer privacy various algorithms continuously evaluate the telemetry data so that the service can learn and adapt with your application the fourth feature is advanced security and compliance so sql database provides a range of built-in security and compliance features to help your application meet various security and compliance requirements which are advanced threat protection and like auditing for compliance and security data encryption as well as data discovery and classification and also azure active direct integration and multi-factor authentication the fifth feature is easy to use tools so sql database makes building and maintaining applications easier and more productive sql database allows you to focus on what you do best building great apps you can manage and develop an sql database by using tools and skills you already have sql database supports building applications with python java node.js php ruby and.net on mac os linux and windows as well so sql database supports the same connection libraries as sql server now let's understand the server in azure sql database so in azure sql database a server is a logical construct that acts as a central administrative point for a collection of databases at the server level you can administer logins firewall rules auditing rules thread detection policies and auto failover groups a server can be in a different region than its resource pro a server must exist before you can create a database in azure sql database or a data warehouse database in azure synapse analytics all databases managed by a single server are created within the same region as the server remember that azure sql database server is distinct from the sql server instance or we can say which is like the sql server virtual machine which we will discuss later in this video now the next thing we have to understand is single database in azure sql so the single database resource type creates a database in azure sql database with its own set of resources and is managed via a server which we have talked about recently okay so with a single database each database is isolated and portable each has its own service tier with the dtu based purchasing model or v-code based purchasing model and a guaranteed compute size so some of its features are dynamic scalability so you can like build your first app on a small single database at low cost in the serverless computer or a small compute size in the provision compute tier you change the compute service gear manually or programmatically at any time to meet the needs of your solution so second is monitoring and alerting you use the built-in performance monitoring and alerting tools combined with the performance ratings using these tools you can quickly assess the impact of scaling up or down based on your current or project performance needs additionally sql database can emit metrics and resource logs for easier monitoring third is security sql database provides a range of built-in security and compliance features to help your application meet various security and compliance requirements so azure sql database has been certified against a number of compliant standards as well now the next is azure sql database elastic pool so elastic pool helps you manage and like scale multiple databases in azure sql database so azure sql database elastic pools are simple cost effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands the databases in an elastic pool are on a single server and share a set number of resources at a set price elastic pulls on azure sql database enables software as a service developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database so software service developers build applications on top of large scale data tiers consisting of multiple databases a common application pattern is to provision a single database for each customer but different customers often have varying and unpredictable usage patterns and it's difficult to predict the resources requirement of each individual database user traditionally you had two options over provision resources based on peak usage and overpay or under provision to save cost at the expense of performance and customer satisfaction during peaks so elastic pool solves this problem by ensuring that databases get the performance resources they need when they need it they provide a simple resource allocation mechanism within a predictable budget so now we need to understand when we should go for an elastic pool so pools are like well suited for a large number of databases with specific utilization patterns for a given database this pattern is characterized by low average utilization with relatively infrequent utilization spikes conversely multiple databases with persistent medium high utilization should not be placed in the same elastic pool the more databases you can add to a pool the greater your savings become depending on your application utilization pattern it's possible to see savings with as few as two s3 databases so we will be assessing database utilization patterns so in this figure you can see this is an example of a database that spends much time idle but also periodically spikes with activity this is a utilization pattern that is suited for a pool so this chart illustrates dtu usage over a one hour time period from 12 to 1 where each data point has one minute granularity at 12 10 you can see db1 means database 1 peaks up to 90 dtus but its overall range usage is less than 5 dt use an s3 compute size is required to run this workload in a single database but this leaves most of the resources unused during the period of low activity so a pool allows these unused dtus to be shared across multiple databases and so reduces the details needed and overall cost so building on the previous example suppose there are additional databases with similar utilization patterns as gb1 in these two figures the utilization of four databases 20 databases are layered on to illustrate the non-overlapping nature of their utilization over time using the dtu based purchasing model the aggregate duty utilization across all 20 databases is illustrated by the black line in the preceding figure this allows the aggregate dtu utilization never exceeds 100 ddus and indicates that the 20 databases can share 100 edtus over this time period this results in a 20 times reduction in dtu's in a 13 times price reduction compared to placing each of the databases in s3 compute sizes for single databases so this is all the concept of elastic pool so overall now we have a conceptual understanding of azure sql database so let's practically try our hands on it so you can directly go to microsoft azure reporter this is the portal so first let's create the database sql database go to create you can create the resource group first like you can see there's no resource group so you can create a resource group give it as azure sql so you can give the database name like you can give a sql database and you can like select a server so for that you can create a server like i have explained you know sql server how we have to we need an sql server for sql database so that's what we are creating here you can give a server name and give here and then enter server admin login so you can give a admin login as well so azure admin then password okay so password and then we have to confirm password just a second so i will then choose my location so that will be asia pacific central india now if you want to go for elastic pool you can choose that or if you want to go for a single database only you can choose it no so you can create an elastic pool from here if you want to go for an elastic pool so you can like create new so yeah being created now you can configure the elastic pool so here you can see like so you can see here the different purchasing models as well as the service studios in it so you can see that we copies purchasing multitude is purchasing model and it has like general purpose and business critical service to yours and likely to have the basic standard and premium one so you can go for general purpose or you can go with basic one as well so it will be enough 50 to use is enough 4.8 gb is enough so yeah we can apply for it in this manner we can do or we can this is how you can configure an elastic pool and if you want to go with the single database you can just create no so right now i have chosen so let's go with elastic pool and yeah that's it so rest everything is okay you can just go to review and create and you can like create it so it's validating right now it takes a little time so you can check in the resource group as well and go for the source groups here remember we have created a resource group remove this part i could get validated first now that you have a theoretical understanding of azure sql database let's try our hands on azure portal for deploying the azure sql database and let's see how it works so you can directly go to azure portal then you can go to sql database and create the sql database create a source group you create a new one the resource is created i will give the database name now we have to create a server we will create a new server okay so server name i have given then i have to give a admin login so i will just show database at your db admin your password it's done then you have to select the region so my region is asia pacific central india now server will be created so now if you want to use elastic pool you can use it if you go over a single database you can go for no and you can like configure it from here like how many cores you want what's the storage you want so everything is given here so you can like select the model whichever you want to choose so i can choose like the basic one i can go with this one on this so i will just apply it from here or if i want to go with elastic tool so i can create an elastic tool here just give me the scale maybe so it's been created now i can configure it from here in a similar manner like the core based purchasing model is given due to your base purchase morning is given service tiers are given so for this one also i can go for basic that will be enough so you can see the price here it's costing you just apply and it's been done everything is okay then i can just review and create and review the price again and i can now just create just create so the deployment is in progress it takes one or two minutes max so i can go to resource drive from here and suppose which i have created deployment is a stream progress so here you can see the source which we are giving the name it has a azure resource sql right as your sql resource it is here right now nothing has been created in this resource because the deployment is still in progress let's wait for it while yeah so completed let's check it yeah so the server is being created the server is being created now we can just uh open the server and here first we have to change the firewall settings so we can just go to show firewall settings so here it is given we have to end at the client ip and it is given like uh 0.158 so we can give it from zero to two five five completely and we will just save it and we can go to the overview and if you go back to the resource it is uh you can see like if you refresh until now the deployment might have been completed and you can see the database as well like it is showing here your deployment is complete so yeah come back to here so you can see here like yeah so database has been created elastic pool is also being created so you can just go to database and find what we have already said like this you can see on the left side pretty cool features given here so in computer and storage if you come you can again reconfigure your elastic full settings whatever storage you want to select and service yours you want to select you can select okay then there are also connection strings which is a pretty cool feature as you can see here it is given here and if you are working on java then jdbc is also there odbc php who everything is here then there are replicas as well similarly a lot of things are there you can use them as per your need power bi power apps everything is given so next thing we have to do is to connect it to the management sql server so if you don't have a management sql server you can just uh download it from here just go to management together so sql server management studio is there so you can just click here and download it but i already have uh downloaded with myself all i have to do is go to the studio so yeah so here it is just open it and we can copy the server name from here copy the clipboard so we have to add the client time in firewall right so we just add the client ip and it is 2158 so we'll just give it from 0 to 255 so we will just save it from here so yeah then we can just go to the resource again and we can see like database has been created as db you can see we can directly go to database you can see here as well like you can see like it's showing like your deployment is complete so database has been deployed and now you can see like there are pretty cool features given on the left side of it you can go to compute and storage so here you can like again change your service to yourself and purchasing models and everything if you want to change so if you can go to connection strings as well so it's pretty cool which you can see like audio net is already here and if you are working on javascript pc is also here and then odbc php code everything is there okay so similarly like pretty cool features given as per your need you can use those features for security purpose for against the visualization purpose power bi and everything is given here you can use all of them so we will connect it with management sql server studio so if you don't have it you can just go to google management sql server you can get sql server management studio come here click here and just a second just loading yeah so you can just come here and download it from here now what we have to do is because i already have downloaded with myself so i will just open it just a second so here's our name is required so we can just copy this over here we have it here server name you can see all the information is given here last two questions strings and everything is given here so right now i request over here i copied it i will just paste it here yeah sql server authentication will only remain here okay login as azure remember we have given login and password but you have to remember these id and password which you have created for sql server if you remember right so don't forget them so login is a azure sql plugin is azure db admin so we will just connect it from here so we have it here now you can go to new query and you can write the query here itself table variable character yes done and you can now insert the values into the table so it will be like persons age we can give done and values that i can give any name like the name is given and then i will give the age so first name last name and h right so it's been done it has been centered so after this you can like select it so select select from persons okay so yeah you can now execute the query line eight eighth line this mistake right in persons okay let's just say i execute now yeah so you can see it has been executed you can see the table has been created and with first and last name and age now you can even see that in tables you can see just a second yeah so db of persons has been created okay a table is created and similarly you can do it for your data studio as well you can download it on your data studio i already have it so you can start a new connection from here you have to give the server name so and go back to here you can copy the server name from here paste it here you can select the sql login then username and password you have to give so the same issue ap admin and the password which we have created for the sql server remember same thing okay database i have to select it's loading just a second yeah so my database name was sqldb right yeah so i will just name it xp db1 so group if there is any no so i will just connect so we can come to persons which we have already created it's been connected from within so we can just go to persons you can see you'll be able to see the query for it go for edit data so yeah you can see the table here it's been given okay the amon sahu and 26 this is whatever code was right so it's directly connected here i hope you have understood this so this was all about azure sql database so yeah now let's move forward so next we are going to talk about the second major product of for azure sql family there is a joe square managed instance first let's understand what is the azure sql managed instance so azure sql managed instance is a intelligent scalable cloud database service that combines the broadest sql server database engine compatibility with all the benefits of a fully managed and evergreen platform as a service then sql manager instance has like near 100 compatibility with the latest sql server so database engine providing a native virtual network implementation and addresses common security concerns as well and a business model favorable for existing sql server customers sql managed instance allowing existing sql server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes at the same time sql manage instance preserves all platform service capabilities automatic patching and version updates automated backups high availability etc so all this drastically reduce management overhead and tcu as well now let's understand why the azure sql management is required so azure sql manager instance for that we need to understand the difference between azure sql database which is platform as a service and azure sql manager instance so if you know about the like there's one more service an azure sql family that is a sql server on azure virtual machine so when we create a virtual machine instance and we install the sql server on it so it has like a lot of features there's a feature difference between the managed instance and the usq distribute database and we talk about which is onto my server so what's the feature difference feature lessons can be i will give you one of the examples like there is a sql server agent on when you install sql server on virtual machine so there is this sql server agent which helps in executing your jobs but the same feature is not their azure sql database so there are other features except sql server agent that are like linked server and sql server auditing sql data synchronization tv mail which are supported by sql manager instance but not by the azure sql database so that's where sql management instance is required so if someone is migrating data from azure sql server like from the virtual machine to platform as a service as your sql database remember this like keep this in mind that azure sql database is a platform as a service and whereas a microsoft sql server which we're talking about the on-premises server that is infrastructure as service so if you are moving from infrastructure as a service to platform as a service so first of all what we need is we want all the features of sql server whatever was there on sql server we want that on azure sql database if a user wants to move all the data and second thing is like from migrate if you are like migrating to azure then there should be like less maintenance overhead so with less maintenance overhead what it means is if someone is moving from a sql server to a user database they don't want to again create a virtual machine and store sql server they want the less maintenance less maintenance overhead which means like they don't want to go for extra steps it should be minimized there should be less amount of steps to get it executed so that's where the azure sql manager instance comes into play it has some cool features like it has 100 compatibility with the latest sql server enterprise edition so whenever you are using it we will get the availability of the latest sql server whatever the latest addition is other than that it has a backup and high availability as well so with virtual network we can say like vnet implementation what it means it addresses like common security concerns and grants uh network administrations and full control over access and configuration using like firewalls and security groups so which on the security bases what it does is it keeps it isolated whatever you're making through manage instance it will create a virtual network and that the sql will be installed and the apps are in your private network and access your managed instance only so by security point of view so it is very secure because it is isolated and azure sql database which is a public service can be like accessed in multiple manners whereas through managed instance it is isolated so that's your azure sql management play a great role that is in migration especially so the next thing is key features of azure sql managed instance so if you see like manage instance for sql database provide like several features that makes a deployment option stand out from single or elastic pool deployment these features include link servers that enable you to operate a distributed database means it enables you to read data from remote resources and execute commands for those sources from your database instance for example you can use this feature to execute tsql statements that include tables from an outside sql server or database this features enables you to configure many api data sources as link servers including azure cosmos db microsoft access and excel additionally you can use the link servers to implement sharing without direct loading or the use of custom application code second thing is service brokers that provide native support of messaging and queen so this is a feature that provides like latest support for asynchronous messaging and queen so you can use it to build distribute applications and enable communication between separate databases so when you use service broker you can easily distribute workloads across databases for example separating quick versus process intensive tasks on front-end versus vacant servers this feature manages the communication path for you enabling you to focus on development and testing without sacrificing data consistency third is database mail so there's a kind of feature that enables you to send email messages from your azure sql managed instance so with this you can send query results also you can like notify users of completed processes and attach files from any other in-network resources so now let's see the similarities between the azure sql database and azure sql managed instance so if you look in the management so like both sql database and sql manager instance preserves all platform service capabilities that drastically reduce management overhead and total cost of ownership that is tco and if you look into the backup so both supports like automatic backup full backups are like taken every seven days different shield 12 hours and like log backups every 5 to 10 minutes so backup retention is like seven days default and maximum 25 days and third is availability so azure sql database is like 99.99 to 99.99 variability is guaranteed for every database whereas in the same thing in azure sql manager instance uh here also like 99.99 every database and can't be like managed by user then there is a host accessibility so there's like no direct control over underlying computes so both are like fully managed sql server database engine based on the latest stable enterprise edition of sql server both are like deployed on standardized hardware and software that is owned hosted and like maintained by microsoft lastly there's one more similarity that is licensed so both have like written license model with pay as you go now let's look at the key differences like between the show sql database and the choice spirit management if you talk about recovery model so azure sql database has a recovery model from automated backups only whereas in manage instance it has like from automated backups and from full backups placed on azure block storage then second is a active jio replication so in this the sql database is like supported in all service tiers other than hyperscale and then there is sql managed instance it is not supported alternative solution is like auto failover groups then the next one is auto scale so sql database is like only supported in serverless model whereas in many instance it's not supported you need to choose the reserve compute and storage now the next one which is automatic tuning so which is actually supported in sql database but not in sql manage instance and next is elastic jobs which are like supported in sql database but not supported in sql managed instance but in trace of that sql agent can be used okay so next is long term backup retention which is supported in sql database and it keeps automatically taken backups up to like 10 years and in managing strengths it's not supported yet but like manual backups can temporarily work then there is hyperscale architecture which is supported in sql database but not in managing instance then there is sql server profiler which is not supported in sql database but supported in manage instance then cross database transactions which are not supported in sql database but supported in managed instance and lastly the database mail which is not supported in the sql database but supported in sql managed instance so with this we come to the end of second major product that is managed instance this was all the theoretical concept of managements now let's uh try our hands on it by deploying it on azure portal as well as connecting through it let's likely go to the azure portal so here we go to source groups we created this one right this was our resource group so here we can like create a new one so we will create from here we will create a virtual machine first virtual network i need to say so virtual network we will create as we have been through the explanation i hope you remember this explanation right when i was talking about why we need a managed instance that's where i talked about virtual network as well so here create this network so let's suppose group i draw same name i have to give so give it as accepted and everything is done if you can create it so yeah it's being created now employment is in progress it won't take much time so deployment is successful it's been done now we can again go back to our resource that is the joe squared source here you can see i will refresh it and just directly go to resource from here so virtual network has been created just a second so here it has been created through dmi and now the next thing is we are going to make the managed instance so let's create the managed instance android sql manage instance so let's create it so resource group is selected and instance name you can give so i will just here so virtual network i have named with the same okay so i will just give it instead of dot i have given just group mi and we can configure it from here okay first i will select the region that is central india and i will configure managed instance so yeah generation general purpose is given so i don't get much lower than that let's trade it for 64. so during everything is okay apply so i will just create a password so i'll draw manage instance so then password will be 16 characters remember that okay so review and create so the requirement is in progress yeah so remember this though it takes like at least two to three hours to get deployed so we ain't gonna wait for it keep that in mind that it takes two to three hours so this was all about the deployment of azure sql managed instance so let's move ahead to have a brief understanding of third major product in azure sql family and that is sql server on azure virtual machine so sql server on azure virtual machines enables you to use full versions of sql server in the cloud without paying to manage any on-premises hardware sql server virtual machines also simplify licensing costs when you pay as you go azure virtual machines run in many different geographic regions around the world they also offer a variety of machine sizes the virtual machine image gallery allows you to create a sql server virtual machine with the right version edition and operating system this makes virtual machines a good option for many different sql server workloads so now the question arises when to use sql server on azure virtual machine so as you have read earlier like sql server azure virtual machine is useful when you want to migrate your existing databases to azure cloud without doing much work secondly another reason to select sql server on azure virtual machine is when you want to use your existing sql server licenses and want to keep control of your database and virtual machine and third factor can be the cost so cost can be the major reason if you have high available systems with high traffic so sql server on azure virtual machine can save your significant compared to other options azure reservations can save up to 70 of your cost on sql server so now let's look at the key features of using sql server on azure virtual machine the first is automated updates so sql server virtual machines can use automated patching to schedule a maintenance window for installing important windows and like sql server updates automatically second thing is automated backups so sql server on azure virtual machines can take advantage of automated backup which regularly creates backups of your database to blob storage you can also manually use this technique azure also offers an enterprise class backup solution for sql server running in azure virtual machines a fully managed backup solution it supports always on availability groups long term retention point in time recovery and central management and also monitoring so third feature is high availability so if you require high availability consider configuring sql server availability clubs this involves multiple instances of sql server on azure virtual machines in a virtual network you can configure your higher availability solution manually or you can use templates in the azure portal for automatic configuration the fourth key feature is performance so azure virtual machines offer different machine sizes to meet various workload demands sql server virtual machines also provides automated storage configuration which is optimized for your performance requirements so now that you have a theoretical understanding of sql server virtual machine let's see how it works practically so you can directly go to azure portal and then you can go to the source group and you can like create a resource group say the new resource group is a server and you can select your region as well so mine is a central india after that you can just review and create so the resource is created then you can go to your resource and you can go to create just search for sql server 2019 for on windows on windows on windows 7 okay so we are going to select this one only but in this there are some options where you can select for the free one i am going to select free sql server license sql 2018 developer on windows server 2019 then there are different plans and like usage information plus support everything is there that would be a default only and just create it now you have to create the virtual machine you can give the name for it so i will give it as sql vm one and the region i have to select so i will select my region that is central india then let it be at default everything psq no infrastructure required image is okay i have to select the size so there are different sizes you can select so i can select the sizes from here and sizes are given but i'm already going with the standard one so that will be enough for me so i will just select this one and i will give the username so that will be azure admin server and then give your password and do don't forget the username and password you will require this while connecting to the ssms so the password is this password mesh public inbound ports so so select this and rdp is selected already so we will just go to the next one that is disks and in this we are going to select the standard ssd which is better than hdd as it's faster then we go to networking so our virtual network is this only so everything will remain default so all the options remain default in this and then management also the options will remain default so is in advance then you can just go to sql server settings directly and here so now in sql server settings you can like see the sql connectivity and port and everything then you can come to authentication and you can enable this so that will remain the login and id password which you have given now because we are going to use this for connecting it with ssms so azure keyword integration everything is okay this is okay so you can just go to review and create finally and you can just create it from here so this deployment will gonna take like 20-25 minutes so we ain't gonna wait here for that you can download the deployment details from here like this you can download and you can go and see the deployment details so what i did is i already created a virtual machine before only just for the demo purpose i will show you so i had a different resource for that so when it get created the vm1 i created so this was sql vm1 and these nine records are being created so you can just go to sql vm1 so this virtual machine is actually stopped right now so i will just start it but when you create the virtual machine it will automatically be in the on position okay because this one i have stopped so i have started it it's getting started so virtual machine you can see like it's started now you can see the public ip address and everything like here itself so we'll just refresh so now just go back to the resource so and you can see all the resources here so here we have ip address and everything and you can see the size as well which is the standard ds1 v2 once virtual cpus and 3.5 gene memory so now we will connect through this ip through the database engine later on we will use this ip so right now we can just go to and connect through rdp and it says okay this is okay you can just download the rdp file as well so the rdp file is downloaded you can just open it from here and connect and put the username and password which you have assigned for it so i will assign azure server admin under my password as well yes so now i will just connect yes so as you see like this server is uh launched so we can just go and launch the ssms in microsoft sql server management it takes a little time when you launch it for the first time the ssms so now you can give the server name here and authentication you can give windows authentication or you can give sql server authentication for connecting it to the sql server so right now i'm giving windows authentication and we'll just connect so now you see it got connected this is the version and these are the databases the system databases and you can also see for the security which we have chosen which is for walk-ins like you can see for sql server like this is sql vm1 and for login thing you can use see it as a azure server admin so we will just check the version so take a new query we will give it as select at the rate version and you can just execute it it is showing there was a microsoft sql server 2017. so after this you can come back to your source so now you can go to the resource group so here you can see total nine records have been created when you create the virtual machine you can see like the virtual network virtual machine sql virtual machine then public ip address network security group network interface and three disks so you can go to the virtual network which has been connected through subnet and you can see like there's a default subnet and available ips are 250 and you can go back again to the resource now we can go to security group that is sql my nsg in this you can see you can connect through rtp through port 3389 and for connecting through sql you can use the port 1433 with your local system now let's come back you can see you have the source group azure sql resource which i have already made and the azure server resource which should be upgraded so let's see now we have left this one okay for deployment so this deployment is completed as you can see so let's come back as i've already explained you all the process and suppose if you don't want to continuously use your server you can even stop your server so what you can do is you can go to your resource and you can go to your virtual machine so that just in the case you don't want your build to build high and you want to put a so you can just stop the virtual machine whenever you are not using it so you can just click on stop as you can see stop virtual machine is stopping processing is going on we will also stop this one as well so we can just go to the explorer so it's better because otherwise it will keep on building and it may like cost a lot so i will just stop this one as well go to vm1 and stop it so let's check the last one so this one we can see like this one is stopped already successfully stop virtual machine so you can see the virtual machine is stopped that's the process so i hope you have understood the process of sql server on azure virtual machine now let's look at some of the use cases for azure virtual machine so the first one is developer or test environment it's like an important use case for replicating or migrating data to sql posted on azure which is for developer and test environments so before deploying to the production environment it is a pertinent that the data is tested against developer test environments azure sql databases can like act as a target for just such environments the life production environment can be replicated to the developer or test environment using a database copy second is a business continuity so one of the most important use cases for sql on azure is using it as a dr target to maintain business continuity azure sql databases can provide an sla of up to 99.99 by maintaining several copies of the data this provides a business continuity as it allows you to restore geo-redundant copies of the data or use active geo-redundant copies as failover points in case of outages at data centers or in regions besides azure sql database you can also use availability groups to fulfill business continuity demands not only you can use availability groups and azure sql virtual machines but also use azure sql virtual machine instances as a target for high availability and disaster recovery third is scaling out read-only workloads so apart from providing bc or dr capabilities active jio replication can also be used to offload read-only workloads such as reporting jobs to secondary copies you can also like extend on premises sql server instances using readable always on reticles both is backup and restore so azure sql databases backed up automatically on a regular basis and there are no storage costs for to 200 of the maximum provision database storage you can like restore backups to any point in time going back to the retention period which is determined by the azure sql service tier in use on-premise sql server databases and transaction logs can also be backed up directly to azure using the backup to url feature and stored in azure storage so azure sql databases can also be stored on local storage by exporting them to backpack files fifth is advanced analytics so another important reason for hosting sql in azure is to make use of azure's advanced analytics platform such as azure storage blog and azure data lake store common scenario with advanced analytics is when users reference data from various data sources so you can use azure data lake store as the staking area perform and you can like also perform transformation activities using high voice spark and finally load the data into azure data warehouse for bin reporting vi means business intelligence and reporting with this we come to the end of today's session of azure sql service so i hope you had a great time learning and understanding about it and if you have any queries please feel free to leave them down in the comment section below until next time thank you i hope you have enjoyed listening to this video please be kind enough to like it and you can comment any of your doubts and queries and we will reply them at the earliest do look out for more videos in our playlist and subscribe to edureka channel to learn more happy learning

Channel: edureka!

Views: 7,129

Keywords: yt:cc=on, azure sql database, azure sql, azure sql database tutorial, azure database, azure sql server, azure sql db, azure sql managed instance, azure database microsoft azure sql database, azure sql database pricing, how to connect azure database from sql management studio, azure database services how to deploy sql database to azure, azure sql database documentation, azure db, how to move sql server database to azure, azure free sql database, edureka

Id: 8ulro2MPiYs

Length: 54min 50sec (3290 seconds)

Published: Tue Sep 07 2021