Use Synonyms to Abstract the Location of SQL Server Database Objects
By: Ray Barley | Updated: 2009-08-20 | Comments | Related: More > Synonyms
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?
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.
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).
- 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.
Last Updated: 2009-08-20
About the author
View all my tips