Posts From June, 2013

Where is my database? 

I have been working on the DBTD_ASSERT_DB_EXISTS assert for SQL Server lately, to keep functionality compatible we have ventured in to creating compatible logic for Netezza and Oracle, and that is where database engine architecture pushed us in to rough territory.

Because the question is: Where is my database?


The cave paintings are probably the earliest created by human databases. Back then data were stored data on the media called – the “Rock”. There were a lot of rocks to store a data on, the indigenous pupil would say “let’s go draw something on Our Rock” and his fearsome friend will reply “How about Cave by the river”. Even back in those times, databases had names known to everyone in a tribe. In nova days we have quite improved our data storage facilities and data delivery systems, but in general the “database” term still means a “collection of data” and as long as you are able to connect to it (and get data from there) everyone is fine. Even if you venture into ANSI SQL stands book for Structured Query Language you will not find set-in-stone definition of the “the Database” concept as container of schemas or object.

Putting people aside (because it is clear for us what database is) let’s look on how different product manufactures use this term:


Oracle Architecture

From very simplified architectural point have Oracle following necessary components:

  • Host – a hardware or virtualized computer (server);
  • Oracle Database Instance or just Oracle database. Each instance can be split in to following two parts:
    • The Oracle Instance – is the set or application and processes to manage the database, it is identified by individual SID (or Oracle System ID)
      Multiple instances can be installed on an individual host
    • The Oracle Database - is identified by Global Database Name and represents a unique collection of files and settings that define characters set, security, connectivity, memory allocation and disk usage, and many other parameters that eventually control how different database objects (tables, views, indexes, packages etc.) are created, used and accessed.

A few extra important comments:

  • Multiple instances of oracle can be installed on one host, as long as there is enough resources on the host
  • In general there is one database per Oracle instance
  • Database has its own schemas, users, tables, and many other database objects
  • Permission managed on the database/instance level
  • Future Oracle products claim to support multi database per single host configurations, so it is coming to the market
  • Many oracle developers might never even use CREATE DATABASE statement, and there is a good set of reasons for it


SQL Server

SQL Server Architecture

In the SQL Server world when you have installed SQL Server – you got yourself environment to manage as many databases as you need. Here are a few SQL Server components:

  • Host - a hardware or virtualized computer (server)
  • Database Engine Instance – is the set of programs and services that manage a system databases and one or more user databases. Each instance would have following components:
    • SQL Server Engine Services – set of core programs and services;
    • Built In System Databases: Master, Model, MSDB, TempDB – used internally by SQL Server Engine Instance;
    • User Databases – user defined databases.

A few important comments:

  • One host can run multiple SQL Server Engine Instances, as long as there is enough resources on the host
  • There are many databases per SQL Server Engine Instances
  • Each Database has its own schemas, users, tables, and many other database objects
  • Permission managed on the database level


Netezza Architecture

When you work with Netezza – you have got yourself appliance to manage databases.

Here are the few components of Netezza appliance:

  • Factory configured Netezza data warehouse appliance – the hardware and software
  • Set of built in super users to manage hardware, software and databases
  • Databases

A few important comments:

  • Like refrigerator to store groceries, Netezza would store databases. Appliance has been architected in a way to compliment hardware and database engine software to get the better of two words
  • There could be many databases created per appliance, as long as there are enough resources
  • Each Database has its own tables, views, procedures and functions, sequences, and libraries
  • Permissions are managed on the appliance level
  • There are no individual users in the database


What about our question now?

Assuming DBTD_ASSERT_DB_EXISTS assert stored procedure should let us know if database with the given name exists or not we are running in to bit of issue here with the question itself.

While questioning existence of the database in Netezza and SQL Server can provide us with meaningful answer. In the Oracle, however, such a question will have a little sense. Hopefully not for long, or at list until we will get to the future Oracle versions that will support so called CDB  or “Container Database” and PDB or “Pluggable Database”.

For now Oracle developers rather would want to check: DB_NAME, DB_UNIQUE NAME, HOST, INSTANCE, or INSTANCE_NAME along other specific database identification parameters.

Reflection on the History

Caveman DBA 1:    “Let’s go and draw a new record on that “Rock-by-the-river”
Caveman DBA 2:    “Hm… Sure …”
Caveman DBA  3:   "What river?” 


Alex Podlesny


Friday, June 28, 2013 11:14:00 AM

Dependency Injection pattern in database development 

To get yourself familiar with dependency injection
pattern please check Wikipedia link here:

The primary database focus is to persist data and guaranty data integrity… well, at list we can say it about many databases. As a result database development evolved in its own procedural-way, and it is hard to argue against such a way or to change it, because in most cased database designs have tendency to harden, solidify, and resist a change after they get built. Besides, who want a change when over the years masses of applications got built on that-old-table. Just try to change it and wait for something to fail…
Whatever reason you have for a change try the Dependency Injection pattern. This pattern injects the “depended-on” code to the destination code automatically by knowing the requirement of the destination code and altering an expected outcome of the destination code. Let’s take very popular BOO and FOO templates for our example:

   IF dbo.FOO() < dbo.FOOTheshold()
      EXEC BOO 'Have to do BOO logic';
      ELSE MOO 'Have to do MOO logic, instead';

Here is how to introduce dependency injection into our code:

  1. Create following two schemas: INTERFACE, PROD_IMPLEMENTATION


  1. Move actual functions and procedures with business logic to the PROD_IMPLEMENTATION schema, like this one:

  @Message VARCHAR(50)
   PRINT 'Very complicated proprietary logic';

  1. Create synonyms in the INTERFACE schema that will point to the functions and stored procedures in the PROD_IMPLEMENTATION schema. Note that synonyms can have the same names, because they are located in the different schemas than actual business logic. For instance:


  1. Then change dbo.sp_BOO_manager stored procedure to use synonyms from the INTERFACE schema instead of objects themselves. Here is what you will get:

      EXEC INTERFACE.BOO 'Have to do BOO logic';
      ELSE INTERFACE.MOO 'Have to do MOO logic';

Now our sample code does not bounds to the actual business logic directly, instead it calls an abstracted logic through an interface (which we can override when needed), allowing the calling process to inject desired functionality.
Let’s do it…
Make a new object somewhere in another database:

   @Message VARCHAR(50)
   DECLARE @OverrideName VARCHAR(100)
   PRINT 'Very simple log message';
      (OverrideName, [Message], EventDate)
      VALUES (@OverrideName,@TmpMessage, GETDATE());

Then (on your development server) change BOO synonym to point to the mocked object:


After this change, you can call business logic and verify that expected message got logged in to a test table.

--call logic
EXEC dbo.sp_BOO_manager;
--assert result
   @v_TableName = 'TEST_MESSAGES',
   @v_ColumnName = 'OverrideName',
   @v_ValueDataType = 'VARCHAR(100)',
   @v_UserMessage = 'BOO functionality should run';

There are a few ways to introduce dependency injection pattern in databases, we have chosen SYNONYMS, because of the following benefit:

  • Synonyms are very simple database objects;
  • Synonym does not change actual business logic they represents;
  • Synonym provides less invasive way to perform injection;
  • When creating synonym developer only need the name of the synonym (already known from consumer side) and the name of the object it represents (which is also known by developer from a provider side );
  • Locating synonyms and business logic in the different schemas allows synonym to have the same name as the object it abstracts, this preserve an existing naming convention used by development team;
  • Synonym can be created on the following objects:
    CLR Stored Procedures, Table-valued Functions, Scalar Functions, and Aggregate Functions, SQL Stored Procedure, Replication-filter-procedures, Extended Stored Procedures, SQL Scalar Functions, SQL Table-valued Functions, SQL Inline-table-valued Function, Views, User-defined Tables, includes local and global temporary tables

Well, here you go, now you have one more tool in your development toolbox. Use injection wisely, make sure you have basic infrastructure tests to go with it, and don’t forget about security.

Thursday, June 6, 2013 2:55:00 PM
  • RSS
  • Add To My MSN
  • Add To Windows Live
  • Add To My Yahoo
  • Add To Google


  • Entries (13)
  • Comments (0)