August 30, 2014 at 8:03 am
first, I'm going to tell you that there's a system admin guy on my team that seems to come up with junk statements and makes dubious claims. Recently, he told me that whenever joining on tables from different databases it is an 'Enterprise Best Practice' to join using linked server objects to make those joins even if the databases are hosted on the same server. (where linked servers don't exist, to create them, as well)
For example.
ServerAlpha has 5 databases:
DatabaseA
DatabaseB
DatabaseC
DatabaseD
And I need to build a query using tables from DatabaseA and DatabaseB.
Typically in those situations I just use
databasename.schemaname.tablename
....but he is suggesting instead I always use
linkedservername.databasename.schemaname.tablename
His reason: that you never know when the databases will be decoupled from the server and you don't want to have to go back into all your ssrs reports and edit the joins.
My thought about this is that the databases should be kept together given the high incidence of cross over joins AND it is just as possible for future database redesigns to involve merging two databases or revamping them so much that regardless of what server they're on, the query needs to be edited.
I believe this senior system admin made this statement only because I was wanting to join on two databases that in down-level didn't exist on the same server (they do in production) and didn't want to get involved in mirroring the production environment, but if you would please weigh in on this idea of ALWAYS using linked server objects when qualifying table joins, I'd appreciate it.
--Quote me
August 30, 2014 at 9:22 am
polkadot (8/30/2014)
first, I'm going to tell you that there's a system admin guy on my team that seems to come up with junk statements and makes dubious claims. Recently, he told me that whenever joining on tables from different databases it is an 'Enterprise Best Practice' to join using linked server objects to make those joins even if the databases are hosted on the same server. (where linked servers don't exist, to create them, as well)For example.
ServerAlpha has 5 databases:
DatabaseA
DatabaseB
DatabaseC
DatabaseD
And I need to build a query using tables from DatabaseA and DatabaseB.
Typically in those situations I just use
databasename.schemaname.tablename
....but he is suggesting instead I always use
linkedservername.databasename.schemaname.tablename
His reason: that you never know when the databases will be decoupled from the server and you don't want to have to go back into all your ssrs reports and edit the joins.
My thought about this is that the databases should be kept together given the high incidence of cross over joins AND it is just as possible for future database redesigns to involve merging two databases or revamping them so much that regardless of what server they're on, the query needs to be edited.
I believe this senior system admin made this statement only because I was wanting to join on two databases that in down-level didn't exist on the same server (they do in production) and didn't want to get involved in mirroring the production environment, but if you would please weigh in on this idea of ALWAYS using linked server objects when qualifying table joins, I'd appreciate it.
Quick thought, this doesn't hold even a thimble's fill of water, if the database is moved to another server, that means that all databases have to be moved to the same linked-server as linked-server names cannot be duplicates. Better approach would be to parametrize the source whenever it's possible.
😎
August 31, 2014 at 11:21 am
Clarification of your answer please.
If there are 2 servers whose databases are linking to a 3rd server's database, do each of the 2 server's linked server names have to be unique when accessing the 3rd server's database?
IE. are you saying this scenario is not possible?:
ServerA/DatabaseA has linked server name for DatabaseC on ServerC called 'Portal123'
ServerB/DatabaseB also has linked server name for DatabaseC on ServerC called 'Portal123'
DatabaseC is a moving target (gets moved around from server to server:-)) and 'Portal123' linked server accommodates queries from both DatabaseA and B?
--Quote me
August 31, 2014 at 11:51 am
polkadot (8/31/2014)
Clarification of your answer please.If there are 2 servers whose databases are linking to a 3rd server's database, do each of the 2 server's linked server names have to be unique when accessing the 3rd server's database?
Going back to the initial post:
....but he is suggesting instead I always use
linkedservername.databasename.schemaname.tablename
His reason: that you never know when the databases will be decoupled from the server and you don't want to have to go back into all your ssrs reports and edit the joins.
Lets say we have a server SRV_A with a linked server name LSRV_A and on it databases DB_1 and DB_2. A query in DB_1 according to the SA would be select * from [what ever] join LSRV_A.DB_2.... Now the DB_2 is moved to another server, SRV_B, the LSRV_A would have to be changed to point to SRV_B etc., leaving this to the requirements that any direction of linked server connection has to have its own "alias". Doesn't make thing easier.
😎
August 31, 2014 at 12:39 pm
Now the DB_2 is moved to another server, SRV_B, the LSRV_A would have to be changed to point to SRV_B etc.,
Yes, and that's his point. That if a lot of queries are joining those two databases, there would be a single place to make update if/when the database moves to another server.
So, it is reasonable, except to me because in our production environment these two databases are very much entwined in content. Redesign plans are underway. Those redesign plans are most likely going to result in creation of a brand new database. In this case there is no avoiding editing the ssrs queries, furthermore, don't queries across linked server objects take longer?
--Quote me
August 31, 2014 at 1:14 pm
polkadot (8/31/2014)
Now the DB_2 is moved to another server, SRV_B, the LSRV_A would have to be changed to point to SRV_B etc.,
Yes, and that's his point. That if a lot of queries are joining those two databases, there would be a single place to make update if/when the database moves to another server.
So, it is reasonable, except to me because in our production environment these two databases are very much entwined in content. Redesign plans are underway. Those redesign plans are most likely going to result in creation of a brand new database. In this case there is no avoiding editing the ssrs queries, furthermore, don't queries across linked server objects take longer?
That's possibly your best defence against this eccentric suggestion. Compare the execution plan of a query joining cross-database and between local and remote, where remote is a linked server to a db on local. Then explain the hoops you have to leap through to make linked-server queries even reasonably efficient. This is something you could test and document yourself.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 31, 2014 at 1:19 pm
polkadot (8/31/2014)
Now the DB_2 is moved to another server, SRV_B, the LSRV_A would have to be changed to point to SRV_B etc.,
Yes, and that's his point. That if a lot of queries are joining those two databases, there would be a single place to make update if/when the database moves to another server.
So, it is reasonable, except to me because in our production environment these two databases are very much entwined in content. Redesign plans are underway. Those redesign plans are most likely going to result in creation of a brand new database. In this case there is no avoiding editing the ssrs queries, furthermore, don't queries across linked server objects take longer?
No, in my opinion you are right, let me explain further: If DB_1 queries DB_2 and DB_2 also queries DB_1 on the same linked server using the same linked server name and one is moved, how do you resolve this with changing the linked server configuration, it's not possible. So DB_1 would have to have it's own linked server configuration for connecting to DB_1 and vice versa. Quickly becomes quite entangled.
😎
August 31, 2014 at 1:37 pm
hmmmmm.
--Quote me
August 31, 2014 at 1:42 pm
ChrisMhome. Performance comparison of linked query versus without is verified. Execution time is 2x longer with the linked query that brought me to this forum.
--Quote me
September 3, 2014 at 3:35 pm
thinking about this further, I don't see evidence from my reading, that two different servers can't both point to the same third machine using the same linked server name.
Additionally, am finding it's advocated elsewhere, a s a way to quickly change from test to prod
http://www.sqlcircuit.com/2012/09/sql-server-how-to-create-linked-server.html
--Quote me
September 3, 2014 at 3:40 pm
polkadot (9/3/2014)
thinking about this further, I don't see evidence from my reading, that two different servers can't both point to the same third machine using the same linked server name.Additionally, am finding it's advocated elsewhere, a s a way to quickly change from test to prod
http://www.sqlcircuit.com/2012/09/sql-server-how-to-create-linked-server.html
Quick question, does A query B and B query A where A and B reside on the same server, referencing the same linked server alias as the first of a four part reference?
😎
September 3, 2014 at 7:51 pm
Quick question, does A query B and B query A where A and B reside on the same server, referencing the same linked server alias as the first of a four part reference?
Yes, both databases are on the same server in production. The linked server object only supports one direction: Database A can query B, but Database B can't query A. Yes, the linked server name has to be the first name in a four part reference and the query runs fine without using the linked server alias, so it affects performance but the sys admin advises this as best practice in the event the databases are separated.
Let's assume, that Server A continues to the host the database which I have designated as the anchor for the report query. In this case, the report will still run because all one has to do is update the linked server object with new server name for database B.
Please advise if any white paper on true enterprise best practices for extensibility of environments with minimal reworking of dependent objects: sprocs, queries in SSRS reports, SSIS ETL packages, etc.
--Quote me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply