Home Dashboard Directory Help
Search

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


Status: 

Active


364
3
Sign in
to vote
Type: Suggestion
ID: 311079
Opened: 11/18/2007 5:26:59 PM
Access Restriction: Public
Duplicates: 288421
4
Workaround(s)
view

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.
Details
Sign in to post a comment.
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 knyazs 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
Sign in to post a workaround.
Posted by a hennings on 4/8/2013 at 10:11 AM
I had scripts that needed to be run-able on the server or remotely through a linked database. When I found that aliasing wouldn't allow me to alias the linked database, I worked around it by adding the server to my windows hosts file. This is not safe, not permanent, and not recommended. But it worked for me and could work for you.
Posted by TrungGap on 5/4/2012 at 8:16 PM
I agree with Erik,

Beside looped back linked server is not officially supported, and always have transaction issue.

I feel that the looped back linked server is broken, since our code works fine on sql 2000. Now, we have to rewrite it to make it work in our dev environment.
Posted by BDouble on 5/4/2012 at 7:05 AM
The workaround listed below is great if you have a small simplistic data model, but if you have a large scale data model with hundreds or thousands of tables / objects, it is a really poor solution.

The true solution would be for Mircosoft to put some attention to this matter. They have now gone through 2 major DB releases (2008, Denali) without addressing this feature.

The lack of this feature is pushing me away from SQL Server to another solution.
Posted by ErikEckhardt on 9/15/2010 at 6:29 PM
While you can't create a SYNONYM of another database or a linked server, you CAN create SYNONYMs with 4-part names. This has the drawback that in comparions to aliased databases, the SYNONYM list could get very long, but it does allow switching databases to your heart's content.

CREATE SYNONYM dbo.User FOR LinkedServer.TestDB.dbo.User -- works fine