Microsoft Azure Training - [44] Azure SQL Database - Part 1 - Introduction

hi everyone welcome to cloud Rangers
Microsoft Azure training as you know I'm
Shana smell like Microsoft Azure MVP and
that's my contact information below
there my email Twitter blog LinkedIn
YouTube where you are most likely
viewing this right now and the slides
that slideshare.net where I'm going to
post the slides when we are done with
this session so today we are going to
talk about as your sequel database and
this is the part one introduction and
there's a lot of theory concepts in this
session unfortunately because we have to
go through all the concepts before we
can get to the demo or get to all the
offerings that Microsoft sequel azure
has to provide and in this session we
are going to have a table - how to
create a sequel database however at the
end ok but let's get to some not really
complicated but some complex concepts
that we have with sequel leisure and I
mean as your sequel and what I'm going
to do is I'm trying to make this as
simple as possible even the hard topic
so I'm hoping that I did the background
work to make this as simple for you ok
so let's get started so as your provides
a range of services when it comes to
managing data and technically in the
curriculum you'll notice sequel
databases fall under Azure storage
because it is some sort of storage right
you are storage you're storing data and
so far in storage we have done the blob
storage we need some you know queue we
did we actually even did table and which
was interesting there's no schema there
and we did some others like that but
this one is more about the sequel
database or what we call the relational
database so most businesses rely on some
sort of relational databases these days
let that be Microsoft sequel MySQL or
Oracle or whatever these are all
relational databases means there is a
collection of two dimensional tables
where you will hear about rows and
columns and what's happening here is
that in Microsoft Azure sequel database
we have a relational database management
as a service offering here ok so this is
actually a database as a service
can actually call that and it is based
on Microsoft SQL Server engine so this
service is not based on MySQL or Oracle
or anybody else which is not a really
big surprise given that you know this is
a Microsoft offering it's Microsoft
cloud offering ok so we need to do two
things here
so Microsoft Azure provides two basic
types of relational database services
now that when I'm looking at this slide
I'm thinking maybe I should put the
second one in the first one and first
one in the second one for the sake of
simplicity but let's start with the
second one infrastructure as a service
they're really two platform as a service
and infrastructure as a service let's
start with infrastructure as a service
okay so this is what we know let's say
you're trying to create a sequel server
on a physical server or hyper-v or
VMware virtual machine or in Azure and
infrastructure as a service VM this is
what we know we create a VM we join it
to the domain and we go install
Microsoft sequel server we go and we
need all the regular management tasks we
need to make sure the server is patched
server is updated we need to maintain
all of that we need to make sure the
hardware does not fail it's
well-maintained we need to also make
sure there's business continuty like
things like backups things like dr
scenarios that we have to account for
and it's licensed based so you've
licensed sequel servers per core I
believe now in sequel 2012 2014 and now
come 2016 so that's infrastructure as a
service option but what we are trying to
say is that forget all of that we are
going to go platform as a service here
the database is as a service it
eliminates the requirements to manage
the underlying operating system and the
database server platform what it means
is that you do not have to create a
server you do not have to install sequel
all you have to do is just go create the
database like it's as simple as possible
and it frees all the admin tasks from
all the server management things like
update patching you don't have to worry
about all this you don't have to worry
about underlying hardware you have to
worry about no sequel installation
updates it will do that for you and
guess what it also has built-in
back up and business continuing services
so it will do the backups for you based
on how you configure it it will also
have the dr setup which is quite hard to
do on premises if you guys actually have
done a dr scenario disaster recovery
scenario for your on-premise solution it
wasn't easy
you have to get a different location
different data center you have to create
like some sort of VPN tunnel between
them you have to do sequel always on or
data mirroring it's quite complicated
but sequel database for measure as a
service comes with all of that in there
and it's built hourly means if you are
using a database today you don't use it
tomorrow you don't pay for it you can
have as many databases created as you
need and all will be paid hourly so
obviously there is like a lot of benefit
going to platform as a service and
that's what today's session is all about
ok ok so we are going to talk about
service tears and this is one of the
most important topics to understand and
the bit complex as well when it comes to
as your sequel database ok so multiple
service tiers are offered by as your
sequel to handle different workloads and
the service tiers basically determine
what are the capabilities or performance
and price of hosting or getting the
database as a service okay so in
on-premise environment or even as your
infrastructure as a service VMs you can
install sequel and when you install
sequel there are a couple of things you
take care of you make sure you are
putting in the right CTU you're putting
in the right memory you're putting in
the right disks like regular disk or
solid state disks and what your database
administrator will tell you or if you
are database administrator you think is
that I want the maximum performance of
sequel I can get out of this right and
it has a lot of application dependency
like your application might be something
as simple as a telephone book where you
know people are just going and looking
up people's numbers or addresses or
whatever or it can be a mission critical
application where people are making
hundreds of queries per second to
get some sort of information like a
stock exchange or whatever okay so your
application will determine the kind of
hardware or resources you need for your
database with on-premises there are a
lot of ways to do that and definitely
I'm not an expert to explain that but
there are a lot of ways to you know
benchmark what kind of sequel
environment you need in as your sequel
obviously since you are not creating the
virtual machines yourself it's a little
hard to determine that and that's why as
you're created different service tiers
to sort of gauge what kind of
performance you want per database basis
or in a pool so one thing I would let
you know is that since this is the cloud
what happens is that you can have a
database created and you can put a
service tier on it and you can change
that service tier at any given time if
you find out that today you have 50
people making queries to the database
and a month later it's becoming 500 you
can change the service tier to something
else than what you started off with and
there's a minimal downtime like we are
talking about almost nothing less than
four seconds or something like that okay
so there are three service chairs out of
the box one is basic one is a standard
and what is premium so these are the
service tiers and this service tiers can
have multiple performance levels and
what it means by is that if you look at
this table there's a basic and that's
the service tier and that's the
performance level but the next here
which is the standard here it has for
performance level is 0 to s3 right and
with premium you have multiple as well
so each service tier can have multiple
performance level we almost call it call
it as like the power to your database
like what kind of power your database
needs ok now before I get into what kind
of power your database needs and of what
this power is denoted by I want to talk
about how the service tiers are applied
your service tiers are applied at two
levels one is a single database level
and one is at a multiple database the
multiple databases level so with single
database each database can have its own
tier and it's all
limit so what will happen is that you
create just one database let's say my
database one that's the name of your
database you say that this database will
require a certain amount of resources
and I'm going to assign basic tier to it
and the next database you create you
make it like standard database because
you need more resources and you say it's
going to be standard s3 based on some
resources you know the amount of
resources it needs okay so this is
almost like each database will will
function independently of other
databases so if you have one database
which is like basic data can be standard
another can be premium another can be
standardized - so all these databases
will have their different tiers like
they're independent of each other and
they're running on their own servers in
reality they're probably not running on
their own servers but the resources the
way it will be assigned to them and how
isolated they will be it will feel like
they're all running on own servers with
different resources on that server okay
this kind of single database is good for
when you have some consistent type of
load like you have a database which you
know is going to run not all the time or
maybe all the time so there's some
predictability like if you know it's if
let's say it's a business address book
or it's an HR directory you know that
people are going to query every now and
then versus let's say it's a stock
exchange with some you know stock ticker
info in a database you know that people
are going to be on there all the time
hundreds or thousands of transactions
per second so these are for databases
where you understand how the usage
pattern is going to be okay then you
have multiple databases multiple
databases is pretty simple as well it's
basically you are taking a bunch of
single databases you're putting them in
a pool let's say it's a container and on
the container you put that here like
it's gonna be basic standard and premium
so what this will do is it's basically
just going to share all those resources
okay and I'll get to that in the next
slide what we mean by sharing all these
resources because in Azure sequel
database this is probably one of the
most complex concept what we are talking
about right now not the single database
or the multiple data
but how the resources are assigned to it
okay so let's talk about a little bit
about this table over here so I have
mentioned already there are three tiers
basic standard and premium and some of
the tiers have different performance
levels and what we are talking about
over here
is basically the power that is given to
the databases the most important number
over here is the dtu's and the next
slide will explain what dtu's are but
basically it's the unit of power you're
giving to this databases and it has
other requirements of resources as well
like either basic tier your maximum
database sides not going to be more than
two gigs you are not going to have more
than 300 concurrent sessions so
depending on this table you have a basic
idea of how much resources you need to
put on your database should that be a
basic tier or a standard or a premium
should that be a standard a s1 or s3 or
maybe a premium with p1 performance
level so you have to determine that and
the way you determine that is based on
the dtu's and I'll tell you in a bit
once again what did use are because
that's probably the most complex concept
you are going to take from this session
an understanding that is very important
not only for your exams your real life I
mean I by no means I'm like a sequel
expert but I'll try to make this as
simple as possible for you you're going
to have that happy filling by the time
you're done with the details over here
but basically you say I'm going to
create one database which will need
about let's say 30 dtu's so you look
over here and say that ok if I need 30
is between 20 and 50 so maybe I'll need
a s2 database for my single database
with multiple databases what you'll do
is let's say you have three databases
you created you take all three and you
put them in a container and you say
these databases are going to share
resources but there let's say never
going to go more than 100 so you are
going to put a standard s3 100 on the
database pull all the databases are
going to share that 100 ok and as long
as let's say you have three databases
and none of the go more than 100 any
given time you'll refine and I'll show
you the calculation in a bit
okay so let's leave it at this right now
and move to the next slide in the next
slide we are going to talk about the
dtu's the database transaction unit so
what is database transaction unit so so
far I have used the word power few times
and performance tears many many times
actually so the three service tears that
we talked about have multiple
performance levels like we said right
the resources kept increasing as we
looked at the table each performance
level provides an increasing set of
resources or power to the databases and
it in turn delivers increasing
throughput so what we mean is that as we
increase the power or increase the peers
and the performance levels you are going
to get more out of this databases like
the throughput which is basically
transaction per unit of time let that be
transaction per seconds or minutes
whatever but what we are interested is
really transactions per second so this
power is technically what DTU or
database transaction unit is and it just
in unit the Microsoft created to
simplify how as your sequel database
power would be calculated and in general
what we say is that increase of power is
basically increased database performance
and power of each service tier and
performance level will be expressed in
video so I have a document over here
which kind of well that link which kind
of says how the DTU was determined or
how did Microsoft come up with this unit
technically it's a benchmark means
Microsoft did some tests and came up
with that number and it said that what
does each number met so for choosing the
amount of resources you need for your
database you'll have to go through this
TTU and find out how many dtu's your
your your database will need and I'm
going to tell you a little bit about the
benchmark this is a little bit of topic
and how did they do it but I think it's
interesting so like I mentioned you is
like a benchmark okay to develop this
what Microsoft did was it took s
of operations like typical for wall TP
requests like you know select queries
insert queries and made it the minimum
or made it as atomic as possible and
what it did was it ran these queries at
any given time and it saw has it meant
measured how many transactions could be
done at this tier level or it's
different performance level in a in a in
each performance level in a fully loaded
condition so let me go back to this
table and show you so let's say
Microsoft took a bunch of transactions
and they give them similar you know it
gives them a similar environment and
it's said that ok this is the basic tier
I'm going to run this operations right
now and see how many of these operations
can be done in five in a second given
the conditions I have so these
conditions are really not public means
they have not said that how much of
resources were given but what they found
out is that given those conditions in a
second in a basic tier you could get up
to five details or database transaction
rates or five videos over there in
similar condition if you know if you go
and try to add it Microsoft wanted put
these resources in there as well for
those I guess those could be changed but
they just put that over there so then
they went and changed the scenario and
put it put more resources or whatever in
there and it went and saw that you know
in s0 performance level which is a
standard tier performance level they
found about ten transactions they could
do per second given those you know those
those resources that they provided there
and these are typical rate I mean these
were typical transactions like select
insert or whatever and they did that and
they went and measured these performance
levels in there
the way I look at it I still called look
at details as transactions per second
it's probably the closest you can come
do clarifying or understanding what it
means it's definitely a little bit more
complex than that but what I feel is
that if you have an application which
requires level 100 transaction per
second maybe you need to look at the BTU
with 100 which will be a standard is 3
performance level that's at least the
most simplest way I can think of now
that's not even the complex one the
complex thing is that ok let's say you
have an on-premise database you will
wonder that ok how will I determine my
own premise database running today in
that environment I'm going to migrate it
to Azure and I don't want a VM I want
the super-awesome measure sequel
database how will I find out what DTU is
required because transaction per second
is not really the easiest and the most
simplest thing to determine right so the
good part was that Microsoft created
something called as as your sequel
database DTU calculator and this is the
link that is there it's a very nice tool
where you take this bunch of PowerShell
script and you run it on your on-premise
environment or even it could be on your
as your VM and you calculate based on
the performance it collects from your
environment or that server it will be
able to tell you that hey this is the
amount of DTU you typically need for
running this database I think that's one
of the best things Microsoft came up
with for helping out with these details
there but if you don't have a database
where you can run this tool and get that
then you might have to try and dial up
and down a little bit to calculate how
much DTU you can get and in the portal
you will see when you run this database
Microsoft actually gives you an advisory
telling you hey looks like you set this
on a twenty day to you but your
application requires a little bit more
significantly at given times of the day
because the usage goes up and down for
example if it's a stock exchange most
likely you have the most amount of
queries done for the tickers in the day
time than in the night time because most
people are probably checking this during
the day time so what it will do is it
will be able to tell you
that hey this is the maximum Duty you
it's requiring so you might want to dial
this up a little bit or quite a bit
depending on that so once again I want
you to think of DTU as you know
transaction per second that's probably
not the best I mean it's definitely not
the best explanation but it's the
closest you're going to come to
depending on how Microsoft did the
benchmark and everything else so this
article from Microsoft Deena's your
documentation sequel database benchmark
overview actually tells you how they
went and performed it and basically it
was a bunch of queries give it at a
certain time to certain set of resources
which determined those numbers okay I
hope that helps you understand this a
little bit now that was for single
databases by the way so this database
transaction unit was all for single
database however when we are talking
about the database pools things are a
little different and in the database
pull rather than dtu's we use EDT use
okay or elastic videos this is a little
bit of card this is this definitely
complex but I'm going to give it a shot
and we are going to get through this
together okay easy to use are exactly
like did use but you are actually taking
these details and putting it in a pool
of databases okay so like I mentioned
elastic databases or elastic you know
database units the way this works is
that you have one database and you say
okay I understand or this database is
very predictable like I get this example
couple of slides ago they say you know
address book it's slow it's not going to
be queried all the time I'm going to
just give it a basic care maximum you
know five details the database size is
never going to be more than two gigs I'm
good to go
or you might come and say it's a very
busy database but it's still printable
like I know that let's say the stock
ticker for a stock exchange a lot of
queries in the day time hundreds of
transactions per second I'm going to
give it a high D to you like maybe a
premium tier and let it have all its
details it's still predictable
but this is where the problem is that's
talk database the stock exchange
database is probably not going to be as
busy in the night time so you give it a
premium you give it a premium DT you or
premium tier but you are saying that you
know I'm only really looking at like
let's say 1000 dtu's in the morning
because 1,000 transactions will happen
per second but rest of the night
maybe it goes down to 100 so there is no
way for you to do auto scaling on this
one yeah at night you could go and
change the tier like in this slide we
said that service tears can be changed
at any time so at night you might say
all right I'm going to take this down
from a premium to a standard you know s3
performance level so this is a single
database you're tuning it as you want
this is not necessarily the best
scenario all the time and you want to
have a little bit of flexibility you
want to put you know multiple things
together and work out something and
that's where the multiple databases or
lastic databases came into picture and
the way this works is that how you have
virtual machines on your own premise
environments you remember the whole idea
about for having virtual machines on
VMware or hyper-v was what once that hey
all these VMs are going to be using the
same physical server but they're going
to we are go to counter on the fact that
all of them are not going to be busy and
requiring all resources all the time so
you will share all these resources
between these all VMs the concept is
similar with elastic database pools as
well you say okay I have a contact book
address list database I have this talk
exchange at database I have three and
four other databases okay and these
databases are not all busy all the time
so what I'm going to do is I'm going to
create a container or this is called the
database pool and I'm going to assign
still a basic standard or premium tier
to it and which makes sense it's not
like you can have a database that would
go
all the way from 0 to 1,000 at any given
time they still have the restrictions of
a single database based on the tier you
put on them let the basic premium
whatever but they're not going to do
this all the time okay so for an example
let's look at the next slide here I'll
come back to the slide over here we have
one database here that is and this is
the DTU utilization based on time so the
way this is working is that from 12
o'clock to 12:05 you can see it went up
to 50 dtu's and then 1210 it went to 90
DDoS and rest of the time you know it
never really crossed 50 the average by
looking at this graph over here I can
say the average was you know like less
than ten DDoS it goes up to ten ninety
but rest of the time really this
database is not really as busy so if we
created a single database which tier
would this be in so let's go look at our
table here we saw it go up to ninety
details right so looking at this we can
say let's assign a standard s3 on this
database because we know it goes up to
90 DDoS but this is the problem though
looking at this graph you can see most
of the time it did not go up to 90 you
have to put it on a s3 which is actually
100 because you're you're saying that
yeah it might have a spike so I need
that right I mean if it's let's say you
did not have it up to s3 you had
something like 50 over here this point
over here would actually not work sorry
jumped really quick at this point would
actually not work and the reason it
would not work is because you don't have
that kind of DDoS and you are also
putting an s3 so you are actually losing
a little bit of you know money in there
because your s3 will cost you obviously
differently than any other performance
level each performance level is built
differently there's a different amount
on there so sure you could put it s3 but
it's going to go to cost you a little
bit this could be
West so let's look at another scenario
over here
where we have four databases all over
here okay so with four databases the
thing and my slide is moving over here a
little bit just give me a second
so with four databases where you can see
over here is that they're all still
hitting up to 19 but they're all doing
that at a different time so technically
you could take this four databases and
put each database
you know separately and is three
performance level here
or you can tell them I can put them all
in a bucket and just put one s
three-tier on them and the way this
would work is that they all will be
requiring at least close to hundred
dtu's but not at the same time so they
can share that okay now this is the
thing though sure they're going to be as
three and what happens if they're all
doing this at the same time will this
work so looking at this table this one
goes up to 100 you can obviously have
multiple databases over here but in this
scenario it would probably not work
because you know single database would
go up 200 multiple databases 100 you
have to make them work separately if you
want them to all hit 100 at the same
time or there will be a possibility you
need to at least go for a premium P for
500 where they all can go there at the
same time because at any given time that
would be the d2 your requirement or at
least during the peak so over here the
table for the EDT use is kind of similar
because it has the same clears but you
can see easy to use purple so in our
scenario if we say that this database is
not going to hit 90 all at the same time
we can put them all in the sample and
put basic tier with 100 as the DTO now
if you come back and say all right Sean
but there are times all four databases
might require that then we'll say that
hey you probably made something up to
400 it will still be same tier but we
are going to increase the performance
level to 400 details because those
databases might go up there at any given
time concurrently so if they are not
going to do this concurrently you can
stay at 100 and if they do concurrency
I mean concurrently you can go up to 400
or 800 or even more based on what the
requirements are right because you DT
use or data use are not the only thing
that changes here
let's see basic also had 100 and the
standard also has 100 but look at other
resources like the database size they
change quite a bit a lot of other things
changes but you can get away with that
and the price is considerably cheaper so
rather than this for databases all
having like one one at a time like
having an s3 they can just be on a basic
pool and or a standard pool and it still
be cheaper and I'm going to get the
calculator out for the as your pricing
calculator to show you in fact one last
example over here you could get away
with DTU utilization with a single pool
even with 20 databases based on this
scenario if you look at this graph none
of them are actually hitting the hundred
utilization all at a give different time
this is 20 databases all at different
times reaching that same EDT you or the
detail utilization so I think it's a
pretty good example of how you can use a
single pull to host multiple databases
and share the resources end of the day
this is for just making it cost
effective if you have all the resources
and you know you know saving cost is not
really an issue you can have a single
database sitting at premium idle at
night where you don't care that's fine
but this is for people who want to make
the most out of the elasticity when it
comes to as you're okay and I'm going to
pull up the calculator and show you a
little bit of a calculation here just
give me a moment
so this is the URL for the pricing
calculator as your Microsoft calm it's
English us pricing calculator so in here
what we are going to do is basically
look at the pricing for sequel databases
okay and the way I did that over here
was that let's actually hit enter over
here to show you how this is done when
you load this page I just went to like
the most popular one and went to sequel
databases you can actually view
estimates for many other things over
here this is how you really calculate
you know how much your things are going
to cost so over here I mean east us
let's put at single database I'm not
going to look at the elastic okay these
are the two options single database and
let's say I'm going to have a standard
here and we know that standard here has
multiple performance level right so
these are all the different performance
level for standard here is three so
let's say I'm going to use s3 or hundred
dd use okay in hundred DT use for one
database seven forty four hours so which
basically you know it's pretty much
considering the entire month here so
it's going to cost one database if you
do go in the single database way in s3
one hundred and fifty dollars per month
okay change your I mean obviously I'm
using US dollars here
you can change to whatever wherever you
are if I have two databases it's going
to almost cost me double like 300 per
month so that is two is three databases
with one hundred details let's say I'm
going to have one database or two
database one database but I'm not going
to the single database way right I'm
going to use the last database inelastic
database again it's going to be standard
and I'm going to use elastic 100 DB use
you'll notice for elastic database for
one database it actually costs more okay
it's not $150 it's $200 so it probably
does not make sense for you to take one
database and go to a elastic pool
because it
going to cost you more but look at this
what I have like two databases over here
if I have two databases I'm going to
come over here actually no that's fine
if I have two databases it's going to
cost me one Lee $223 okay if you recall
the calculation over here when it was
single database and there was two
databases it was costing me $300 but if
it's elastic database and I take this
one and this is a pull do not change
that okay this is just one pole we would
have put two databases in single pole
it's going to cost me two hundred twenty
three dollars only so as soon as I'm
sharing the 100 details the price
actually becomes lower if I actually
make three it will be more lower per
database the only catch is these
databases cannot all require the same
resources all at the same time because
then 100 do is not going to be good
we'll have to let's say get 200 edu then
the price will actually go up so two
things I want to give you finally over
here from this discussion one is you
need to determine if your database will
be single database or elastic you need
to determine how much your DTU
requirement is per database you need to
determine that you know if your
databases will require you to be I mean
they can share right I mean they are not
going to be all requiring the same dtu's
or same resources at any given time
together simultaneously because that's
not going to work
so that was one the second thing you
have to look at the pricing so when it's
a single database with one data you know
one database it costs you $150 but look
as soon as you make it elastic one pull
one database over here it actually costs
you more so this is not just the wallet
catch you have with one day two days
over here you might notice there are
more combinations or more math that goes
behind what you will find that there may
be a time when having a say
the database it's probably more
cost-effective than having the mini pool
so that these are the complex
calculations or these are the complex
you know thought processes that you need
to have before jumping into putting them
in a single or putting them in a
duplicate or a container as a pool with
all the databases in there so this is a
little complex like I mentioned for me
to get my head around this took a little
bit of time when I was learning this and
things actually have become much simpler
with with the pool it was in preview for
a while so organizations are finding it
much easier with this one's my rule of
thumb if you want to ask my personal
experience from the field is that go
with a single database first because you
already have one thing you have to worry
about which is that how much DTU it is
so once you have figured out how many
dtu's it really requires and you have
multiple databases and you figure out
all the details they require the portal
actually shows you very nice graphs and
analytics with advisories like what you
need to do in there so you can then take
those different usage patterns and then
determine if you can put them in a pool
or not so some people are very confident
and really good at this map really good
at figuring up the usage they go with
the pool right away say that hey I need
three databases I know what their usage
is going to be I'm going to put them
together I'm going to assign it et tu to
them and we are good to go for me I like
taking one database making it a standard
database and taking it from there
calculating everything and then
eventually putting them in different not
even one pool like you could have
multiple tools you might have a pool
with 100 as ddu we have another pool
with 200 basic tiers then you have
another tool with premium you know
databases in there so you can have
multiple pools as well
it just all comes down to knowing the
usage and how much it's going to cost
you okay so I hope this was really nice
and simple for you I try to explain this
as well as I could repeat the videos if
you need to simplify but I thought those
were the most simplest simplest ways to
explain this all right
so let's go to our demo right now where
I'm actually going to create a database
and we are going to go to the program to
do this so let's go to my client
computer here so in this demo what I'm
going to do is I'm going to have
everything done in I'm going to have
everything done in the portal and not
use PowerShell I'm going to leave that
for a different session but let's get
started so I do not in my resource group
I have nothing over here so the first
thing you need to do obvious they have a
subscription the first thing you need to
do is create a sequel server that this
is not like a typical sequel server I
have this over here if you don't have it
and by the way actually let's explain
this I explained this before or someone
emailed me anyways so the reason I see
things over here differently than yours
is because that's how I customized and
put them there the way you would see and
these are the ones I'm using most
commonly and in this session I was going
to use bunch of sequel stuff so I went
and put them over here in a different
session I might have something else the
way you put things over here is by going
to browse you go to browse these are all
the things that are available to you
okay so let's say I wanted to know
something about all my virtual machines
out well I already have that so that's
why you can see the Goldstar here let's
say I want to have something like let's
go up in here
I'll just see everything with my
actually I have everything I really
wanted over there I want to see
everything with my C over here
oh my jewel networks all my
subscriptions yeah let's see that so I
want to see how my subscriptions are so
I'll come down over here go out my
subscriptions I actually have my
subscription already over here as well
anyways you get the idea you need
anything from here
you don't see on your one put a gold
star on there it will show up over here
okay let's search services out as soon
as do that you can see the search
services showed up over here if you want
to remove them you take them out from
there as well okay so I went and put the
ones that are more relevant to the
sessions we have like having virtual
machines virtual networks storage it's
important for me sequel servers
so or you can go to new over here and
search for something as well if you
don't find it like you could go over
here and do a sequel server and come
with all the sequel server things that
you need from there as well so anyways
let's first go and create a sequel
server this sequel server is not like a
mystery Racal magical VM that's being
created behind no this is more like a
container like you have multiple
databases that can sit on a sequel
server right so this is think of it as
more of a container than a server itself
so before you create a database you want
to have a sequel server so the way you
do that is go into sequel servers go to
add and it's going to ask you for
information here this sequel server
actually has to be like it has to have
any unique name because it will be
sequel server or database that
windows.net this is how you will be
connecting to your database from your
application or from your management
studio I'll get that on next session but
this is how you'll be so let's call mine
Ranger sequel a survey ok the reason I
put Ranger before and actually somebody
emailed me about that as well the reason
I put Ranger before all these names
rather than just a sequel server
obviously want to make this unique and
the second thing is the way I work is
that if I put a custom name like Ranger
before everything when I have all the
resources I know that that's something I
have created rather than someone
creating that for me from the system
okay so my server is good and it's
available over here it's called Ranger
sequel a serving I'm going to put a
username and password for this server
which I'll be needing for creating my
database it's actually going to look for
the password twice ok this is my
subscription I do not have a resource
group so I'm going to go create a new
resource group I'll call it Ranger
sequel resource group ok be creative
with yours and it's going to ask for a
location my location is east us too and
with this one I'm going to give this
checkbox I'll explain that in a bit once
the service created what it means allow
as your services to access server ok
let's go create this actually does not
take too long this server
we'll be done really really shortly over
here
so just refresh I mean it's not really
creating a virtual machine or anything
at the backend so it should be done
relatively soon okay so the deployment
must have succeeded by now all right so
I have a server already let's go in
there see what's available in there
you'll see that surprisingly not a whole
lot one it says there's no databases on
this server - if I go to properties here
it will show me like the server name
showing the server login you know it's
not want to Active Directory or anything
like that nothing in there this is the
thing I wanted to show you the firewall
by default
whichever computer I use to get to the
browser it went down added me to the
firewall so I mean it did not add me to
the firewall but it's telling me that
where I'm connecting from so this is
technically my IP address but if you
look at the firewall rules configured
it's not there okay allow access to
azure services which we did as a
checkbox was because we want this portal
to have access to the database which we
do so if we create the database any
database right now it actually will be
able to create it on this server because
we keep this one as on so we know that
this portal is allowed the reason they
are actually displaying this over here
is because that's probably the first
thing you want to do right I mean you
want to get on a database I mean
database Explorer or something or your
application from your computer to do
this in production environment you're
going to probably have like everybody
open to this one if you wanted them
allow I mean if you wanted them to get
to your database but that's probably not
going to be the case in production you
probably will have a web app or an
application running on a server which
has its own IP address public address
you should only allow that not everybody
okay because this is available to the
public we just saw that there was a
Public DNS entry to get to this database
as a security precaution you want to
make sure that
you only allow the servers or the
clients that you want to connect that's
why by default there no nobody allowed
to this database okay I'll show you the
details of or the implications of this
in the next session but in here
conveniently it gives me my computer
from where I'm running this browser to
put it in here okay if I really wanted
to do that I would have done that right
now I'm going to leave this loan in this
database there's also other things like
you know auditing like who's connecting
was not connecting those things you can
turn on and off over here but what I'd
really want to show you over here is
that there's not a whole lot that you
can do over here at this point okay all
right let's close this all over here so
that's my server now let's get to
actually creating it database the way to
do that is after you create the server
go to sequel databases in sequel
databases there's no database over here
so let's go ahead and click on add in
add I'm going to create a database and
I'm going to it's going to look for a
database name I'm going to call it my DB
one okay this might get this one it's
asking that do you have a resource group
yeah well this is the resource group
it's asking select source so what it
means by that is that do you want this
to a blank database or Microsoft gives
you like a sample database like do you
want a sample and this is the
adventureworks so if you guys already
worked with creating databases
on-premises you know that you can
download a sample database to work with
or you can do this from a backup like
you know get it get it back up from
somewhere else and get this back up over
here and restore it on this database so
I'm going to say let's go ahead and
create a sample database where you know
it's still a database but adventureworks
it's pre populated database that
Microsoft provides you over there
it's looking for the server admin login
so right now it's looking for the
servers we have only one server so it
went and picked on that it's called
Ranger sequel server the one that we
created over here and if you remember
when we created it it was looking for
some credentials those are the
credentials you need so if you have
multiple servers created or even one
server like I do you have to have that
credentials to create a database on the
server okay now comes the tear if you
recall that when we were creating the
database I mentioned that you also have
to give it a tear so what here do you
want so these are all the tears that
table we had basic we had standard and
we had premium and premium and standard
actually had different you know
performance levels so I'm going to go
with something very small okay I'm going
to go with the s zero hundred DD use my
database can go up to two hundred fifty
gigs so so for an example I'll just go
do that okay so that's all I need so I'm
going to hit on create it's going to
take a little bit of time to create this
I think it takes a couple of minutes so
I'm going to pause the video and come
back once it's created alright so we
take it took about a couple of minutes
but it went it created my database so
look look at here so it's my database
and once you go over here you can see
activity logs you can actually see
performance so you this is a good thing
you can see how much T to use or stuff
your database is requesting so you can
see over here I just created right now
so there's not a whole lot going on here
this is the thing I like a lot its
recommendations it so after a while when
Microsoft Azure analyzes your database a
little bit it can start giving
recommendations that what performance
here you need to be and things like that
good properties it actually shows the
properties is tended to use one lien max
size of database 250 and just like that
we actually have a database created
right now okay so this is a database
over here so what we can do is create
another database so like I said you can
create as many databases as you want
this my database - I'm going to make
this a blank database maybe it's going
to be a ranger sequel server so this
could be on the same server because
that's the volley server we have no
let's see the pricing we'll make it a
little different last one we made it is
0 this one will keep pace 3 yeah sure
and over here we'll do a create let's
see it's actually creating another
database ok so this one is a standard a
0 will be another one in standard s3 so
couple of things one we can
as many as we want and the second thing
is how do you actually connect to this
database we know that there's a sample
database in there right but how do you
connect to that so one way is to
actually use the Microsoft sequel server
management studio this is a free
download you can now get online before
it was not free but it's something you
can get free right now so just go Bing
or Google for it menu sequel server 2016
management studio and you should get it
ok
so what I'm going to do over here is how
do you connect to this database the way
you connect to this database is through
your sequel server and if you remember
in the sequel server when we went to the
when we are creating it or when you go
to the properties it actually shows you
the sequel server name Ranger sequel
serve dot database dot windows.net so
you want to take this copy this over
here when you're when you have
downloaded Microsoft sequel server
management studio this is the first
thing that will come up so the server
name you want is paste it over here the
reason Shawn is over here is because I
tried connect it to something else
before this session but then it's going
to look for your login your password
this is the same login and the password
we put in there when we created this one
ok so I want to show you something over
here I'm going to go and put in the
password and I'm not going to click on
connect so this is the server I'm going
to connect to the server and it's going
to show me all the databases that this
server has ok so right now how many
databases up there probably just about
both of them actually got created my
standard is 0 and my second one which is
the empty database which is standard s3
so this is also an example of different
pricing tiers ok because maybe my this
database has more requirements than this
one but anyways when I connect to the
database server
I should see these two databases but one
more thing can I connect from my
computer so this is running on my
computer connect to this database server
chances are no and the reason behind
that is if you remember when your
database server is first created the
firewall does not allow anybody to
connect okay so in this firewall there
is nobody over here no firewall rules
okay so if I try to connect on this it
will actually give me an error it's
actually giving me an error that your
client IP address does not have access
to the server so not only it's giving me
an error it's actually telling me that
if you want to sign in and add your
server which is your client IP which
should also determine so this is the
same as this one right you can add it
over there then it will allow you so one
way to do that is right from here where
you can go and add those IP addresses or
if you remember conveniently it allowed
it actually showed my client IP address
over here I could have taken this over
here put my name or what my computer's
name and put my start and end IP as the
same IP address and configured that but
I'll do it from here okay so it's
basically just looking for your as your
credentials so I would do it from here
personal account obviously in a
production environment you probably do
not want to go to every computer that
needs access to the management console
or database manually so you probably go
and do that from there so it's actually
asking do you want to arrange or do you
want to add my client IP it might be a
range if you want you know certain
applications in a certain range on your
servers to do that I'm going to just use
individually my IP address so it's in
progress it went and added that and it
got connected to my from my on premises
client over here management studio to
the one integer and if we come over here
let's close this we should be able to
see this added right now so it went and
gave a random name and it went and added
my IP address over here like I said I
could have added it manually but I kind
of made this client do that so if I go
and look at databases right now I should
see two databases these are the two
databases I have my first database
should actually have tables because this
is like a you know like this is some
sort of a example did
the Microsoft creative so if I go and
select top 1000 rows it will actually go
now make that query and show me all this
information takes a little bit time but
it will go and do that right so look at
this it actually has some data so
Microsoft gives you that database for
free to actually play with and my second
database I knew was empty so technically
it should not have any tables in there
okay so there's no tables in there which
is fine so there are some tasks that I
can do from the management studio to
take care of this what I was trying to
get at over here now is that there are
two in this server there are two
databases over here as well okay
so come to the databases here so if you
have an application that's written you
have to have a connection string you
want to make sure it's on the server
firewall you probably want a couple of
admins in your organization where you
can you know have them or you can allow
their client computers access to this
database to do some management so there
is some security tasks you can do here
like I only have one login you would
actually create multiple logins right I
mean it actually tells you how to create
and the login where it will go to the
server and you know for your application
connection string you can actually
create a login here as well so certain
tasks and we'll go over this details in
some future session now the pool okay so
all the databases that we created so far
were individual databases right let's
say now I found out this one was s0 this
one was s3 I was like hey the maximum
I'm going to go is s3 but my this second
database does not require s3
capabilities all at a given time so
maybe this guy does not have so many
requirements so what I'm going to do is
I'm going to create one pool right now
and I'm going to put both of them
together so if you remember our pricing
calculator right now to host this two
databases let me see where my pricing
calculator is so I have a single
database with a standard s0 with one
database it's going to cost me about
fifteen dollars a month
and I have another one which is s3 over
here so I'm going to go do that
standard is three so it's going to be
$150 per month so right now I'm paying
about 150 plus 15 so what is that 165
165 dollars per month but then I have to
have both of them up I'm using hundred
and sixty-five dollars and I know that
both of my databases are not super
active all the time so what I want to do
is go back here and say I'm going to put
this in a pool and the way I do that is
create a sequel elastic pool over here
once again if you don't have this tab go
to browse and get that I'm going to
create a pool and in the pool I would
name it I'm going to call it Ranger DB
pool database pole it's going to be
similar to what I have it's going to be
the server and it same server and
everything else and I lost it come here
go to add I'm going to call Ranger DB
pool sorry about that and it's gonna be
the same server look it's actually
looking for a pricing tier again so I'm
going to say that you know what I'm
going to go up to 100 to 1200 EDT use
right it's similar so I'm going to say
I'm going to take a standard tool but in
a standard pool now I need to go to
configure the pool here it will give you
more options so right now my edu is 100
given on how my sequel database
requirements where is 3 I can leave it
at that
inelastic pool you can go however a
little bit more because you are saying
that you know 100 DT you probably might
not work what if like both of the work
at the same time I'll need more details
than that so in my pricing calculator I
believe s0 is 10 so you know I need 110
details but I don't have that over here
if both of them go up at the same time
so I can say like you can see over here
my EDT UMAX is 100 but my UT to minimum
is 0 0 to 100 but I can't go more so you
can actually get this dial and say you
know what go up to 200 details so what
that will do is if
concurrently this databases are going
you've got Mordy deal or if you don't
you want to save money because you know
none of them would go more than 100 you
can do that as well okay so my monthly
cost over here you can see it's going to
be 273 which is kind of similar if I
come over here and go elastic database
and if I was going 200 did use 223
there's a little discrepancy over here
but based on whatever their calculation
was 0 to 100 they found out it's going
to be 273 actually I know where the
discrepancy is because my portal is in
Canadian dollars because I'm in Canada
is doing the calculation over here
however in US dollar
so let's actually change that the
Canadian dollar so 271 pretty close with
everything else it's going to cost me
around there okay so you can do that
calculation over here so let's do select
so I'm going to say that it's still
going to be that many configure tool let
me see which databases I did not select
any databases so I can come over here
there currently no database is selected
in the pool so yeah let's leave it like
that let's try to create it I will
create the pool and once you create the
pool you can assign the databases to go
in the pool so just like that
I saved about $15 a month given that I'm
going to only use up to s 3 performance
level of the standard tier at both my
databases can share the resources ok so
that's how the elastic pull sort of
works I mean obviously there's a lot of
calculation you already put in the back
end when this is going to be done but
for now that's how just you create it in
the later sections we'll go into more
details on monitoring it we'll go into
more details on how to connect to this
all separate databases performance
I mean character these databases and
perform tasks on them and things like
that but this is just a basic scenario
of how to create this so let me refresh
that still creating the pool alright so
the pool got created over here let's go
jump into the pool and over here you can
see there's no data available nothing is
going on over here just got created so
what we are going to do you
is either you can create databases
directly to the pool or you can
configure pool over here from here from
here anywhere go to configure pool and
you can add to the pool okay so this
pool will go up to 100 so add to the
pool these are the two databases we have
created we can take both of these
databases select them and say that these
are going to go inside this tool okay so
save that so now it's taking those
single databases and putting them in
this poll takes a little bit of time so
it's adding them right now already so it
got added over here minimum zero maximum
100 just like that we put them in the
pool and we saved about 15 bucks a month
right so well now you can realize that
if you have multiple databases they will
actually you know they will probably
keep probably save some money doing this
quite a bit actually so if we go to the
sequel databases over here you can see
that the pricing here now change to
lastik standard okay so before we put
showing is 0 s 3 right now it's like
elastic standard okay that's how we put
it in there so if you leave them up and
running a little bit if you run some
queries you'll eventually find out that
it started showing some matrix over here
so right now we do not have any graphs
and not really anything that will help
you figure out what's the performance
over here but overall before I hit the
session I just want to show you and well
not show you I want to like make you
appreciate how you just created a
database so fast in the poor oh that's
what I mean living all the concepts and
complexities aside the ease of creating
a database if we were you we're an app
developer rather than having your system
in Google create a server put everything
on there give you a development
environment you just pretty much have
access to a database just like that
where you can have your connection
string connect to this database and
start putting data right now for
whatever prototyping you're doing or
you're doing and you know what by
default all of these things are sort of
being backed up it's sort of
has already all the availability and you
don't have to worry about dr so if this
was production environment little more
and you can actually have a very
resilient environment taken care of for
you
you don't have to worry about this
database server you don't know what you
have to worry about the databases
performance it even gives you advisory
on how to fix your performances okay so
i want you to appreciate the platform as
a service and the sequel server offering
from is your because of that okay so i
guess that sort of concludes our session
in this session so we looked at what as
you're sick will you know offering is
from a platform as a service point of
view what are you know the really two
ways to do this is as infrastructure as
a service where you create a VM and put
sequel server on there or you know
platform as a service what we saw in the
demo to create doing this we went and
saw different pricing tiers or
performance tiers where you can you know
put different resources on your
databases depending on your application
workload or the kind of power you need
on there we talked about dtu's did you
know you know the database transaction
units or date of transaction units what
you can do is you know calculate the DT
use to calculate what kind of
performance your database needs we
talked about how to create a standard
database or create a pool and put
databases in there - overall save and
make it cost effective or you know make
the performance more elastic or
auto-scaling and we saw in this session
how to you know make a sequel server and
then add create a database and add it to
the server in the demo also we created a
tool and added database on the pool so
quite a bit and this was just part of
our introduction I'm going to get into
details in the next section as well just
to get you started so let's leave the
session over here for now I want you to
go a little bit more but I think we were
on top of the hour right now so let's
conclude here guys that's my training
site YouTube that's where you're viewing
this right now the slides are going to
be posted over there twitter is at shown
as well you can email me if you want to
connect on LinkedIn that's where you go
do not forget to like the videos guys
and thank you for viewing and I'll see
you