>> Have you heard of SQL Data Sync?
In this episode of Data Exposed,
Mara Steiu shows us how it works,
when to use it and even a cool look at
a demo of bidirectional replication across the globe.
Learn more in this episode of Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and
welcome to this episode of Data Exposed.
Today I'm joined once again by Mara Steiu,
our Program Manager on the Azure SQL Team.
Mara, thanks so much for coming back on the show.
>> Thanks for having me again, Anna.
>> Yeah, of course. Today we are going to be
talking about a slightly different topic.
Last time we talked about changing a capture and
today we're going to be talking about SQL Data Sync.
This is something I don't know a ton about.
I'd love if you could just kick us off by telling us
more about what SQL Data Sync is and how it works?
>> Yes, sure. Very simply put a SQL Data Sync had to
synchronize your data across Azure SQL Databases and SQL Server.
The whole concept of SQL Data Sync evolves around the Sync Group.
The Sync Group consists of a Hub Database,
which has to be in Azure SQL Database.
One or more Member Databases which can be
either Azure SQL DBs or SQL Server DBs and VM for instance.
There are four key properties around the Sync Group.
When you create a Sync Group,
you have to set up the Sync Schema,
which is basically the selection
of tables that you want to have synchronize.
Then you have the sync direction and hear,
and you can do one direction replication or bidirectional sync,
which is really cool about SQL Data Sync.
You can go from Member to Hub,
Hub to Member or both.
Then you have a Sync Frequency.
When you set up your Sync Group you have
the option to run Sync manually on demand whenever you need it.
But you can also have it as Automatic Sync.
If you select "Automatic",
you can select frequency of as low as 30 seconds for instance,
but also as high as 30 days.
There's a lot of options that you have there.
Lastly, there is also a conflict resolving policy.
When you set up a Sync Group,
you have to tell the SQL Data Sync what you want happen
if you have some conflicts when changes are being made.
You want the Hub to win so the changes in Hub
override those on the Member or you want the Members to win.
You'll have to select one of these two options when
you create a Sync Group.
This would be a brief intro to how a SQL Data Sync works.
As I was mentioning SQL Data Sync support hub and spoke topology.
As you can see in this diagram,
the changes between Members are only done with
a Hub Database, which has to be in Azure SQL DB.
As you can see in the figure 2 the Members
get synchronized for changes that go to the Hub DB.
In the figure 2, you have a Member DB that's in
Azure and another one that's on-prem and for
on-prem data synchronization you need Sync Agent as well.
The Sync Agent is for SQL Server members.
This is pretty much a data security measure because it
uses encrypted connections and unique token and agent key.
The direct communication with the database
is behind a firewall is prevented.
Another security measures that we
recently implemented in SQL Data Sync
and is still in public preview is the Private Link.
Basically, this allows you to establish a secure connection between
the Sync service and your Member and
Hub Databases during the data synchronization process.
However, it's worth noting that if you want to use Private Link,
your members have to be on Azure SQL DB.
Yeah, that's pretty much it.
>> Awesome, cool. This is a good overview.
I also like how you're stressing the importance of security,
because when we're moving data around,
security is a huge piece of that.
Now, Mara, what are some use cases that you're
seeing for customers actually using this?
>> Yes. We actually see a broad range of uses for SQL Data Sync.
However, these would be the three key scenarios
that we see SQL Data Sync being used for.
First of all, we have Hybrid Data Synchronization.
You can obviously use this for
hybrid applications between SQL Server and Azure SQL DB and
this might be very useful for customers considering
moving to the Cloud
and putting some of their applications on Azure,
but still having databases on-prem as well.
You might have Distributed Apps.
Across database is storing
different workloads of your application.
You use SQL Data Sync for
Global Synchronization simply to
keep databases in regions across the world synchronized.
Then we'll actually see
this use case in practice in a demo very soon.
>> Cool, I would love to
see a demo of this Mara seems really cool.
Before we get into the demo,
just one last question.
Is there anything people should be aware of as far as
gotchas when they set up SQL Data Sync or choose to use it.
>> Sure. That's a very good question.
We get a lot of questions around performance impact.
It's worth knowing before you start using SQL Data Sync that
all the side tables and
sync artifacts are stored in your user's database.
Before you start using it you must hope
that you have the right service here or you're
having enough vCores inorder to just struggle on that data.
Then it's also important to be aware that we guarantee
eventual consistency or if
changes and eventually made and there's no data loss.
However, it's worth noting
that a transactional consistency is not guaranteed.
Lastly, there are some limitations that
are very well documented and shapes some links.
But for instance, all the tables that you had must have
a primary key. AAD authorization is not supported as of now,
and somewhat of imitations.
Also it's worth mentioning that SQL Data Sync is
not supported in Azure SQL Managed Instances.
>> Got it. These are good things to keep
in mind and we'll put a link to
a bunch of different resources in
the description for folks to go deeper into this.
Mara, can you show us an example of this in action?
>> Sure. As I was mentioning I'm going to show
you the scenario of
synchronizing data across databases
in different regions of the world.
Let's assume we have a foods business and they want
to synchronize their inventory across US and Asia.
You want to make sure you have
consistent inventory across these agents.
We're going to do bidirectional data synchronization
using SQL Data Sync in the Azure portal.
You can also decide to use SQL Data Sync using
Power Shell or REST APIs and again I share more links.
But for the purposes of this demo we're using the Azure portal.
Let us see quickly how this works.
I'm on the US database right now,
which is going to be my Hub Database.
As you can see, I have four inventory items in the US.
Here's my Asia database,
my Member Database, which is also in Azure SQL DB.
Which means that we can use Private Link.
However this only has the first three items that we see in the US.
We want to make sure these databases are in sync,
which is not the case right now.
I'm going to create the Sync Group and I start on
the Hub Database side and then create a name for the Sync Group,
which is US to Asia.
I'm going to create a new meta-database in my own Resource group.
This is going to be on the East US region in the US.
However, we are going to set it up as basically in this workload.
As you can see, I can set up Automatic Sync so I will
have the sync frequency every two seconds I want
sync run, and I want to use
Private Link for security reasons as we were
talking before and then create in the Sync Group.
However, before the Sync Group is created,
I must manually approve the Private Endpoint connection,
which is the Private Link.
I'm going to approve it first.
You're going to put our response message,
and it's quickly being approved.
Once the Private Endpoint has been approved,
you can see the Sync Group has has been created,
and now I'm adding Sync Members,
so on the Hub Database,
I have to log in with the server details on the Hub Database.
I'm adding an Azure Database as a member,
which is the Asia member.
I'm selecting the web server.
I'm doing bidirectional sync and logging in the
Asia test sever with a username and password.
Once again, I have to use Private Link for the Sync Service to
connect to the Member Database as
well as I had to do on the Hub Database side,
and in this case, I'm going to show you a different way to
approve the Private Endpoints.
I'm going to go on the server side for the Asia Demo,
and I'm going to go into the Private Endpoint connections
that's just another way to approve the Private Endpoint.
Once the Private Link is approved on the Member Database,
you can see that the Member Database
will be added to the Sync Group.
Here and lastly, I have to configure the Sync Group.
Here I can select the Sync schema,
which we talked about so I'm going to to
select which tables I want to Sync,
and in our case,
we only have one table and it detected it has three columns.
I'm going to add all of them on the Hub Database side,
and I'm going to do the same on the Member Database side.
Remember that the tables need primary keys,
as I was mentioning, is one of the limitations.
Here you can see the Sync Group has been created and it's
running automatically every two seconds,
which is the frequency we set.
We have two databases the table as expected,
and here you can see down some monitoring as well.
Now the databases should be in Sync bidirectionally.
What I'm going to check right now,
if we have the same inventory across both databases,
because remember, we had one more item
in the US when compared to Asia.
I'm going to open the Query editor for
both Member and Hub Databases.
Very quickly I'm going to check.
Here you can see all the SQL Data Sync artifacts
on your source database being stored,
which is why I was saying you must be aware of
your space before you start using SQL Data Sync.
I'm in Asia Demo Member now, we have the same inventory,
same as on Hub.
>> Very cool. We can see it's working and Sync is in progress.
>> Yes. Here I'm also adding
some more items on Asia and they will get reflected
on the US side, as expected.
Here you can see is
if I insert data on the Member Database in
Asia, it should get reflected as
expected in the Hub Database, the dragon fruit.
Unfortunately I made the very small spelling error,
I'm fixing that and hopefully it will work.
Sometimes it takes some time.
Now you can see it's in Asia.
If I run it in the US, it does not show up yet,
I have to wait a little,
and once I run it again it should show up on the Hub Database.
Because remember we do have the frequency every two seconds,
but the actual sync takes a bit of time,
so and that's something to be aware of.
As you can see, this work,
and we had set very simple bidirectional application
between two regions around the world.
>> Awesome. So Mara, just a few questions now,
super interesting and I learned a few things.
But, when you are doing this,
when you say bidirectional,
does this mean that I can write to both Hub and
Member Databases and then those changes will be replicated across?
>> Yes, it goes from Member to Hub and Hub to Member.
That's pretty powerful, but that also
explains why you need this conflict resolution,
like if there's any sort of conflict,
maybe with the primary key, then who wins?
That's a really cool. The other thing I noticed
during your demo is this service,
the wizard, basically, you took us through the portal.
It's actually going out and creating
the Private Endpoint connections for both of those servers?
>> Yes, it creates the Private Endpoint connection
for the Sync Service to connect
to the hub and the Private Endpoint
connected from sync service to connect to the Member Database.
>> That's pretty cool, taking a lot of the live work out of it,
and you just have to go on and approved that
you're okay with this service being created?
>> Yes, you have to make sure you manually approve it before the
sync gets created and the Member Database gets created.
>> Very cool. Then my final question for you,
I'm sorry, you've got a lot of questions today.
My final question to you is for viewers that have
watched the video on CDC and now are
watching this video on SQL Data Sync can you give
some insights on what to use when?
>> If you, for instance,
have a high volume of data,
and you want to send the data changes,
you want to stream them to other sources,
you might, find CDC more helpful to use.
Because integrated with Azure Data Factory for instance,
you can send high volumes of data.
However, if you have smaller databases,
smaller data and you don't need transactional consistency.
As I was mentioning about SQL Data Sync,
SQL Data Sync might be the right tool to use.
It depends on your data volume, if you need it.
Close up to near real time,
so closer to real time or less those,
SQL Data Sync might also take longer,
so it depends on the time and the volume of data.
For higher volumes and closer
to real time, I really recommend the CDC approach.
For those, the image,
you don't necessarily need the real time or near
real time and if you have less data that you might
decide to use SQL Data Sync.
>> Awesome, cool. That's a really helpful which is kind of
guidelines and I love it's the classic example,
it depends on what your scenario is.
But it's good to know that we have these two services and
your input on what to use when.
Thanks so much Mara again for joining us on the show.
Again, it was really useful for me. I learned a lot.
Your demo was really cool.
I think our viewers probably feel the same way.
To our viewers out there, be sure to check
the links in the description to learn more about
SQL Data Sync and also leave a comment and let us
know what you thought of Mara's demo and the service itself.
With that being said, thanks for joining us and
we hope to see you next time on Data Exposed.
[MUSIC]