August 2, 2019 at 12:00 am
Comments posted to this topic are about the item Are Synonyms Useful?
August 2, 2019 at 1:48 am
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...
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
Change is inevitable... Change for the better is not.
August 2, 2019 at 7:01 am
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.
August 2, 2019 at 9:29 am
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!)
August 2, 2019 at 2:14 pm
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
Change is inevitable... Change for the better is not.
August 2, 2019 at 4:21 pm
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
August 2, 2019 at 7:01 pm
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.
August 2, 2019 at 8:25 pm
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.
August 5, 2019 at 1:39 pm
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.
August 5, 2019 at 1:40 pm
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
August 5, 2019 at 1:58 pm
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
Change is inevitable... Change for the better is not.
August 5, 2019 at 2:00 pm
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
Change is inevitable... Change for the better is not.
August 5, 2019 at 2:13 pm
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.
August 5, 2019 at 2:19 pm
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
Change is inevitable... Change for the better is not.
August 5, 2019 at 2:34 pm
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 23 total)
You must be logged in to reply to this topic. Login to reply