Tag Archives: Synonyms

Free download: Blog Yearbook 2016

I have made a compilation of all my blog posts of the past year, and it was more work than you would think.
Especially because it also contains a non-published new article.

After filling in the form you can download the e-book for free.

    I promise you the following:

    • I will not give or sell your email address to any third party.
    • I will not spam you.
    • If I start with a newsletter, you can easily unsubscribe or change the frequency.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

    Isolate cross database “databasename” dependencies using synonyms and schemas (Data Vault Series)

    Intended audience

    This post could be interesting for professionals working on datawarehouse projects using the Microsoft SQL Server database engine. This post assumes that you have at least a basic understanding of Data Vault principles.


    Those who have read the book “Building a Scalable Data Warehouse with Data Vault 2.0” by Dan Linstedt and Michael Olschimke will know that database schema’s are used in the examples in the book for the “raw” and “biz” (business) layer in the Data Vault.

    If you do not know this book, buy it, if you are the intended audience you should read it ( no I have no commercial interest in saying this), so check it out on Amazon.

    What is done in the book is putting tables or views in the database schema’s “raw” and “biz”, respectively, making it transparent if a satellite is either virtual (and at least partially computed) or persisted in a physical table.

    Putting these data layers in different schema’s is a great starting point for a my interdatabase connectivity concept.

    What data layers can we have

    • Staging area
    • Raw Data Vault
    • Business (Data) Vault
    • Metrics Vault
    • Error Vault
    • Information Marts

    A rule of thumb is that each layer resides in it’s own database, but also multiple layers can be in one database. What should be avoided is spreading one layer over multiple databases. This does not count for different Information Marts though, which should be put in their own database.

    Whether to put layers together can depend amongst others on backup requirements (the biz layer could for instance be restored from a database backup OR by doing some reprocessing from the Raw Data Vault, which could lead to the decision to backup the database less frequently).

    A typical set up could be:

    • Staging Area – separate database, “stg” database schema.
    • Raw Data Vault – together with Business Vault, “raw” database schema.
    • Business Vault – together with Raw Vault, “biz” database schema.

    To keep the example simple I will leave the other data layers out for now.

    What is the problem?

    To be short: SQL statements copying data to a different database.

    When using data flows in SSIS, this problem does not occur.

    Why is it bad?

    Database names are prefixed in FROM clauses, causing a dependency on the database name.

    So what, I still don’t see the problem

    Scenario’s where it can become a hassle to manage this include:

    • Running different environments on the same database instance (e.g. development and test), differing the environments by database names. When deploying code or copying databases, all interdatabase dependencies must be changed.
    • Running different enviroments for multiple clients in a BIAAS (Business Intelligence As A Service) solution on the same database instance.

    So, lets divide datalayers using database schema’s ..

    This is an example of the initial set up.

    Please note that you should keep all objects (tables, views, stored procedures, udf’s) from one data layer together in one database.

    Now, to pull data from the “stg” to the “raw” layer, SQL statements could be like (simplified example):

    INSERT INTO [raw].[TableName]
    SELECT ..
    FROM [EDW_STG_TEST].[stg].[TableName]

    But, as you can see the database name is prefixed before the Staging table. And this is a problem (at least it makes managing the solution more complex).

    How can we solve this?

    Managing interdatabase dependencies using synonyms

    First, a little explanation from MSDN:

    A synonym is a database object that serves the following purposes:

    • Provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
    • Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

    Read more on MSDN.

    What if we would have made a synonym for the staging table in the “EDW_DV_TEST” database, also in a “stg” schema in that database?

    Then we could omit the database name from the SQL statement:

    INSERT INTO [raw].[TableName]
    SELECT ..
    FROM [stg].[TableName]
    That is what we want!

    In a picture:


    The good news is, creating the synonyms can be automated with a script, so a change of the Staging database name only requires running a SQL script in the EDW_DV_TEST to drop and recreate the synonyms for the staging database objects!

    One other advantage is, you could merge different layers into one database or spit them into different databases. For example all code will keep on working if you move all Staging tables to the EDW_DV_TEST database, therewith replacing the synonyms already there in the stg schema by actual tables. All you would have to do is change the connection string for the processes that fill the staging tables.

    In a similar way, you could decide to separate the raw and biz layer into different databases.

    Script to create or change the synonyms


    ---) Author: Hans Michiels
    ---) Creates synonyms for tables, views, user defined functions
    ---) and stored procedures in a different database.
    ---) All synonyms in the specified schema are dropped
    ---) prior to (re)creation.
    (c) Copyright 2016 - hansmichiels.com
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    GNU General Public License for more details.
    You should have received a copy of the GNU General Public License
    along with this program. If not, see http://www.gnu.org/licenses/.

    USE [AdventureWorks2014] -- Replace with database you want to create the synonyms in.


    DECLARE @SourceDatabase SYSNAME
    DECLARE @SourceSchema SYSNAME
    DECLARE @SynonymsSchema SYSNAME

    DECLARE @SynonymsPrefix NVARCHAR(128)
    DECLARE @SynonymName SYSNAME

    DECLARE @CurSchemaName SYSNAME
    DECLARE @CurObjectName SYSNAME
    DECLARE @CurObjectType CHAR(2)

    DECLARE @nsql NVARCHAR(4000)
    DECLARE @DebugPrint NVARCHAR(500)
    DECLARE @SimulationMode CHAR(1)

    ---) CONFIGURATION: adjust the variable values to meet your needs.

    SELECT @SourceDatabase = 'AW2014_Staging' -- Database in which objects exist
                                              -- for which synonyms must be created.
         , @SourceSchema = 'stg' -- Schema name of source objects.
         , @SynonymsSchema = 'stg' -- Schema name to put synonyms in.
         , @SynonymsPrefix = '' -- Optional Synonyms name prefix.
         , @SimulationMode = 'Y' -- Use Y if you only want the SQL statement in the
                                   -- output window without it being executed.


    IF NOT EXISTS(SELECT 1 FROM sys.schemas WHERE name = @SynonymsSchema)
        SELECT @nsql ='CREATE SCHEMA ' + QUOTENAME(@SynonymsSchema) + ' AUTHORIZATION [dbo];';
        IF @SimulationMode = 'Y' PRINT '----- SIMULATION MODE: SCRIPT IS NOT EXECUTED ------ ';
        PRINT @nsql;
        IF @SimulationMode != 'Y' EXECUTE sp_executesql @nsql;

    ---) MAIN PART I: Drop all existing synonyms in the specified schema.

        SELECT syn.name AS [SynonymName]
        FROM sys.synonyms syn
        JOIN sys.schemas sch
          ON syn.schema_id = sch.schema_id
        WHERE sch.name = @SynonymsSchema
    IF @SimulationMode = 'Y' PRINT '----- SIMULATION MODE: SCRIPT IS NOT EXECUTED ------ ';

    SET @DebugPrint = '-- Dropping all existing synonyms in schema ' + QUOTENAME(@SynonymsSchema)
    PRINT REPLICATE('-', LEN(@DebugPrint))
    PRINT @DebugPrint
    PRINT REPLICATE('-', LEN(@DebugPrint))

    OPEN ExistingObjCursor
    FETCH NEXT FROM ExistingObjCursor INTO @SynonymName


        SET @nsql = 'IF OBJECT_ID(''' + QUOTENAME(@SynonymsSchema) + '.' + QUOTENAME(@SynonymName) + ''', ''SN'') IS NOT NULL'
              + ' DROP SYNONYM ' + QUOTENAME(@SynonymsSchema) + '.' + QUOTENAME(@SynonymName) + ';'

        PRINT @nsql;
        IF @SimulationMode != 'Y' EXECUTE sp_executesql @nsql;

        FETCH NEXT FROM ExistingObjCursor INTO @SynonymName

    CLOSE ExistingObjCursor
    DEALLOCATE ExistingObjCursor

    ---) MAIN PART II: Recreate all synonyms in the specified schema.

    IF OBJECT_ID('tempdb..#SourceObjects') IS NOT NULL BEGIN
        DROP TABLE #SourceObjects

    CREATE TABLE #SourceObjects
        ( [SchemaName] SYSNAME NOT NULL
        , [ObjectName] SYSNAME NOT NULL
        , [ObjectType] CHAR(2) NOT NULL

    -- Insert USER objects
    SELECT @nsql =
    'INSERT INTO #SourceObjects([SchemaName], [ObjectName], [ObjectType])'
    + ' SELECT sch.name AS [SchemaName]'
    + ' , obj.name AS [ObjectName]'
    + ' , obj.type AS [ObjectType]'
    + ' FROM [' + @SourceDatabase + '].sys.objects obj'
    + ' JOIN [' + @SourceDatabase + '].sys.schemas sch'
    + ' ON sch.schema_id = obj.schema_id '
    + ' WHERE sch.name = ''' + @SourceSchema + ''''
    + ' AND obj.type IN ( '
    + ' ''FN'' /* SQL_SCALAR_FUNCTION */ '
    + ' , ''P '' /* SQL_STORED_PROCEDURE */ '
    + ' , ''TF'' /* SQL_TABLE_VALUED_FUNCTION */ '
    + ' , ''U '' /* USER_TABLE */ '
    + ' , ''V '' /* VIEW */ '
    + ' )'
    + ' ORDER BY obj.type, sch.name, obj.name'

    PRINT '/*'
    EXECUTE sp_executesql @nsql;
    PRINT '*/'
        SELECT [SchemaName], [ObjectName], [ObjectType]
        FROM #SourceObjects
    IF @SimulationMode = 'Y' PRINT '----- SIMULATION MODE: SCRIPT IS NOT EXECUTED ------ ';

    SET @DebugPrint = '-- Recreate synonyms in schema ' + QUOTENAME(@SynonymsSchema)
    PRINT REPLICATE('-', LEN(@DebugPrint))
    PRINT @DebugPrint
    PRINT REPLICATE('-', LEN(@DebugPrint))

    OPEN ObjectCursor
    FETCH NEXT FROM ObjectCursor INTO @CurSchemaName, @CurObjectName, @CurObjectType


        SELECT @SynonymName = @SynonymsPrefix + @CurObjectName
        SET @nsql = 'CREATE SYNONYM ' + QUOTENAME(@SynonymsSchema) + '.' + QUOTENAME(@SynonymName)
                  + ' FOR ' + QUOTENAME(@SourceDatabase) + '.' + QUOTENAME(@CurSchemaName) + '.' + QUOTENAME(@CurObjectName) + ';'

        PRINT @nsql;
        IF @SimulationMode != 'Y' EXECUTE sp_executesql @nsql;

        FETCH NEXT FROM ObjectCursor INTO @CurSchemaName, @CurObjectName, @CurObjectType

    CLOSE ObjectCursor
    DEALLOCATE ObjectCursor

    ---) CLEAN UP
    IF OBJECT_ID('tempdb..#SourceObjects') IS NOT NULL BEGIN
        DROP TABLE #SourceObjects

    Download the script here.

    Conclusion / wrap up

    Database synonyms can be used to isolate interdatabase dependencies into objects that can be easily updated with a changed database name.

    This makes managing the Data Vault solution easier.

    This concept can also be of use in non-Data Vault database environments.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.