July 16, 2014 at 4:44 am
Hi. I've been wondering about something I'm observing today, and wondered if anybody might be able to explain it to me. (I've tried Googling but I'm not getting the right sort of answers!)
I have a linked server set up from server A to server B (they're geographically distant), and I have some long-running queries (mostly selects but also updates) going from A to B (and back again). I've been watching both sides of the process with sys.dm_exec_requests because I'm observing the wait types, and I noticed that on server A, all the activity takes place in database_id 6 (which is correct), but on server B it's all happening in database_id 1, which is master.
Is that something to do with how linked servers work? Do they always connect 'through' master? Is this because the linked server settings don't specify a catalog? (Does that make sense?)
Thanks
July 16, 2014 at 6:03 am
a linked server can (and arguably should) use a default catalog. If one is not specified the linked server will allow access to all databases the user has access to. A linked server is a uni directional path, it allows access to one SQL Server instance from within another, the link is not transitive.
Always specify a catalog where possible and always specify an element of security would be the primary considerations when setting up your linked server(s)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 16, 2014 at 7:54 am
Thanks. So does that explain why the activity I'm seeing is in master?
(I did actually originally set the linked server up with a Catalog, but I was overruled by my Snr DBA ;-).)
July 16, 2014 at 8:04 am
Oh, also, would not specifying a catalog make the whole process any slower?
Thanks again.
July 16, 2014 at 8:39 am
Beatrix Kiddo (7/16/2014)
Thanks. So does that explain why the activity I'm seeing is in master?
If the users default database is master then yes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 16, 2014 at 8:40 am
Beatrix Kiddo (7/16/2014)
Oh, also, would not specifying a catalog make the whole process any slower?Thanks again.
If anything should be faster as it won't have to enumerate all the other databases on the instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 16, 2014 at 8:43 am
Cool, thanks very much. I always like to learn a bit more about what's going on under the hood.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply