Use Synonyms to Abstract the Location of SQL Server Database Objects

By:   |   Comments   |   Related: > Synonyms


Problem

I have an ETL process where the source and staging databases are on the same SQL Server instance.  Over time many stored procedures have been deployed to the staging database and these stored procedures access tables in the source database by using the three part name; i.e. [databasename].[schema].[tablename].  We are ready to move the staging database to a new server.  We want to update the stored procedures in staging so that they can access the tables in the source database whether the source database is on the same or a different SQL Server instance.  In other words we want to make a single change to the stored procedures that will allow them to work regardless of where the source database is deployed.   Do you have any ideas on how to do this? 

Solution

SQL Server 2005 introduced a new feature called synonyms.  Essentially a synonym allows you to specify an alias for a database object.  The short answer to your question is that you can create a synonym in the staging database for each table that you use in the source database, then change all of your stored procedures in staging to use the synonym instead of the three part name.  When your stored procedures use the synonym to reference the source database tables, you can then move the source database to a different server and all you have to do is change the definition of the synonym; you do not have to change the stored procedures. 

Example

Let's restate the problem:

  • We have stored procedures in the staging database that access tables in the source database using three part names

  • We want to change the stored procedures to access the source tables via synonyms so that we don't have to change the stored procedures if we move the source database to another server

In the original problem statement, you noted that the stored procedures in staging are using three part names to access the tables in the source database.  If we move the source database to a different server, we can setup a linked server and use four part names to access the tables in the source database.  So what we want is to create a synonym that will abstract the use of the three or four part names.  We can create a T-SQL script that creates a synonym for a list of tables, using the 4 part name if a particular linked server exists, and using the three part name if we are not using a linked server. 

The T-SQL script is as follows: 

use mssqltips_staging
go
set nocount on;
declare @schema_name sysname
declare @cmd nvarchar(256)
-- STEP 1: create a schema for the synonyms
set @schema_name = N'oltp'
if not exists (
 select * from sys.schemas
 where name = @schema_name
)
begin
 set @cmd = 
   N'create schema ' + 
   @schema_name + 
   N' authorization dbo'
  exec sp_executesql @stmt = @cmd 
end
-- STEP 2: get the schema id
declare @schema_id int
select @schema_id = schema_id 
from sys.schemas
where name = @schema_name;
-- STEP 3: create synonyms for list of tables
declare @table_list table (
 table_name sysname
)
declare @table_name sysname
declare @prefix nvarchar(50)
declare @synonym sysname
declare @linked_server sysname
set @linked_server = N'[L00458\sql2008]'
-- create a synonym for these tables (insert each table)
insert into @table_list (table_name) values ('customer')
insert into @table_list (table_name) values ('product')
if exists (
 select * from sys.servers
 where name = @linked_server
)
 -- 4 part name
 set @prefix = @linked_server + N'.' +  
               @schema_name + N'.dbo.'
else
 -- 3 part name
 set @prefix = @schema_name + N'.dbo.'
select top 1 @table_name = table_name 
from @table_list
while @table_name IS NOT NULL
begin
 -- set the schema and name for the synonym
 set @synonym = @schema_name + N'.' + @table_name
 -- delete the synonym if it exists
 if exists (
  select * from sys.synonyms
  where name = @table_name
  and schema_id = @schema_id
 )
 begin
  set @cmd = N'drop synonym ' + @synonym 
  exec sp_executesql @cmd
 end
 -- create the synonym
 set @cmd = N'create synonym ' + 
            @synonym + 
            N' for ' + 
            @prefix + 
            @table_name
 print @cmd
 exec sp_executesql @cmd
 delete top (1) from @table_list
 set @table_name = null
 select top 1 @table_name = table_name 
 from @table_list
end
go

The main points about the above script are:

  • STEP 1: create the oltp schema in the staging database (if it doesn't already exist); all synonyms will be created in this schema.  This is not a requirement; the intent is just to isolate the synonyms from the other database objects.

  • STEP 2: retrieve the schema_id for the oltp schema; it will be used later.

  • STEP 3: create a synonym for each table in the @table_list table variable.  Drop the synonym if it exists before creating it.  Note that if a particular linked server exists the synonym will specify a four part name else it will specify a three part name.

You can take a look at what synonyms have been created by querying the system view sys.synonyms.  Run the above script before creating the linked server, then use this query to check the synonyms created:

select 
  sc.name + '.' + sy.name as synonym_name 
, sy.base_object_name  
from sys.synonyms sy
join sys.schemas  sc on sc.schema_id = sy.schema_id
where sc.name = 'oltp'
go

You will see output like the following; note that the base object name reflects the three part name; i.e. the source database is on the same server as the staging database (assuming the source database name is oltp):

3partnames

Now create the linked server by running a script like the following:

declare @linked_server sysname
set @linked_server = N'[L00458\sql2008]'
exec master.dbo.sp_addlinkedserver 
  @server = @linked_server
, @srvproduct=N'SQL Server'
exec master.dbo.sp_addlinkedsrvlogin 
  @rmtsrvname = @linked_server
, @useself = N'True'
, @locallogin = NULL
, @rmtuser = NULL
, @rmtpassword = NULL

Run the script again to create the synonyms, then run the script to view the synonyms that were created; note the base object name is now the four part name; e.g.:

4partnames

After creating the synonyms, change all stored procedures to use the synonyms instead of the three part names.  If the source database is ever moved, create (or drop) the linked server, then run the T-SQL script to create the synonyms.  The script automatically drops the synonym if it exists before creating it.  This is done to enable switching between the three part and four part names (and vice versa).

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article

















get free sql tips
agree to terms