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...
- 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.
- 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.
- 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.
- 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.
- 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. 😀
is pronounced "ree-bar
" and is a "Modenism
" for R
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."
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)