Expand synonym to other entities (database, linked server)
Synonyms provide a great layer of abstraction, allowing us to use friendly and/or local names for verbosely named or remote tables, views, procedures and functions. I have often felt that they are lacking, however, in enabling us to abstract a database or a server.
In one system I have a CRM_test database and a CRM_production database, and it would be nice to be able to change only a single synonym in order to move the main database from test to production. This is much easier than changing 80 synonyms that point to the individual objects inside the CRM database.
Similarly, if I have a linked server in dev and I want to test queries against the production server, I currently have to either search and replace for linked server names in procedure code, or drop and re-create linked servers. The former is tedious, and the latter is not always practical.
Upvotes: 310<=-=Dec 13 2007 9:53AM=-=>
Thanks for your feedback on this. As I replied to another change request from you earlier (on a synonym support for Databases) we are unable to take up this alias/synonym support for databases in SQL Server 2008 due to time/resource constraints. We will definitely investigate this further to see if we can take this up in the next SQL Server release.
The inability to create database synonyms severely restricts the usefulness of synonyms in general. The use of synonyms is substantially complicated when moving from one database to another (e.g., Dev to UAT to Production) because you can’t create a synonym for a database. This would be a very useful feature and would likely increase the use of synonyms in a greater variety of projects.<=-=Nov 18 2008 3:36PM=-=>
I’d like to see database synonyms as well. Server synonym I would think can be accomplished with aliases.<=-=Nov 20 2008 2:07PM=-=>
We have multi server environment with different db names in each server
I can run multi server query but not on different db names.
If i can alias with same db name over all servers then management will be easy
Thanks<=-=Feb 13 2009 6:45AM=-=>
We can alias database servers; we can alias objects within the database. Why can’t we alias the database itself?<=-=Aug 17 2009 5:34AM=-=>
A good workaround for the issue where you need to search and replace linked server names in procedure code, or drop \ recreate linked servers between Prod and Test, is to setup what’s commonly called a ‘SQL Alias’ on the local DB Server, via SQL Server Configuration Manager. This way you have an Alias called something like “CRM” that points to “TestServerName\InstanceName” (or just “TestServerName” if you don’t have Named Instances), and then create the Linked Server pointing to the Alias “CRM” instead of the Real “TestServerName\InstanceName”. The Four-Part Names used in any code now refer to “CRM.CRM.dbo.object” instead of TestServerName.CRM.dbo.object, and can remain. The SQL Alias (which actually is just a local Registry Setting set in a GUI by SQL Config Manager or ODBC) can be changed easily without having to amend your code, and can even be automated using SMO scripting. Also, I think you can set a different name in the underlying Linked Server stored procs that you can’t in the GUI, and so have a common name pointing to different Servers in Prod\Test. Still, agree with you all that having Synonyms for Databases would make all of this this a lot easier.<=-=Oct 21 2009 1:45AM=-=>
would be very useful to have database alias, while switching test/prod enviroments in some scenarios<=-=Jun 22 2010 9:37AM=-=>
give this feature to us, its ungly to string replace source code sql files<=-=Sep 15 2010 6:30PM=-=>
There is a workaround. Don’t modify your source code any longer!<=-=Feb 15 2011 8:50AM=-=>
The synonym workaround becomes very difficult to manage when databases have a large number of objects, or objects are frequently added/removed.<=-=Sep 9 2011 7:49AM=-=>
seriously still not available for Denali?!?!?!?!?!?
Any progress on this? This request has been outstanding for 5 years. This is a desired feature wanted my many individuals. please give this issue some kind of priority.<=-=Jul 2 2012 6:10AM=-=>
Today we use more than 30 000 databases, and a feature like this should be very interesting for us to have flexibility on the developper platform. Vote+1<=-=Dec 7 2012 10:23AM=-=>
Can Micro$oft stop working on all the whizzbang crap that you blow to marketing dept but nobody needs and start giving us something genuine useful. Like this. I mean seriously. Years?! You guys suck big time esp with price rises and all. Seriously tempted to start migrating all our database to PostgreSQL.<=-=Apr 12 2013 8:40AM=-=>
Anytime now… I think people are ready for it. (after 6 years)<=-=Apr 30 2013 5:49PM=-=>
Yes I agree that Synonyms helps us to create at Database level. The work arounds I’m currently working are expensive. Please consider this to add in future version.<=-=Jun 9 2013 8:38AM=-=>
Microsoft, your motto for this site is “Your feedback improving Microsoft products”, but is it really or this is just marketing trick? This would be very useful for many people and you are doing absolutely NOTHING regarding this for years… Why don’t you just shut down this site and leave us in the dark instead of giving us false hope???
Microsoft BI Developer
I would add that this capability exists already in Oracle. Please compete.<=-=Sep 19 2013 6:14PM=-=>
This would defintely be nice. Hardcoded linked servers in stored procs are a nightmare to change when migrating databases between environments.<=-=Jan 10 2014 11:27AM=-=>
A synonym at the database level would definitely be nice.<=-=Feb 18 2014 12:39PM=-=>
Really surprised something this basic, and useful, hasn’t been added 7 years after it was requested.<=-=Feb 21 2014 8:11PM=-=>
The closest workaround to this is going into SQL Server Configuration Manager’s Aliases section to alias a SQL Instance (I mention this for the benefit of those posters who are reluctantly using Hosts files as a workaround).
The closest MS have come to addressing this need was the proposed Connection Director / Connection Plans feature, which was pulled from SQL 2008 R2 http://blogs.msdn.com/b/sqlnativeclient/archive/2009/10/19/sql-server-connection-director-will-not-be-in-the-rtm-versaion-of-the-release.aspx. Apparently this feature would have allowed Connection Strings to live in Active Directory.
Microsoft, as much as I dislike Oracle, you can integrate an Oracle Database network name with AD waaay better than SQL Server does!<=-=Aug 15 2014 3:04AM=-=>
We use Oxyliard system that requires a separate db for each user. Synonyms on server level would help us alot.<=-=Oct 13 2014 2:03PM=-=>
Unless I missed where someone else has asked, it would also be beneficial to allow creating session or variable database synonyms.<=-=Jan 24 2017 12:36PM=-=>
This enhancement request has been open for almost a decade. It is also one of the highest voted. Why can’t we get some love on this Microsoft. Come on. I’ll beg if I have to. Is that what you want?<=-=Aug 2 2017 6:06AM=-=>
Is Microsoft still alive?<=-=Sep 20 2017 8:54AM=-=>
Coming up on the ten year anniversary of this feature request with 484 votes.
My god has it been that long. Windows 7 would be two years ago. Windows XP was by far the most popular operating system, with Windows Vista slowly growing. The financial crisis would still be six months away, Adele wasn’t known to the world, and Juno came out.
Siraj Jamdar commented
In the past, I used SQL Server alias names heavily (using SQL Server Configuration Manager) in order to abstract hard-coded database names in 3-part naming conventions. That was okay, as long as one used native client libraries in client-server mode. It didn't work too well for abstracting database names when using views in one database for cross-database queries.
It would be handy to be be able to define synonyms at the database level to abstract the database name. In hybrid scenarios that would be handy to point to on-premises databases as well as Azure databases, without excessive code changes of legacy applications. For third-party apps, users may not have option to access and change the code at all.