Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Referencing Remote Data


Referencing Remote Data

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36008 Visits: 18728
Comments posted to this topic are about the item Referencing Remote Data

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
brdudley
brdudley
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1533 Visits: 1737
I've only used synonyms on a couple of occassions. In each case, there were rather involved procedures which accessed tables outside the database -- in one case even outside the server -- and needed to be deployed multiple times.

In one instance, the first implementation used dynamic SQL to give the flexibility, so Synonyms were a huge advantage. Views would probably have worked, but would have required considerably more effort.

A more recent system uses views extensively, in part to abstract location of remote files. New columns tend to require quite a few changes.

It always seems like synonyms would be more useful if they could replace a portion of the object name instead of the entire name -- similar to an Envronment variable or logical name. Parsing would become harder, but it seems possible. It is common to access multiple objects from the same database or a parallel database on a different server.
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8252 Visits: 6124
As a developer who often takes full advantage of the split between developer and DBA roles (please read as a healthy respect for another's technical speciality), I like the use of synonyms or views as this allows the developer to do their work without prescribing how the DBA do theirs. Whilst I would like to think that I am better than your average developer when it comes to SQL, and SQL Server in particular, I hope that the DBAs I will work with would know databases far more than I.

My opinion is just an extension of how I think of stored procedures (SP). Whilst the SPs I write for systems often ship as is and I have taken serious consideration when writing them, I expect over time that my initial SQL will change and that a DBA may consider my initial version as an executable specification. My SPs may be "good enough" and may stand the test of time, however, I do not hold them so dear to me that I care whether they are changed by an expert who does so in a way that improves the system without breaking the code that relies on it.

In short (maybe), the layer of abstraction should be used in order to aid maintenance.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
David McKinney
David McKinney
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 2090
Hi Steve - good topic!

I'm a heavy user of synonyms -- in fact for every external database access whether on the same server or not.

My world is one of a fair number of interdependant databases. I can view these external dependencies by simply looking at (or scripting) the synonyms. (Although, if important, I back this up with a variety of search techniques, to ensure that nothing gets missed.)

A 'policy' (Policy Management) ensures that there are no 3 or 4 part names in my databases (as these should be in the synonyms.)

The other major benefit is that I can easily redirect my synonyms to help with testing etc.. In other words by simply changing the synonym, I can point to another instance of the base object. Indeed I've used this technique to render a database with many external database references 'stand-alone' by redirecting the synonyms to internally situated tables. (This was done for the purposes of demos / testing.

I would recommend everyone considers use of synonyms. The only case I've encountered where it wasn't a good idea is in a reporting database, which had exclusively stored procedures pointing to tables in other databases. This resulted in hundreds of synonyms, and added little value.

A final point is that SQL Prompt (for those who use it) still doesn't properly support synonyms. It's got to be the number one most requested item on the red-gate support forum.

All the best,

David.
Sigerson
Sigerson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 1232
A great little hidden gem, and thanks for pointing it out. One bonus of using synonyms (I've just learned) is the ability to GRANT or DENY privileges on the synonym itself. This comes in handy for me because I need to make a case management d/b talk to our main accounting d/b, a level of access that worries the CFO about possible data corruption. With a synonym, it sounds like I can build a link that is explicitly prevented from updating the accounting data. This may not be the most-used tool in the box, but it will certainly come in handy from time to time.

Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36008 Visits: 18728
David McKinney (1/10/2012)
Hi Steve - good topic!

A 'policy' (Policy Management) ensures that there are no 3 or 4 part names in my databases (as these should be in the synonyms.)


Great idea. Hadn't thought of that. That would make a a nice short article if you want to tackle it.


The other major benefit is that I can easily redirect my synonyms to help with testing etc.. In other words by simply changing the synonym, I can point to another instance of the base object. Indeed I've used this technique to render a database with many external database references 'stand-alone' by redirecting the synonyms to internally situated tables. (This was done for the purposes of demos / testing.

Now that's interesting.


A final point is that SQL Prompt (for those who use it) still doesn't properly support synonyms. It's got to be the number one most requested item on the red-gate support forum.


Send a note over as a reminder.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36008 Visits: 18728
Capt. Sigerson (1/10/2012)
A great little hidden gem, and thanks for pointing it out. One bonus of using synonyms (I've just learned) is the ability to GRANT or DENY privileges on the synonym itself. This comes in handy for me because I need to make a case management d/b talk to our main accounting d/b, a level of access that worries the CFO about possible data corruption. With a synonym, it sounds like I can build a link that is explicitly prevented from updating the accounting data. This may not be the most-used tool in the box, but it will certainly come in handy from time to time.


?? I'm confused. It "sounds like I can build a link.."? Can you explain that further?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4564 Visits: 9485
The thing about querying remote tables, especially when joining remote tables with local tables, is that the performance impact of the SQL coding tequnique is critical. I'm not sure that using synonymns to abstractly swap out a reference to a local table versus a remote table would result in the most efficient SQL, and it may often result in a very poor query performance. So even when the time comes to point that synonymn to a remote table on another instance, the developer may end up having to go back and re-code the structure of the SQL code anyhow. The usage of synonymns may actually obfuscate the underlying problem and make refactoring harder, because when reviewing the SQL code, it looks like only local tables are being referenced.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Sigerson
Sigerson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 1232
Hi Steve,

I was just thinking of MS-Access 'linked tables' when I wrote that. The Synonym command resemble that ability. I should have said 'allows me to create a synonyn.'

Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8252 Visits: 6124
Eric M Russell (1/10/2012)
The thing about querying remote tables, especially when joining remote tables with local tables, is that the performance impact of the SQL coding tequnique is critical. I'm not sure that using synonymns to abstractly swap out a reference to a local table versus a remote table would result in the most efficient SQL, and it may often result in a very poor query performance. So even when the time comes to point that synonymn to a remote table on another instance, the developer may end up having to go back and re-code the structure of the SQL code anyhow. The usage of synonymns may actually obfuscate the underlying problem and make refactoring harder, because when reviewing the SQL code, it looks like only local tables are being referenced.


I think that this is a valid point, however, I would guess that the opposite is most likely to be true i.e. that the code will assume a remote server using a particular synonym but in non-production environments it may happen to be changed to a local database.

As always, it is about a smart team knowingly using a feature appropriately as opposed to a team using a smart feature.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search