Database alias in Microsoft SQL Server

by Bohumír Kubík

We have a scenario where more databases with the same schema are running on one SQL server instance (imagine the same application deployed for several customers with dedicated databases). We also needed to specify database name in stored procedures (in fact, we don’t have only one database per customer, but a pair of databases so that we have to specify the other database name).

The problem is that when you compare database schema among all of them, which is what you’ll be doing almost every time you need to make a change to the database, the compare tool will report differences in all procedures that specify database name.

For example, it treats with procedures like

SELECT * FROM Customer1_SecondDB.dbo.Table1;

and

SELECT * FROM Customer2_SecondDB.dbo.Table1;

as different. But we want to find differences only in the procedure’s logic.

Since SQL Server does not support database aliases, there are two possibilities:

1. Give up, and take care of changes by hand (at least make sure the database name is not changed by automatically generated change scripts). This is possible for two or three customers but will cost you a week to synchronize database for a dozen.

2. Make use of Synonyms. Although synonyms cannot be created for databases directly, we can still use it. The idea is that we create a synonym for every object in the database and then stored procedures will refer to those synonyms instead of fully qualified object names.

In Customer1_FirstDB we create synonym:

USE Customer1_FirstDB;
CREATE SYNONYM dbo.SecondDB_Table1 FOR Customer1_SecondDB.dbo.Table1

In Customer2_FirstDB we create synonym:

USE Customer2_FirstDB;
CREATE SYNONYM dbo.SecondDB_Table1 FOR Customer2_SecondDB.dbo.Table1;

The result is that in every database (Customer1_FirstDB, Customer2_FirstDB), procedures look like

SELECT * FROM dbo.SecondDB_Table1;

It is then very easy to find differences in schema among databases and also to create change scripts that will be able to upgrade all databases at once.

Creating a synonym for every object in the database might not seem to save a lot of time, but we need to make it only once. Moreover, you can generate the synonyms automatically.

I generate synonyms in next 3 steps. If you have more than 2 databases, you can modify it easy.

Table DBSynonym for storing a list of tables and views, that i want to create.

USE FirstDB
GO
CREATE SCHEMA [Admin]
GO
CREATE TABLE [Admin].[DBSynonym](
    [SynonymName] [nvarchar](100) NOT NULL,
    [SchemaTable] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_DBSynonym] PRIMARY KEY CLUSTERED 
(
    [SynonymName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I use this script for easier filling table:

INSERT INTO FirstDB.Admin.DBSynonym (
    SynonymName,
    SchemaTable
)
SELECT 
    '[' + TABLE_NAME + ']', 
    '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM SecondDB.INFORMATION_SCHEMA.TABLES

Stored procedure for generating synonyms:

CREATE PROCEDURE [Admin].[SetSynonyms]
AS
BEGIN
    DECLARE @SynonymName nvarchar(100)
    DECLARE @SchemaTable nvarchar(100)
    DECLARE @Command nvarchar(MAX)
 
    DECLARE @CommandPattern nvarchar(1000) = 
'IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = REPLACE(REPLACE(''%%SYNONYM_NAME%%'', ''['', ''''), '']'', ''''))
DROP SYNONYM SecondDBSchema.%%SYNONYM_NAME%%
 
CREATE SYNONYM SecondDBSchema.%%SYNONYM_NAME%% FOR ' + 'SecondDB' + '.%%SCHEMA_TABLE%%'
 
    DECLARE cmd_cursor CURSOR FAST_FORWARD
    FOR
    SELECT SynonymName, SchemaTable
    FROM Admin.DBSynonym
 
    OPEN cmd_cursor
    FETCH NEXT FROM cmd_cursor INTO @SynonymName, @SchemaTable
 
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @Command = REPLACE(REPLACE(@CommandPattern, '%%SYNONYM_NAME%%', @SynonymName), '%%SCHEMA_TABLE%%', @SchemaTable)
        EXEC(@Command)
 
        FETCH NEXT FROM cmd_cursor INTO @SynonymName, @SchemaTable
    END
 
    CLOSE cmd_cursor
    DEALLOCATE cmd_cursor
END

Tags: Sql Server

1 Comment

  • Arshpreet said

    Thanks for the article, You can use this simpler query to generate a script for creating synonyms for each objects in a database to a new database with same names etc..

    SELECT 'CREATE SYNONYM '+QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME([name])+' FOR ' +
    QUOTENAME(DB_NAME()) + '.' +
    QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' +
    QUOTENAME([name])
    FROM [sys].[objects]
    WHERE [type] in('P','U','V','TF','FN')

Add a Comment