Up next in 10
Data Replication Options for Azure SQL PaaS Services by Mara Steiu || SQL Server Virtual Conference
Oct 30, 2023
Mara Steiu - Program Mnager at Microsoft
Conference Website: https://www.2020twenty.net/sql-server...
C# Corner - Community of Software and Data Developers
https://www.c-sharpcorner.com
#SQL #powershell #dbatools #conference #sqlserver
Show More Show Less View Video Transcript
0:03
uh hi everyone
0:04
uh hi everyone
0:04
uh hi everyone very excited to be here uh as you've
0:07
very excited to be here uh as you've
0:07
very excited to be here uh as you've seen in my very detailed intro i'm
0:09
seen in my very detailed intro i'm
0:09
seen in my very detailed intro i'm i'm a pm at microsoft in aztec sql
0:13
i'm a pm at microsoft in aztec sql
0:13
i'm a pm at microsoft in aztec sql and i'm very excited to give you some
0:15
and i'm very excited to give you some
0:15
and i'm very excited to give you some information about data
0:16
information about data
0:16
information about data replication options in azure sql pass
0:19
replication options in azure sql pass
0:20
replication options in azure sql pass services so this is what we have
0:23
services so this is what we have
0:23
services so this is what we have prepared for today we'll start at a very
0:25
prepared for today we'll start at a very
0:25
prepared for today we'll start at a very high level why is data application
0:27
high level why is data application
0:28
high level why is data application relevant and then we'll go a bit into
0:29
relevant and then we'll go a bit into
0:29
relevant and then we'll go a bit into more depth in
0:31
more depth in
0:31
more depth in common scenarios and solutions for data
0:34
common scenarios and solutions for data
0:34
common scenarios and solutions for data application so the list that i will go
0:36
application so the list that i will go
0:36
application so the list that i will go through is in no ways
0:38
through is in no ways
0:38
through is in no ways the most comprehensive list of options
0:40
the most comprehensive list of options
0:40
the most comprehensive list of options there are so many other scenarios there
0:42
there are so many other scenarios there
0:42
there are so many other scenarios there are so many other great solutions
0:44
are so many other great solutions
0:44
are so many other great solutions third-party apps but these are some of
0:46
third-party apps but these are some of
0:46
third-party apps but these are some of the key
0:47
the key
0:47
the key um application options that i thought
0:50
um application options that i thought
0:50
um application options that i thought would be interesting to
0:51
would be interesting to
0:51
would be interesting to to discuss for today so we'll go to sql
0:54
to discuss for today so we'll go to sql
0:54
to discuss for today so we'll go to sql data sync
0:55
data sync
0:55
data sync change data capture change tracking
0:57
change data capture change tracking
0:57
change data capture change tracking active jog application
0:59
active jog application
0:59
active jog application and engage scale out feature
1:03
and engage scale out feature
1:03
and engage scale out feature okay and uh just to give you some
1:05
okay and uh just to give you some
1:05
okay and uh just to give you some context from the discussion
1:06
context from the discussion
1:06
context from the discussion so i'm sure you the other speakers have
1:09
so i'm sure you the other speakers have
1:09
so i'm sure you the other speakers have already told you about the great
1:10
already told you about the great
1:10
already told you about the great offerings in azure sql and all the way
1:12
offerings in azure sql and all the way
1:12
offerings in azure sql and all the way from infrastructure gazette service
1:14
from infrastructure gazette service
1:14
from infrastructure gazette service up to platform as a service the context
1:17
up to platform as a service the context
1:17
up to platform as a service the context for
1:17
for
1:17
for from this talk will be mostly focused on
1:20
from this talk will be mostly focused on
1:20
from this talk will be mostly focused on azure
1:21
azure
1:21
azure sql database application solutions
1:24
sql database application solutions
1:24
sql database application solutions however some of the solutions also apply
1:27
however some of the solutions also apply
1:27
however some of the solutions also apply to
1:27
to
1:27
to sql server on prem on other vms or azure
1:30
sql server on prem on other vms or azure
1:30
sql server on prem on other vms or azure sql mi
1:31
sql mi
1:31
sql mi but we'll be mostly focusing on azure
1:33
but we'll be mostly focusing on azure
1:33
but we'll be mostly focusing on azure sql
1:34
sql
1:34
sql dp just to clarify the the context for
1:38
dp just to clarify the the context for
1:38
dp just to clarify the the context for the talk
1:39
the talk
1:39
the talk okay so let's get started so very
1:41
okay so let's get started so very
1:41
okay so let's get started so very briefly why is data application relevant
1:44
briefly why is data application relevant
1:44
briefly why is data application relevant there are so many use use cases for the
1:47
there are so many use use cases for the
1:47
there are so many use use cases for the data application in the
1:48
data application in the
1:48
data application in the daily life so if you for instance if you
1:50
daily life so if you for instance if you
1:50
daily life so if you for instance if you have an emergency
1:52
have an emergency
1:52
have an emergency 911 system and you want to make sure
1:54
911 system and you want to make sure
1:54
911 system and you want to make sure that any call you receive is
1:56
that any call you receive is
1:56
that any call you receive is automatically
1:57
automatically
1:57
automatically visible across the country to mobilize
2:00
visible across the country to mobilize
2:00
visible across the country to mobilize resources as needed for any patients you
2:03
resources as needed for any patients you
2:03
resources as needed for any patients you will need real-time data application
2:06
will need real-time data application
2:06
will need real-time data application if you have a business and you sell and
2:07
if you have a business and you sell and
2:07
if you have a business and you sell and you want to make sure your inventory is
2:09
you want to make sure your inventory is
2:09
you want to make sure your inventory is in sync across multiple regions actually
2:12
in sync across multiple regions actually
2:12
in sync across multiple regions actually we'll see a demo around that
2:14
we'll see a demo around that
2:14
we'll see a demo around that you would use again data applications so
2:16
you would use again data applications so
2:16
you would use again data applications so there are so many uses
2:17
there are so many uses
2:17
there are so many uses for data application in our daily lives
2:21
for data application in our daily lives
2:21
for data application in our daily lives but now let's go a bit
2:23
but now let's go a bit
2:23
but now let's go a bit into more depth into data replication so
2:26
into more depth into data replication so
2:26
into more depth into data replication so we'll go through these key scenarios for
2:28
we'll go through these key scenarios for
2:28
we'll go through these key scenarios for for today so we are going to discuss
2:30
for today so we are going to discuss
2:30
for today so we are going to discuss some
2:31
some
2:31
some solutions for synchronizes distributed
2:33
solutions for synchronizes distributed
2:33
solutions for synchronizes distributed applications and here we're gonna see
2:36
applications and here we're gonna see
2:36
applications and here we're gonna see synchronizing data across databases that
2:38
synchronizing data across databases that
2:38
synchronizing data across databases that store different workloads so you might
2:40
store different workloads so you might
2:40
store different workloads so you might have a production database that you want
2:42
have a production database that you want
2:42
have a production database that you want to synchronize with another database
2:44
to synchronize with another database
2:44
to synchronize with another database that has um
2:45
that has um
2:45
that has um analytics workload then you might use
2:48
analytics workload then you might use
2:48
analytics workload then you might use the application for business
2:49
the application for business
2:50
the application for business continuity which is essential and this
2:52
continuity which is essential and this
2:52
continuity which is essential and this refers to
2:53
refers to
2:53
refers to the procedures that um that would enable
2:56
the procedures that um that would enable
2:56
the procedures that um that would enable your business to operate
2:58
your business to operate
2:58
your business to operate even in the face of disruptions such as
3:00
even in the face of disruptions such as
3:00
even in the face of disruptions such as data center out disease
3:02
data center out disease
3:02
data center out disease large-scale rtgs and regional disasters
3:05
large-scale rtgs and regional disasters
3:06
large-scale rtgs and regional disasters and so on
3:07
and so on
3:07
and so on you might also want to use the
3:08
you might also want to use the
3:08
you might also want to use the application for scaling out
3:10
application for scaling out
3:10
application for scaling out read-only workloads so to offload your
3:13
read-only workloads so to offload your
3:13
read-only workloads so to offload your read-only workloads
3:14
read-only workloads
3:14
read-only workloads instead of running them on the git right
3:16
instead of running them on the git right
3:16
instead of running them on the git right replica so this way
3:18
replica so this way
3:18
replica so this way some read-only workloads can be isolated
3:21
some read-only workloads can be isolated
3:21
some read-only workloads can be isolated and
3:22
and
3:22
and they will not affect the performance of
3:23
they will not affect the performance of
3:24
they will not affect the performance of gateway replicas and lastly you might
3:26
gateway replicas and lastly you might
3:26
gateway replicas and lastly you might want to keep data synchronized
3:27
want to keep data synchronized
3:28
want to keep data synchronized also while migrating and also after
3:30
also while migrating and also after
3:30
also while migrating and also after migrating so you might say i want my
3:32
migrating so you might say i want my
3:32
migrating so you might say i want my source database on prime and my target
3:36
source database on prime and my target
3:36
source database on prime and my target database on azure sql to be
3:38
database on azure sql to be
3:38
database on azure sql to be synchronized during migration and after
3:41
synchronized during migration and after
3:41
synchronized during migration and after so that they remain uh functional so
3:44
so that they remain uh functional so
3:44
so that they remain uh functional so these are some scenarios that we are
3:46
these are some scenarios that we are
3:46
these are some scenarios that we are going to look into today and now we are
3:48
going to look into today and now we are
3:48
going to look into today and now we are going to start
3:49
going to start
3:49
going to start exploring these solutions into a bit
3:52
exploring these solutions into a bit
3:52
exploring these solutions into a bit more depth so we're going to start with
3:56
more depth so we're going to start with
3:56
more depth so we're going to start with the
3:57
the
3:57
the sql data sync i might be biased here
3:59
sql data sync i might be biased here
3:59
sql data sync i might be biased here because i'm
4:00
because i'm
4:00
because i'm the pm for sql data sync so we'll also
4:03
the pm for sql data sync so we'll also
4:03
the pm for sql data sync so we'll also do
4:03
do
4:03
do a demo here um so
4:06
a demo here um so
4:06
a demo here um so sql data sync at a very high level
4:08
sql data sync at a very high level
4:08
sql data sync at a very high level allows you to synchronize data
4:10
allows you to synchronize data
4:10
allows you to synchronize data between data among databases that are in
4:13
between data among databases that are in
4:13
between data among databases that are in azure sql and sql server on-prem
4:16
azure sql and sql server on-prem
4:16
azure sql and sql server on-prem or doc vms and the whole synchronization
4:19
or doc vms and the whole synchronization
4:19
or doc vms and the whole synchronization around the
4:20
around the
4:20
around the is around the concept of a sync group a
4:23
is around the concept of a sync group a
4:23
is around the concept of a sync group a synchro
4:23
synchro
4:23
synchro is just a group of databases that you
4:25
is just a group of databases that you
4:25
is just a group of databases that you want to synchronize
4:26
want to synchronize
4:26
want to synchronize and it consists of one hub database that
4:29
and it consists of one hub database that
4:29
and it consists of one hub database that has to be on azure sql
4:31
has to be on azure sql
4:31
has to be on azure sql and one on or more databases that can
4:34
and one on or more databases that can
4:34
and one on or more databases that can also be on
4:35
also be on
4:35
also be on other sql server instances for instance
4:37
other sql server instances for instance
4:37
other sql server instances for instance on cam
4:39
on cam
4:39
on cam um so some properties accounting groups
4:43
um so some properties accounting groups
4:43
um so some properties accounting groups we'll see in the demo as well but you
4:45
we'll see in the demo as well but you
4:45
we'll see in the demo as well but you would set up the sync schema which
4:46
would set up the sync schema which
4:46
would set up the sync schema which basically specifies
4:48
basically specifies
4:48
basically specifies what are the tables that you want
4:49
what are the tables that you want
4:49
what are the tables that you want synchronized within a database then you
4:51
synchronized within a database then you
4:51
synchronized within a database then you set up sync direction and what is really
4:53
set up sync direction and what is really
4:54
set up sync direction and what is really interesting about sql data sync is that
4:56
interesting about sql data sync is that
4:56
interesting about sql data sync is that it allows you to do bi-directional
4:58
it allows you to do bi-directional
4:58
it allows you to do bi-directional synchronization as well so not just one
5:01
synchronization as well so not just one
5:01
synchronization as well so not just one way
5:01
way
5:02
way so more specifically you can you can do
5:04
so more specifically you can you can do
5:04
so more specifically you can you can do things from
5:05
things from
5:05
things from member to hub hub to member and both
5:08
member to hub hub to member and both
5:08
member to hub hub to member and both then you can set up the sync frequency
5:10
then you can set up the sync frequency
5:10
then you can set up the sync frequency so you can set it up as little as
5:12
so you can set it up as little as
5:12
so you can set it up as little as one second at as large as um
5:15
one second at as large as um
5:15
one second at as large as um 30 days uh here is worth mentioning even
5:18
30 days uh here is worth mentioning even
5:18
30 days uh here is worth mentioning even if you set it up
5:19
if you set it up
5:19
if you set it up one second this doesn't mean that the
5:21
one second this doesn't mean that the
5:21
one second this doesn't mean that the sync is real time
5:23
sync is real time
5:23
sync is real time but it will take one second from when
5:25
but it will take one second from when
5:25
but it will take one second from when the last thing was completed and the new
5:27
the last thing was completed and the new
5:28
the last thing was completed and the new thing will be triggered
5:29
thing will be triggered
5:29
thing will be triggered within a second so that's what this
5:31
within a second so that's what this
5:31
within a second so that's what this means more specifically
5:33
means more specifically
5:33
means more specifically and conflict resolving policy so this
5:36
and conflict resolving policy so this
5:36
and conflict resolving policy so this defines
5:37
defines
5:37
defines what should happen when you have
5:39
what should happen when you have
5:39
what should happen when you have conflict in terms of data modification
5:41
conflict in terms of data modification
5:41
conflict in terms of data modification and here you have two options you can
5:43
and here you have two options you can
5:43
and here you have two options you can either say the hub wins in which case
5:47
either say the hub wins in which case
5:47
either say the hub wins in which case changes in the hub will override those
5:49
changes in the hub will override those
5:49
changes in the hub will override those in the members
5:50
in the members
5:50
in the members and then you can select member queens in
5:53
and then you can select member queens in
5:53
and then you can select member queens in which is the opposite changes
5:54
which is the opposite changes
5:54
which is the opposite changes in the member would override those in
5:56
in the member would override those in
5:56
in the member would override those in the hub
5:58
the hub
5:58
the hub so as you can see here uh you have a
6:01
so as you can see here uh you have a
6:01
so as you can see here uh you have a haban hub and spoke topology for sql
6:05
haban hub and spoke topology for sql
6:05
haban hub and spoke topology for sql data sync
6:06
data sync
6:06
data sync so all the member databases only sync to
6:09
so all the member databases only sync to
6:09
so all the member databases only sync to the hub database so if you want to
6:10
the hub database so if you want to
6:10
the hub database so if you want to synchronize multiple member databases
6:13
synchronize multiple member databases
6:13
synchronize multiple member databases that are on
6:13
that are on
6:14
that are on sql server on prem for instance you can
6:16
sql server on prem for instance you can
6:16
sql server on prem for instance you can do that but
6:17
do that but
6:17
do that but indirectly through the hub database
6:19
indirectly through the hub database
6:19
indirectly through the hub database which is on
6:20
which is on
6:20
which is on azure sql um so more specifically
6:24
azure sql um so more specifically
6:24
azure sql um so more specifically changes from the hub downloaded to the
6:26
changes from the hub downloaded to the
6:26
changes from the hub downloaded to the member and then changes from the member
6:28
member and then changes from the member
6:28
member and then changes from the member uploaded to to the hub it's also
6:31
uploaded to to the hub it's also
6:32
uploaded to to the hub it's also important to know that
6:33
important to know that
6:33
important to know that if you are using member databases that
6:35
if you are using member databases that
6:35
if you are using member databases that are on prem you will have to
6:37
are on prem you will have to
6:38
are on prem you will have to install a local sync agent
6:41
install a local sync agent
6:41
install a local sync agent and so sql data sync works through dml
6:44
and so sql data sync works through dml
6:44
and so sql data sync works through dml triggers like insert update deletes
6:46
triggers like insert update deletes
6:46
triggers like insert update deletes which are recorded in a site table in
6:48
which are recorded in a site table in
6:48
which are recorded in a site table in the user's database we're seeing the
6:50
the user's database we're seeing the
6:50
the user's database we're seeing the demo
6:51
demo
6:51
demo and since it's trigger-based
6:53
and since it's trigger-based
6:53
and since it's trigger-based transactional consistency is not
6:55
transactional consistency is not
6:55
transactional consistency is not guaranteed but
6:56
guaranteed but
6:56
guaranteed but eventual consistency is guaranteed so
6:59
eventual consistency is guaranteed so
6:59
eventual consistency is guaranteed so data sync does not
7:01
data sync does not
7:01
data sync does not cause data loss
7:05
so before going on to the demo just some
7:07
so before going on to the demo just some
7:07
so before going on to the demo just some use cases specifically for sync
7:10
use cases specifically for sync
7:10
use cases specifically for sync it allows hybrid data synchronization so
7:12
it allows hybrid data synchronization so
7:12
it allows hybrid data synchronization so this is great if you want
7:14
this is great if you want
7:14
this is great if you want to uh if you're considering moving to
7:16
to uh if you're considering moving to
7:16
to uh if you're considering moving to the cloud and would like to put some of
7:18
the cloud and would like to put some of
7:18
the cloud and would like to put some of your application in
7:19
your application in
7:19
your application in azure this is this is a great great for
7:22
azure this is this is a great great for
7:22
azure this is this is a great great for you
7:23
you
7:23
you uh it's also great for distributed
7:25
uh it's also great for distributed
7:25
uh it's also great for distributed applications we talked about it already
7:27
applications we talked about it already
7:27
applications we talked about it already and lastly for globally distributed
7:30
and lastly for globally distributed
7:30
and lastly for globally distributed applications and
7:31
applications and
7:31
applications and this is helpful because in order to
7:33
this is helpful because in order to
7:33
this is helpful because in order to minimize network latency it's best to
7:35
minimize network latency it's best to
7:35
minimize network latency it's best to have your data in a region close to you
7:37
have your data in a region close to you
7:37
have your data in a region close to you and make sure it's
7:38
and make sure it's
7:38
and make sure it's synchronized with other data
7:41
synchronized with other data
7:42
synchronized with other data great um so now we're gonna do
7:45
great um so now we're gonna do
7:45
great um so now we're gonna do a demo for sql data sync in which we'll
7:48
a demo for sql data sync in which we'll
7:48
a demo for sql data sync in which we'll synchronize
7:49
synchronize
7:49
synchronize uh two azure sql databases across us
7:52
uh two azure sql databases across us
7:52
uh two azure sql databases across us and asia
7:56
let's see um i'll play this to make sure
7:59
let's see um i'll play this to make sure
7:59
let's see um i'll play this to make sure there are no glitches
8:00
there are no glitches
8:00
there are no glitches here you can see the hub database it's a
8:03
here you can see the hub database it's a
8:03
here you can see the hub database it's a food business we
8:04
food business we
8:04
food business we see a foot inventory very simple
8:07
see a foot inventory very simple
8:07
see a foot inventory very simple uh we have some footage i want to make
8:09
uh we have some footage i want to make
8:09
uh we have some footage i want to make sure our inventory is in sync
8:11
sure our inventory is in sync
8:11
sure our inventory is in sync with our asia center where we can see
8:15
with our asia center where we can see
8:15
with our asia center where we can see only three of those sales have been
8:17
only three of those sales have been
8:17
only three of those sales have been recorded so far so the melon has not
8:19
recorded so far so the melon has not
8:19
recorded so far so the melon has not been recorded and we want to make sure
8:21
been recorded and we want to make sure
8:21
been recorded and we want to make sure all data is in sync and then we'll add
8:23
all data is in sync and then we'll add
8:23
all data is in sync and then we'll add some more to the inventories to
8:25
some more to the inventories to
8:25
some more to the inventories to to bi-directional sync so i go on the
8:28
to bi-directional sync so i go on the
8:28
to bi-directional sync so i go on the hub database and create
8:29
hub database and create
8:29
hub database and create a sync group here i set up the scene
8:33
a sync group here i set up the scene
8:33
a sync group here i set up the scene group name let's say
8:35
group name let's say
8:35
group name let's say us to asia because we're syncing data
8:38
us to asia because we're syncing data
8:38
us to asia because we're syncing data across these regions
8:40
across these regions
8:40
across these regions you have to set up you can either choose
8:42
you have to set up you can either choose
8:42
you have to set up you can either choose an existing database for metadata
8:44
an existing database for metadata
8:44
an existing database for metadata or a new one we recommend you do a new
8:46
or a new one we recommend you do a new
8:46
or a new one we recommend you do a new metadata database it has to be in the
8:48
metadata database it has to be in the
8:48
metadata database it has to be in the same region
8:49
same region
8:50
same region as the hub database and
8:53
as the hub database and
8:53
as the hub database and we'll do it as a basic pricing tier
8:55
we'll do it as a basic pricing tier
8:55
we'll do it as a basic pricing tier because that's enough for the purpose of
8:57
because that's enough for the purpose of
8:57
because that's enough for the purpose of this demo
9:00
and automatic sync we want the sync to
9:03
and automatic sync we want the sync to
9:03
and automatic sync we want the sync to be triggered every two
9:04
be triggered every two
9:04
be triggered every two seconds and we want hub win in case we
9:07
seconds and we want hub win in case we
9:07
seconds and we want hub win in case we have any conflict for data modification
9:09
have any conflict for data modification
9:09
have any conflict for data modification private linking shows that connection
9:11
private linking shows that connection
9:11
private linking shows that connection between your hub database here you can
9:13
between your hub database here you can
9:13
between your hub database here you can learn more
9:14
learn more
9:14
learn more have database and the sync service is
9:17
have database and the sync service is
9:17
have database and the sync service is secure so it's a private endpoint the
9:19
secure so it's a private endpoint the
9:19
secure so it's a private endpoint the private type address that is managed by
9:21
private type address that is managed by
9:21
private type address that is managed by microsoft
9:23
microsoft
9:23
microsoft it's still in private in public preview
9:25
it's still in private in public preview
9:25
it's still in private in public preview so what you have to do is to
9:27
so what you have to do is to
9:27
so what you have to do is to do a manual approval for the private
9:29
do a manual approval for the private
9:29
do a manual approval for the private endpoint
9:30
endpoint
9:30
endpoint before you can move onwards so here
9:34
before you can move onwards so here
9:34
before you can move onwards so here we're approving the private link
9:41
so this is being approved and now the
9:44
so this is being approved and now the
9:44
so this is being approved and now the sync group should be created and now we
9:47
sync group should be created and now we
9:47
sync group should be created and now we want to add member databases so we have
9:49
want to add member databases so we have
9:49
want to add member databases so we have to log in on the hub database on azure
9:51
to log in on the hub database on azure
9:51
to log in on the hub database on azure sql
9:52
sql
9:52
sql and then here you can see you can add
9:54
and then here you can see you can add
9:54
and then here you can see you can add either from on-prem
9:55
either from on-prem
9:55
either from on-prem or from azure we're going to add another
9:59
or from azure we're going to add another
9:59
or from azure we're going to add another azure
10:01
azure
10:01
azure member with the asia database
10:04
member with the asia database
10:04
member with the asia database and i'll go to my test server in
10:07
and i'll go to my test server in
10:08
and i'll go to my test server in asia
10:11
and i select and i do buy that
10:13
and i select and i do buy that
10:14
and i select and i do buy that actionable sync
10:15
actionable sync
10:15
actionable sync which we talked about and again i have
10:17
which we talked about and again i have
10:17
which we talked about and again i have to log on with the credential for the
10:18
to log on with the credential for the
10:18
to log on with the credential for the server
10:19
server
10:19
server on in asia and i use private link again
10:23
on in asia and i use private link again
10:23
on in asia and i use private link again for the connection between the sync
10:24
for the connection between the sync
10:24
for the connection between the sync service and the member database to make
10:26
service and the member database to make
10:26
service and the member database to make sure that it's all
10:27
sure that it's all
10:27
sure that it's all secured so i will use that private
10:30
secured so i will use that private
10:30
secured so i will use that private endpoint
10:32
endpoint
10:32
endpoint private link only works if your cabin
10:35
private link only works if your cabin
10:35
private link only works if your cabin members are in azure
10:36
members are in azure
10:36
members are in azure as of now so if you have
10:39
as of now so if you have
10:40
as of now so if you have uh on-prem it won't work and now i'm
10:42
uh on-prem it won't work and now i'm
10:42
uh on-prem it won't work and now i'm going again
10:43
going again
10:43
going again on the asia test server to approve the
10:45
on the asia test server to approve the
10:45
on the asia test server to approve the private endpoint i have to manually
10:47
private endpoint i have to manually
10:47
private endpoint i have to manually approve it
10:49
approve it
10:49
approve it once again before i can have the member
10:52
once again before i can have the member
10:52
once again before i can have the member added to the scene group
10:56
okay so now and move
11:00
okay so now and move
11:00
okay so now and move onwards
11:06
now we created the single group edit
11:08
now we created the single group edit
11:08
now we created the single group edit members and now we select the data that
11:10
members and now we select the data that
11:10
members and now we select the data that we actually want to synchronize
11:12
we actually want to synchronize
11:12
we actually want to synchronize so we refresh schema on the hub only the
11:15
so we refresh schema on the hub only the
11:15
so we refresh schema on the hub only the tables that have a primary key
11:17
tables that have a primary key
11:18
tables that have a primary key will be shown here so primary key is
11:20
will be shown here so primary key is
11:20
will be shown here so primary key is necessary if you want to
11:22
necessary if you want to
11:22
necessary if you want to add a specific table so in this case i
11:25
add a specific table so in this case i
11:25
add a specific table so in this case i will just
11:26
will just
11:26
will just synchronize all the data is like a
11:27
synchronize all the data is like a
11:27
synchronize all the data is like a little data
11:29
little data
11:29
little data for the purposes of this demo and i'll
11:31
for the purposes of this demo and i'll
11:31
for the purposes of this demo and i'll do the same for the member database i'll
11:34
do the same for the member database i'll
11:34
do the same for the member database i'll select which data i want synchronized
11:42
select which data i want synchronized
11:42
select which data i want synchronized and as you can see we have primary key
11:44
and as you can see we have primary key
11:44
and as you can see we have primary key again which is essential
11:45
again which is essential
11:45
again which is essential here you can see um the status and you
11:49
here you can see um the status and you
11:49
here you can see um the status and you can also get logs
11:50
can also get logs
11:50
can also get logs on monitoring and the single has been
11:52
on monitoring and the single has been
11:52
on monitoring and the single has been created
11:54
created
11:54
created i'm showing you again the the logs and
11:56
i'm showing you again the the logs and
11:56
i'm showing you again the the logs and the databases
11:59
the databases
11:59
the databases and now let's see whether the
12:00
and now let's see whether the
12:00
and now let's see whether the synchronization actually happens so the
12:02
synchronization actually happens so the
12:02
synchronization actually happens so the expected behavior is to see
12:04
expected behavior is to see
12:04
expected behavior is to see same data in asia that we had in the
12:07
same data in asia that we had in the
12:07
same data in asia that we had in the u.s right because one of the items in
12:11
u.s right because one of the items in
12:11
u.s right because one of the items in the inventory was missing in asia
12:13
the inventory was missing in asia
12:13
the inventory was missing in asia asia and i'll go again to the
12:16
asia and i'll go again to the
12:16
asia and i'll go again to the asia database as well to check it out on
12:19
asia database as well to check it out on
12:19
asia database as well to check it out on the query editor
12:27
so as you can see uh sync adds some
12:30
so as you can see uh sync adds some
12:30
so as you can see uh sync adds some tables on the user's database
12:33
tables on the user's database
12:33
tables on the user's database um but on the hub and the member
12:36
um but on the hub and the member
12:36
um but on the hub and the member databases you can see those tables that
12:38
databases you can see those tables that
12:38
databases you can see those tables that have been added
12:40
have been added
12:40
have been added um and they have different uh sync
12:43
um and they have different uh sync
12:43
um and they have different uh sync schema and other
12:44
schema and other
12:44
schema and other detail about the sync operation now
12:47
detail about the sync operation now
12:47
detail about the sync operation now we're checking to make sure that we have
12:49
we're checking to make sure that we have
12:49
we're checking to make sure that we have synchronized data in both regions so in
12:51
synchronized data in both regions so in
12:51
synchronized data in both regions so in asia it seems that we had an
12:53
asia it seems that we had an
12:53
asia it seems that we had an um item that was missing initially and
12:55
um item that was missing initially and
12:56
um item that was missing initially and i'm showing you here
12:56
i'm showing you here
12:56
i'm showing you here that is the same as in the us so the
12:59
that is the same as in the us so the
12:59
that is the same as in the us so the synchronization work but just to make
13:01
synchronization work but just to make
13:01
synchronization work but just to make sure it's
13:02
sure it's
13:02
sure it's bi-directional so not just from hub to
13:04
bi-directional so not just from hub to
13:04
bi-directional so not just from hub to member but also the other way around
13:06
member but also the other way around
13:06
member but also the other way around i will insert some value in asia as well
13:10
i will insert some value in asia as well
13:10
i will insert some value in asia as well and we'll check if it shows up in the us
13:13
and we'll check if it shows up in the us
13:13
and we'll check if it shows up in the us and i'm gonna add a dragon food
13:18
as you might see i made a spelling gag
13:20
as you might see i made a spelling gag
13:20
as you might see i made a spelling gag so this won't work and then i'll fix it
13:31
okay so we added this and then we're
13:34
okay so we added this and then we're
13:34
okay so we added this and then we're gonna check if it shows up
13:35
gonna check if it shows up
13:36
gonna check if it shows up in the u.s because we set up
13:38
in the u.s because we set up
13:38
in the u.s because we set up bi-directional
13:41
bi-directional
13:41
bi-directional thing it takes it takes a bit as we as i
13:44
thing it takes it takes a bit as we as i
13:44
thing it takes it takes a bit as we as i previously mentioned it takes
13:46
previously mentioned it takes
13:46
previously mentioned it takes a little so i'll go back to it
13:50
i went back to the asia ones and now i'm
13:52
i went back to the asia ones and now i'm
13:52
i went back to the asia ones and now i'm running this again let's see
13:55
running this again let's see
13:55
running this again let's see and this shows so the thing has happened
13:58
and this shows so the thing has happened
13:58
and this shows so the thing has happened bi-directionally
14:00
bi-directionally
14:00
bi-directionally on on board member and hub great
14:03
on on board member and hub great
14:03
on on board member and hub great let's move onwards
14:09
just a second
14:13
so we'll move onwards to change data
14:15
so we'll move onwards to change data
14:15
so we'll move onwards to change data capture
14:17
capture
14:17
capture so okay so change data capture
14:21
so okay so change data capture
14:21
so okay so change data capture allows you to record again dml changes
14:24
allows you to record again dml changes
14:24
allows you to record again dml changes in sql server azure sql mi and soon on
14:27
in sql server azure sql mi and soon on
14:27
in sql server azure sql mi and soon on azure sql tv so
14:29
azure sql tv so
14:29
azure sql tv so keep an eye out of that on that so as
14:31
keep an eye out of that on that so as
14:31
keep an eye out of that on that so as you can see on this diagram
14:33
you can see on this diagram
14:33
you can see on this diagram uh html changes are made to source table
14:35
uh html changes are made to source table
14:35
uh html changes are made to source table they are added to transaction log and
14:37
they are added to transaction log and
14:37
they are added to transaction log and then you have a capture process
14:39
then you have a capture process
14:39
then you have a capture process reading from the log and adding changes
14:41
reading from the log and adding changes
14:41
reading from the log and adding changes to the track tables associated change
14:43
to the track tables associated change
14:43
to the track tables associated change tables
14:44
tables
14:44
tables and cdc functions afterwards um
14:47
and cdc functions afterwards um
14:48
and cdc functions afterwards um enumerate changes from the change tables
14:49
enumerate changes from the change tables
14:50
enumerate changes from the change tables and return them in a
14:51
and return them in a
14:51
and return them in a result setting a future result set which
14:53
result setting a future result set which
14:53
result setting a future result set which you can use
14:55
you can use
14:55
you can use for other purposes afterwards in this
14:57
for other purposes afterwards in this
14:57
for other purposes afterwards in this case we got the data
14:58
case we got the data
14:58
case we got the data change data from all of ltp into a data
15:01
change data from all of ltp into a data
15:01
change data from all of ltp into a data warehouse
15:03
warehouse
15:03
warehouse um so there are some key concepts around
15:06
um so there are some key concepts around
15:06
um so there are some key concepts around cdc you have the
15:07
cdc you have the
15:07
cdc you have the change table so each insert update
15:10
change table so each insert update
15:10
change table so each insert update operation appears as a single row in the
15:12
operation appears as a single row in the
15:12
operation appears as a single row in the change table so
15:14
change table so
15:14
change table so column values after insert and the
15:16
column values after insert and the
15:16
column values after insert and the column values before the delete
15:18
column values before the delete
15:18
column values before the delete and for update you would have one
15:20
and for update you would have one
15:20
and for update you would have one argument key to identify values
15:22
argument key to identify values
15:22
argument key to identify values before the update and second row to
15:24
before the update and second row to
15:24
before the update and second row to identify column values after
15:26
identify column values after
15:26
identify column values after the update then you have the capture
15:29
the update then you have the capture
15:29
the update then you have the capture process so as already discussed
15:32
process so as already discussed
15:32
process so as already discussed we already discussed about it but it
15:34
we already discussed about it but it
15:34
we already discussed about it but it it's important to highlight that
15:35
it's important to highlight that
15:35
it's important to highlight that because the capture process extracts
15:37
because the capture process extracts
15:38
because the capture process extracts change data from the transaction log
15:40
change data from the transaction log
15:40
change data from the transaction log there is a built-in latency between the
15:43
there is a built-in latency between the
15:43
there is a built-in latency between the timer changes committed to a source
15:45
timer changes committed to a source
15:45
timer changes committed to a source table and the time that
15:46
table and the time that
15:46
table and the time that you can see it in the in the change
15:48
you can see it in the in the change
15:48
you can see it in the in the change table but that the latency is typically
15:52
table but that the latency is typically
15:52
table but that the latency is typically small and also the maximum number of
15:55
small and also the maximum number of
15:55
small and also the maximum number of capturing instances that
15:56
capturing instances that
15:56
capturing instances that can be associated with a single search
15:58
can be associated with a single search
15:58
can be associated with a single search table is two
15:59
table is two
15:59
table is two and for capture instance i mean a change
16:02
and for capture instance i mean a change
16:02
and for capture instance i mean a change table and maximum to
16:04
table and maximum to
16:04
table and maximum to query functions and last of all also
16:07
query functions and last of all also
16:07
query functions and last of all also all objects associated with the change
16:09
all objects associated with the change
16:09
all objects associated with the change with the capture against us are in the
16:11
with the capture against us are in the
16:11
with the capture against us are in the cdc schema
16:13
cdc schema
16:13
cdc schema of the enable db and you'll see that in
16:14
of the enable db and you'll see that in
16:14
of the enable db and you'll see that in a moment then you have the cleanup
16:17
a moment then you have the cleanup
16:17
a moment then you have the cleanup process so
16:17
process so
16:17
process so this is very important because um is
16:20
this is very important because um is
16:20
this is very important because um is responsible for
16:21
responsible for
16:21
responsible for enforcing the detention-based cleanup
16:23
enforcing the detention-based cleanup
16:23
enforcing the detention-based cleanup policy so it basically takes out
16:25
policy so it basically takes out
16:25
policy so it basically takes out it removes expired change table entries
16:28
it removes expired change table entries
16:28
it removes expired change table entries and by default
16:30
and by default
16:30
and by default three days of data are retained
16:33
three days of data are retained
16:33
three days of data are retained and lastly you have the city query
16:35
and lastly you have the city query
16:35
and lastly you have the city query functions to obtain the change
16:37
functions to obtain the change
16:37
functions to obtain the change information
16:39
information
16:39
information so in order to enable ctc you have to do
16:42
so in order to enable ctc you have to do
16:42
so in order to enable ctc you have to do that first at the database level so you
16:44
that first at the database level so you
16:44
that first at the database level so you have
16:45
have
16:45
have to run the stroke procedure that i'm
16:47
to run the stroke procedure that i'm
16:47
to run the stroke procedure that i'm showing you here
16:48
showing you here
16:48
showing you here and once you do that you will create the
16:50
and once you do that you will create the
16:50
and once you do that you will create the cdc schema
16:52
cdc schema
16:52
cdc schema and the and the system tables will be
16:54
and the and the system tables will be
16:54
and the and the system tables will be created
16:55
created
16:56
created afterwards within that database you have
16:58
afterwards within that database you have
16:58
afterwards within that database you have also you also have to enable cdc
17:00
also you also have to enable cdc
17:00
also you also have to enable cdc at the tables level and um once you do
17:04
at the tables level and um once you do
17:04
at the tables level and um once you do that you'll have the
17:05
that you'll have the
17:05
that you'll have the capture and the cleanup jobs created and
17:08
capture and the cleanup jobs created and
17:08
capture and the cleanup jobs created and you also have a
17:09
you also have a
17:09
you also have a table to track changes on the socks on
17:12
table to track changes on the socks on
17:12
table to track changes on the socks on the socks table you can see
17:14
the socks table you can see
17:14
the socks table you can see that up there so it's very important to
17:16
that up there so it's very important to
17:16
that up there so it's very important to ensure that
17:18
ensure that
17:18
ensure that sql server agent is enabled for the two
17:20
sql server agent is enabled for the two
17:20
sql server agent is enabled for the two jobs to be
17:21
jobs to be
17:21
jobs to be to be created the cleanup and the
17:23
to be created the cleanup and the
17:23
to be created the cleanup and the capture and also if you want to monitor
17:26
capture and also if you want to monitor
17:26
capture and also if you want to monitor cdc
17:27
cdc
17:27
cdc sql server has um dynamic management use
17:30
sql server has um dynamic management use
17:30
sql server has um dynamic management use for
17:31
for
17:31
for for that
17:34
use cases specifically for cdc very many
17:37
use cases specifically for cdc very many
17:37
use cases specifically for cdc very many you can crack data changes for audit
17:39
you can crack data changes for audit
17:39
you can crack data changes for audit purposes
17:40
purposes
17:40
purposes you can send them to other subscribers
17:43
you can send them to other subscribers
17:43
you can send them to other subscribers do analyt
17:43
do analyt
17:43
do analyt analytics on change data etl operations
17:48
analytics on change data etl operations
17:48
analytics on change data etl operations event-based programming you can get an a
17:51
event-based programming you can get an a
17:51
event-based programming you can get an a ai models on them so much there's so
17:54
ai models on them so much there's so
17:54
ai models on them so much there's so much you can do
17:56
much you can do
17:56
much you can do with cdc
17:59
great and now let's see change tracking
18:02
great and now let's see change tracking
18:02
great and now let's see change tracking so change tracking is also similar but
18:05
so change tracking is also similar but
18:05
so change tracking is also similar but different from
18:06
different from
18:06
different from uh cdc because so it records that in a
18:09
uh cdc because so it records that in a
18:09
uh cdc because so it records that in a table
18:10
table
18:10
table those were changed so without capturing
18:12
those were changed so without capturing
18:12
those were changed so without capturing the actual data that uh
18:14
the actual data that uh
18:14
the actual data that uh that was changed so after you configure
18:17
that was changed so after you configure
18:17
that was changed so after you configure exchange tracking on the table
18:19
exchange tracking on the table
18:19
exchange tracking on the table any dml statement that effect goes in
18:22
any dml statement that effect goes in
18:22
any dml statement that effect goes in the table will
18:23
the table will
18:23
the table will cause change tracking information to be
18:26
cause change tracking information to be
18:26
cause change tracking information to be recorded
18:27
recorded
18:27
recorded but while change tracking so you can see
18:30
but while change tracking so you can see
18:30
but while change tracking so you can see that an operation has been done such as
18:32
that an operation has been done such as
18:32
that an operation has been done such as update
18:32
update
18:32
update delete insert and the value of the
18:34
delete insert and the value of the
18:34
delete insert and the value of the column from the primary key of that row
18:36
column from the primary key of that row
18:36
column from the primary key of that row as you can see here
18:38
as you can see here
18:38
as you can see here um some key concepts you have the change
18:40
um some key concepts you have the change
18:40
um some key concepts you have the change tracking
18:41
tracking
18:41
tracking table every table will change tracking
18:44
table every table will change tracking
18:44
table every table will change tracking enabled
18:45
enabled
18:46
enabled um similar to cdc but in this case
18:49
um similar to cdc but in this case
18:49
um similar to cdc but in this case you have an internal on this table used
18:51
you have an internal on this table used
18:52
you have an internal on this table used by the
18:53
by the
18:53
by the change tracking functions to determine
18:55
change tracking functions to determine
18:55
change tracking functions to determine change versions and
18:56
change versions and
18:56
change versions and those changed since a particular version
18:59
those changed since a particular version
18:59
those changed since a particular version then you have the change tracking query
19:02
then you have the change tracking query
19:02
then you have the change tracking query function so
19:03
function so
19:03
function so this again supply the details on the
19:05
this again supply the details on the
19:05
this again supply the details on the changes in an easily consumed
19:07
changes in an easily consumed
19:07
changes in an easily consumed format so you can get all kind of data
19:09
format so you can get all kind of data
19:09
format so you can get all kind of data on the changes
19:11
on the changes
19:11
on the changes then you have an auto cleanup policy
19:15
then you have an auto cleanup policy
19:15
then you have an auto cleanup policy as we've seen before and uh change
19:17
as we've seen before and uh change
19:17
as we've seen before and uh change tracking current function which is
19:18
tracking current function which is
19:18
tracking current function which is interesting because
19:19
interesting because
19:19
interesting because so every time a user accesses the table
19:22
so every time a user accesses the table
19:22
so every time a user accesses the table you can get the version number
19:24
you can get the version number
19:24
you can get the version number active at that moment and if you keep
19:26
active at that moment and if you keep
19:26
active at that moment and if you keep that version number
19:27
that version number
19:27
that version number safely store somewhere next time when
19:30
safely store somewhere next time when
19:30
safely store somewhere next time when you go and access the
19:31
you go and access the
19:31
you go and access the the table again uh you can see
19:35
the table again uh you can see
19:35
the table again uh you can see you can get the change date the net
19:36
you can get the change date the net
19:36
you can get the change date the net change data from that con
19:38
change data from that con
19:38
change data from that con from that function number so that that's
19:41
from that function number so that that's
19:41
from that function number so that that's that actually
19:42
that actually
19:42
that actually is very valuable a small comparison
19:46
is very valuable a small comparison
19:46
is very valuable a small comparison between change tracking and
19:47
between change tracking and
19:47
between change tracking and cdc uh you can see one of the missing
19:50
cdc uh you can see one of the missing
19:50
cdc uh you can see one of the missing chronos
19:51
chronos
19:51
chronos and asynchronous um
19:56
and asynchronous um
19:56
and asynchronous um change tracking mainly tells you yes a
19:57
change tracking mainly tells you yes a
19:58
change tracking mainly tells you yes a change has been made
19:59
change has been made
19:59
change has been made while cdc also shows you historical
20:01
while cdc also shows you historical
20:01
while cdc also shows you historical change data
20:02
change data
20:02
change data and because of that cdc of course has
20:05
and because of that cdc of course has
20:05
and because of that cdc of course has more overhead so when we talk about
20:07
more overhead so when we talk about
20:07
more overhead so when we talk about storage and
20:08
storage and
20:08
storage and performance overhead well change
20:10
performance overhead well change
20:10
performance overhead well change tracking has less
20:12
tracking has less
20:12
tracking has less um so both of them seem very similar in
20:15
um so both of them seem very similar in
20:15
um so both of them seem very similar in the sense that
20:16
the sense that
20:16
the sense that you have to first enable them at the
20:18
you have to first enable them at the
20:18
you have to first enable them at the database level and then at the tables
20:20
database level and then at the tables
20:20
database level and then at the tables level
20:21
level
20:21
level and the bot can be enabled on the on the
20:24
and the bot can be enabled on the on the
20:24
and the bot can be enabled on the on the same database
20:26
same database
20:26
same database without um additional considerations
20:31
without um additional considerations
20:31
without um additional considerations again to enable change tracking as i was
20:34
again to enable change tracking as i was
20:34
again to enable change tracking as i was mentioning you have to do that on them
20:36
mentioning you have to do that on them
20:36
mentioning you have to do that on them database level as you can see here you
20:38
database level as you can see here you
20:38
database level as you can see here you have
20:39
have
20:39
have the retention policy you you can
20:42
the retention policy you you can
20:42
the retention policy you you can configure that
20:43
configure that
20:43
configure that and then when you enable it at the table
20:45
and then when you enable it at the table
20:45
and then when you enable it at the table level um
20:46
level um
20:46
level um column tracking basically enables your
20:48
column tracking basically enables your
20:48
column tracking basically enables your application to
20:50
application to
20:50
application to synchronize only those columns that were
20:52
synchronize only those columns that were
20:52
synchronize only those columns that were updated so this
20:53
updated so this
20:53
updated so this is great because you can improve um
20:56
is great because you can improve um
20:56
is great because you can improve um efficiency and performance however
20:59
efficiency and performance however
20:59
efficiency and performance however maintaining column tracking also add
21:01
maintaining column tracking also add
21:01
maintaining column tracking also add some extra storage overhead so
21:03
some extra storage overhead so
21:03
some extra storage overhead so this is off by default that's why you
21:05
this is off by default that's why you
21:05
this is off by default that's why you have to specifically put it as
21:08
have to specifically put it as
21:08
have to specifically put it as as on and then you can see how how to
21:11
as on and then you can see how how to
21:11
as on and then you can see how how to determine whether
21:13
determine whether
21:13
determine whether change tracking has been enabled and one
21:15
change tracking has been enabled and one
21:15
change tracking has been enabled and one example of a function that would get you
21:18
example of a function that would get you
21:18
example of a function that would get you the version number
21:22
and now let's move onwards to active
21:24
and now let's move onwards to active
21:24
and now let's move onwards to active geographication
21:26
geographication
21:26
geographication so with active jog application you can
21:29
so with active jog application you can
21:29
so with active jog application you can you can create to get double secondaries
21:31
you can create to get double secondaries
21:31
you can create to get double secondaries of
21:32
of
21:32
of azure sql databases on servers in the
21:34
azure sql databases on servers in the
21:34
azure sql databases on servers in the same or different regions and this is
21:36
same or different regions and this is
21:36
same or different regions and this is great for business continuity especially
21:39
great for business continuity especially
21:39
great for business continuity especially because
21:39
because
21:39
because it allows the application to perform
21:41
it allows the application to perform
21:41
it allows the application to perform quick disaster recovery of individual
21:44
quick disaster recovery of individual
21:44
quick disaster recovery of individual databases if you have all the outtake
21:47
databases if you have all the outtake
21:47
databases if you have all the outtake options and disasters like
21:50
options and disasters like
21:50
options and disasters like natural disasters human neighbors
21:52
natural disasters human neighbors
21:52
natural disasters human neighbors malicious acts and so on
21:55
malicious acts and so on
21:55
malicious acts and so on um so it asynchronously replicates
21:57
um so it asynchronously replicates
21:57
um so it asynchronously replicates committee transactions
21:59
committee transactions
21:59
committee transactions on the primary to a second login so
22:01
on the primary to a second login so
22:01
on the primary to a second login so asynchronously so
22:03
asynchronously so
22:03
asynchronously so well at any given point in time the
22:05
well at any given point in time the
22:05
well at any given point in time the second doggy might be slightly behind
22:07
second doggy might be slightly behind
22:07
second doggy might be slightly behind the primary but the secondary is
22:10
the primary but the secondary is
22:10
the primary but the secondary is guaranteed to never have partial
22:11
guaranteed to never have partial
22:11
guaranteed to never have partial transactions
22:13
transactions
22:13
transactions up to four second degrees are supported
22:16
up to four second degrees are supported
22:16
up to four second degrees are supported in same
22:17
in same
22:17
in same or different regions and again the the
22:20
or different regions and again the the
22:20
or different regions and again the the failover
22:20
failover
22:20
failover has to be manually initiated by the user
22:24
has to be manually initiated by the user
22:24
has to be manually initiated by the user and
22:24
and
22:24
and afterwards the new primary has a
22:27
afterwards the new primary has a
22:27
afterwards the new primary has a different
22:27
different
22:27
different connection than point some specific
22:31
connection than point some specific
22:31
connection than point some specific uh scenarios in this case you might use
22:33
uh scenarios in this case you might use
22:33
uh scenarios in this case you might use this for database migration so you can
22:36
this for database migration so you can
22:36
this for database migration so you can use it to
22:37
use it to
22:37
use it to migrate a database from one server to
22:39
migrate a database from one server to
22:39
migrate a database from one server to another online with
22:40
another online with
22:40
another online with minimum downtime and for application
22:43
minimum downtime and for application
22:43
minimum downtime and for application upgrades so for instance you can create
22:46
upgrades so for instance you can create
22:46
upgrades so for instance you can create an extra secondary
22:48
an extra secondary
22:48
an extra secondary as a failed copy during application
22:50
as a failed copy during application
22:50
as a failed copy during application upgrades and you can also use it for
22:53
upgrades and you can also use it for
22:53
upgrades and you can also use it for load balancing read only workloads
22:55
load balancing read only workloads
22:55
load balancing read only workloads between which we discussed about
22:57
between which we discussed about
22:57
between which we discussed about a few minutes a few minutes earlier so
23:01
a few minutes a few minutes earlier so
23:01
a few minutes a few minutes earlier so it is not this is not supported on azure
23:03
it is not this is not supported on azure
23:03
it is not this is not supported on azure sql mi
23:04
sql mi
23:04
sql mi so if you are looking for azure sql mi
23:07
so if you are looking for azure sql mi
23:07
so if you are looking for azure sql mi you might want to look into
23:08
you might want to look into
23:08
you might want to look into auto failover groups and that's
23:12
auto failover groups and that's
23:12
auto failover groups and that's pretty much it for active drug
23:15
pretty much it for active drug
23:15
pretty much it for active drug application
23:16
application
23:16
application and lastly for the avid scale out
23:20
and lastly for the avid scale out
23:20
and lastly for the avid scale out feature
23:22
feature
23:22
feature let's see so basically um this works
23:25
let's see so basically um this works
23:25
let's see so basically um this works in premium business critical and hyper
23:27
in premium business critical and hyper
23:27
in premium business critical and hyper scale and these
23:28
scale and these
23:28
scale and these tiers are provisioned with one or more
23:31
tiers are provisioned with one or more
23:31
tiers are provisioned with one or more secondary replicas which can be used for
23:33
secondary replicas which can be used for
23:33
secondary replicas which can be used for like load balancing with only workloads
23:36
like load balancing with only workloads
23:36
like load balancing with only workloads so for basic uh standard and general
23:39
so for basic uh standard and general
23:39
so for basic uh standard and general purpose
23:40
purpose
23:40
purpose these do not include replicas and the
23:42
these do not include replicas and the
23:42
these do not include replicas and the gate scale out feature
23:43
gate scale out feature
23:43
gate scale out feature is not available in these service tiers
23:47
is not available in these service tiers
23:47
is not available in these service tiers however like each each single database
23:49
however like each each single database
23:49
however like each each single database elastic pool
23:51
elastic pool
23:51
elastic pool and managed instances in the premium and
23:53
and managed instances in the premium and
23:53
and managed instances in the premium and business critical
23:54
business critical
23:54
business critical are automatically provisioned with with
23:56
are automatically provisioned with with
23:56
are automatically provisioned with with the primage right replica
23:59
the primage right replica
23:59
the primage right replica and several secondary only replicas and
24:01
and several secondary only replicas and
24:02
and several secondary only replicas and for hyper scale
24:03
for hyper scale
24:03
for hyper scale by default you have one second login
24:05
by default you have one second login
24:05
by default you have one second login replica
24:07
replica
24:07
replica created for from a new database so
24:10
created for from a new database so
24:10
created for from a new database so you can configure the sql connection
24:13
you can configure the sql connection
24:13
you can configure the sql connection string to to direct your application to
24:16
string to to direct your application to
24:16
string to to direct your application to a corresponding replica again uh
24:19
a corresponding replica again uh
24:19
a corresponding replica again uh to monitor you can use the many dynamic
24:22
to monitor you can use the many dynamic
24:22
to monitor you can use the many dynamic management
24:23
management
24:23
management views and if you have a long running
24:26
views and if you have a long running
24:26
views and if you have a long running query
24:27
query
24:27
query causing blocking it will automatically
24:29
causing blocking it will automatically
24:29
causing blocking it will automatically be terminated and through blocking we
24:31
be terminated and through blocking we
24:31
be terminated and through blocking we mean
24:32
mean
24:32
mean if an object modified on the primary
24:35
if an object modified on the primary
24:35
if an object modified on the primary while the query
24:36
while the query
24:36
while the query frequently locks the same object on the
24:38
frequently locks the same object on the
24:38
frequently locks the same object on the only replica
24:40
only replica
24:40
only replica and lastly it's always transitionally a
24:43
and lastly it's always transitionally a
24:43
and lastly it's always transitionally a transactionally consistent stay but at
24:45
transactionally consistent stay but at
24:45
transactionally consistent stay but at different points in time there may be
24:47
different points in time there may be
24:47
different points in time there may be some
24:47
some
24:47
some small latency between different replicas
24:52
small latency between different replicas
24:52
small latency between different replicas great so to bring this all together
24:55
great so to bring this all together
24:55
great so to bring this all together we've been looking at some
24:57
we've been looking at some
24:57
we've been looking at some interesting scenarios like synchronizing
25:00
interesting scenarios like synchronizing
25:00
interesting scenarios like synchronizing different
25:00
different
25:00
different uh workloads across regions and scaling
25:03
uh workloads across regions and scaling
25:04
uh workloads across regions and scaling out
25:04
out
25:04
out read-only workloads we expect some of
25:06
read-only workloads we expect some of
25:06
read-only workloads we expect some of these solutions
25:07
these solutions
25:07
these solutions uh time has been very limited so we
25:09
uh time has been very limited so we
25:09
uh time has been very limited so we didn't have the chance to go in depth
25:11
didn't have the chance to go in depth
25:11
didn't have the chance to go in depth for
25:12
for
25:12
for for all of them and this is in no means
25:14
for all of them and this is in no means
25:14
for all of them and this is in no means the most comprehensive list
25:16
the most comprehensive list
25:16
the most comprehensive list um i encourage you to to do more digging
25:19
um i encourage you to to do more digging
25:19
um i encourage you to to do more digging into this and
25:20
into this and
25:20
into this and please feel free to reach out with
25:22
please feel free to reach out with
25:22
please feel free to reach out with questions or feedback
25:24
questions or feedback
25:24
questions or feedback and yes this is all thank you
25:32
[Music]
#Arts & Entertainment
#Enterprise Technology
#Engineering & Technology