A talk given by Kai Voigt at Linuxtag in Wiesbaden, based on LDAP vs. SQL and additional experience we had, based on our work with directory services.

SQL vs. LDAP

Both SQL and LDAP are infrastructures to store, retrieve and modify data. Various implementations are available, either free or commercial software.

This talk will introduce SQL and LDAP, explain what they have in common, where they are different and how they should be used. The most popular implementations will be listed, and which of them make most sense in different environments.

SQL and LDAP are based on different data structures. SQL is a relational data model, while LDAP is a hierarchical model. Both will be looked at in detail.

Examples from the real world illustrate when SQL or LDAP should be used. Sometimes it’s best to just use one system, in some cases a mixture of both makes sense.

Also, common mistakes will be demonstrated when people use the wrong system, either by lack of knowlegde or by following some hype.

Kai Voigt

Kai Voigt works as an Instructor and Consultant for MySQL and used to implement LDAP based systems and applications in former engagements. He was invited as a speaker at various conferences, including Roxen User Conference and Open Source Database Conference.

Goals

• Understand different data structures
• Know about operations on data
• Know common LDAP problems
• Know LDAP benefits
• Make the right decision

Implementations

• SQL

  • MySQL
  • PostgreSQL
  • Oracle
  • MS SQL
  • DB2

• LDAP

  • OpenLDAP
  • SUN
  • MS Active Directory
  • Netscape

Data Structures

SQL Structures

• Catalogs, Databases, Tabkles, Rows, Columns
• Columns hold single values (or NULL)
• Columns have scalar data types
• Indexes on table level
• Foreign key constraints between tables

• Foreign Keys, Constraints

LDAP Structures

• Data Objects in Hierarchy
• Attributes and data types defined by Objectclasses (may/must exist)
• Primary Key: Distinguished Name
• Indexing on data type
• No foreign key constraints

• LDAP tree, dn

An LDAP node

• DN: city=1,cont=asia,o=myworld
• Set of attributes
  • Name=Tokyo
  • Population=18.000.000
  • Language=Japanese
  • Langiage=Chinese
  • Country=Japan

Hierarchies in SQL

• Storing
  • Primary Key of parent as column
• Accessing
  • Joining the table to itself
  • Just static depth

Data sizes

• SQL
  • Larger data, higher latency
• LDAP
  • Smaller data, lower latency

Operations

SQL Operations

• Selection: WHERE clauses
• Projection: SELECT plus Columns List
• Aggregation: GROUP BY
• Combination: INNER/OUTER JOIN
  • and Rename for Self-Join

LDAP Operations

• Selection on attribute values
• Projection on attributes (no synthetic values
• Aggregation: not possible
• Combination: not possible)

SQL Usage

• Storage for large scale data

• Business Logic

• Conclusion: complex and concurrent queries

LDAP Usage

• Authentication

• Configuration Files

• Address Books

• Conclusion: Many trivial quick read requests

Common Problems

Top 5

• Using only LDAP
• Using LDAP for massive write requests
• Changing LDAP structure
• Using LDAP for complex queries
• Bad data design in LDAP

Using LDAP only

• Data Types, Objectclasses
  • Need to be extended in most cases
  • Server restart required
• No referential integrity
• No relations between
• DNs unstable

Write Requests

• No transactions
  • “add” and “modify” are atomic
• No locking
• No bulk updates
• No scalable replication

Changing Structure

• Schema definition out-of-band
• MAY inflation
• No introspection

Complex Queries

• DN is primary key
  • DN contains structural information
  • DN is unstable
    • Moving an Object kills you
• No SQL Joins

Data Design

• Organisational hierarchies into LDAP trees
• DN not opaque
• Large data in LDAP
• Multiple Value Attributes

Other Problems

• No simple server-side functions
• No normalization
• No powerful SQL features
  • Stored Routines, Views, Triggers

LDAP Benefits

Speed

• Low Connection Overhead
• Simple Queries
• Common Operations
  • Existence Testing
  • Single Attributes
  • Authentication

Compatibility

• Standards
  • Wire Protocol
  • Data Types
• Interoperability
  • e.g. Apple Mail Client + SUN LDAP Server

The right decision

No rule of thumb

• Analyze data structures and operations

• Run benchmarks

• Individual solution for each project

• Solution might be changed in the future

Best Practice

• SQL as leading system
• periodic or triggered exports to LDAP
• No writes to LDAP system by applications
• Backup required only on SQL system

Thank you!