using synonyms and aliases

I'm starting to explore for the first time the use of synonyms and aliases
on SQL Server 2005, to avoid having to hard-code server names in stored
procedures that do cross-server queries and joins. Books Online is rather
terse on the subject.

Anyone know of a good online tutorial or discussion? Basically I want to be
able to define a server name in configuration rather than in code, so that
if we move the remote databases to a different server I can adjust a setting
in a single place rather than recoding the sp's.

Thanks in advance,
Tom Dacon
Dacon Software Consulting

I use synonyms pretty extensively. I don't know of any good tutorial
online, I learned the hard way by rolling up my sleeves and just playing
with them. In a few cases I have 500+ databases that all use functions and
procedures that are centrally located (but the code in each database doesn't
need to know that). This is more about modifying the function code in one
place as opposed to changing the name or location of the target, but both
could be achieved. (I also use it for storing central lookup tables that
need to be referenced by many databases... I would rather have a synonym
that stores a reference, than copy the table to each database, or hard-code
the other database name in all of the stored procedures, etc.) But I don't
think I need to explain all of that to you... I think you understand the
benefits already.

Basically, you can do this:

USE CentralDatabase;
GO
CREATE FUNCTION dbo.PrettyDate
(
@dt SMALLDATETIME
)
RETURNS CHAR(10)
AS
BEGIN
RETURN CONVERT(CHAR(10), @dt, 120);
END
GO

Then in any database you can say:

CREATE SYNONYM dbo.PrettyDate FOR CentralDatabase.dbo.PrettyDate;
GO

Then all of your code can call dbo.PrettyDate() without having to know
anything about CentralDatabase. You can change the name of the function or
put it in another database, and the developers will never know (as long as
you update their synonym(s)).

A couple of caveats:

You cannot ALTER a synonym. You will need to drop and re-create them (they
behave kind of like alias types in this regard).

You cannot use sp_helptext against a synonym to determine its target (you
get Msg 15197, "there is no text"), and sp_help provides nothing useful
(ooh, it's a synonym, really?). Instead you must piece together the
definitions from sys.synonyms.

If you use locking hints against objects (e.g. WITH (NOLOCK)) or set certain
isolation levels, this will break if you try to apply the hints to synonyms
that cross an instance boundary.

If you are using service broker, you need to be very aware of permissions
issues when your activation procedure "jumps" to another database or server
via a synonym. You can go through a mess of server signed certificates, or
do what I did, and just resign to the fact that for service broker you
should have all of your code local to that database. This actually bit me
this morning and I am still investigating the possible solutions (e.g.
granting EXEC to public on the target object). I am not sure when I will
have time to re-visit that one, but just wanted to mention it...







On 9/29/08 12:52 PM, in article OcMOOPlIJHA.1160.DeleteThis@TK2MSFTNGP04.phx.gbl, "Tom
Dacon" wrote:

> I'm starting to explore for the first time the use of synonyms and aliases
> on SQL Server 2005, to avoid having to hard-code server names in stored
> procedures that do cross-server queries and joins. Books Online is rather
> terse on the subject.
>
> Anyone know of a good online tutorial or discussion? Basically I want to be
> able to define a server name in configuration rather than in code, so that
> if we move the remote databases to a different server I can adjust a setting
> in a single place rather than recoding the sp's.
>
> Thanks in advance,
> Tom Dacon
> Dacon Software Consulting
>
>