Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Referencing Remote Data Expand / Collapse
Author
Message
Posted Monday, January 9, 2012 11:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:25 PM
Points: 31,279, Visits: 15,740
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
Post #1232931
Posted Tuesday, January 10, 2012 12:14 AM
Default port

Default portDefault portDefault portDefault portDefault portDefault portDefault portDefault port

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 1,433, Visits: 1,597
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.
Post #1232952
Posted Tuesday, January 10, 2012 1:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 5,742, Visits: 3,666
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!!!
Post #1232962
Posted Tuesday, January 10, 2012 1:17 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 652, Visits: 1,894
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.
Post #1232969
Posted Tuesday, January 10, 2012 6:32 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:35 PM
Points: 284, Visits: 829
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
Post #1233132
Posted Tuesday, January 10, 2012 7:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:25 PM
Points: 31,279, Visits: 15,740
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
Post #1233210
Posted Tuesday, January 10, 2012 7:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:25 PM
Points: 31,279, Visits: 15,740
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
Post #1233214
Posted Tuesday, January 10, 2012 7:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 1,754, Visits: 4,966
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.
Post #1233225
Posted Tuesday, January 10, 2012 7:51 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:35 PM
Points: 284, Visits: 829
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
Post #1233238
Posted Tuesday, January 10, 2012 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 5,742, Visits: 3,666
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!!!
Post #1233241
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse