January 6, 2019 at 9:47 pm
How many times does the view for a table:
select * from table_name
get improved by using the synonyms?
Is this standards SQL?
412-977-3526 call/text
January 7, 2019 at 1:24 am
In a previous role, I used synonyms to allow us to keep a fraud detection application running 24x7 with regular updates of 3rd party supplied data (known dodgy IP addresses, etc). There were two identical sets of tables, call them setA and setB with each table being referenced by a synonym. The application used the synonym at all times. When it was time to load the data, we cleared down the tables not currently in use, populated them with the latest supplied data, then redefined the synonyms to point to the newly-loaded set of tables. A metadata table kept track of which version of each table was current at any time and supported a completely automated refresh and switch process.
The main issue I've seen with synonyms is DBA suspicion; those who think synonyms are an unnecessary complication are not motivated to support a database which uses them, and they are apt to blame the synonyms as soon as anything goes wrong.
January 7, 2019 at 3:10 am
We use synonyms to point between our test and production 3-rd party databases.
It helps when we deploy code between our test and production SQL boxes.
We've found it also saves typing out the full 4-part table name (we use linked servers to access the base data).
When we get our databases sorted out properly, we can just repoint our synonyms and not have to change anything else.
January 7, 2019 at 4:12 am
richardmgreen1 - Monday, January 7, 2019 3:10 AMWe use synonyms to point between our test and production 3-rd party databases.
It helps when we deploy code between our test and production SQL boxes.We've found it also saves typing out the full 4-part table name (we use linked servers to access the base data).
When we get our databases sorted out properly, we can just repoint our synonyms and not have to change anything else.
We did about the same thing, but it does cause confusion when you look at code and do not realize that the table is actually on another server.
5ilverFox
Consulting DBA / Developer
South Africa
January 7, 2019 at 5:51 am
robert.sterbal 56890 - Sunday, January 6, 2019 9:47 PMHow many times does the view for a table:select * from table_name
get improved by using the synonyms?
Is this standards SQL?
There's no performance difference between a Synonym and a Pass-Through-View (PTView for short).
The reason why I prefer Synonyms for inter-database code is because it keeps things more separate when it comes to views. I also don't have to update any views if someone makes the mistake of using SELECT * for the PTView.
As for the reason why I use Synonyms for such things instead of using 3 or 4 part naming is because there is no guarantee that database names and which server a database will be moved to will be the same. I also have multiple copies of certain databases in Dev and Staging and that means the database MUST have different names. When the code moves from one environment to another, I don't need to do a thing because the 2 part naming convention in the code recognizes the common named Synonyms in the database where the code is deployed to. There is no need to find the database name in any of the code and change it. The Synonyms take care of all of that.
The same goes for a restore from, say, Production to a lesser environment. Instead of having to change any code for database name changes due to being in a different environment, I only need to generate the code for the Synonyms, do a couple of Search'n'Replaces, drop all the Synonyms, and use the generated script to rebuild them to point them to the correct databases.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2019 at 5:57 am
Ivanova - Monday, January 7, 2019 1:24 AMIn a previous role, I used synonyms to allow us to keep a fraud detection application running 24x7 with regular updates of 3rd party supplied data (known dodgy IP addresses, etc). There were two identical sets of tables, call them setA and setB with each table being referenced by a synonym. The application used the synonym at all times. When it was time to load the data, we cleared down the tables not currently in use, populated them with the latest supplied data, then redefined the synonyms to point to the newly-loaded set of tables. A metadata table kept track of which version of each table was current at any time and supported a completely automated refresh and switch process.The main issue I've seen with synonyms is DBA suspicion; those who think synonyms are an unnecessary complication are not motivated to support a database which uses them, and they are apt to blame the synonyms as soon as anything goes wrong.
Heh... no suspicion on the part of this DBA. In fact, I won't allow for anything more than 2 part naming in any of the databases on boxes that I'm responsible for.
And the use of "table flopping" as you've described is something I do all the time, as well, to keep tables online while a sister table is being loaded as a replacement. In fact, I'm the one that finally got people to start doing that when large sets of tables needed to be copied from external sources (IBM Power Systems, in this case). It's extremely effective and, if there's a failure during the load, you're still "in business" with the old data.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2019 at 6:45 am
I have used them for the same reason as Jeff. I actually found the developers were the ones who didn't much like them strangely, couldn't work out why and didn't get much help from them on why either, still made them use them. 🙂
January 7, 2019 at 7:41 am
At my prior job we used 3 part naming, our database names were the same on DEv, TEST, QA, PROD. My current job the database names start with the prefix of the environment, PROD_, TEST_, DEV_...
We uses synonyms a lot here, as a developer it doesn't bother me. The main issue is that all synonyms aren't named similar, some are just the table name, others have the suffix of '_syn'.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 7, 2019 at 8:36 am
Yes, we have used them before to make the transition from a development server to a production server easier.
In our case, some of the synonyms were referencing databases/tables via a Linked Server.
If I can remember correctly, is was not possible to run these CREATE scripts unless the Linked Server and the remote databases/tables where physically available? (i.e. some sort of pre-check).
January 7, 2019 at 11:00 am
I've known of synonyms in SQL Server for years, but haven't created them. Definitely can see the usefulness of them, especially when some tables' named have been appended with things like "_dev" or "_test", which I've seen. I can see the usefulness of synonyms, but doubt that I'd use them much. I've just poked around at some of the databases I've got access to, looking for synonyms. I see that synonyms are used, but not heavily.
Kindest Regards, Rod Connect with me on LinkedIn.
January 7, 2019 at 7:45 pm
Synonyms are important for dependency management. As Jeff Moden alluded to, sooner or later your friendly infrastructure maven is going to come a-knocking and say "Hey, good news guys, we're decommissioning server X and from 1 March all the databases that were there, are going to be moved to server Y." Over the years your team has written dozens or even hundreds of dependencies that pull data via linked servers. Depending on how lax your DBAs have been over the years there could be any number of things you don't know a lot about, engaging in a bit of consensual cross server querying.
If you've been diligent, you have not hard-coded the server names into your SQL. Instead you've created a synonym and put the four-part name in that. So when the time comes to mover over to your shiny new server Y, you simply script out all your synonyms, change them to point to the new server, and run. Simples.
If you haven't been diligent, help desk is going to field days of calls for broken views, stored procedures, reports etc etc etc.
The one gripe I have against synonyms, is that in all versions of SSMS (that I'm aware of) intellisense sees a synonym as a syntax error. It's an annoyance rather than a deal breaker, but I have often wondered whether the way SSMS reads the DBs' metadata could be improved to map the synonym through to the source.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
January 10, 2019 at 12:55 am
I think a problem with synonymes (as with several other "solutions") is SourceControl. When I use them to refer to database db_prod or db_test (depending on my environement) it will always be a change and potential conflict in SourceControl except I exclude the synonmys from it, what would lead to other problems (because views / procedures break without the synonym)
God is real, unless declared integer.
January 10, 2019 at 9:02 am
t.franz - Thursday, January 10, 2019 12:55 AMI think a problem with synonymes (as with several other "solutions") is SourceControl. When I use them to refer to database db_prod or db_test (depending on my environement) it will always be a change and potential conflict in SourceControl except I exclude the synonmys from it, what would lead to other problems (because views / procedures break without the synonym)
So what's the difference between synonyms and views that use more than 2 part naming? That's not a question on my part. It's meant to be thought provoking. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply