By: Ray Barley | 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):
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.:
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
- Take a look at our earlier tips on synonyms for additional details: How and why should I use SQL Server 2005 synonyms and Benefits and limitations of using synonyms in SQL Server 2005.
- Accessing objects in a different database is a good example of where synonyms can really come in handy.
About the author
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
