Referencing Remote Data

  • Comments posted to this topic are about the item Referencing Remote Data

  • 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.

  • 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!!!

  • 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.

  • 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

  • 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.

  • 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?

  • 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.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • 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

  • 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!!!

  • We have a reporting system that contains monthly snapshots of our core system database. Each snapshot is a database with a naming convention like Snapshot20111231. We frequently have SSRS reports that compare this month vs last month. I use synonyms in the main report query in order to avoid the messiness of constructing a dynamic sql statement. The reports are based on sprocs where the first thing that happens is redefining the synonyms to point to the month end snapshots the user requests via report parameters. It works well. Only drawback I see is the elevated permissions required by the reporting login to drop and recreate the synonyms.

  • We frequently have SSRS reports that compare this month vs last month. I use synonyms in the main report query in order to avoid the messiness of constructing a dynamic sql statement. The reports are based on sprocs where the first thing that happens is redefining the synonyms to point to the month end snapshots the user requests via report parameters.

    That's a pretty creative solution to managing repetitive files where the date is part of the names. I have a couple of processes to implement this way. Thanks for posting it.

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • I like synonyms over views. They can be fully automated for remote servers during a restore process. Since they have a standardized form and their data is in sys.synonyms, they are especially easy to change over. Views have to be scripted to properly change the objects and that's a pain.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply