Synonyms | Hans Michiels – Blog and more

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.

Introduction

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.

Dv01E01_dbschema1
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.sql

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.

That is what we want!

In a picture:

Dv01E01_dbschema2

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

DV-S01E01-Create_synonyms_for_objects_in_a_different_database.sql

--\
---) 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
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
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.
GO

--\--------------------------------------------------------------------------
---) DECLARATIONS
--/--------------------------------------------------------------------------

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.

--\--------------------------------------------------------------------------
---) INITIALIZATION
--/--------------------------------------------------------------------------

IF NOT EXISTS(SELECT 1 FROM sys.schemas WHERE name = @SynonymsSchema)
BEGIN
    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;
END

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

DECLARE ExistingObjCursor CURSOR LOCAL STATIC FOR
    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

WHILE @@FETCH_STATUS = 0
BEGIN

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
END

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
END

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 */ '
+ ' , ''IF'' /* SQL_INLINE_TABLE_VALUED_FUNCTION */ '
+ ' , ''TF'' /* SQL_TABLE_VALUED_FUNCTION */ '
+ ' , ''U '' /* USER_TABLE */ '
+ ' , ''V '' /* VIEW */ '
+ ' )'
+ ' ORDER BY obj.type, sch.name, obj.name'

PRINT '/*'
EXECUTE sp_executesql @nsql;
PRINT '*/'
DECLARE ObjectCursor CURSOR LOCAL STATIC FOR
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

WHILE @@FETCH_STATUS = 0
BEGIN

    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
END

CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO

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

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.

Hans Michiels – Blog and more

Tag Archives: Synonyms

Free download: Blog Yearbook 2016

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