query

Azure SQL Database Tutorial | Relational databases in Azure



Sharing buttons:

hello everyone this is Adam and I'm back

with another interesting topic today

we're talking about SQL database if you

need to manage query and ensure

structure of your data in database this

is one of the primary services to do so

so let's jump straight to it shall we

so to talk about SQL database we need to

understand the basics so SQL database

another is a relational databases

service

that means it's a latest stable version

of Microsoft SQL Server database engine

in a cloud but let's get down a bit more

to basics what is relational database

and why would you want to use it so in

case you have an entity called person

and this entity has three values first

name last name in an age you can

actually start putting those values as

columns so first name last name and age

and create a table so within this table

you just input your values into

appropriate columns that's fairly simple

right of course when another entity

comes for instance John Mayer you just

input this additional row but relational

databases are a bit more than just table

and rows it's all about defining a table

in this case persons table but also

defining something called constraints so

you can say that your first name it must

be longer than three characters and

shorter than 50 and for H you can say

that must be of type integer to make

sure that only integer values are there

but also you can say that the volume

must be lower than 130 so you can define

those constraint to ensure that data

quality is there and you can also do

that with a simple SQL statement

creating table and all those constraints

in a very simple manner but that's not

where it ends with relational databases

you can actually use multiple tables so

if you have a person table you can

define addresses table and combine it

using for instance address ID and it's

up to database to ensure that based on

the relational definition is making sure

that the rows are matching from all the

tables and it's up to you to define

what kind of relationship you want

between your tables of course if you

want you can combine multiple tables

using relational database models and of

course once you do that database again

ensures the quality is stays there but

what is great here is that if you have

all those relationship defined you can

actually combine data from multiple

tables using SQL queries to for instance

calculate how many orders were there per

country this is a powerful feature of

the relational databases so why would

you use relational databases well for of

course because if you're using

structured data you can actually manage

it very well using sima constraint and

relationships but also if you want to

use those rich weary capabilities to

explore your data then relational

databases are perfect choice for you

with that said we can actually go to the

database itself the azure SQL database

or SQL offers free deployment methods

first one is a single database it's a

fully isolated and fully managed

database for you so simply said this is

a SQL Server migrated to the cloud and

redesign the bit to fit the cloud needs

and clouds capabilities you additionally

have something called elastic pole so

you can actually have multiple single

databases that would share resources

across there are several scenarios where

you want it and we're going to talk

about those in couple of minutes and the

further option is MANET instance

so since single database is migrated SQL

Server to the cloud why would you want

manage instance because single database

one moved to the cloud had to get rid of

some features in order to be really

cloud ready solution but if you need a

full capabilities of this SQL Server and

you want to manage all those

capabilities by yourself magnet instance

is there for you of course it's the most

pricey of all the options so make sure

that you only use it for lift of shift

scenarios or where this set of features

that you need are only available on the

SQL Server on

premise otherwise always try to design

for a single database in a cloud so one

important thing to understand here is

when you're gonna be creating SQL

database in a cloud you're going to be

actually creating two objects one of

them is SQL Server database and the

second one is SQL database server so the

server think of it like a logical unit

it's a central administrative unit where

you can manage your single database or

pool database what kind of things like

logins firewall rules auditing threat

detection etc etc and a single server

can contain multiple databases if you've

been working with SQL Server on-premise

this is pretty much identical here and

other SQL database offer you to by

database in free pricing tiers or maybe

not pricing tiers by pricing models

first one is DTU this is the database

transaction unit it's Microsoft unit of

performance something like in the middle

between V cores memory IO ops and other

metrics that is really hard to estimate

this way and recently Microsoft

introduced a vicar base so you basically

by virtual course this is a currently

recommended way to buy it and it's also

easier to estimate if you're moving from

on-premises you most likely already know

how many virtual cores do you need for

your database to be really well suited

for this and lastly you have serverless

kunti this is a décor and I will talk

about services just a bit the major

difference here is that for DTU you

scale storage together with compute so

that twice the compute the twice the

storage you get which is not ideal

because even if you're not using that

storage you get twice the storage with

every pricing tier up but with V core

you can actually scale storage

independently from the compute so you

can say I want only five gigs of RAM and

five cores and then hundred of gigs of

memory

sorry storage or other way around you

can

say I want very small storage but I want

very powerful machine additionally

because this is a virtual car based

purchasing model you can use hybrid

benefit so if you have those SQL Server

licenses ready you can actually use them

in a cloud so we were talking about this

elastic pool when would use elastic pool

since I already said this is the shared

capacity that must involve more than one

database so if your database has very

uneven used during out the day maybe

more during the early stages of the day

and less during the later stages or

maybe very rarely it's used maybe for

reporting purposes only this will mean

that you need to scale your database to

the certain level for instance 20 DTU to

make sure your users will never feel

slow down so bit above what you need but

if you have two databases with similar

usage but maybe differently span across

the day the second database you will

also need to scale it up to 20 DT use as

you can imagine that means you're paying

for two databases for 48 years but what

would happen if you would over like this

and put it on a single diagram you would

get something like this as you can see

the usage spans really nicely across the

day across multiple databases even

though during one period of the time

you're over using 20 DQ's that means you

can actually still scale to 30 but fruit

I did use is still smaller smaller than

40 see Rhonda paying 40 30 dtu's in this

case so saving up money this elastic

pole allows you to share resources

across multiple databases

therefore it's perfect for scenarios

where you have many databases what would

be the scenario like this well for

instance developing and applications

like software is a service where you

spread a move to pull clients into

multiple databases that will unwittingly

use those databases so elastic pull is

perfect here and we were also talking

about the service

what is the service you would have

scenarios where your database will be

only one so we cannot share

sources because you don't have to share

it with anyone but you still have very

uneven usage maybe only during the first

part of the day use the database aim

maybe you don't during the second part

with several s you can actually say that

you want to scale database between one

and four V course like an automatic

scaling and with this knowledge

Microsoft RAC you use it and buy some

tracked usage you will pay only for what

you use but not X notice something

interesting you paid only for what you

use plus a bit more why is that because

you define that you pay minimum of one

week or so will pay for that but also

notice something need the end here the

diagram dropped and that's not my

mistake those actually period of

inactivity here and with several s you

can actually say that if your database

is inactive for some time please turn it

off and then you're gonna pay literally

zero of course with this model you're

gonna pay for bit more for usage so you

really nicely need to plan your usage

ahead if you don't know you can switch

it back and forth to see what you'll use

it is but if you do it design very

nicely and you have very rare use it of

database this might be very perfect

scenario for you so that's the service

pricing when it comes to additional

features database is all about data

quality and protection of your data

because data is country most valuable

asset on the planet

therefore SQL database comes with all

those availability features like

automatic backups point in time restores

so you just can one click and get back

in time to a specific time of your

database you have active to your

application across regions you have

failover groups and redundant databases

all those features are there for

Microsoft to provide you with this very

highly available database and all those

feature and are again for you to protect

your data store your data manage your

data and ensure quality of your data

with all that said we can actually go to

demos and then was for today for our

creation and overview of the database

connecting to database this time from

management studio but I will also show

you the latest tool from Microsoft

called our raw data studio so let's jump

right to it

so let's go into the portal this is a

new look of the portal just before they

ignite so now to create a new resource

you got here click on the create new

resources very similar experience and

you hit on SQL database or just search

for SQL database in a marketplace you

hit create when it pops up and of course

now we can start creating first of all

you always need to choose resource group

when you do it you need to provide the

database name this is again the database

within the SQL Server date so I'm gonna

choose it f-4e demo DB right and I need

to choose the server but since I don't

have any server now I also need to

create server during this step so I'm

hitting on create new and I need to

provide a name a for e DB server then I

need to provide an admin login a for e

admin I need to provide a password it's

matching and I need to provide location

as always I'm you choosing north Europe

because it's the closest data center to

me

heading ok and you have the check box do

you want to use elastic pull as we were

saying if you have this scenario where

you're gonna be sharing resources select

yes otherwise leave it at snow and very

important configure database because by

default what you're purchasing will be

generation 5 - v cores 32 gigs of

storage so it's quite big database if

you're just testing make sure to take

smaller hit on configure database and

you're presented with this UI first

important thing is to notice on the very

top you have the pricing tiers have

general-purpose

hyper scale and business critical but

also if you look here

looking for all their tears if you move

here you have basic standard and premium

basic standard premium rdt you based all

of them are DQ based notice that basic

is 5d tier

standard is from ten to three thousand e

tu and premium is from 125 to four

thousand DQ of course as soon as you

slide that slider you get the pricing

estimation for your database and if you

go to vicar based purchasing model you

have general purpose hypo scale and

business critical of course those are

the more beefier type of servers as you

can see the most difference is here with

the eye ops right from 7,000 IELTS on

general purpose so operate IO operations

per second so you'll disc speed for two

hundred thousand four hyper scale and

business critical so getting those SSD

drives really fast drive sir

so if general purpose you can choose how

many cars do you want

and here's also that serverless option

that we were talking about but I will

actually go and create the simple

databases I'm gonna choose the standard

maybe take 20 DQ and remember that we

are saying for the key you use scale

storage the same way you stay scale the

DTU so even if I move the slider to hunt

100 megabytes I'm still paying the same

as for 250 gigabytes because this is

already paid for so I'm just gonna leave

it and hit apply so right now I will be

creating an SQL server called a for e DB

server with one database inside a for e

de mode demo DB and that's gonna be

pricing standard s1 so basically

standard is this s and one is the

position on the slider so that will be

20 d tiers 250 gigs of storage hit

review and you can actually hit create

this will take about one minute

so I will actually skip ahead

okay so my database was created that

means I can actually go to my resource

group and see what was created if you

hit on this you'll go to resource group

and you will notice that you have your

SQL Server here I'll actually refresh I

actually had to refresh second time to

get the database so as we were creating

we got to resources and SQL Server

that's our centralized unit for managing

databases and an SQL database so let's

go into both to see what do we have her

in the database in the overview tab you

have the most important thing the status

online notice that there is no stop

button because you cannot stop database

the only tier that allows you to stop

database is the server list otherwise

you will pay for entire month of use it

regardless if you use it or not the only

thing you can do to reduce costs is

scale pricing tier you have the location

here you have your server name this is

the server that you will use for the

connection string to connect to database

there are no currently elastic pools

assigned you have the pricing tier here

and on the left hand side you have

couple cool features the query editor

allows you to connect to the database

from the browser and manage your

database I don't really recommend this

because this requires you to open public

IPS to be able to connect from the

browser so let's leave that for now

especially this is in preview so you

might not know when this will be taken

out or maybe production iced so what do

we need first of all configure so even

after creation of database you are

always allowed to change that fear back

to any weak or ordi cubase tears you can

go to zero replication if you want to

add some reed replicas you can do it

here and just select which region do you

want it you have connections shrink tab

so you can just copy your connectors and

string this stuff is pretty cool because

you have a do dotnet you have JDBC if

you're working with Java ODBC by PHP or

go even sample code to connect to this

database so it's pretty neat you have

also sync so you can actually sync to

other databases you can add a research

so integrate with many services have

advanced data production and some other

stuff about security but what we need so

far now let's do something simple let's

connect to this database so I'm going to

grab the server name and on the second

screen I'm gonna use SQL Server

management studio this free tool and

most of the people in the market knows

this tool if they were working with SQL

Server to connect you need to hit on

connect hit database engine paste in the

server name you need to place the name

and you have two options here you either

use SQL Server authentication this is

the account that you set up when

creating in our case this was a 4e admin

but you should actually never use this

you should always use Active Directory

Universal so your actual Active

Directory account to be able to log into

this server using this Active Directory

you need to first set yourself as an

admin to do so

you go back to the database here go to

the server and on the server you will

have the section here Active Directory

admin

what you just need to do is set admin so

now you can actually set admin but

notice interestingly I cannot use my own

account this is because my account is a

guest account in this Active Directory

so I need to use account from this

active directory like the one that I've

set up before so Adam demo at RF

everyone calm so you just hit on it it's

locked and remember to hit save now I

can actually log in as an administrator

to this SQL Server using my Active

Directory account that I created

previously so I just copy/paste this

name go back to SQL Server paste it hit

connect and notice interestingly I got a

pop up that SQL Server management studio

recognize that there's no fire will rule

allowing me to connect so currently

firewall is blocking me to connect to

the server and this makes sense because

SQL as we said is all about protecting

your data so it does not allow anyone

accessing by default so what do you need

to do right now is go to overview

and you need to go to server because our

we already said the firewall is managed

on a server level so you can actually

find the several level firewall settings

here firewalls and virtual networks in

here you have the client IP address

that's my current IP my current public

IP which I need to add in order to be

able to connect to database to do so I

cannot just click this add client IP

which will add additional row in this

table and I can just call it atom home

this way I will know that this IP is my

home IP that I will be connecting from

and I just can hit save since my IP is

dynamic I might need to come back here

in future and change it but for now this

should be okay so if I go back here next

console is and hit connect again right

now I connected the reason they didn't

ask me for credentials because I was

already connecting using this account

previously so it remembered my

credentials but of course if I open my

database I see my a for each demo DB and

then can just click new query and start

querying my database of course within

that database out of the box

are no tables no views no nothing so

what we can do right now is actually

create some views and maybe create a

table okay

so let's create that person's table that

we were using during the demo so it

would be create table statement persons

free columns and some constraints just

gonna run it comment completed

successfully you can refresh tables and

see your table being there if you can

expand it see what are the columns what

are the constraints everything that just

created and if you want to add an

additional role to this table you can

simply add a query insert into person's

first name last name and age and input

some values if I would type a

Martok 32 this will actually fail

because if I run this now it will

conflict with the constraint that we

said because the name has to be longer

than two three letters say if I'm gonna

type Adam and just run it it runs

successfully you can now just select

from the table to see our data there

this is as simple as it gets

because database is about putting date

other think figure on to your database

model for business needs and just using

it so the very last amount that we want

to do is pretty much similar to this

let's open the same scenario using Azure

data studio our data studio is the

newest tool from Microsoft allowing you

to also connect and work with other SQL

database so this looks like like that

and if you been working with SQL and

you've been working with Visual Studio

code you might recognize this is very

similar you why so what do you need to

do here you need to select connection

type SQL Server you need to provide

server name

so let's actually grab this from our

portal so I'll go should go back to

portal go to the overview go to the

actually here's the server name so let's

copy that let's go to connection here

and of course we're not using SQL server

login we're using Active Directory login

so we need to add an account when you

add an account you will get this pop up

you need to go to this website so let's

go to this website f11 open this website

it asks you to provide a code so you

need to provide this code and login and

now it asks you do you want to use the

account that you're currently signed in

if I will be using this to connect I

could do so but I need that account that

I specified as an admin so I'm gonna

remove this and copy my account so

so let's paste that in that was our Adam

demo account go next provide a password

and just copy the password and it says

you have signed into our data studio you

can actually close this tab at that

point full screen that and go back to

the data studio notice that I'm already

logged in if this works I can actually

expand database section to get the list

of databases because I got monster and a

4e demo DB that means I locked

successfully using this account I can

actually hit connect and right now I'm

in the database the same way I did using

data studio the interface is quite

different but I like this interface much

more than the visual studio sorry than

the management studio because it has new

features it scales nicely with UI

because you just hint Lee simply hit f1

go zoom in and entire interface zooms in

and there's new features coming in

because you have some notebooks queries

backups restoring an additional feature

coming in you have even get integration

from here but this tool is very similar

as you see you can just select from your

table and just run it same stuff with

management studio same stuff here

this tool is just much simpler but one

of the cool feature is that first of all

it remembers connections so you can

actually save those connections for

later use and if you're connecting to

more databases when you're adding more

servers you are also already logged in

that means if you're using same account

across multiple databases you will only

have to log in once to connect to all of

them there's a lot of good features here

so I really advise you to use data

studio so if we go back to the portal

what else do we have here of course on a

server level had a bit more options you

have SQL elastic pools here if you want

to you can see or deleted databases if

you delete some you can track your

quotas here you can import and export

database a lot of stuff you can manage

backups here so by

default your database is only backed up

seven days you just click here configure

attention and change it to any value you

want for instance thirty five or maybe

you want to do some long-term retention

and type 365 for entire way year of

backups or simply just one year right so

this is how long your backups will be

configured and it's just few presses of

a button hit apply and it's done there's

quite more features here but to

summarize the most important one always

set yourselves as an admin to database

so you can connect using your account

literally you can actually forget about

that Active Directory had been accounted

should really never use it anyway

remember about firewall rules because by

default it will not allow you to connect

this goes for all the public services as

well if you're using data factory load

gaps your web application you need to

make sure that the I piece of those

services are added here as a rules

otherwise you won't be able to connect

there are so many services in Azure but

when it comes to database I really

stressed out what are the pricing tiers

what are the deployment models that you

can use because it's one of the critical

topics for you to choose when choosing

SQL database it's a big topic there's

gonna be more videos on it but today

that was the quick introduction into our

SQL database now you know when to use it

how to use it how to connect to it Kri

to it so I guess it's up to you to start

using it now for today that's it

if you liked the video hit thumbs up

leave a comment subscribe if you want to

see more and see you next time

[Music]