[MUSIC].
>> Hello everyone. My name is Colin Murphy.
I'm a Senior Product Marketing Manager at
Microsoft and I'm here today joined by Anna Thomas,
Data and Apply scientists in our data division. So welcome Anna.
>> Thanks Colin. Excited to be here.
>> Today we're going to talk about
linked servers in Azure SQL Database.
So Anna, can you explain what is linked servers and how it's used.
>> Yeah. Sure. So linked servers
in Azure SQL Database Managed Instance,
allow you do things like access data from
outside of Azure SQL Database Managed Instance,
and you might want to do this to do distributed queries, updates,
commands across your whole enterprise or your whole data state.
You're also able to address
diverse data sources in a similar fashion.
So it's really interesting,
it's almost exactly the same as in SQL Server.
The only difference is that we're still
working on building out functionality
to other providers apart from SQL Server and Azure SQL Database.
>> So can you name examples of some of the other providers to
see what's the host supports on prim with link server?
>> Yeah. So on prim,
I believe you can connect to things like Oracle
or DB2 or other places that your data is located.
>> Cool.
>>Yeah. But what's needed to connect to this is
an OLE DB provider and an OLE DB data source.
So we're just going to get into
how you might set up that linked server.
I know there's a lot of comments here
but we'll just take it line by line.
So basically, what we're going to do
is use this sp_addlinkedserver.
In doing that, we specify what we want to call
our linked server as well as the provider and the data source.
Once we've got that input,
we'll go ahead and add
the link server login using our username and password.
>> Great. Then I just noticed that we came to the IP address.
So this would be through like VPN or express route.
So you will see not connecting directly till you connect to a VPN
so effectively it's the same network,
you just have your own private Cloud using part of it is on prim.
>> Right. Yeah. Exactly. I should
note that this instance that we're
connecting to is actually an instance of
SQL Server 2019 running on Linux.
>> Okay. Cool
>> So once we have that created,
we can optionally set a bunch of different options.
You can see we'll use SP Server option to denote that in
each case and then you just specify
the server and the name of the option and the value.
So some of the options that you can do are
enabling or disabling link server for distributed query access.
You can enable RPC from into the server,
and you can also specify
the timeout value for connecting or querying.
You can see here I'm using zero which is just the default.
A few other options that you can set here,
but let's just go ahead and create that linked server.
So you can see it creates pretty quickly.
We can go ahead and refresh this
and take a look and see if we have it,
and sure enough, we've got
our SQL Server 2019 link server appearing here now.
Now, what this enables us to do is
something actually quite interesting is to do
something like cross database join between
Azure SQL Database Managed Instance and SQL Server 2019.
So here you can see I'm joining name from
both instances and the last modified
date from each MI and SQL Server.
So I'm basically able to do joint across both,
and get side-by-side the values
for MI as long as the values for SQL Server.
So that's pretty cool.
Finally, I can check the updatability of
the link server and I just wanted to call this
out because I find it quite interesting.
But this allows me to see if it's read
only or read write or the type
of access I have to a linked server.
In this case you see that I have read write
access to that SQL Server 2019 Instance.
>> That's really cool. So with
the link server and doing this cross database join,
I can actually leverage
all the investments I've made in SQL Server on prem,
same skill set and now I can actually start using
the Cloud with Azure SQL Database Management Systems,
and nothing is changed, it's just the same.
>> Yeah. Exactly. There's one other cool thing too that you
can do that's because of the way Managed Instance is setup.
I can actually leverage the secondary that we have,
and use it as a read-only source and
so I can use this to offload heavy workloads that are read based.
>> All this is great. Thank you Anna.
So that's really useful information.
I didn't know what link server was before, so I thank you again.
So everyone thank you for watching in and give
us a like and if you like this,
then subscribe and then we'll give you
more information on where to
get info on link server. So thank you very much.
>> Thanks.
[MUSIC]