>> Changed data capture in SQL Server has been
a very popular feature for a very long time.
In this episode, learn what it is,
and learn what's new in
Azure SQL Managed Instance and Azure SQL Database.
This week on Data Exposed.
[MUSIC].
Hi. I'm Anna Hoffman and welcome to this episode of Data Exposed.
Today, I'm joined by Mara Steiu,
a Program Manager on the Azure SQL team.
Mara, thanks so much for joining us today.
Can you start by telling us a little bit about what you do?
>> Hi Anna. Thanks for having me.
I'm a Program Manager in the Azure SQL team at Microsoft,
and I'm focused on data replication technology,
such as change data capture,
change tracking, and SQL Data Sync.
>> Awesome. Well, it seems like you're the exact right person for
us to have on the show to talk more about change data capture.
Now, this is something that a lot of
SQL people might be familiar with,
but some people might also be new to this topic.
I'm pretty new to CDC or change data capture.
I'd also like to understand how it differs between SQL Server,
Azure SQL Managed Instance,
and Azure SQL database.
Mara, can you take us through like,
what is CDC and how does it work?
>> Sure. At the very high level,
CDC corrects and recurse the MN changes.
Inserts, updates, deletes that happened on your tables.
More specifically, the way it works is that first you
have to enable CDC on your database,
and then you enable CDC on
each table that you want to track for the ML changes.
Once you do that for each source table that you enable,
you have an associated CDC paper that has
been created in which we'll be able to see the data changes.
CDC consists of two main processes,
that is a capture process,
which takes the changes once they get to
the transaction log and puts them in the CDC table.
There is also a cleanup process which cleans
the CDC tables on a retention-based policy.
Lastly, you have some credit functions that
consume the data changes from the CDC table.
You are asking me how CDC differs across Azure SQL MI,
SQL Server, Azure SQL dB.
In our SQL Server and on Azure SQL MI,
we have this tool
captured and cleanup processes and SQL Server Agent drops.
However, media symphony Cynthia
analyze this integer sequence databases.
In sequence databases, these jobs are replaced by a CDC scheduler.
The scheduler guns automatically clean up and capture.
However, customers still have the option to
manually executes scan on or clean up,
but you can safely assume that it is being
done on the back on by the scheduler.
Apart from the scheduler,
there is not much that differs functionally for customers
across the different deployment types that we have for CDC.
>> That makes sense.
>> Yeah.
>> It's basically like we are taking the same exact technology,
but we had to add something to take the place of SQL Server Agent
since SQL Server agent isn't available in Azure SQL database.
It makes sense, it's the same technology apart from that.
Mara I have another question for you.
What are some use cases you're seeing people use CDC for?
>> Yeah. We see people using CDC for multiple purposes.
However, some of the most common scenarios that we've
seen are around auditing purposes and analytics,
so taking data from the change tables to run audit and analytics.
Then we see many customers sending changes to
other downstream subscribers or sending them from
the weathy system to the data lake or data warehouse.
Here we see, for instance,
Azure Data factory being used a lot to move change data from
the change tapers to other subscribers and
lastly we also see event-based programming applications.
Have your applications system
instantaneously respond to the changes in
your data and some applications from
these might be dynamic pricing.
Dynamic product pricing, you write based on
changes in your inventory that you meant.
You can achieve that.
This would be some of
the key scenarios that we've seen CDC used for.
>> Awesome. I especially like that fourth one.
Because instead of having to pull or whatever,
I could just instantaneously react to changes in my data.
That's pretty cool. Now, if I'm a customer and I'm like,
oh, this is awesome, I want to start using it.
How do I go about enabling or disabling it?
>> Yeah. That's a great question.
As I was mentioning,
first you have to enable CDC at the database level.
Here once you run this procedure that I
show you here and we'll also do a demo
so on to see how it works in practice.
Once you enable CDC at the database level,
these five tables that you can see here get
created in the system's stable on your database,
so it's important to monitor your space usage
closely because all these CDC artifacts are
being stored on the same source database.
Once you enable CDC at the database level,
the CDC schemer obviously gets created as well.
You can see in these tables what are the capture columns,
what are the change tables that we enabled for CDC,
what is the DDL history?
Here you can see all the DDL changes that have been
made since the capture instance has been created.
However, it's worth noting that,
once you make a DDL change on your source table,
that does not automatically get reflected in your change table.
You have to restart your captured instance.
That's something we constantly raise customers agonists around.
Then you have the index columns and
the long block sequence number time mapping.
This is all the data you can see in these tables.
Yeah. You also have to enable CDC at the table level.
Once you achieve that,
you can see that change table being
created and the CDC jobs stable.
This is for Azure SQL databases specifically.
If I mentioned that in my sequence server, sorry for interrupting,
you also have to make sure that the SQL Server Agent is
running for these jobs to be created
once you enable CDC at the table level.
>> Got it. This seems pretty
straightforward and I like to caveat again,
calling up a SQL Server Agent verse
what the CDC schedulers going to do.
Now Mara, during this, you mentioned a few things to be aware of.
Is there anything else that people should be aware of when
they enable this in Azure SQL database?
>> Yes. We constantly get a lot of questions around
performance considerations for CDC in general,
and it's very hard to offer some specific benchmarks or to
predict performance just because
it depends so much on every user's workload.
However, there are some key common factors
that users should be aware of in general.
You should be aware of the number of CDC-enabled tables,
the frequency of changes in those tracked tables,
the space available in your source database.
Because as I was mentioning,
all the CDC artifacts are being
stored in the same source database,
so it's good to be aware of that before you start using CDC.
Whether you are using an elastic pool or a single database.
Because in elastic pool,
you must also be aware of how many databases are
enabled for CDC apart from the number of tables,
just because you have those databases actually
having common resources such as disk space.
The more databases you enable within that pool,
the higher the test to get to the limit
of your elastic full disk space, for instance.
You should be aware of these differences, and in general,
we advise customers to look up for the CPU usage,
memory, and logs output.
>> Awesome, very cool. This is good to know
and great tips for people getting started.
Speaking of getting started and taking a look at this thing.
I understand you prepared something to
show us of actually seeing CDC in action.
>> Yes. I prepared a short demo
on how CDC works on an Azure SQL database.
This is a general-purpose database.
Before we get started,
something else that I wanted to mention is that CDC in
Azure SQL databases works on tiers higher than standard 3.
For instance, if you have a basic tier database in Azure SQL DB,
unfortunately, you can't use CDC only.
There is one more thing to be aware of before
you get started with CDC in Azure SQL DB.
Let's get started with the demo.
Here I have a simple database with some customers,
it's only one customer so far,
myself and I want to enable CDC on this database.
As you can see,
if I start here and I go into the system tables,
system tables will be creating the ones that we just talked
about along with the CDC schema.
This is taking just a little and you
can see the list of
captured columns once you enable CDC at the table level,
you can select which columns to be tracked for.
The change tables, these are obviously empty as if
now I'm just trying how they look like ddl history,
index columns and log sequence number, time mapping.
Then we're going to go to
our source table that we want to enable for CDC.
As I was mentioning, it only has one single customer as of now,
and I want to enable CDC at the table level as well.
Here you have some additional parameters
which I'm not using right now.
You can activate net changes
tracking so by default you get all changes,
but you can also support net changes when you enable tables,
and you can select the columns that you want for drag.
Right now, obviously, you can see
the change table has been created.
Once we enable CDC at the table level,
and this change table is empty as expected.
They all show up here,
the change table and the CDC jobs.
The CDC jobs, shows you
the default parameters which you can change
like retention policy that I was mentioning,
threshold, and so on.
Now let's insert some data into
these tables I'm adding Anna as a new customer,
and you can see this schedule by default automatically
has very quickly detected these DML change,
so Anna has already been added to the change table.
However, you can still run scan manually as you can see here.
Scans still works manually as it used to
on SQL Server and Azure SQL MI.
Here we can change the retention formula cleanup policy.
Let's say we want to clean up the change table,
for instance, every 30 seconds.
We're going to do this,
and if we go back to the cdc_jobs table,
which got created after we enabled the table,
you can see that the retention has been changed to 30 seconds.
The customer can configure this,
which is very convenient.
Let's say now we're done,
we want to disable CDC.
I'm looking at all the system tables and here I can
see all my tables and CDC artifacts.
If I forget which ones are enabled for CDC,
in my case, I only have one so it is quite easy,
but I can easily see here is tracked by CDC,
the 7th one which is the customer table.
I disable the table and then I
disabled the database for CDC and once you do that,
all the CDC artifacts are automatically
deleted and as expected, they go away.
That's pretty much it.
That's a very simple demo for how CDC
works in Azure SQL databases.
Here you can see the system tables section is empty.
>> Cool, Mara, this is a great demo.
I love how you took us through the whole story of,
how do I go enable this at a database level,
at table level, and how do I see it in action?
I didn't even know I was going to be a part of his demo,
it sounds cool to you to get added into your customer's table.
As we close out,
Mara, do you have any tips for our viewers?
Whether they're just getting started
or they're very familiar with this?
Any tips on how they get the
most out of this or [inaudible] or anything else?
>> Definitely. As I was saying,
we just very recently released
the public view of CDC in Azure SQL databases so we have
a bi-weekly blocks series that you can keep track of at this link.
As I was mentioning,
one of the most common scenarios for using CDC is
to send data changes to other downstream subscribers.
In this series of blogs,
we're going to show you every other week how to use
Azure Data Factory and other cool technologies in
integration with CDC to
send your change data to other subscribers,
and also here are some links for CDC in general in SQL Server,
Azure SQL MI, and Azure SQL DB,
how you can enable, disable,
how it works with other features.
How to administer it and monitor.
Look four months and so on.
>> Awesome, thanks so much Mara and for our viewers,
we're going to put all of those links in the description,
so be sure to check the description and check out these resources.
Thanks again, Mara so much for
coming on the show we really appreciate it.
I learned a lot, I think our viewers learn a lot too.
For our viewers, if you like this video,
go ahead and give it a like,
leave us a comment and let us know what you're
doing with CDC or what you learn in this video,
and we hope to see you next time on Data Exposed.
[MUSIC]