Brandie Tarvin wrote:
RE: Synonyms and 3 / 4 part naming conventions, is this just a preference or are you noticing performance issues? And in the end, why does it matter? If the linked database is on another instance / server, even the synonym is going to be using (I assume) a linked server to get to the data. Unless there's a way to get around that limitation that I'm unaware of. If so, please tell me. I just find it hard to justify the work of building and maintaining synonyms (to my already large list of things to do) to reference things that can be referenced through a linked server and won't generally break if a column is added / dropped / updated.
Just to revisit this and to provide reasons with why I concur with Sean on the subject...
To me, the use of synonyms is a bit like getting a tetanus shot and then then maintaining the immunity with the occasional booster shot. The original shot is a bit of a pain and so is the occasional booster shot. It also takes a bit of time to setup the appointment to get one and then to go to the doctor and actually get the shot. But... it's worth it.
I've been very fortunate (thanks to the shots) to have never gotten tetanus but I have seen people that have and I have to tell you, it's not fun for them. They do call it "lock jaw" for a reason.
I've not been so lucky in the past (almost 20 years ago... lesson learned well) with database object naming. In the early days, people thought it really cool to include DBName..ObjectName and LinkedServerName.DBName..ObjectName in their code virtually without exception. As Mom would frequently say about BB-Gun fights (which I NEVER got involved in), "It's all fun and games until someone loses an eye".
Things happen for unknown reasons. Sometimes they're stupid reasons and sometimes they're valid reasons but, either way, you have to deal with it. The company that I worked for that was using 3 and 4 part naming (including the mistake of not including a valid schema name... that's another but similar story) decided that there would be an official naming convention for linked servers and databases in different environments. We had no choice and had to change them. You don't have to guess what happened there. Every piece of code we had needed to be changed.
We decide to get the proverbial tetanus shot while we were at it. We created the necessary synonyms and change all of the code to exclusively use only the 2 part naming convention whether it was for cross database or cross server using linked servers. In the months that followed, we found it to be a bit of a minor pain to always use synonyms for such things but, just like getting a tetanus booster, it just wasn't that difficult and soon became a matter of rote that we did without even thinking about it.
About a year later, a lot of the technical leadership had changed and you can probably guess what happened. A new corporate mandate came down with a new naming convention. While other parts of the company were scrambling to comply and had lots of downtime, it took us about 15 minutes to setup newly name linked servers (old ones were dropped when everything was completed). After that, things really got easy. We built a simple system stored procedure that ripped though all of the databases that renamed all of the databases and rebuilt all of the synonyms (using an old name vs new name and type list in the proc) in about a minute, which was also our total down time.
Yeah... that's an extreme example that most people will never have to suffer once never mind twice.
We also have Dev and Staging environments that require copies of prod. Yeah... the databases all have suffixes on them for whatever environment you're in. Sounds really painful but, because of synonyms, it's not. And having differently named suffixes on each database have actually provided us with 2 massive benefits. First, for those folks that have multiple connections to multiple systems open in SSMS, it has, indeed, greatly reduced the mistake of making unintended changes because people thought they were connected to the right server. The other huge benefit is that, especially for the Dev environment, it allows us to have multiple copies of the same SETS of databases for different purposes and allows us to do a database refresh from prod (using a restore in most cases) without a whole lot of fuss. All we have to do is change the synonyms and, of course, there's a script for that.
It also makes us nearly immune if someone gets a different religion about naming conventions.