Use Synonyms to Abstract the Location of SQL Server Database Objects

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

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *