Database connection reconnection strategy

In any database connection pool, there is a risk of stale connections due to network outages or database server restarts. How do we refresh the connection pool without restarting the client application?

The standard technique used is to plug-in a validation query; that gets fired every time a connection is requested from the pool. This validation query is typically a default test query that does not result in any IO / disk access.  Examples of validation queries for different databases are given below:

• Oracle - select 1 from dual
• SQL Server - select 1 (tested on SQL-Server 9.0, 10.5 [2008])
• Postgresql - select 1
• hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
• derby - values 1
• H2 - select 1
• DB2 - select 1 from sysibm.sysdummy1
• Mysql - select 1

We were using Spring Boot that uses the Tomcat JDBC Connection Pool by default. We tried setting all the parameters required for the validation check as given here, but in vain. 

Finally we decided to go with HikariCP connection pool as suggested here. 

First, we added the dependency in our maven pom as shown below. 

Next we added the following properties in our application.properties file. We will pick up these properties to create our HikariCP connection. pool.
Finally we wrote a @Configuration bean to wire up the HirakiCP datasource as below:

Ruminating on Transaction Logs

Understanding the working of transaction logs for any RDBMS is very important for any application design. Found the following good articles that explain the important concepts in a simple language.

http://www.simple-talk.com/sql/learn-sql-server/managing-transaction-logs-in-sql-server/
http://www.techrepublic.com/article/understanding-the-importance-of-transaction-logs-in-sql-server/5173108

Any database consists of log files and data files. In the MS world, they are known as *.ldf and *.mdf files respectively. All database transactions (modifications) are first written to the log file. There is a separate thread (or bunch of threads) that writes from the buffer cache to the data file periodically.  Once data is written to a data-file, a checkpoint is written to the transaction log. This checkpoint is used as a reference to "roll forward" all transactions. i.e. All transactions after the last checkpoint are applied to the datafile when the server is restarted after a failure. This prevents transactions from being lost that were in the buffer but not yet written to the data file.

Transaction logs are required for rollback, log shipping, backup, etc. The transaction log files should be managed by a DBA or else we would run into problems if the log file fills up all the available hard disk space. The DBA should also periodically back-up the log files. The typical back-up commands also truncate the log files. In some databases, the truncation process just marks old records as inactive so they can be overwritten. In such cases, even after truncation, the size of the log file does not reduce and we may have to use different commands to compact or shrink the log files.

A good post on truncation options for SQL Server 2008 is given below:
http://www.codeproject.com/Articles/380879/About-transaction-log-and-its-truncation-in-SQL-Se

Peformance impact of TDE at database level

I was always under the opinion that column-level encryption is better and more performant than database or tablespace level encryption. But after much research and understanding the internal working on TDE (Transparent Data Encryption) on SQLServer and Oracle, it does not look to be a bad deal !

In fact, if we have a lot of columns that need to be encrypted and also need to fire queries against the encrypted columns, then a full database (tablespace) level encryption using TDE seems to be the best option.

I was a bit skeptical on the issue of performance degradation in using full database TDE, but it may not be so. First and foremost, column-level (cell) encryption can severely affect the database query optimization functions and result in significantly worse performance than encrypting the entire database.
When we use TDE at the database (tablespace) level, then the DB engine can use bulk encryption for entire blocks of data as they are written to or read from the disk.

It is important to note that full database TDE actually works at the data-file level and not at each table/column level. To put it in other words, the data is not encrypted but rather entire data files (index files, log files, etc.) are encrypted.

Microsoft states that the performance degradation of using database level TDE is a mere 3-6%.
Oracle states that in 11g, if we use Intel XEON processesor with AES instruction set, then there is a "near-zero" impact on database performance.

It is important to note the terminology differences regarding TDE used by Microsoft and Oracle. Microsoft refers to full database encryption as TDE (not column-level). Oracle calls it TDE-tablespace and TDE-column level.

Also TDE is a proven solution from a regulatory perspective - e.g. PCI. Auditors are more comfortable approving a proven industry solution that any custom logic that is implemented in application code.

Beware of using Hibernate 'increment' id generator

While reviewing one of the development projects using Hibernate as the ORM tool, I noticed that they were using the "increment" ID generation feature.

The way the implementation of this feature works is that it checks the highest value of the ID in the database and then increments it by one. But this approach fails miserable in a clusterned environment, because many threads might be writing to database concurrently and there is no locking.

It is best to stick to database specific features for auto-id generations, e.g. sequences in SQLServer 2012, Oracle 11g and identity columns in old versions of SQL Server.

How does Oracle RAC manage locks?

Typically databases store locks in-memory and hence it is challenging to load-balance or cluster 'write' operations to a shared database across multiple database instances.
For e.g. In my previous blog, I mentioned how SQL Server does not have any Oracle RAC equivalent.

So how does Oracle RAC managed to maintain locks & data consistency across multiple nodes of the RAC cluster?

The secret sauce is the high speed private network called the "interconnect". RAC has something called as "Cache Fusion Mechanism" that allows for inter-instance block transfers and cache consistency. The global cache services (GCS) is a set of processes that ensures that only one instance modifies the block at any given time. GCS also flags in-memory blocks as "invalid" whenever the blocks are changed in other nodes.

The following links have a good explanation on the RAC consistency mechanism:
http://www.rampant-books.com/art_rac_global_block_management.htm

http://www.rampant-books.com/art_burleson_rac_cache_coherency.htm

Connection Strings Reference.

Found this cool site containing a consolidated listing of "connection-strings" that can be applied to most of the databases out there. Quite valuable for quick reference :)

http://www.connectionstrings.com/

Estimating the size of SQL Server Database

MSDN has a very good link that explains in simple language on how we can estimate the size of the database. The size of the database would depend on the size of the tables and the indexes. The below link gives us easy formulas that can be used to calculate the database size.
http://msdn.microsoft.com/en-us/library/ms187445

A good samaritan also created excel templates that use these formulas for database sizing.

On Oracle, you can use PL/SQL SPROCs to calculate the table and index sizes. For e.g. you have the CREATE_TABLE_COST procedure of the DBMS_SPACE package that can be used to find out the bytes required for a table. You just have to input the potential number of rows expected.

HSQL in-memory database

Recently, we were experimenting with the HSQL in-memory database for a particular usecase. It was interesting to observe the default behaviour of persisting the database during a shutdown - The entire database was saved as a SQL script file ! When the server starts again, it loads the SQL script and fires all the CREATE TABLES and INSERT statements to recreate the database in memory.

Wikipedia gives a good overview of this HSQL feature and compares the default memory tables with cached tables. Snippet:

The default MEMORY type stores all data changes to the disk in the form of a SQL script. During engine start up, these commands are executed and data is reconstructed into the memory. While this behavior is not suitable for very large tables, it provides highly regarded performance benefits and is easy to debug.

Another table type is CACHED, which allows one to store gigabytes of data, at the cost of the slower performance. HSQLDB engine loads them only partially and synchronizes the data to the disk on transaction commits. 

I was a bit concerned about the viability of all-in-memory tables for large datasets, but it looks like HSQL is being actively used in projects where millions of rows are stored in memory. The only limitation is that of the Java Heap size that can be configured to be very large on a 64-bit machine.

It is possible to convert from memory tables to cached tables. You need to shutdown the database first. Then edit the .script file and modify the line "CREATE MEMORY TABLE" to "CREATE CACHED TABLE".

Snippet from the FAQ page:

If only memory tables (CREATE TABLE or CREATE MEMORY TABLE) are used then the database is limited by the memory. A minimum of about 100 bytes plus the actual data size are required for each row. If you use CREATE CACHED TABLE, then the size of the table is not limited by the memory beyond a certain minimum size. The data and indexes of cached tables are saved to disk. With text tables, indexes are memory resident but the data is cached to disk.


The current (2.0) size limit of an HSQLDB database is 16GB (by default) for all CACHED tables and 2GB for each TEXT table. If you use large MEMORY tables, memory is only limited by the allocated JVM memory, which can be several GB on modern machines and 64bit operating systems.

The statements that make up the database are saved in the *.script file (mostly CREATE statements and INSERT statements for memory tables). Only the data of cached tables (CREATE CACHED TABLE) is stored in the *.data file. Also all data manipulation operations are stored in the *.log file (mostly DELETE/INSERT) for crash recovery. When the SHUTDOWN or CHECKPOINT command is issued to a database, then the *.script file is re-created and becomes up-to-date. The .log file is deleted. When the database is restarted, all statements of the *.script file are executed first and new statements are appended to the .log file as the database is used. A popular use of HSQLDB is for OLAP, ETL, and data mining applications where huge Java memory allocations are used to hold millions of rows of data in memory.

One limitation of HSQLDB is that it currently does not support server side cursors. (This allows it to run without any writeable media). This means the result of a query must always fit in memory, otherwise an OutOfMemory error occurs. In the rare situation that a huge resultsets must be processed, then the following workaround can be used: Limit the ResultSet using Statement.setMaxRows(1024), and select multiple 'smaller' blocks. If the table is for example 'CREATE TABLE Test(Id INT IDENTITY PRIMARY KEY, Name VARCHAR)' then the first block can be selected using 'SELECT * FROM Test'. The biggest ID should be recorded and the next block should be selected using 'SELECT * FROM Test WHERE Id>(biggest_id)' until no more records are returned. Don't forget to switch off the limit using setMaxRows(0).

Object Model or Data Model - What comes first?

Throughout my career, I have asked this question multiple times to myself - what to model first? The database entities or the object model? Start with ER diagrams (conceptual, logical) or UML class diagrams.

Well, the answer is that - it depends. Many a times, it also depends on the culture of the organization. In many organizations, the 'data' teams are more strong and powerful and insist on ER modeling first. It is possible to run both these work-streams in parallel, as there is a lot of conceptual common-ness during the initial domain modeling. What bugs me is the differences in the semantics of UML and ER. Does not make sense to do both at the conceptual level.

Now over the past few years, there have been a plethora of ORM tools that bridge the object-relational impedance mismatch. These ORM tools have a host of features that allow us to just work with the object model and abstract away the creation of the data model.
For e.g. Entity Framework 4.0 has full support for 'code-first' approach that is detailed in this article.
I simply loved the 'convention-over-configuration' approach in EF 4.0. These features enable us to only work with the object model and not worry about the data schema at all. Such features would suffice for 70-80% of business cases, I believe. 

A snippet from the article will give the reader an idea of the abstraction that is provided -

In addition to supporting a designer-based development workflow, EF4 also enables a more code-centric option which we call “code first development”. Code-First Development enables a pretty sweet development workflow. It enables you to: 

• Develop without ever having to open a designer or define an XML mapping file 
• Define your model objects by simply writing “plain old classes” with no base classes required 
• Use a “convention over configuration” approach that enables database persistence without explicitly configuring anything 
• Optionally override the convention-based persistence and use a fluent code API to fully customize the persistence mapping

Byte code instrumentation and the ORM magic

All ORM tools use some kind of byte-code instumentation to do the persistance magic behind the scenes. But as an architect, it is important to understand what Hibernate or any JPA tool does to the entity classes?

Hibernate 'enhances' entity classes at runtime using a byte-code library called Javaassist. For e.g. it adds a '_dirty' flag to each field. It also adds a '_loaded' flag for each field to support lazy loading. A good blog explaining these concepts is here. So Hibernate reads the XML configuration or obtains annotations at runtime using reflection to apply byte-code instumentation.

There are various ways of doing byte-code instumentation using libraries such as CGLib, ASM, Javaassist, etc.
This byte-code enhancement can be done at compile-time or run-time. For Hibernate, besides a few special cases which require compile time 'enhancement' to byte-code; all common scenarios can be satisfied with runtime instrumentation.

The following link gives a good overview of all the enhancement options available in JPA.
http://openjpa.apache.org/builds/1.2.1/apache-openjpa-1.2.1/docs/manual/ref_guide_pc_enhance.html

In the .NET world, NHibernate uses the Linfu or Castle Dynamic Proxies byte-code enhancement providers. 
http://nhforge.org/blogs/nhibernate/archive/2008/11/09/nh2-1-0-bytecode-providers.aspx

JavaDB (Derby) in JDK 1.6 and above

JDK 1.6 and above ship with a default pure Java database called as "JavaDB". Is is based on the open source Apache Derby project.

By default, on a Windows platform JavaDB gets installed at "C:\Program Files\Sun\JavaDB".
Set the 'DERBY_HOME' system property to this path. Also put 'DERBY_HOME/bin' in the PATH property.

There is a good tutorial here that should get you up and running with JavaDB in 10-15 mins :)

Derby does not have a default GUI admin tool, but one can use many third-party tools such as SQuirrel and others. I think JavaDB provides a good alternative to MySQL for some scenarios.

RAID basics

Found this good blog that explains in simple terms, the various levels of RAID (Redundant Array of Independent Disks).

RAID 10 has become the defacto standard for relational databases, due to the excellent redundancy and performance given by them.  In RAID 10 (also known as 1+0), blocks are mirrored and also striped. 

Operational Reports vs MIS Reports

Once organizations create a data warehouse, a lot of people push all the reporting needs to the DW. But do all reports need to run from a DW?
The answer lies in understanding the difference between operational and informational (MIS) reports. A good article by Bill Inmon on this difference can be found here.

Operational reports are typically detail oriented and shows the latest up-to-date records. Operational reports are used by stakeholders for short term tactical decision making. MIS reports look at summary data over a longer time horizon and are used for strategic decision making.

Examples of operational reporting include bank teller end-of-day window balancing reports, daily account audits and adjustments, daily production records, flight-by-flight traveler logs and transaction logs.

Examples of informational reporting include monthly sales trends, annual revenue, regional sales by product line for the quarter, industry production figures for the year, number of employees by quarter and weekly shipping costs by carrier.

Datawarehouse vs Datamart

The debate between creating a datamart or sourcing data from the datawarehouse springs up from time to time across organizations. Found some good links on the web on this great debate:
information-management
exforsys.com
opensourceanalytics.com

Referential Integrity across databases in SQL Server

Having worked on Oracle databases for a long time, I was quite comfortable with the idea of maintaining RI across different schemas in same database. For e.g. Ur database may have a schema containing master tables and your transaction schema references the lookup data in the master tables.
But to my suprise, prior to SQLServer 2005, it was not possible to have separate schemas in SQL Server. This meant that there was no easy way to maintain RI across databases in SQLServer. The only options were using messy triggers or a CHECK constraint with a UDF (user defined function)

Maintaining RI across databases is still not possible in SQLServer, but SQLServer 2005/2008 have added the concept of schemas in databases; i.e. it is possible to have multiple schemas in each database and each schema offers the same logical separation that a separate database would. We can also configure the schema to be located on a separate filegroup or a separate disk, thus maximizing performance. In SQLServer 2005/2008, it is possible to have RI constraints across schemas. I think this is the best design approach to take. If it is not possible to have your master data in the same database, then the second best approach is to use replication to get the master data into your database.

What is longitudnal data?

A dataset is considered to be longitudnal if it tracks the same kind of information at multiple points of time. For e.g. the marks of students over multiple years, patient health records over a period of time, etc.

The most important advantage of longitudnal data is that we can measure change and the effect of various factors over the data-point time values. For e.g. what is the effect a particular drug had on a cancer patient? The effect of different teachers on a student?

So essentially, longitudnal data helps in establishing cause-n-effect relationships. Longitudnal data stores are also being used for predictive modeling and other areas. Longitudnal data stores are very popular in the Life Sciences and Healthcare industry.
I am interesting in learning the best practices for creating and optimizing a data-model for longitudnal data stores.

Updatable views using the 'INSTEAD OF' trigger

All views are not updatable in a database - especially if the underlying query is a complex one. This article gives interesting examples of the challenges faced in updating views.

But latest versions of databases, support a INSTEAD OF trigger, that enables us to make all views updatable. INSTEAD OF triggers fire in place of the triggering action.

The links below show how the 'INSTEAD OF' trigger can be used in different databases:

Oracle: http://www.psoug.org/reference/instead_of_trigger.html

DB: http://www-128.ibm.com/developerworks/db2/library/

techarticle/0210rielau/0210rielau.html

SQLServer: http://msdn.microsoft.com/en-us/magazine/cc164047.aspx