December 30, 2008 at 11:58 am
i'm sure there's an answer to this, but i can't find it...
why are synonyms defined per database and not per server? in my situation, i have 3 databases on one server that have queries that access a single database on a remote server. so for example, each database would have a query that looks like this:
select * from RemoteServer.Database.dbo.SomeTable
i can define a synonym in each of the 3 databases so that each query now looks like this:
select * from MyRemoteTable
and it works fine. each database's query is able to grab the data. but now i've got 3 synonyms that i'd need to drop/create when the server name changes (which is MUCH better than updating x queries on y databases). so what i've done is created a new database that only contains ALL the synonyms i'd use on that server, added users to it, marked it as read-only, then updated some of my queries (as a test) to that db's synonyms. this means i have only one place to drop/create my changed synonyms when the time comes. so now that query (in all 3 databases) would look like:
select * from SynonymDB.dbo.MyRemoteTable
and it works fine. no problems that i can see.
my question:
is there a way to define server-level synonyms so i don't have to do what i've done, and if not, can anyone see any problems with what i've done?
December 30, 2008 at 12:03 pm
Try using Views for this instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 12:06 pm
Hmmm, well I may have misunderstood what you are trying to do. still trying to figure it out...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 12:08 pm
i'm trying to get away from using server names in multiple queries. if i used a view, i'd still have to explicitly name the server, so when we changed servers, i'd have to update ALL the views. i want to update as little as possible.
if i misunderstood what you're saying, please clarify...
i'm aware that i could just rename the servers and that would solve all my problems, but in my organization, things aren't that easy... i've been given new server names, so i have to plan accordingly.
December 30, 2008 at 12:10 pm
OK, how about using the Aliases in SQL Server Configuration Manager, under "SQL Native Client Configuration"? I am not sure if this works or not, but it's worth a try.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 12:36 pm
i like... will check it out to see if it works.
thanks!
December 30, 2008 at 1:18 pm
ok, so i LOVE the alias thing... it definitely works, and it's definitely usable. and you know there's a "but" coming...
the thing is, i've still got multiple procs, views, queries, etc that would all reference the alias that's "outside" the server. i can pick alias names that will likely not ever change, but i can't say they won't (i know what you're thinking, and i'm with you, but you know how it is... the higher ups implement rules/standards/requirements [sox, pci, etc], sometimes without much consideration of the impact on the rest of us). so if i had to change the alias names, as remote as that possibility may be, i'd be back to updating all references.
but since synonyms are "inside" the server, any management requirements that live outside won't (shouldn't) affect them. the outside names can change as much as they want and all i'd have to do is update the synonyms (when scripted, it's a 5 minute max job).
so while i really like the alias thing, i have to be thorough before implementing one or the other... so i'm back to my original question...
fwiw: i'm advising the higher ups as i send this of my intention to use aliases to see if it's a viable option...
December 30, 2008 at 1:25 pm
forgot to include that with aliases, if we add servers and split databases between old and new, i'd still have to update all referencing objects. with synonyms, i'd just drop/create the necessary synonyms.
December 30, 2008 at 1:29 pm
Well, I am not sure what else you can do. I do not think that EndPoints can be used for this, but I am not sure.
You may be able to manipulate the Names of Linked Servers for this purpose. When you create them through SSMS, it forces the "Linked Server Name" to be the same as the Remote Server's name, but I believe that if you create it your self from T-SQL, you can set the Linked Server Name to be whatever you want. What I am not sure of though is if you can have multiple Linked Servers to the same Remote Server name, so that might be a limitation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 1:33 pm
The other thing that I will mention, is how I do it: Views.
Yeah, I know we already went over that, but I do it a little bit differently. I have a sProc that takes a Server.Database name and then generates all of the Views.
So if the target server name changes, I just re-run this to drop and recreate all of the Views. Takes about 5 seconds. Of course you do need a list of the target table names somewhere (i use a local table for this), but other than that, it's pretty simple.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 1:59 pm
i think linked servers are the answer... i was able to create a linked server to a SQL box and give it my own name. this solves ALL of my issues (i think)...
1. it's "inside" sql, so any outside rules won't matter
2. i can drop/recreate the linked server so that it points to any new servers
3. i can access all the objects i need... tables, views, stored procs, etc
4. they're "server level", meaning i can access them from any database
but probably the best things about them:
1. i can create them to be user/db specific. that is, one linked server points to db1 on server1 AS user1, while another linked server points to db1 on server 1 as user2, while another points to db2 on server1 as user3, and so on
2. i can create them using the sql native client, which (in theory, anyway) means that db mirroring will STILL be an option for me
so i think i'm going this route... thanks!
December 30, 2008 at 2:05 pm
Cool. Glad I could help. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply