>> You might have heard of Ledger for
Azure SQL database and SQL Server.
In this episode, we will dive into some examples, some demos,
and how you can get started with
Ledger in SQL Server and Azure SQL.
This week on Data Exposed, MVP Edition.
>> [MUSIC]
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed, MVP Edition.
Today, I'm super lucky to be joined by Karen.
Karen, thanks so much for joining us today.
Can you kick us off by telling us a little bit about what you do?
>> Oh, wow. Great question.
The title I've been using lately is Data Evangelist,
because I'm a little bit of everything.
I feel like I'm speaking for the data.
I'm a Microsoft MVP and data platform.
Lately, I've been focusing on security and data quality,
and the topic today shall pull those together.
>> Yeah, exactly, and it's a great leading,
and I'm so glad we have you on my show,
because I know you've been working with customers,
with Ledger Technologies,
and today we're going to be taking a deeper look
because we've done some episodes with Peter,
and Yaakov, and other folks from the team.
But what we're going to do with you is a deeper look.
How do I actually get started?
What are people actually doing with this,
and all that good stuff.
That I'll just pass it to you.
I've only seen that inter-related stuff,
so yeah, right into it.
>> Oh, that's great, and thank you for having me here.
There's been a lot of content,
as you said already,
on Data Exposed about the basic fundamentals.
I'm going to point everyone to go watch those for
that introductory level stuff.
I wanted to talk today, just a little bit,
about why you might want to use these in your plain old databases.
As also, how can you go
from databases you already have in production,
to taking advantage of SQL Ledger Tables, and databases.
Some of the fundamentals about blockchain in general.
I'm not just talking about ledger tables here.
It's that we typically say that it's a special type of
system that is optimized for immutability and building trust,
and tamper evidence into your data systems.
These have timestamp transactions,
immutability, distributed multiply-owned nodes,
and each node has blocks that are chained
together along with cryptographic hashes of those.
The difference between what SQL Ledger does,
and what full-blown blockchain solutions do,
is that SQL Ledger is right inside existing SQL Server,
and Azure SQL DB products and services.
It doesn't have that one component of blockchain,
which is distributed multi-owned nodes, and that's actually,
from my point of view,
of benefit, because, of course,
we don't want people seeing
all the transactions in our own ledgers.
This fits in that spot between full-blown block chain,
and regular SQL Server.
I want to point out that a lot of people,
as soon as I start talking about blockchain,
they tune it out because they see blockchain is
a fluffy buzzword that's only
associated with evil transactions, and bitcoin.
But that's not true,
and I want to point out as well as I get a lot
of feedback as I talk about this,
that Blockchain does not validate the accuracy of the data.
If you lie to the Blockchain,
or to a ledger table,
they're going to happily process
all that data and say it's totally true.
Just a brief overview of
ledger tables in Azure SQL DB, and SQL Server.
You have the SQL Database Engine.
You have these ledger views,
these ledger tables that show you your ledger.
You can have either updateable,
or append-only ledger tables,
and if they are updateable,
they're still really only append only,
but with a history table that keeps track of what it used to be,
so they're really a hybrid of what you think of updating a table,
and I love this feature in
the Microsoft products because many other database projects,
products in this area,
don't support this logical updatability to it.
Then there's the solid red line.
The way you're going to be able to trust your data,
is you have these fancy dancing DBAs,
who are processing data against the tables,
doing their normal DBA stuff,
because it's just a property of a table in SQL Server.
It's not a special type of system.
It's just a table with an additional property,
and we'll see that in a minute.
You build this giant wall of defense between that,
and where all the digests are stored,
which are the ways that you will be able to see
if the data in the database has been tampered with,
because the ledgers themselves are held in
a separate accounting system,
meaning a separate storage system,
and if one person has access to both of these things,
and you're not using confidential computing,
you might not be able to trust it.
Some key features in ledger tables.
You can have both a ledger database.
That means all the tables in it are ledger tables.
It uses database digests that
in Azure SQL DB are automatically created,
these storage accounts that hold all the digest.
You have the ledger tables that can be updated, or append-only.
You should use immutable storage
for recording these transactions,
and then there's a feature where you can go
ask the ledger system, hey,
is the data in my database still consistent
with what was stored in the immutable storage.
When I say it's just a simple property of a table,
if we look at this very simple database,
this create script,
you can see in order to make it an append only table,
all I do is my regular create statement.
I can generate the scripts no
matter how I normally generate them,
and just add with Ledger on,
and append only on,
and append only on is optional.
If you just say ledger on,
it should make it append only.
>> Quick question.
>> Yeah.
>> This is for a new table,
but what if I had an existing table,
could I still turn on ledger?
>> You can, but not like using an alter statement,
so you can't just say alter the table to add this property,
and I'm going to have a demo where I
show you how you need to do it.
>> Cool.
>> Yeah, that's my first question.
Also with an updatable table,
it's the same thing.
You'd say, "System Versioning On," and that means,
behind the scenes,
SQL Server, Azure SQL DB,
is maintaining all of these history tables,
taking care of generating all the blocks,
and everything, so that you can go back,
and ask the ledger,
this is the current value,
what were the previous values?
>> To answer your question,
Anna can you alter it?
No. You have to use a migration like process.
Depending on the size and the complexity of
the tables that you would like to convert,
what you would do is create a new ledger table.
Basically, what I do,
script it out of
SQL Server Management Server as a create statement,
give it a new name,
give it the properties,
then copy the data from the existing table to
the new table and then clean up the previous table.
Now of course, if it's a very large table or you have
those life complexities like relational integrity,
referential integrity, and all of those things,
you'll have to do this just like
any other migration by creating the tables in the right order,
turning off the referential integrity,
and moving onto those things as well.
I have a little demo of how this would work.
In this demo, I have this table from NASA.
Did I mentioned I'm also a NASA data [inaudible]?
I like to use NASA open data for my demos.
In this table, I've got something called Near Earth comments.
This is NASA data that keeps track of where
all the comments are that are near Earth
because we might need to keep track of them.
I'm going to create this as
a ledger table because I wouldn't want someone to go in
and accidentally mess with the data or update the wrong table.
I have this table,
but now I'm creating it again new as a ledger table,
and I'm adding system versioning on,
telling it what I want it to name
the history table and saying making it a ledger one.
Then I'm using this stored procedure to copy
data in batches from the previous table to the new table.
That happened really quickly because this table isn't that
large and it doesn't have any dependencies on other tables,
but now we have that protection for
these comments that we're going to know
that people probably aren't messing with the data.
>> You can even say it says ledger updateable.
>> Yeah. That's how I'm naming it for my demos.
I do recommend, I'm not a fan of what I call metadata stuffing,
which is putting properties of something inside the name,
but it probably is a good idea as you're learning these things to
make sure that everyone knows it's a special type of table.
>> Karen. Sorry. There's a little icon.
It's slightly different,
is that like the ledger indicator?
>> I believe so, yes.
>> Yeah.
>> The thing is this is rate in Microsoft tools,
but if you use third party tools such as
a data modeling tool or
a database tool or something like that or just,
I don't know, scripting,
you won't necessarily have those indicators, but great question.
That was an append-only table.
Also in my database,
I have a table that
the original non ledger table was called MeteoriteLandings,
but I want that to be append only because of course,
a meteorite doesn't really land twice.
Once one going in there and messing with it.
If I try to update the original table,
it's going to let me do it,
but if I try to update the ledger table,
it's going to say you can't update append only tables.
That means if you are using ledger tables, your applications,
your systems that use it will also have to be able to
process these types of error messages as well.
One of the last things I wanted to talk about is,
how are we going to trust this data more?
You saw in that little mini demo that it's going to stop me from
an append-only ledger table is going to update like T SQL,
but I also want to be able to ask SQL Server,
is the data in the database still
consistent with all of
the data that's over in that immutable storage?
What digests verification does
is it checks the hashes and the digest to
see if anybody has altered the data and
cause the chains to lose track of each other.
It does a report based on where the digest is,
but this whole verification is totally
dependent upon separation of duties.
You can't have verification that you can trust if
the same person or the same group
has access to both the database and the digest,
and they're not in something
really locked down like confidential computing.
If I go back to SQL Server Management Studio,
you can see that I can run this query which is available in
the docs resources where I'm going to just ask for this database,
are there any digests that are out of sync with the databases?
You can see that it says leisure database has
succeeded and I throw in the snarky response,
but double-check no-one has changed the digest location
because that is also something you need to manage.
It's also telling me where
the digest is that it's verifying against.
There's all these things that need to be taken care of so that you
can truly have confidence in your data,
but we have to manage
all the additional features that make that work.
>> Got it. This is super cool.
I haven't seen it at this level of detail.
As we start to wrap up, Karen,
one question that I had that I
think other folks might have as well is,
how does this impact performance?
>> I do get that a lot.
I remember getting that question all the time with
does low level encryption or
everything and my answer to that is the same.
Of course there's extra processing that has to happen.
It's going to impact performance,
but my response to that is that's
okay because if you need ledger tables,
you need ledger tables.
All the ways we use to try to do this in the past by writing
our own log table via post transaction auditing,
all of those take costs and risks as well.
Just like encryption, if you need encryption,
you pay the price of encryption.
If you need ledger tables and ledger databases,
then set your standards so that you're going to take
advantage of these features and
understand the cost-benefit and risks.
>> Got it. Now, that's a great answer.
Thank you, Karen. Karen I learned so much in his app.
I think our viewers did as well.
As we wrap, to our viewers,
in the description we're going to put some links
and items for you to check out to learn more.
If you'd like this episode, go ahead and give it a like,
leave us a comment and let us know what you
think of these ledger capabilities and some of
the things Karen showed us today and
we hope to see you next time on Data Exposed.
[MUSIC]