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?

Architecture

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

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

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?” 

 

Thx
Alex Podlesny


 

Friday, June 28, 2013 11:14:00 AM
Comments are closed on this post.
  • RSS
  • Add To My MSN
  • Add To Windows Live
  • Add To My Yahoo
  • Add To Google

Statistics

  • Entries (13)
  • Comments (0)