Are Synonyms Useful?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 718065

    Comments posted to this topic are about the item Are Synonyms Useful?

  • Jeff Moden

    SSC Guru

    Points: 996088

    As many folks do, we have many databases associated with one "system".  We have many uses for synonyms.

    It's important to also know that database naming can change and tables can even change systems.  Neither of those events happen frequently but, if you've made the mistake of using 3 or 4 part naming conventions in your code (and we have a shedload of code), it only has to happen once to realize the serious mistake you've made whether the code is front end code or stored procedures because you have to go find it all, make the change, and retest it all to make sure you didn't phat phinger or break anything.

    With that understanding, we strongly enforce the 2 part naming convention and use synonyms as the bridges between databases and over linked servers.  As a result, we only need to change a very small handful of synonyms (and it's just not difficult to write a proc to find and fix them all) and, maybe, repoint or add a linked server if the database moved to another server or the server was named, which is also trivial compared to finding and fixing all possibilities in code.

    Some of the reasons we have different databases for the same "system" are...

    1.  It allows us to make ETL and import databases using the SIMPLE recovery model for performance reasons.  We also don't have to backup those databases because all of the data is temporary in nature.  All of the stored procedures, views, functions, and even the table structures are all available in SourceControl if the worst ever happens.  It also means that all that temporary data isn't beating the hell out of the log files on the production database.
    2. Audit and tables like invoice details, etc, are usually the largest tables in any database.  It's just stupid to backup a half Terabyte of data that will never change ever again on a nightly basis.  Total waste of time and backup disk space.  We partition the tables that we move to such databases and set their older file groups to Read Only.  In the event of a disaster recovery, we don't need the huge audit tables right away and so we also have an empty table in the "main" database that normally just sits there.  The large audit tables would really slow down a "get back in business" restore of the main database(s) and the applications would fail if they didn't actually have an audit table to point to.  That's the purpose of the totally empty table.  Normally, our synonym would be pointing to the big audit table in the other database.   If we need to do a DR restore on a different box, we just restore the main database(s) and repoint the synonyms to the currently empty tables in the main database and it's business as usual until we can restore the large audit database.
    3. Item #2 also helps minimize the size of the main database to make it a whole lot easier to copy the main database to smaller dev and staging systems.
    4. We also use synonyms for those table that we need to load but keep online the whole time (whatever the reason is).  So, we create two tables and two synonyms.  We can keep table A online while loading table B and, once complete, just repoint the synonyms.  Next time around, we just do the reverse.  An awesome side benefit is that if the load to one table or the other fails or we discover that there was something wrong with the data after we did the synonym repointing, we just need to repoint the synonyms again and we're almost instantly back to using the "last known good data".  This is especially helpful for DW and reporting systems.
    5. It's also a pretty cool index maintenance trick to prevent the need for a lot of freespace required for clustered index rebuilds on large tables except when you're done with the rebuild, you just drop the source table.  Since I use 1 filegroup/file for each of the A/B tables, I can just drop the now empty file group and rebuild it the next time I need to use it.  Of course, the synonyms are repointed once the rebuild is complete.  And, yeah, the "WITH DROP EXISTING" option of CREATE INDEX is what I used to effectively do a rebuild while moving the data and it's NASTY FAST.  Since we use SAN Replication instead of replication in SQL Server, I can also get away with using the BULK_LOGGED Recovery Model during such rebuilds to take advantage of "Minimal Logging".  If I ever need to shrink the PRIMARY file group, I don't have to deal with all that bigstuff.

    Prior to using synonyms, I used to use "Pass through views" to do the same things.

    With only extremely rare exceptions (so rare, I can't remember what they are but I remember having to use the exception more than a decade ago), 2 part naming is the way to go and synonyms make that a whole lot easier to tolerate

    And a good cinnamon makes oatmeal taste better, too.  😀

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Alex Gay

    SSCrazy

    Points: 2195

    I think that Jeff has covered most of the salient points involved with using synonyms.  I have used them for everything allowable, including stored procedures.  We had a routine where we had to run an SP on another database, and then extract the report table created (we didn't control the code in the DB) and this worked well, it may even still be in use.

    Do plan how you will use synonyms.  Like Cursors (sorry Jeff) there are times where they are very useful, but it probably isn't now.  Try to come up with an easier, more maintainable solution first, if you can't come up with one then synonyms might be the answer.  Like triggers synonyms are invisible objects that do work in the background, so document your code to indicate where an object is a synonym (or a trigger will be called) it may seem like overkill now, but future you will be grateful that you made the effort.  We have all seen pictures of beautifully cabled computer rooms and horrendous rats nests of server cables, same tools, different approach.  Synonyms are the same.

  • richardmgreen1

    SSCrazy Eights

    Points: 9856

    We also use synonyms as we have test and live systems (the database names are different).

     

    We just point the synonyms to the right database (depending on the system) and off we go.

    We also use them for various functions that are kep in our "Utilities" database.

    In that way, we only have to alter one copy of the functions and it will propogate automatically.

     

    We also have a stored procedure that drops and recreates all the synonyms every day (the vendor has a nasty habit of just adding new tables on what appears to be a whim!)

  • Jeff Moden

    SSC Guru

    Points: 996088

    Alex Gay wrote:

    I think that Jeff has covered most of the salient points involved with using synonyms.  I have used them for everything allowable, including stored procedures.  We had a routine where we had to run an SP on another database, and then extract the report table created (we didn't control the code in the DB) and this worked well, it may even still be in use.

    Do plan how you will use synonyms.  Like Cursors (sorry Jeff) there are times where they are very useful, but it probably isn't now.  Try to come up with an easier, more maintainable solution first, if you can't come up with one then synonyms might be the answer.  Like triggers synonyms are invisible objects that do work in the background, so document your code to indicate where an object is a synonym (or a trigger will be called) it may seem like overkill now, but future you will be grateful that you made the effort.  We have all seen pictures of beautifully cabled computer rooms and horrendous rats nests of server cables, same tools, different approach.  Synonyms are the same.

    No apologies necessary.  As with everything else in SQL Server, things like Cursors, While loops, rCTEs, and SQLCLR DO have a place.   Heh... as you know, though, that place isn't "everywhere".  Almost nothing is a panacea when it comes to SQL Server.

    I've earned the reputation of being a "Cursor" hater and I'm not really.  I just hate to see them when people use them when there's a much better way to be had for a given task... and that happens a lot.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jtallent-840140

    SSC Rookie

    Points: 37

    Steve,

    In a past life I first became familiar with synonyms in Oracle 7.3. Once SQL Server introduced them, I saw similar benefits to applying them. As with anything there will be pros and cons, but for me, as a software developer and database architect, synonyms give me two main compelling benefits: A) they are a first-class DB object which gives me another layer of abstraction and the ability to grant/revoke permissions to them; B) they allow me to more easily switch out the target to another DB during development or testing through a simple ALTER SYNONYM statement. I mainly design data-access through the use of stored procedures, so another benefit it is an organizational element.

    I also make heavy use of schemas in database designs, typically avoiding creating objects owned by dbo. Synonyms will typically be placed in their own schema as it relates to their target. Could I achieve the same thing using three-part or even four-part C14N? Sure. But if synonyms make practical, logical sense, i.e. they solve a particular problem without introducing new ones, I take full advantage of them. Testing results will usually be the final arbiter in the end, but I've not had any negative experience with them thus far.

    Just my two-cents. 🙂

    JT

  • below86

    SSChampion

    Points: 11331

    We (I) use synonyms a lot.  I tend to like to write SQL to do the data manipulations or whatever the task is.  Some of my co workers tend to use SSIS to do all of that for them, so many of them don't use the synonyms we have in place.  At my prior job we used the 3 part naming convention, we were a small shop and no real threat of a database being moved to a different server.  I strictly stick to the 2 part naming convention now.

    As I said using synonyms allows me to write SQL to get the task done.  Without the use of synonyms I would be stuck trying to get SSIS to do it.  IMHO, it is a lot harder to debug an issue within a huge data flow task in SSIS than it is to debug the SQL code.

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 718065

    Nice set of uses and thanks for responding. I was hoping that more people would comment, but I suspect many people don't use them or know about them. Likely an educational opportunity here.

     

  • tsalha

    SSC Rookie

    Points: 42

    We once used to utilizing them, but with the advent of database projects in VS, we abandoned them again from our development. This due to the reason, that VS itself is capable of keeping track of the foreign database names during deployment. Just include the other DB as resource in the project and off you go. No need to maintain the synonyms anymore. Of course, in case of a renaming of a db there is a complete redeployment needed of all code containing the db names, but on the other hand, you get much better code validation as the foreign db needs to be checked, too.

  • Eric M Russell

    SSC Guru

    Points: 125074

    Synonyms can be useful for whomever created them, but confusing for others who stumble across them by accident. It depends on the purpose, but I prefer views instead.

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

  • Jeff Moden

    SSC Guru

    Points: 996088

    Eric M Russell wrote:

    Synonyms can be useful for whomever created them, but confusing for others who stumble across them by accident. It depends on the purpose, but I prefer views instead.

    Interesting.  I've steered away from pass-through views because they generally need to be rebuilt when someone modifies the underlying table.  Not so with synonyms or at least no issues there that I'm aware of.  I've also found that many people are as confused by views as they are synonyms.  Last but not least, synonyms only contain easy to locate and programmatically read and change.  With views, you have to read the definition, isolate the FROM clause and replace that and then regenerate the view, both of which mean you need to read the definition of the view rather than just the metadata contained in a synonym.

    It's import to use because we copy databases a lot a each environment has naming specific to the environment to help keep people from accidentally deploying to the wrong environment that they just happen to have open in SSMS or some other tool.  It's a bit of a PITA to have such naming but there are sub-environments in the Dev and Staging boxes to support multiple projects on the "same" databases, each requiring its own set of databases.  Your situation may be quite a bit different.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996088

    tsalha wrote:

    We once used to utilizing them, but with the advent of database projects in VS, we abandoned them again from our development. This due to the reason, that VS itself is capable of keeping track of the foreign database names during deployment. Just include the other DB as resource in the project and off you go. No need to maintain the synonyms anymore. Of course, in case of a renaming of a db there is a complete redeployment needed of all code containing the db names, but on the other hand, you get much better code validation as the foreign db needs to be checked, too.

    Are you saying that the names of the databases appear as 3 or 4 part names in the code?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • tsalha

    SSC Rookie

    Points: 42

    Hi Jeff,

    in our case as an automatically generated 3-part name (I guess even no linked server is supported with this model, but not sure). It is important to know, that in the CODE (inside VS) you find this variable [$(ForeignDatabaseName)].[SchemaName].[TableName]. At Deployment time inside the DB it will take the form of the real DB Name.

    I think this is really useful if e.g. you have a staging and archive database and a data warehouse… thus, systems, that you maintain by yourself. Other real external systems we typically do always query via middleware code or ssis.

     

  • Jeff Moden

    SSC Guru

    Points: 996088

    @tsalha,

    Are we talking about front end code or stored procedures here?  And, please pardon my ignorance of VS.  I've never actually used it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • tsalha

    SSC Rookie

    Points: 42

    I use the aforementioned 3-part schema if I query a foreign table from a stored procedure. As said, staging and DWH is a good example for this scenario. Here I control both databases.

    If I happen to query data from a DB I do not control by myself, I typically use ssis or c# code instead to transfer data from a to b.

    It is possibly to use the dacpac of a foreign database as VS ref, but I do not like this method, because often you will not get one, especially if it sits on a remote server you do not have sufficient rights on.

Viewing 15 posts - 1 through 15 (of 24 total)

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