Expand synonym to other entities (database, linked server) - by AaronBertrand

Status : 

 


381
3
Sign in
to vote
ID 311079 Comments
Status Active Workarounds
Type Suggestion Repros 9
Opened 11/18/2007 5:26:59 PM
Duplicates 288421 Access Restriction Public

Description

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.
Sign in to post a comment.
Posted by TMX12 on 10/13/2014 at 2:03 PM
Unless I missed where someone else has asked, it would also be beneficial to allow creating session or variable database synonyms.
Posted by Dimon KOH on 8/15/2014 at 3:04 AM
We use Oxyliard system that requires a separate db for each user. Synonyms on server level would help us alot.
Posted by alphatross on 2/21/2014 at 8:11 PM
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!
Posted by Alex_Barnes on 2/18/2014 at 12:39 PM
Really surprised something this basic, and useful, hasn't been added 7 years after it was requested.
Posted by richard75013 on 1/10/2014 at 11:27 AM
A synonym at the database level would definitely be nice.
Posted by RobNicholson, MCSM on 9/19/2013 at 6:14 PM
This would defintely be nice. Hardcoded linked servers in stored procs are a nightmare to change when migrating databases between environments.
Posted by Tom Loomis on 6/23/2013 at 9:26 AM
I would add that this capability exists already in Oracle. Please compete.
Posted by John SY on 6/9/2013 at 8:38 AM
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???

Miljan Radovic,
Microsoft BI Developer
Posted by Mubeen1 on 4/30/2013 at 5:49 PM
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.
Posted by Do3 on 4/12/2013 at 8:40 AM
Anytime now... I think people are ready for it. (after 6 years)
Posted by Andrew Ooi on 12/7/2012 at 10:23 AM
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.
Posted by SROBLIN on 7/2/2012 at 6:10 AM
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
Posted by BDouble on 5/4/2012 at 7:02 AM
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.
Posted by Choco_Smith on 9/9/2011 at 7:49 AM
seriously still not available for Denali?!?!?!?!?!?
http://msdn.microsoft.com/en-us/library/ms187552(v=sql.110).aspx

Posted by Victor M Alcazar on 2/15/2011 at 8:50 AM
The synonym workaround becomes very difficult to manage when databases have a large number of objects, or objects are frequently added/removed.
Posted by ErikEckhardt on 9/15/2010 at 6:30 PM
There is a workaround. Don't modify your source code any longer!
Posted by fotis12 on 6/22/2010 at 9:37 AM
give this feature to us, its ungly to string replace source code sql files
Posted by river0 on 10/21/2009 at 1:45 AM
would be very useful to have database alias, while switching test/prod enviroments in some scenarios
Posted by alphatross on 8/17/2009 at 5:34 AM
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.
Posted by Abercrombie07 on 2/13/2009 at 6:45 AM
We can alias database servers; we can alias objects within the database. Why can't we alias the database itself?
Posted by Jagblue on 11/20/2008 at 2:07 PM
Yes

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
Posted by www.21concepts.com on 11/18/2008 at 3:36 PM
I'd like to see database synonyms as well. Server synonym I would think can be accomplished with aliases.
Posted by Mike C_1 on 6/6/2008 at 5:43 PM
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.
Posted by Microsoft on 12/13/2007 at 9:53 AM
Hi aaron,
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.

thanks
Srini Acharya